Update function in web form using SQL express

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 :)

Re: Update function in web form using SQL express

hi,

update query syntax problem

it should be

SqlCommand command = new SqlCommand( "update personaldetails set firstname = '" + FIRSTNAME + "',surname = '" + SURNAME + "' where id = " + ID.ToString(), con);

Re: Update function in web form using SQL express

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.

Re: Update function in web form using SQL express

@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

Re: Update function in web form using SQL express

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();

Re: Update function in web form using SQL express

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!";
            }
            
        }

Re: Update function in web form using SQL express

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);
        }

Re: Update function in web form using SQL express

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

Re: Update function in web form using SQL express

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.

Re: Update function in web form using SQL express

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.

Re: Update function in web form using SQL express

hi,

1) check the connection string, is it proper

2) check the query by executing in sql server

3) check the field datattypes are they matching

Re: Update function in web form using SQL express

No worries, here is the error in full detail:

----------------------------------------------------------------------

My webform, the interface the user sees in browser.

Basically here the ID 1 is already entered in the database (using the add button). So when the user selects update with new info in first/surname the database should be updated, However when I click update I get the following error in Visual:

and when I stop debugging, the web browser gives me this error:

Server Error in '/' Application.

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
  at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteScalar()
  at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\sit322_ass2\assignment2\WebServices\WebService.asmx.cs:line 118
  --- End of inner exception stack trace ---

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.
  at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteScalar()
  at WebServices.WebService1.UpdatePerson(Int32 ID, String FIRSTNAME, String SURNAME) in C:\Users\Timothy\Desktop\database\WebServices\WebService.asmx.cs:line 118
  --- End of inner exception stack trace ---

Source Error:

Line 153:        [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://tempuri.org/UpdatePerson", RequestNamespace="http://tempuri.org/", ResponseNamespace="http://tempuri.org/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
Line 154:        public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME) {
Line 155: object[] results = this.Invoke("UpdatePerson", new object[] { Line 156:                        ID,
Line 157:                        FIRSTNAME,

Source File: C:\Users\Timothy\Desktop\database\PersonalDetails\Web References\DbWebService\Reference.cs Line: 155

---

I'm not sure why this is happening!?Really confused...

Here are the two relevant codes again:

personal details webform cs. code, function for update

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!";
            }
            
        }

webservice cs. update SQL function

        [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 set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'",
                  ID, FIRSTNAME, SURNAME));

                                            
            // 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);
        }

Re: Update function in web form using SQL express

tusharrs

1) check the connection string, is it proper

2) check the query by executing in sql server

3) check the field datattypes are they matching

sorry to sound a little amature-ish

But how do I do this?

I think the data types are matching.

when I was adding my add function I also had the same error, but that was because I had a typo in the SQL command, I accidently misspelled personaldetails (the database refrence). Thus why I think this is an SQL command error.

The add function currently works perfect.

Re: Update function in web form using SQL express

hi,

set connection to sqlcommand object

i.e.

command.SqlConnection = connection;

SqlCommand command = new SqlCommand( String.Format("UPDATE PersonalDetails set FirstName = '{1}', SurName = '{2}' WHERE ID = '{0}'", ID, FIRSTNAME, SURNAME) , connection );

Re: Update function in web form using SQL express

Thanks again for the reply.

I believe I tried this at one stage and still got error, but shall try again. Just at work at the moment so will try it when I get home, and mark as answer if it solves my problem.

Any chance (using the provided code) that I made an error somewhere else in the code?

regards, tim.

Re: Update function in web form using SQL express

You didn't initialize connection in sql command. Use the following before execute the query.

command.Connection = connection ;

Re: Update function in web form using SQL express

You can also use cmd.ExecuteNonQuery(); for insert also.

Re: Update function in web form using SQL express

yep fixed that and still get the error, this is inreasingly frustrating.

I fixed it to this

