CODEWITHSUNDEEP
c#sqlweb-services
Amrit Sharma
Amrit Sharma

Reputation: 1916

Sql to update data

I have following code to set the text box values on page load.

protected void Page_Load(object sender, EventArgs e)
        {
            localhost.UserRegistration m = new localhost.UserRegistration();
            int user = m.ID(Session["Username"].ToString());
            DataSet ds = m.GetUserInfo(user);
            if (ds.Tables.Count > 0)
            {
                TextBox1.Text = ds.Tables[0].Rows[0]["emailAddress"].ToString();
                TextBox2.Text = ds.Tables[0].Rows[0]["password"].ToString();
            }


        }

So when first user opens the page, the user will be shown their email address and password in textbox. when they make changes and click update, the same value that was on page load will be sent to the database, not the new one that is changed.

I have the following web service method to update the user details

[WebMethod(Description = "Updates a single user")]
        public string UpdateUser(int user, string emailAddress, string password)
        {
            // Create connection object
            int ix = 0;
            string rTurn = "";
            OleDbConnection oleConn = new OleDbConnection(connString);
            try
            {
                oleConn.Open();
                string sql = "UPDATE [User] SET [emailAddress]=@emailAddress, [password]=@password" + " WHERE [ID]=@user";
                OleDbCommand oleComm = new OleDbCommand(sql, oleConn);

                oleComm.Parameters.Add("@user", OleDbType.Integer).Value = user;
                oleComm.Parameters.Add("@emailAddress", OleDbType.Char).Value = emailAddress;
                oleComm.Parameters.Add("@password", OleDbType.Char).Value = password;


                ix = oleComm.ExecuteNonQuery();
                if (ix > 0)
                    rTurn = "User Updated";
                else
                    rTurn = "Update Failed";
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                rTurn = ex.ToString();
            }
            finally
            {
                oleConn.Close();
            }
            return rTurn;
        }

This is how table look in database

enter image description here

Client Side Code

protected void Button1_Click(object sender, EventArgs e)
        {


            string email = TextBox1.Text;
            string pass = TextBox2.Text;
            localhost.UserRegistration m = new localhost.UserRegistration();
            int usr = m.ID(Session["Username"].ToString());
            m.UpdateUser(usr, email, pass);

        }

Can Somebody tell me why....

Upvotes: 1

Views: 198

Answers (3)

Pilgerstorfer Franz
Pilgerstorfer Franz

Reputation: 8359

I think you are mixing up SqlParameters and OleDbParameters. OleDbParameters does not support named parameters!!

see MSDN OleDbCommand.Parameters

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

I think you should adapt your code to

...
oleConn.Open();
string sql = "UPDATE [User] SET [emailAddress]=?, [password]=? WHERE [ID]=?";
OleDbCommand oleComm = new OleDbCommand(sql, oleConn);

oleComm.Parameters.Add("@emailAddress", OleDbType.Char).Value = emailAddress;
oleComm.Parameters.Add("@password", OleDbType.Char).Value = password;
oleComm.Parameters.Add("@user", OleDbType.Integer).Value = user;
...

EDIT

protected void Page_Load(object sender, EventArgs e)
{
  if (!Page.IsPostBack)
  {
      localhost.UserRegistration m = new localhost.UserRegistration();
      int user = m.ID(Session["Username"].ToString());
      DataSet ds = m.GetUserInfo(user);
      if (ds.Tables.Count > 0)
      {
          TextBox1.Text = ds.Tables[0].Rows[0]["emailAddress"].ToString();
          TextBox2.Text = ds.Tables[0].Rows[0]["password"].ToString();
      }
  }
}

Upvotes: 2

Pushpendra
Pushpendra

Reputation: 538

Replace the below line :

string sql = "UPDATE [User] SET [emailAddress]=@emailAddress, [password]=@password" + " WHERE [ID]=@user";

To 

string sql = "UPDATE [User] SET [emailAddress]=@emailAddress, [password]=@password where [ID]=@user";

and also check the datatype of Id in database is int or not

Upvotes: 0

Amrit Sharma
Amrit Sharma

Reputation: 1916

Ok got it working. Changed sql query like below.

string sql = "UPDATE [User] SET [emailAddress]=@emailAddress, [password]=@password" + " WHERE [ID]= "+ user;

But it updates the wrong field. emailAddress is updated as user ID, password is updated as email Address.

Upvotes: 0

Related Questions