SQL Updating Question

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 1115 Views - Last Post: 08 June 2012 - 04:53 AM Rate Topic: -----

#1 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

SQL Updating Question

Posted 05 June 2012 - 07:41 AM

What does this block of code mean, particularly the statements after the SET? And also the AddWithValue? Looked it up at MSDN, did not understand much.

           using (SqlCommand cmd =
                new SqlCommand("UPDATE EmployeeDetails SET Name=@NewName, Address=@NewAddress" +
                    " WHERE Id=@Id", conn))
            {
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Munna Hussain");
                cmd.Parameters.AddWithValue("@Address", "Kerala");

                int rows = cmd.ExecuteNonQuery();

                //rows number of record got updated
            }




Is This A Good Question/Topic? 0
  • +

Replies To: SQL Updating Question

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: SQL Updating Question

Posted 05 June 2012 - 07:47 AM

SET is a keyword in SQL that usually works with UPDATE. It tells SQL that the following statements are column names and the values you want said column names.

Quote

Name=@NewName

In that case column "Name" is being set to the parameter value of @NewName.

AddWithValue is a method that adds parameters to the command object. Like a variable for your SQL string. You give it a variable name, in one instance "@Name", and then a value that you want it to represent ("Munna Hussain").

using the 'add with value' is a good way to clean up your input so you are not open for sQL injection or just malformed sql statements that could cause your UPDATE to fail.

It leaves the checking and formatting to the underlying object and usually alerts you to something going wrong before it tries to execute.
Was This Post Helpful? 1
  • +
  • -

#3 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1525
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: SQL Updating Question

Posted 05 June 2012 - 07:47 AM

What does SET do in a SQL query?

AddWithValue

The code updates the database table EmployeeDetails.
Was This Post Helpful? 2
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: SQL Updating Question

Posted 05 June 2012 - 07:48 AM

Let me guess - trying to figure out some code from 2010?
Was This Post Helpful? 0
  • +
  • -

#5 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

Re: SQL Updating Question

Posted 05 June 2012 - 08:00 AM

View Postmodi123_1, on 05 June 2012 - 07:48 AM, said:

Let me guess - trying to figure out some code from 2010?


So let me get this straight. Name is the column name, @Name is like a variable for Name, then the second parameter is the value you want the column Name to have?

This post has been edited by znerihc: 05 June 2012 - 08:03 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: SQL Updating Question

Posted 05 June 2012 - 08:07 AM

Quote

So let me get this straight. Name is the column name,

Yes.. in table "EmployeeDetails" there should be a column called "Name". We know this because SQL update statements are structured as <column name>=<value>.



Quote

@Name is like a variable for Name,

No.. @Name is a like a variable who's value is being assigned to column Name. It is *NOT* a variable _FOR_ the column Name.


Quote

then the second parameter is the value you want the column Name to have?

Second parameter? Where?
Was This Post Helpful? 0
  • +
  • -

#7 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

Re: SQL Updating Question

Posted 05 June 2012 - 08:16 AM

View Postmodi123_1, on 05 June 2012 - 08:07 AM, said:

Quote

So let me get this straight. Name is the column name,

Yes.. in table "EmployeeDetails" there should be a column called "Name". We know this because SQL update statements are structured as <column name>=<value>.



Quote

@Name is like a variable for Name,

No.. @Name is a like a variable who's value is being assigned to column Name. It is *NOT* a variable _FOR_ the column Name.


Quote

then the second parameter is the value you want the column Name to have?

Second parameter? Where?



Sorry, just figured out the second parameter. I was referring to the second parameter of AddWithValue.

Here is a code that I tried implementing. I get an error that says converting varchar value "@ID" to int. What is the problem with it?
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(commandString, connection))
                    {
                        command.Parameters.AddWithValue("@ID", id);
                        command.Parameters.AddWithValue("@One", p.DormerFirstName);




By the way, Is there a better way of updating entries in an SQL database through c#?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: SQL Updating Question

Posted 05 June 2012 - 08:25 AM

Quote

Here is a code that I tried implementing. I get an error that says converting varchar value "@ID" to int. What is the problem with it?

What's your sql statement look like?

Quote

By the way, Is there a better way of updating entries in an SQL database through c#?

No, this is the most tried and true... you can go off and let other widgets or controls just hide this, but at their core this is how an update works.


Side note, you are more than welcomed to stop bulk quoting the post right above you. ;)
Was This Post Helpful? 0
  • +
  • -

#9 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1182
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: SQL Updating Question

Posted 05 June 2012 - 08:25 AM

Look at your error and think about it. What is the datatype for that database column? What datatype is the variable id that you're passing to the AddWithValue method? Is there a discrepancy between the two? (Hint: there is, that's what your error is stating...) Can you convert the id variable to the datatype that the database is expecting?
Was This Post Helpful? 0
  • +
  • -

