Hello I am making a web form in visual 2010 ultimate.
I am using the follwoing system
Webform: has form where the buttons have an onclick function to the behind .cs sheet.The cs. sheet then refrences to my web service where the SQL database (express is connected) is refrenced.
My webform basically asks for the user to enter a unique ID (primary key, error comes up if ID is already taken) a firstname and surname.
I then have a add, update and delete button.
I already added the add function and it succesfully adds a user to the database if the ID is unique (otherwise error message to user). So that is all good!!
However, now I am trying to add my update function and am having trouble making this work. Everytime I try to make a user update I get an error (IMO the error indicates wrong command in SQL).
This is my web method for the update.
[WebMethod]
public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME)
{
// Connect to the Database
SqlConnection connection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;
AttachDbFilename='|DataDirectory|\Database.mdf';
Integrated Security=True;
User Instance=True");
// Open the connection
connection.Open();
// Create a SQL command object.
SqlCommand command = new SqlCommand(
String.Format(
"UPDATE PersonalDetails Values('{1}','{2}')" + "SELECT COUNT(ID) FROM PersonalDetails WHERE ID = '{0}'",
ID, FIRSTNAME, SURNAME),
connection);
// Execute the SQL command and store the returned integer.
int response = (int)command.ExecuteScalar();
// Close the connection
connection.Close();
// Return the result.
return (response > 0);
}
I believe I am making a mistake here;
// Create a SQL command object.
SqlCommand command = new SqlCommand(
String.Format(
"UPDATE PersonalDetails Values('{1}','{2}')" + "SELECT COUNT(ID) FROM PersonalDetails WHERE ID = '{0}'",
ID, FIRSTNAME, SURNAME),
connection);
I know my aspx page is fine, I am confident my .cs sheet is right also (but will post if requested), but I think this web method is the problem. Not sure how to do UPDATE function and just tried a different t hings with my prior coding knowledge (which is
somewhat limited, as I am in involved in networking).
Any help would be greatly appreciated, I do enjoy programming but struggle with it and this particular problem is giving me a headache :)
It seems you mixed insert, update and select sytax together. Ytu this for your update:
// Open the connection
connection.Open();
// Create a SQL command object.
SqlCommand command = new SqlCommand("UPDATE PersonalDetails Set FIRSTNAME =@FIRSTNAME, SURNAME=@SURNAME WHERE ID=@ID");
//Add parameter value to SQL command
command.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME);
command.Parameters.AddWithValue("@SURNAME", SURNAME);
command.Parameters.AddWithValue("@ID", ID);
command.ExecuteNonQuery();
// Close the connection
connection.Close();
How do I give a response with this?
My cs. code uses a bool.
this is my cs function for the update (which teh button uses on click)
protected void cmdUpdate_Click(object sender, EventArgs e)
{
// Create a reference to the Web service
DbWebService.WebService1 proxy =
new DbWebService.WebService1();
// Create a person details object to send to the Web service.
string SURNAME;
string FIRSTNAME;
string ID;
SURNAME = txtSurname.Text;
FIRSTNAME = txtFirstname.Text;
ID = txtID.Text;
// Attempt to store in the Web service
bool rsp =
proxy.UpdatePerson(int.Parse(ID), FIRSTNAME, SURNAME);
// Inform the user
if (rsp)
{
lblOutcome.Text =
"Successfully updated new user.";
}
else
{
lblOutcome.Text =
"Failed to update user!";
}
}
so I used this (with the above code in .cs ) code in the web service, but still getting the same error :(
really dunno what I am doing wrong.
[WebMethod]
public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME)
{
// Connect to the Database
SqlConnection connection = new SqlConnection(
@"Data Source=.\SQLEXPRESS;
AttachDbFilename='|DataDirectory|\Database.mdf';
Integrated Security=True;
User Instance=True");
// Open the connection
connection.Open();
// Create a SQL command object.
SqlCommand command = new SqlCommand("UPDATE PersonalDetails Set FIRSTNAME =@FIRSTNAME, SURNAME=@SURNAME WHERE ID=@ID");
//Add parameter value to SQL command
command.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME);
command.Parameters.AddWithValue("@SURNAME", SURNAME);
command.Parameters.AddWithValue("@ID", ID);
command.ExecuteNonQuery();
// Execute the SQL command and store the returned integer.
int response = (int)command.ExecuteScalar();
// Close the connection
connection.Close();
// Return the result.
return (response > 0);
}
Yes. you have commited mistake in updat statement. Your statement should have
String.Format(
"UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'",
ID, FIRSTNAME, SURNAME);
I don't know the fields name of table. I just assumed that it is FirstName and SurName. Use as your field name. You can also use. Following code snippet and it is tested.
tiiimmmy
Member
8 Points
29 Posts
Update function in web form using SQL express
Apr 25, 2012 04:15 AM|LINK
Hello I am making a web form in visual 2010 ultimate.
I am using the follwoing system
Webform: has form where the buttons have an onclick function to the behind .cs sheet.The cs. sheet then refrences to my web service where the SQL database (express is connected) is refrenced.
My webform basically asks for the user to enter a unique ID (primary key, error comes up if ID is already taken) a firstname and surname.
I then have a add, update and delete button.
I already added the add function and it succesfully adds a user to the database if the ID is unique (otherwise error message to user). So that is all good!!
However, now I am trying to add my update function and am having trouble making this work. Everytime I try to make a user update I get an error (IMO the error indicates wrong command in SQL).
This is my web method for the update.
[WebMethod] public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) { // Connect to the Database SqlConnection connection = new SqlConnection( @"Data Source=.\SQLEXPRESS; AttachDbFilename='|DataDirectory|\Database.mdf'; Integrated Security=True; User Instance=True"); // Open the connection connection.Open(); // Create a SQL command object. SqlCommand command = new SqlCommand( String.Format( "UPDATE PersonalDetails Values('{1}','{2}')" + "SELECT COUNT(ID) FROM PersonalDetails WHERE ID = '{0}'", ID, FIRSTNAME, SURNAME), connection); // Execute the SQL command and store the returned integer. int response = (int)command.ExecuteScalar(); // Close the connection connection.Close(); // Return the result. return (response > 0); }I believe I am making a mistake here;
// Create a SQL command object. SqlCommand command = new SqlCommand( String.Format( "UPDATE PersonalDetails Values('{1}','{2}')" + "SELECT COUNT(ID) FROM PersonalDetails WHERE ID = '{0}'", ID, FIRSTNAME, SURNAME), connection);I know my aspx page is fine, I am confident my .cs sheet is right also (but will post if requested), but I think this web method is the problem. Not sure how to do UPDATE function and just tried a different t hings with my prior coding knowledge (which is somewhat limited, as I am in involved in networking).
Any help would be greatly appreciated, I do enjoy programming but struggle with it and this particular problem is giving me a headache :)
tusharrs
Contributor
3230 Points
668 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 04:22 AM|LINK
hi,
update query syntax problem
it should be
SqlCommand command = new SqlCommand( "update personaldetails set firstname = '" + FIRSTNAME + "',surname = '" + SURNAME + "' where id = " + ID.ToString(), con);
( Mark as Answer if it helps you out )
View my Blog
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Update function in web form using SQL express
Apr 25, 2012 04:24 AM|LINK
You should add a ; between your two SQL statementslik this:
;SELECT COUNT(ID) FROM ...
You should use parameters for your query instead of string concatenation.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 04:48 AM|LINK
@tushars
i tried this but I got an error still. I think a different error tho. But I beleieve it still has something to do with the SQL command
@limno what is the difference? Sorry, again not very familar with programming.
The system Im using at the moment is the way I got taught.
anywho I added the ; but still got the error.
ps. thanks for the pompt replies btw
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Update function in web form using SQL express
Apr 25, 2012 05:01 AM|LINK
It seems you mixed insert, update and select sytax together. Ytu this for your update:
// Open the connection connection.Open(); // Create a SQL command object. SqlCommand command = new SqlCommand("UPDATE PersonalDetails Set FIRSTNAME =@FIRSTNAME, SURNAME=@SURNAME WHERE ID=@ID"); //Add parameter value to SQL command command.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME); command.Parameters.AddWithValue("@SURNAME", SURNAME); command.Parameters.AddWithValue("@ID", ID); command.ExecuteNonQuery(); // Close the connection connection.Close();Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 05:16 AM|LINK
How do I give a response with this?
My cs. code uses a bool.
this is my cs function for the update (which teh button uses on click)
protected void cmdUpdate_Click(object sender, EventArgs e) { // Create a reference to the Web service DbWebService.WebService1 proxy = new DbWebService.WebService1(); // Create a person details object to send to the Web service. string SURNAME; string FIRSTNAME; string ID; SURNAME = txtSurname.Text; FIRSTNAME = txtFirstname.Text; ID = txtID.Text; // Attempt to store in the Web service bool rsp = proxy.UpdatePerson(int.Parse(ID), FIRSTNAME, SURNAME); // Inform the user if (rsp) { lblOutcome.Text = "Successfully updated new user."; } else { lblOutcome.Text = "Failed to update user!"; } }tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 06:13 AM|LINK
so I used this (with the above code in .cs ) code in the web service, but still getting the same error :(
really dunno what I am doing wrong.
[WebMethod] public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) { // Connect to the Database SqlConnection connection = new SqlConnection( @"Data Source=.\SQLEXPRESS; AttachDbFilename='|DataDirectory|\Database.mdf'; Integrated Security=True; User Instance=True"); // Open the connection connection.Open(); // Create a SQL command object. SqlCommand command = new SqlCommand("UPDATE PersonalDetails Set FIRSTNAME =@FIRSTNAME, SURNAME=@SURNAME WHERE ID=@ID"); //Add parameter value to SQL command command.Parameters.AddWithValue("@FIRSTNAME", FIRSTNAME); command.Parameters.AddWithValue("@SURNAME", SURNAME); command.Parameters.AddWithValue("@ID", ID); command.ExecuteNonQuery(); // Execute the SQL command and store the returned integer. int response = (int)command.ExecuteScalar(); // Close the connection connection.Close(); // Return the result. return (response > 0); }mahedee
Member
450 Points
116 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 06:47 AM|LINK
Yes. you have commited mistake in updat statement. Your statement should have
String.Format(
"UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'",
ID, FIRSTNAME, SURNAME);
I don't know the fields name of table. I just assumed that it is FirstName and SurName. Use as your field name. You can also use. Following code snippet and it is tested.
"UPDATE PersonalDetails SET FirstName = '" + FIRSTNAME +
"',SurName = '" + SURNAME + "'
+ "' WHERE ID= " + ID;
If it helped you, please mark it as answer
Mahedee
Blog: http://mahedee.blogspot.com
tiiimmmy
Member
8 Points
29 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 07:40 AM|LINK
Hi,
I tried this, and it should work but still I get the error, I have no idea why, so frustrating!!
With the 2nd snippet, I get an "Newline in constant" error in the description list.
mahedee
Member
450 Points
116 Posts
Re: Update function in web form using SQL express
Apr 25, 2012 08:17 AM|LINK
Could you please tell me, what is the error? For more information about updating table please read the following topic http://mahedee.blogspot.com/2012/04/simple-demonstration-with-aspnet.html take a closer look on the EmployeeInfoDAL. Hopefully you will get an idea how to update a table.
Mahedee
Blog: http://mahedee.blogspot.com