UpdateCommand

blase satiate

what is wrong here? no error shows and nothing happens (the variables are not empty - I checked)

SqlCommand postname = new SqlCommand("UPDATE sites SET username=@username, userpass=@userpass WHERE useralias=@useralias",connection);
connection.Open();
SqlDataAdapter posttext = new SqlDataAdapter();
postname.Parameters.Add("@username", SqlDbType.NVarChar, 50, username);
postname.Parameters.Add("@userpass", SqlDbType.NVarChar, 50, userpass);
postname.Parameters.Add("@useralias", SqlDbType.NVarChar, 50, theAlias);
posttext.UpdateCommand = postname;

or am I not doing? I need to update the column to other values??.
I translated the interpreter, so I apologize if poorly understood.

16 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satish Bhat15y ago
To post the data modification to the database, you have to call the Update method of the dataadapter which you've not done. And before that there has to be changes in the dataset/datatable in the dataadapter.

Otherwise you can call
posttext.UpdateCommand = postname;
posttext.UpdateCommand.ExecuteNonQuery();
shams khan15y ago
i think this will work for u.check it out, please try.. connection.Open(); SqlCommand postname = new SqlCommand("UPDATE sites SET username=@username, userpass=@userpass WHERE useralias=@useralias",connection); SqlDataAdapter posttext = new SqlDataAdapter(); postname.Parameters.Add("@username", SqlDbType.NVarChar, 50, "username"); postname.Parameters.Add("@userpass", SqlDbType.NVarChar, 50, "userpass"); postname.Parameters.Add("@useralias", SqlDbType.NVarChar, 50, "useralias"); posttext.UpdateCommand = postname; } catch(Exceptio ex) { MessageBox.Show(ex.Message.ToString()); } also use the break point to detect error exactly if found if a problem in this code
blase satiate15y ago
id create
column: 'id'
type: 'int'
and set primary key it.