#10 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

Re: SQL Updating Question

Posted 05 June 2012 - 08:42 AM

Quote

Side note, you are more than welcomed to stop bulk quoting the post right above you. ;)


:) Sorry for that.



View Posth4nnib4l, on 05 June 2012 - 08:25 AM, said:

Look at your error and think about it. What is the datatype for that database column? What datatype is the variable id that you're passing to the AddWithValue method? Is there a discrepancy between the two? (Hint: there is, that's what your error is stating...) Can you convert the id variable to the datatype that the database is expecting?


So I checked the table definition, and the column named "id" is set to data type int. The whole method:

  public void UpdateData(DormerProperties p, int id)
        {
            try
            {
                string commandString = @"UPDATE Dormers SET dormerFirstName = @One  WHERE id = @ID";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(commandString, connection))
                    {
                        command.Parameters.AddWithValue("@ID", id);

                        command.Parameters.AddWithValue("@One", p.DormerFirstName);

                        int rows = command.ExecuteNonQuery();
                    }
                }
            }
///blablabla...



This post has been edited by tlhIn`toq: 05 June 2012 - 09:13 AM

Was This Post Helpful? 0
  • +
  • -

#11 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4559
  • View blog
  • Posts: 7,980
  • Joined: 08-June 10

Re: SQL Updating Question

Posted 05 June 2012 - 09:34 AM

Then you need to give us the whole error message. How would you like it if someone using your code came to you and said, "yeah I got some error about something. Something like 'object not some reference' thingy. Can you fix it"? You'd be upset that they'd even consider giving you such a sloppy bug report. Well, you did the same thing to us:

Quote

I get an error that says converting varchar value "@ID" to int. What is the problem with it?

Was This Post Helpful? 0
  • +
  • -

#12 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

Re: SQL Updating Question

Posted 06 June 2012 - 01:15 AM

Just ignore the previous error. I am experimenting with updating etc:)


In executing this piece of code:

 public void UpdateInformation(int id, DormerProperties p)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            string commandStr =
                "UPDATE Dormers SET  dormerFirstName= @dF, dormerLastName= @dL, dormerMiddleName= @dM, dormerContact = @dC, dormerCurfewStart = @dCS, dormerCurfewEnd= @dCE, dormerGender = @dG, dormerAddress = @dA, dormerYear = @dY, dormerSchool = @dS, dormerCourse = @dormC, dormerRoomNumber = @dRN, dormerRoomLetter = @dRL, fatherFirstName = @fF, fatherLastName = @fL, fatherContact = @fC, motherFirstName = @mF, motherLastName = @mL, motherContact = @mC WHERE id=" +
                id.ToString();
            connection.Open();

            SqlCommand command = new SqlCommand(commandStr, connection);
           
            command.Parameters.AddWithValue("@dF", p.DormerFirstName);
            command.Parameters.AddWithValue("@dL", p.DormerLastName);
            command.Parameters.AddWithValue("@dM", p.DormerMiddleName);
            command.Parameters.AddWithValue("@dC", p.DormerContactNumber);
            command.Parameters.AddWithValue("@dCS", p.CurfewStart);
            command.Parameters.AddWithValue("@dCE", p.CurfewEnd);
            command.Parameters.AddWithValue("@dG", p.DormerGender);
            command.Parameters.AddWithValue("@dA", p.DormerAdrress);
            command.Parameters.AddWithValue("@dY", p.DormerYear);
            command.Parameters.AddWithValue("@dS", p.DormerSchool);
            command.Parameters.AddWithValue("@dormC", p.DormerCourse);
            command.Parameters.AddWithValue("@dRN", p.DormerRoomNumber);
            command.Parameters.AddWithValue("@dRL", p.DormerRoomLetter);
            command.Parameters.AddWithValue("@fF", p.FatherFirstName);
            command.Parameters.AddWithValue("@fL", p.FatherLastName);
            command.Parameters.AddWithValue("@fC", p.FatherContactNumber);
            command.Parameters.AddWithValue("@mF", p.MotherFirstName);
            command.Parameters.AddWithValue("@mL", p.MotherLastName);
            command.Parameters.AddWithValue("@mC", p.MotherContactNumber);

            command.ExecuteNonQuery();

            connection.Close();


        }






I get this error:


See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.SqlClient.SqlException (0x80131904): The parameterized query '(@dF nvarchar(4000),@dL nvarchar(4000),@dM nvarchar(4000),@dC nv' expects the parameter '@dF', which was not supplied.
at System.Data.SqlClient.SqlConnection.onerror(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.onerror(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(Runbehavior runbehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Runbehavior runbehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(Commandbehavior cmdbehavior, Runbehavior runbehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(Commandbehavior cmdbehavior, Runbehavior runbehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Functions.DBOperations.UpdateInformation(Int32 id, DormerProperties p) in C:\Users\Renz Bagaporo\Dropbox\Database App\Functions\Library.cs:line 137
at Database_App.UpdateDialog.SetInformationToPass() in C:\Users\Renz Bagaporo\Dropbox\Database App\Database App\UpdateDialog.cs:line 37
at Database_App.UpdateDialog.SaveBtn_Click(Object sender, EventArgs e) in C:\Users\Renz Bagaporo\Dropbox\Database App\Database App\UpdateDialog.cs:line 31
at System.Windows.Forms.Control.onclick(EventArgs e)
at System.Windows.Forms.Button.onclick(EventArgs e)
at System.Windows.Forms.Button.onmouseup(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


It's been driving me crazy, what could be the problem?
Was This Post Helpful? 0
  • +
  • -

#13 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3650
  • View blog
  • Posts: 11,420
  • Joined: 05-May 12

Re: SQL Updating Question

Posted 06 June 2012 - 02:31 AM

Are you sure that p.DormerFirstName is not null?

You were so good using a parameterized query at the beginning of line 5 and then you reverted back to concatenating strings at the end of line 5 and into line 6. You should also make that parameterized to prevent stuff like this:
Posted Image

This post has been edited by Skydiver: 06 June 2012 - 02:32 AM

Was This Post Helpful? 0
  • +
  • -

#14 znerihc  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 76
  • Joined: 05-February 12

Re: SQL Updating Question

Posted 06 June 2012 - 06:13 AM

View PostSkydiver, on 06 June 2012 - 02:31 AM, said:

Are you sure that p.DormerFirstName is not null?

You were so good using a parameterized query at the beginning of line 5 and then you reverted back to concatenating strings at the end of line 5 and into line 6. You should also make that parameterized to prevent stuff like this:
Posted Image



by making line 6 parameterized, you mean like this?

    public void UpdateInformation(int id, DormerProperties p)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            string commandStr =
                "UPDATE Dormers SET  dormerFirstName= @dF, dormerLastName= @dL, dormerMiddleName= @dM, dormerContact = @dC, dormerCurfewStart = @dCS, dormerCurfewEnd= @dCE, dormerGender = @dG, dormerAddress = @dA, dormerYear = @dY, dormerSchool = @dS, dormerCourse = @dormC, dormerRoomNumber = @dRN, dormerRoomLetter = @dRL, fatherFirstName = @fF, fatherLastName = @fL, fatherContact = @fC, motherFirstName = @mF, motherLastName = @mL, motherContact = @mC WHERE id= @ID";
            connection.Open();

            SqlCommand command = new SqlCommand(commandStr, connection);

            command.Parameters.AddWithValue("@ID", id);
            command.Parameters.AddWithValue("@dF", p.DormerFirstName);
            command.Parameters.AddWithValue("@dL", p.DormerLastName);
            command.Parameters.AddWithValue("@dM", p.DormerMiddleName);
            command.Parameters.AddWithValue("@dC", p.DormerContactNumber);
            command.Parameters.AddWithValue("@dCS", p.CurfewStart);
            command.Parameters.AddWithValue("@dCE", p.CurfewEnd);
            command.Parameters.AddWithValue("@dG", p.DormerGender);
            command.Parameters.AddWithValue("@dA", p.DormerAdrress);
            command.Parameters.AddWithValue("@dY", p.DormerYear);
            command.Parameters.AddWithValue("@dS", p.DormerSchool);
            command.Parameters.AddWithValue("@dormC", p.DormerCourse);
            command.Parameters.AddWithValue("@dRN", p.DormerRoomNumber);
            command.Parameters.AddWithValue("@dRL", p.DormerRoomLetter);
            command.Parameters.AddWithValue("@fF", p.FatherFirstName);
            command.Parameters.AddWithValue("@fL", p.FatherLastName);
            command.Parameters.AddWithValue("@fC", p.FatherContactNumber);
            command.Parameters.AddWithValue("@mF", p.MotherFirstName);
            command.Parameters.AddWithValue("@mL", p.MotherLastName);
            command.Parameters.AddWithValue("@mC", p.MotherContactNumber);

            command.ExecuteNonQuery();

            connection.Close();


        }



it still has the same error:( as you may notice I am updating all of the columns in only one row. is there a better way to do this?
Was This Post Helpful? 0
  • +
  • -

#15 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1182
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: SQL Updating Question

Posted 06 June 2012 - 06:22 AM

Okay, I'm confused. Are you saying that you didn't get the error when you were concatenating id into the query, rather than parameterizing it? Because you were converting id to a string in that version, and using it as an int in the parameterized version. You said that the ID column in the DB is expecting an int, but if the concatenated version works, then try using
command.Parameters.AddWithValue("@ID", id.ToString());


on line 10.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2