Updating Database on button click

Question

i have a button that suppose to update data into the database.

private void button4_Click(object sender, EventArgs e)
        {
            //need update code//
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=PEWPEWDIEPIE\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
            conn.Open();

            SqlDataAdapter daCount = new SqlDataAdapter("select iCount from ComDet where cName = @cName", conn);
            daCount.SelectCommand.Parameters.Add("@cName", SqlDbType.VarChar).Value = ListU.SelectedValue;

            DataTable dtC = new DataTable();
            daCount.Fill(dtC);
            DataRow firstRow = dtC.Rows[0];

            string x = firstRow["iCount"].ToString();
            int y = Int32.Parse(x);
            int z = y + 1;

            //SqlCeCommand cmdC = conn.CreateCommand();
            SqlCommand cmdC = conn.CreateCommand();
            cmdC.CommandText = "Update ComDet set iCount = '" + z + "', ViewTime = '" + lblTime.Text + "', LastView = '" + txtUser2.Text + "' Where cName = '" + ListU.SelectedValue.ToString() + "'";
                conn.Close();
}

but i get this error..

the error

can someone help?

update =

i've changed my code to

cmdC.CommandText = "Update ComDet set iCount = " + z + ", ViewTime = '" + lblTime.Text + "', LastView = '" + txtUser2.Text + "' Where cName = '" + ListU.SelectedValue.ToString() + "'";

but the problem now is that , there's no update.

the iCount in the database is an INT , value is 0. There is also no update for the viewtime and lastview.

where did i go wrong now?

Try outputting firstRow["iCount"] into the debug console and check if that's a valid integer. — Mike Shi
– Mike Shi, Commented Dec 30, 2013 at 17:32
@AlexDenysenko - u sure? because then , how is the update going to detect the Z value? — Mohd Nasrul Iwan Fajaruddin
– Mohd Nasrul Iwan Fajaruddin, Commented Dec 30, 2013 at 17:32
It executes the string man... the string will be 'Update ComDet set iCount = 3, ViewTime ' or whatever the value is — Alex Denysenko
– Alex Denysenko, Commented Dec 30, 2013 at 17:33

logixologist · Accepted Answer · 2013-12-30 17:43:32Z

2

change this:

    cmdC.CommandText = "Update ComDet set iCount = '" + z + "', ViewTime = '" + lblTime.Text + "', LastView = '" + txtUser2.Text + "' Where cName = '" + ListU.SelectedValue.ToString() + "'";

to

    cmdC.CommandText = "Update ComDet set iCount = " + z + ", ViewTime = '" + lblTime.Text + "', LastView = '" + txtUser2.Text + "' Where cName = '" + ListU.SelectedValue.ToString() + "'";

you dont need the "'" apostrophe around it becuase its a number. That would definitely get you string not in correct format error

answered Dec 30, 2013 at 17:43

logixologist

3,8644 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Mohd Nasrul Iwan Fajaruddin Over a year ago

yep.. iCount in Database is an integer.. btw , the code works now , but there is no update in the databse.. how is that?

logixologist Over a year ago

the onyl reason an UPDATE wont work is when the WHERE Clause doesnt find anything with that criteria. Are you sure this is correct: Where cName = '" + ListU.SelectedValue.ToString() + "'

logixologist Over a year ago

Here is what you should do: put in a break point right on that line and copy the SQL into SSMS directly.

Mohd Nasrul Iwan Fajaruddin Over a year ago

maigad.. that was the thing i was missing.. the executenonquery.. i shall put it now.. give me a sec..

logixologist Over a year ago

If you want to continue to lose you hair.. stay on this same path of trying to write out sql statements with apostrophes. You should look into paramaterized queries. Anyone can hack your app by putting in malicious code into the text box (called SQL Injection Attack)

|

DROP TABLE users · Accepted Answer · 2013-12-30 18:07:51Z

1

I would guess maybe the icount value is not a number, i would recommend using TryParse just in case. And that should keep this error from happening. What to do about a bad value getting returned by the query is another issue.

private void button4_Click(object sender, EventArgs e)
        {
            //need update code//
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=PEWPEWDIEPIE\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
            conn.Open();

            SqlDataAdapter daCount = new SqlDataAdapter("select iCount from ComDet where cName = @cName", conn);
            daCount.SelectCommand.Parameters.Add("@cName", SqlDbType.VarChar).Value = ListU.SelectedValue;

            DataTable dtC = new DataTable();
            daCount.Fill(dtC);
            DataRow firstRow = dtC.Rows[0];

            string x = firstRow["iCount"].ToString();

            int y = 0;
            if(Int32.TryParse(x,out y))
            {      
                System.Diagnostics.Debug.WriteLine("iCount was an valid int32");      
                int z = y + 1;

                //SqlCeCommand cmdC = conn.CreateCommand();
                SqlCommand cmdC = conn.CreateCommand();
                cmdC.CommandText = "Update ComDet set iCount = " + z + ", ViewTime = '" + lblTime.Text + "', LastView = '" + txtUser2.Text + "' Where cName = '" + ListU.SelectedValue.ToString() + "'";
             }
            else
                System.Diagnostics.Debug.WriteLine("iCount was NOT a valid int32, value: " + x);
             conn.Close();
}

edited Dec 30, 2013 at 18:07

answered Dec 30, 2013 at 17:32

DROP TABLE users

1,95514 silver badges26 bronze badges

8 Comments

Mohd Nasrul Iwan Fajaruddin Over a year ago

the code runs , but it didnt update anything.. the icount didnt increase , also the viewtime and the lastview.. but there is no error now..

DROP TABLE users Over a year ago

@MohdNasrulIwanFajaruddin ok, that means that the iCount that is coming back from the database is not able to be parsed into an Int32 type, do you know what that value is?

Mohd Nasrul Iwan Fajaruddin Over a year ago

in the database , iCount is 0 , so when i put iCount into X , the Y should parse the X into int. then Z should do Y+1.

DROP TABLE users Over a year ago

@MohdNasrulIwanFajaruddin is it possible that the iCount value contains a decimal 0.00 or any other chars besides digits?

Mohd Nasrul Iwan Fajaruddin Over a year ago

nope , from the design , it is set as INT , and the current value in the database is 0

|

Paulo Correia · Accepted Answer · 2013-12-30 17:31:55Z

0

Have you checked the value of the 'x' variable? The exception informs that the value of X isn't a valid integer, so the FormatException is thrown.

answered Dec 30, 2013 at 17:31

Paulo Correia

6165 silver badges15 bronze badges

4 Comments

Mohd Nasrul Iwan Fajaruddin Over a year ago

how do i check the value?

Paulo Correia Over a year ago

add a breakpoint in the line 65 (where you have the Int32.Parse(x)), and then do a mouse-over the variable x, it should show the value that the variable has.

Mohd Nasrul Iwan Fajaruddin Over a year ago

it says x is null , though the value of iCount is 0 , which is correct in the database

Paulo Correia Over a year ago

The machine is (everytime) right. If x is null, is because your query is returning null. Have you tried your query directly against SQL Server? How many rows it return?

Collectives™ on Stack Overflow

Updating Database on button click

3 Answers 3

16 Comments

8 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

16 Comments

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related