9 Replies - 670 Views - Last Post: 08 February 2013 - 05:19 AM Rate Topic: -----

#1 chdboy  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Problem in UPDATE statement

Posted 03 February 2013 - 08:49 PM

I have problem updating specific record(s) selected to edit from a DataGridView into textboxes ,where the data can be edited and then update to the database.
Now my update statement must have a where clause ,but it's not working,right now the code I'm about to post does updated record(s),but not the specified record in the textboxes to be edited but the whole set of recordes in the table.

Please help me with the where clause in my sql update querry.

here is the code

 Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    con.Open()
    Dim _String As String = "UPDATE Employee SET Firstname = @Firstname, Lastname = @Lastname, Fathername = @Fathername, Nominename = @Nominename, Gender = @Gender, Address = @Address, Pincode = @Pincode, Contactnumber = @Contactnumber, DOB = @DOB, City = @City, Bankdetails = @Bankdetails, Companyname = @Companyname, Designation = @Designation, ESINO = @ESINO, PFNO = @PFNO, Basicsalary = @Basicsalary, DOJ =@DOJ, DOL = @DOL, HRA = @HRA, BasicofPF = @BasicofPF, Conv = @Conv, Loanamount = @Loanamount, Bonus = @Bonus, Otherded = @Otherded, Imagedata = @Imagedata, Imagename = @Imagename, Grosssalary = @Grosssalary, Remark = @Remark "
    'Using update As New SqlCommand("UPDATE Employee SET Firstname,Lastname,Fathername,Nominename,Gender,Address,Pincode,Contactnumber,DOB,City,Bankdetails,Companyname,Designation,ESINO,PFNO,Basicsalary,DOJ,DOL,HRA,BasicofPF,Conv,Loanamount,Bonus,Otherded,Imagedata,Imagename,Imagepath,Grosssalary,Remark) values (@Firstname,@Lastname,@Fathername,@Nominename,@Gender,@Address,@Pincode,@Contactnumber,@DOB,@City,@Bankdetails,@Companyname,@Designation,@ESINO,@PFNO,@Basicsalary,@DOJ,@DOL,@HRA,@BasicofPF,@Conv,@Loanamount,@Bonus,@Otherded,@Imagedata,@Imagename,@Imagepath,@Grosssalary,@Remark where Firstname = " & DGV2.CurrentRow.Cells(1).Value() & "")
    cmd = New SqlCommand(_String, con)
    cmd.Parameters.Add(New SqlParameter("Firstname", SqlDbType.NVarChar, 50))
    cmd.Parameters("Firstname").Value = TextBox1.Text
    cmd.Parameters.Add(New SqlParameter("Lastname", SqlDbType.NVarChar, 50))
    cmd.Parameters("Lastname").Value = TextBox2.Text
    cmd.Parameters.Add(New SqlParameter("Fathername", SqlDbType.NVarChar, 50))
    cmd.Parameters("Fathername").Value = TextBox3.Text
    cmd.Parameters.Add(New SqlParameter("Nominename", SqlDbType.NVarChar, 50))
    cmd.Parameters("Nominename").Value = TextBox4.Text
    cmd.Parameters.Add(New SqlParameter("Gender", SqlDbType.NVarChar, 5))
    cmd.Parameters("Gender").Value = TextBox5.Text
    cmd.Parameters.Add(New SqlParameter("Address", SqlDbType.NVarChar, 50))
    cmd.Parameters("Address").Value = TextBox6.Text
    cmd.Parameters.Add(New SqlParameter("Pincode", SqlDbType.NChar, 10))
    cmd.Parameters("Pincode").Value = TextBox7.Text
    cmd.Parameters.Add(New SqlParameter("Contactnumber", SqlDbType.NChar, 15))
    cmd.Parameters("Contactnumber").Value = TextBox8.Text
    cmd.Parameters.Add(New SqlParameter("DOB", SqlDbType.Date))
    cmd.Parameters("DOB").Value = DateTimePicker1.Text
    cmd.Parameters.Add(New SqlParameter("City", SqlDbType.NChar, 10))
    cmd.Parameters("City").Value = TextBox9.Text
    cmd.Parameters.Add(New SqlParameter("Bankdetails", SqlDbType.NVarChar, 50))
    cmd.Parameters("Bankdetails").Value = TextBox10.Text
    cmd.Parameters.Add(New SqlParameter("Companyname", SqlDbType.NVarChar, 50))
    cmd.Parameters("Companyname").Value = TextBox11.Text
    cmd.Parameters.Add(New SqlParameter("Designation", SqlDbType.NVarChar, 50))
    cmd.Parameters("Designation").Value = TextBox12.Text
    cmd.Parameters.Add(New SqlParameter("ESINO", SqlDbType.NChar, 20))
    cmd.Parameters("ESINO").Value = TextBox13.Text
    cmd.Parameters.Add(New SqlParameter("PFNO", SqlDbType.NChar, 20))
    cmd.Parameters("PFNO").Value = TextBox14.Text
    cmd.Parameters.Add(New SqlParameter("Basicsalary", SqlDbType.NChar, 10))
    cmd.Parameters("Basicsalary").Value = TextBox15.Text
    cmd.Parameters.Add(New SqlParameter("DOJ", SqlDbType.Date))
    cmd.Parameters("DOJ").Value = DateTimePicker2.Text
    cmd.Parameters.Add(New SqlParameter("DOL", SqlDbType.Date))
    cmd.Parameters("DOL").Value = DateTimePicker3.Text
    cmd.Parameters.Add(New SqlParameter("HRA", SqlDbType.NChar, 10))
    cmd.Parameters("HRA").Value = TextBox16.Text
    cmd.Parameters.Add(New SqlParameter("BasicofPF", SqlDbType.NChar, 10))
    cmd.Parameters("BasicofPF").Value = TextBox17.Text
    cmd.Parameters.Add(New SqlParameter("Conv", SqlDbType.NChar, 10))
    cmd.Parameters("Conv").Value = TextBox18.Text
    cmd.Parameters.Add(New SqlParameter("Loanamount", SqlDbType.NVarChar, 20))
    cmd.Parameters("Loanamount").Value = TextBox19.Text
    cmd.Parameters.Add(New SqlParameter("Bonus", SqlDbType.NChar, 10))
    cmd.Parameters("Bonus").Value = Txtbonus.Text
    cmd.Parameters.Add(New SqlParameter("Otherded", SqlDbType.NChar, 10))
    cmd.Parameters("Otherded").Value = TextBox21.Text
    cmd.Parameters.Add(New SqlParameter("Imagedata", SqlDbType.Image))
    cmd.Parameters("Imagedata").Value = imgdata
    cmd.Parameters.Add(New SqlParameter("Imagename", SqlDbType.NChar, 10))
    cmd.Parameters("Imagename").Value = Txtfilename.Text
    cmd.Parameters.Add(New SqlParameter("Imagepath", SqlDbType.NVarChar, 200))
    cmd.Parameters("Imagepath").Value = Txtfilepath1.Text
    cmd.Parameters.Add(New SqlParameter("Grosssalary", SqlDbType.NChar, 10))
    cmd.Parameters("Grosssalary").Value = TxtGross.Text
    cmd.Parameters.Add(New SqlParameter("Remark", SqlDbType.NVarChar, 500))
    cmd.Parameters("Remark").Value = RTB1.Text
    cmd.ExecuteNonQuery()
    MsgBox("Data + Image Updated Into DataBase..", MsgBoxStyle.Information)
    con.Close()