update it by this query: UPDATE sites SET username='test', userpass='test2' WHERE useralias='qiq'
not work((

full code:

            using (SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\stite\\??? ?????????\\visual studio 2010\\Projects\\WebApplication5\\WebApplication5\\App_Data\\Database1.mdf;Integrated Security=True;User Instance=True;"))
            {
                //UPDATE
                connection.Open();
                SqlCommand postname = new SqlCommand("UPDATE sites SET username='test', userpass='test2' WHERE useralias='qiq'", connection);
                SqlDataAdapter posttext = new SqlDataAdapter();
                posttext.UpdateCommand = postname;
                //end UPDATE
                //SELECT
                SqlCommand getname = new SqlCommand("SELECT usersite, username, userpass FROM sites WHERE useralias='" + theTable + "'", connection);
                SqlDataReader gettext = getname.ExecuteReader();
                while(gettext.Read())
                {
                    siteLabel.Text = String.Format("New auth for {0}:", gettext[0]);
                    loginLabel.Text = String.Format("{0}", gettext[1]);
                    passLabel.Text = String.Format("{0}", gettext[2]);
                }
                gettext.Close();
                //end SELECT
            }
Vulpes15y ago
As there's no error, my suspicion is that it can't find the correct record to update and so does nothing.

As there doesn't appear to be a primary key for your table, I'd try setting one - username perhaps.
blase satiate15y ago
not have error
Dorababu Meka15y ago
Can you debug and post if any errors or exception you are getting
blase satiate15y ago
            using (SqlConnection connection = new SqlConnection(...))
            {
                //UPDATE
                connection.Open();
                SqlCommand postname = new SqlCommand("UPDATE sites SET username='"+username+"', userpass='"+userpass+"' WHERE useralias='"+theTable+"'", connection);
                SqlDataAdapter posttext = new SqlDataAdapter();
                posttext.UpdateCommand = postname;
                //end UPDATE
                //SELECT
                SqlCommand getname = new SqlCommand("SELECT usersite, username, userpass FROM sites WHERE useralias='" + theTable + "'", connection);
                SqlDataReader gettext = getname.ExecuteReader();
                while(gettext.Read())
                {
                    siteLabel.Text = String.Format("New auth for {0}:", gettext[0]);
                    loginLabel.Text = String.Format("{0}", gettext[1]);
                    passLabel.Text = String.Format("{0}", gettext[2]);
                }
                gettext.Close();
                //end SELECT
            }
blase satiate15y ago
>"Change all the datatypes to Varchar"

yes, i do it - not happened, the same problem.

I write to the database like this: "qwerty" and "asdfg"
Suthish Nair15y ago
Why using NVarchar (The "N" in NVARCHAR means uNicode.) Change all the datatypes to Varchar. Equal operator is incompatible with text, ntext, or image data types only.
blase satiate15y ago

type NVarChar is default, i must change it? on what?

connection.Open();
SqlCommand postname = new SqlCommand("UPDATE sites SET username='"+username+"', userpass='"+userpass+"' WHERE useralias='"+theTable+"'", connection);

SqlDataAdapter posttext = new SqlDataAdapter();
postname.Parameters.Add("@username", SqlDbType.NVarChar, 50, username); // del it
postname.Parameters.Add("@userpass", SqlDbType.NVarChar, 50, userpass); //del it
postname.Parameters.Add("@useralias", SqlDbType.NVarChar, 50, theTable); //del it
posttext.UpdateCommand = postname;
LGN.Text = username; PSW.Text = userpass;

I deleted these parameters. and changed in a database of these types of data to "Varchar", to"text", to "ntext" - nothing happened. what should be changed?
Suthish Nair15y ago
I got confused now. Its working now or still facing the problem. Because you unchecked the Accepted Answer.
Suthish Nair15y ago
check the datatypes - NVarChar. theTable - what and how the value coming. try hard coding an update statement with values.
blase satiate15y ago
>"Now its working fine, updation happening?"
yes.. UPDATE code is not correct,
i see: all is correct, but all is not work.

only "SELECT" good work.

PS:
>"SELECT works well. shows the values ??from the table that there are"
correctly write*:
"SELECT works well. shows the values ??from the table that there are"*
Suthish Nair15y ago
Now its working fine, updation happening?
blase satiate15y ago
nothing. Values is not empty. Full code:

using (SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=....;Integrated Security=True;User Instance=True;"))
{
//UPDATE
connection.Open();
SqlCommand postname = new SqlCommand("UPDATE sites SET username=@username, userpass=@userpass WHERE useralias=@useralias", connection);
SqlDataAdapter posttext = new SqlDataAdapter();
postname.Parameters.Add("@username", SqlDbType.NVarChar, 50, username);
postname.Parameters.Add("@userpass", SqlDbType.NVarChar, 50, userpass);
postname.Parameters.Add("@useralias", SqlDbType.NVarChar, 50, theTable);
posttext.UpdateCommand = postname;
LGN.Text = username; PSW.Text = userpass; //check values
//SELECT
SqlCommand getname = new SqlCommand("SELECT usersite, username, userpass FROM sites WHERE useralias='" + theTable + "'", connection);
SqlDataReader gettext = getname.ExecuteReader();
while(gettext.Read())
{
siteLabel.Text = String.Format("New auth for {0}:", gettext[0]);
loginLabel.Text = String.Format("{0}", gettext[1]);
passLabel.Text = String.Format("{0}", gettext[2]);
}
gettext.Close();
}
in the db table "Sites" has not changed.
"SELECT" works well. shows the values ??from the table that there are
Suthish Nair15y ago
try..

connection.Open();
SqlCommand postname = new SqlCommand("UPDATE sites SET username=@username, userpass=@userpass WHERE useralias=@useralias",connection);

SqlDataAdapter posttext = new SqlDataAdapter();
postname.Parameters.Add("@username", SqlDbType.NVarChar, 50, "username");
postname.Parameters.Add("@userpass", SqlDbType.NVarChar, 50, "userpass");
postname.Parameters.Add("@useralias", SqlDbType.NVarChar, 50, "useralias");
posttext.UpdateCommand = postname;

Put a breakpoint and check the parameters value.

Also, check the Select statement for above updation