What is the problem with this update statement

Question

1.00/5 (2 votes)

See more:

What problem is with this update statement? Any one help me

UpdateQueryString = "UPDATE tblLogin SET loginName= @name, Password= @password, EmpID= @tempEmId WHERE LoginID= @tempLogId";

please help me

What I have tried:

UpdateQueryString = "UPDATE tblLogin SET loginName= @name, Password= @password, EmpID= @tempEmId WHERE LoginID= @tempLogId";

Error: must declare scalar variable "@name".

Posted 20-Nov-16 3:05am

safwanshah

Updated 20-Nov-16 9:30am

Add a Solution

2 solutions

Solution 1

There's nothing wrong with the statement. You need to set up parameters for @name, @password, @tempEmId and @tempLogId, assign the appropriate values to them, and assign them to the command.

Posted 20-Nov-16 3:25am

Midi_Mick

Comments

safwanshah 20-Nov-16 10:12am

i have changed this statement as follows:
UpdateQueryString = "UPDATE tblLogin SET loginName ='" + UUname + "', Password = '" + UUpassword + "', EmpID = '" + tempEmId + "' WHERE LoginID = '" + tempLogId + "'";

This is working perfect.

Midi_Mick 20-Nov-16 10:16am

That is very bad practice. You should be using your original statement, but add the parameters to the SqlCommand object.
You will have something like:

cmd.CommandText = UpdateQueryString;

You need to add:

cmd.Parameters.AddWithValue("@name", UUname);
cmd.Parameters.AddWithValue("@password", UUpassword);

...and so on

safwanshah 20-Nov-16 10:29am

UpdateQueryString = "UPDATE tblLogin SET loginName= @name, Password= @password, EmpID= @tempEmId WHERE LoginID= @tempLogId";

UpdateCommand.CommandText = UpdateQueryString;

UpdateCommand.Parameters.AddWithValue("@name", UUname);
UpdateCommand.Parameters.AddWithValue("@password", UUpassword);
UpdateCommand.Parameters.AddWithValue("@EmID", tempEmId);

UpdateCommand = new SqlCommand(UpdateQueryString, cc.logcon);

error displayed "Object reference not set to an instance of an object.

Midi_Mick 20-Nov-16 10:36am

Put
UpdateCommand = new SqlCommand(....
BEFORE you add the parameters. Note, you are setting the CommandText in the Constructor, so you don't need
UpdateCommand.CommandText = UpdateQueryString;
line.

safwanshah 20-Nov-16 12:02pm

cc.logcon.Close();
}
cc.logcon.Open(); //logcon.Open();

// UpdateQueryString = "UPDATE tblLogin SET loginName ='" + UUname + "', Password = '" + UUpassword + "', EmpID = '" + tempEmId + "' WHERE LoginID = '" + tempLogId + "'";
UpdateQueryString = "UPDATE tblLogin SET loginName= @name, Password= @password, EmpID= @tempEmId WHERE LoginID= @tempLogId";

UpdateCommand = new SqlCommand(UpdateQueryString, cc.logcon);
UpdateCommand.CommandText = UpdateQueryString;
UpdateCommand.Parameters.AddWithValue("@name", UUname);
UpdateCommand.Parameters.AddWithValue("@password", UUpassword);
UpdateCommand.Parameters.AddWithValue("@EmID", tempEmId);

SelectQueryString = "Select * from tblLogin where LoginID='" + tempLogId + "'";

SelectCommand = new SqlCommand(SelectQueryString, cc.logcon);
LoginReader = SelectCommand.ExecuteReader();

if (LoginReader.HasRows)
{
LoginReader.Close();

UpdateReader = UpdateCommand.ExecuteReader();
MessageBox.Show("Record updated");

UpdateReader.Close();

cc.logcon.Close();
return "1";
}
else
{
UpdateReader.Close();

cc.logcon.Close();
MessageBox.Show("Record not updated");
return "0";
}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);

}
return null;

}
still encountered an error "must declare the scalar variable "tempEmId".

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohtshm Zubair · Accepted Answer · 2016-11-20T09:30:00

Solution 2

yeah last parameter added is wrong

C#

UpdateCommand.Parameters.AddWithValue("@EmID", tempEmId);

It should be

C#

UpdateCommand.Parameters.AddWithValue("@tempEmId", tempEmId);

and one more

C#

UpdateCommand.Parameters.AddWithValue("@tempLogId", tempLogId);

Carefully check the spellings and no. of parameters required. You only need four parameters

@name
@password
@tempEmId
@tempLogId

Posted 20-Nov-16 9:30am

Mohtshm Zubair

Comments

Midi_Mick 20-Nov-16 22:46pm

Thanks for that Mohtshm - I'd gone to bed by the time he replied.

safwanshah 25-Nov-16 12:26pm

Thanks Mohtshm Zubair. It's working perfect. I am very grateful to you and looking forward to a continuous support, please

safwanshah 25-Nov-16 12:26pm

Thanks Mohtshm Zubair. It's working perfect. I am very grateful to you and looking forward to a continuous support, please

safwanshah 25-Nov-16 12:30pm

Thanks a lot Mohtshm Zubair