// Create a SQL command object.
            SqlCommand command = new SqlCommand(
                String.Format("UPDATE PersonalDetails set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'",
                  FIRSTNAME, SURNAME, ID), connection);

still get the same error.

mahedee
You can also use cmd.ExecuteNonQuery(); for insert also.

sorry but what do you mean by this, really confused?

Do I remove any of these lines: ?

            // Execute the SQL command and store the returned integer.
            int response = (int)command.ExecuteScalar();

            // Close the connection PS: I KNOW I DONT REMOVE THIS LINE
            connection.Close();
        
            // Return the result.
            return (response > 0);

Re: Update function in web form using SQL express

I often use this way to connect, insert, update i,e acess database.

Step - 1: Simply create a class DBConnector to connect to the database.

public class DBConnector
    {
        private string connectionString = null;
        private SqlConnection sqlConn = null;
        private SqlCommand cmd = null;


        public DBConnector()
        {
            connectionString = ConfigurationManager.ConnectionStrings["application"].ToString(); //User your connection string here.
        }


        public SqlCommand GetCommand()
        {
            cmd = new SqlCommand();
            cmd.Connection = sqlConn;
            return cmd;
        }


        public SqlConnection GetConn()
        {
            sqlConn = new SqlConnection(connectionString);
            return sqlConn;
        }

    }

Step 2: Insert, Update and delete database using DBConnector class

public class EmployeeInfoDAL
    {
        private SqlConnection sqlConn;
        private SqlCommand cmd;

        private readonly DBConnector objDBConnector;

        public EmployeeInfoDAL()
        {
            objDBConnector = new DBConnector();
            sqlConn = objDBConnector.GetConn();
            cmd = objDBConnector.GetCommand();
        }

//Insert data

public string InsertEmployeeUserInfo(EmployeeUserInfo objEmployeeUserInfo)
        {
            int noOfRowEffected = 0;

            try
            {
                sqlConn.Open();
                cmd.CommandType = CommandType.StoredProcedure; //If you use query instead of storprocedure. User CommandType.Text

                cmd.CommandText = "[fsp_Insert_employee_userInfo]"; //for query use query instead of store procedure



                cmd.Parameters.AddWithValue("@user_id", objEmployeeUserInfo.UserId); // dont use it if you don't need parameter
                cmd.Parameters.AddWithValue("@emp_id", objEmployeeUserInfo.EmployeeId);// dont use it if you don't need parameter

                noOfRowEffected = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            catch (Exception exp)
            {
                throw (exp);
            }
            finally
            {
                sqlConn.Close();
            }

            if (noOfRowEffected > 0)
                return "Employee User information saved successfully!";
            else
                return "Employee User information didn't save";

        }
}

This is tested and I am use its in my project. Please try this way. Hopefully it will work. Becarefule about your connection string.

Re: Update function in web form using SQL express

Hi,

Sorry but one of my requirements is to not use a class I believe, hoping to make the gormat im using work rather then a full workover.

I think I have reduced the problem a little, I changed the web form to do this.

[WebMethod]
        public bool UpdatePerson(int ID, string FIRSTNAME, string SURNAME)
        {

                // In case of failure failure first
                bool rtn = false;
                                      
                // 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 set FIRSTNAME = '{1}', SURNAME = '{2}' WHERE ID = '{0}'",
                      FIRSTNAME, SURNAME, ID), connection);


                // Execute the command.
                command.ExecuteNonQuery();

                // Close the connection.
                connection.Close();
                rtn = true;

                // Return the outcome
                return (rtn);

        }

However the error I am getting now says the following (in visual when I attempt to update on click)

Conversion failed when converting the varchar value 'xx' to data type int.

ANy idea the issue here? The thing is I have ID as a string in the webform cs. (as you can see above) but I do this to read the user input from text field. And I then change it to an int with the int.Parse(ID) statement. Then in the web service cs. sheet the ID is an int, not sure the issue here, anyone can notice the error. I feel like the code I posted is really close to working.