I have tried with the where clause

   Using update As New SqlCommand("UPDATE Employee SET Firstname,Lastname,Fathername,Nominename,Gender,Address,Pincode,Contactnumber,DOB,City,Bankdetails,Companyname,Designation,ESINO,PFNO,Basicsalary,DOJ,DOL,HRA,BasicofPF,Conv,Loanamount,Bonus,Otherded,Imagedata,Imagename,Imagepath,Grosssalary,Remark) values (@Firstname,@Lastname,@Fathername,@Nominename,@Gender,@Address,@Pincode,@Contactnumber,@DOB,@City,@Bankdetails,@Companyname,@Designation,@ESINO,@PFNO,@Basicsalary,@DOJ,@DOL,@HRA,@BasicofPF,@Conv,@Loanamount,@Bonus,@Otherded,@Imagedata,@Imagename,@Imagepath,@Grosssalary,@Remark where Firstname = " & DGV2.CurrentRow.Cells(1).Value() & "")



But it keeps on updated the first row only.

Is This A Good Question/Topic? 0
  • +

Replies To: Problem in UPDATE statement

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3593
  • View blog
  • Posts: 12,364
  • Joined: 12-December 12

Re: Problem in UPDATE statement

Posted 04 February 2013 - 01:53 AM

The statement on line 3 is the correct syntax MSDN, then add the WHERE clause, but the text-value needs to be quoted with apostrophes:

WHERE Firstname = '" & DGV2.CurrentRow.Cells(1).Value() & "'"


However, you should create this as one more parameter; it is safer and you won't need to add the apostrophes.

This post has been edited by andrewsw: 04 February 2013 - 06:23 AM

Was This Post Helpful? 0
  • +
  • -

#3 chdboy  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: Problem in UPDATE statement

Posted 04 February 2013 - 06:08 AM

It's the first row which gets effected ,not the second row which I choose to edit .
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3593
  • View blog
  • Posts: 12,364
  • Joined: 12-December 12

Re: Problem in UPDATE statement

Posted 04 February 2013 - 06:40 AM

View Postchdboy, on 04 February 2013 - 06:08 AM, said:

It's the first row which gets effected ,not the second row which I choose to edit .

Have you corrected the SQL? Assuming that you have, you could step through the code and add cmd to the Watch window, so that you can follow its progress. If you don't know how to do this then you should spend some time learning some basic debugging techniques - time well spent! There are probably tutorials available here at DIC.

Remember also that Cells are zero-indexed, so Cells(0) refers to the first column.

View Postchdboy, on 04 February 2013 - 06:08 AM, said:

It's the first row which gets effected ,not the second row which I choose to edit .

So if you edit the 7th row is it the 6th row that is affected..?

This post has been edited by andrewsw: 04 February 2013 - 06:47 AM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3593
  • View blog
  • Posts: 12,364
  • Joined: 12-December 12

Re: Problem in UPDATE statement

Posted 04 February 2013 - 06:50 AM

Mmm why are you updating records based on their firstname? Where is your primary key?
Was This Post Helpful? 0
  • +
  • -

#6 chdboy  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: Problem in UPDATE statement

Posted 05 February 2013 - 08:59 PM

View Postandrewsw, on 04 February 2013 - 01:40 PM, said:

View Postchdboy, on 04 February 2013 - 06:08 AM, said:

It's the first row which gets effected ,not the second row which I choose to edit .

Have you corrected the SQL? Assuming that you have, you could step through the code and add cmd to the Watch window, so that you can follow its progress. If you don't know how to do this then you should spend some time learning some basic debugging techniques - time well spent! There are probably tutorials available here at DIC.

Remember also that Cells are zero-indexed, so Cells(0) refers to the first column.

View Postchdboy, on 04 February 2013 - 06:08 AM, said:

It's the first row which gets effected ,not the second row which I choose to edit .

So if you edit the 7th row is it the 6th row that is affected..?

I have three rows in my DGV ,and when I click on the second row to edit data in it,the first row gets updated.
Dim _String As String = "UPDATE Employee SET Firstname = @Firstname, Lastname = @Lastname, Fathername = @Fathername, Nominename = @Nominename, Gender = @Gender, Address = @Address, Pincode = @Pincode, Contactnumber = @Contactnumber, DOB = @DOB,  City = @City, Bankdetails = @Bankdetails,  Companyname = @Companyname, Designation = @Designation, ESINO = @ESINO, PFNO = @PFNO, Basicsalary = @Basicsalary, DOJ =@DOJ, DOL = @DOL, HRA = @HRA,  BasicofPF = @BasicofPF,  Conv = @Conv, Loanamount = @Loanamount,  Bonus = @Bonus, Otherded = @Otherded, Imagedata = @Imagedata, Imagename = @Imagename, Grosssalary = @Grosssalary, Remark = @Remark where EmployerID = '" & DGV2.CurrentRow.Cells(0).Value() & "'"

and EmployerID Field has the PK assigned to it.
Is there any thing called Cells or rows selected?
Because no matter what if I use this where clause
DGV2.CurrentRow.Cells(0).Value() & "'"
only the first row gets updated cos of the
Cells(0)
I think.
Was This Post Helpful? 0
  • +
  • -

#7 Guitora Kuronato  Icon User is offline

  • New D.I.C Head

Reputation: -7
  • View blog
  • Posts: 28
  • Joined: 02-December 12

Re: Problem in UPDATE statement

Posted 05 February 2013 - 09:28 PM

Hello,, i sugget you can pick the most correct update sentence at your dataset show at solution explorer, double click your dataset, right click then add new query, and choose select then update,, u just edit from there, then copy and paste it to your form. So, not require that long code again.
Was This Post Helpful? 0
  • +
  • -

#8 chdboy  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: Problem in UPDATE statement

Posted 05 February 2013 - 10:02 PM

I'm updating data from the Datagridview.
Was This Post Helpful? 0
  • +
  • -

#9 Guitora Kuronato  Icon User is offline

  • New D.I.C Head

Reputation: -7
  • View blog
  • Posts: 28
  • Joined: 02-December 12

Re: Problem in UPDATE statement

Posted 05 February 2013 - 10:28 PM

you can try to look for this sample code that write by someone.
There is the datagrid code contain what you want to search
Hope it will be useful
Was This Post Helpful? 0
  • +
  • -

#10 chdboy  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: Problem in UPDATE statement

Posted 08 February 2013 - 05:19 AM

Now I have made an hidden textfield in the edit form and pointing my where clause to that hidden field,now the problem is solved.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1