Problem in UPDATE statement

[chdboy]

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.

[daniel955]

First of all try to enclose the code with a try catch. for better error handling.

Try
    'Your code
Catch ex as Exception
    Msgbox("An error has occured. " & ex.Message)
End Try

[G_Waddell]

Hi,

First of all I would not use firstname to identify the record. Say you have 3 people in your database John Brown, John Green and John Smith and you want to update the John Green record you will end up with 3 identical John Green records. Give your employee table a primary key e.g. EmployeeID and place this as a column in the datagridview. (If you don't want the users to see it, you can set its visible value to false i.e. mygrid.columns(0).visible=false)
Now you can use this in your where clause and only the employee you want is updated.

Also you should probably add the items in the where clause as parameters too for consistancy.

What database are you using? If it is SQL Server you should run a SQL profile to see exactly what is being run on the database when you update the grid.

[chdboy]

Yes I'm using SQL Express edition,How exactly should I run a SQL profile to see what is being run on the database when I update the DataGridView?

[chdboy]

I have make changes in my SQL String

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 EmployeeID = " & DGV2.CurrentRow.Cells(0).Value() & " "

But it only updates the first record in the datagridview,but I have selected the second record for edit.EmployeeID has an PK Key assigned to it.

[chdboy]

I'm still stuck on this Update statememnt :(

[G_Waddell]

Hi
How are you using the Update? Are you specifying the Update as the updagte command on the datasource? Or are you doing it in reponse to a button click or something? Could you show Us the code you are using to fire the update event?

Sorry unfortunately SQL Profiler doesn't come with the Express edition of SQL :(

datagridview.SelectedRows will given you a collection of the rows that are selected on the datagridview so if you just want to update the selected rows you'd do something like this:

Dim DGVRow as DatagridviewRow
Dim EmployeeID as integer


For each DGVRow in MyDataGridView.SelectedRows
    EmployeeID = DGRow.Cells(0).value

next

BUT Looking at your code it seams to me your edit is not actually being done in the grid but on textboxes?

In which case, Why not have a hidden employeeID textbox that you populate with the employee Id of the selected row you are editing? Run your update statement using what you know is the correct ID form the textbox and remember to reload the grid.

[chdboy]

Here is the Update Function I made

 Private Function Update_data()
        Try
            Dim fstream As FileStream
            Dim imgdata As Byte()
            Dim data As Byte()
            Dim finfo As FileInfo
            finfo = New FileInfo(Txtfilepath1.Text)
            Dim numbyte As Long
            Dim br As BinaryReader
            numbyte = finfo.Length
            fstream = New FileStream(Txtfilepath1.Text, FileMode.Open, FileAccess.Read)
            br = New BinaryReader(fstream)
            data = br.ReadBytes(numbyte)
            imgdata = data
            Dim img2 As Double
            img2 = calculate_size(finfo)
            If img2 <= 35 Then
                MessageBox.Show("This is a correct file size")
                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 where EmployerID = '" & DGV2.CurrentRow.Cells(0).Value() & "'"
                '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)
                'Dim update As New SqlCommand("Select * from Employee", con)
                'Dim update As New SqlCommand("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 Firstname = " & DGV2.CurrentRow.Cells(1).Value() & "", con)
                '" & TextBox1.Text & "'"
                'update = New SqlCommand(str, 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()
                DGV2.DataSource = DGVload2()
                Clear1()
                'End Using
            Else
                MessageBox.Show("This file >" & img2 & " is more than 35 Kb in Size", "FILE SIZE ERROR")
                Txtfilepath1.Clear()
                Txtfilename.Clear()
                Txtfilepath1.Focus()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Return True
    End Function

And here is the Code on Update Button

Private Sub BtnUpdae_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        Update_data()
        Clear1()
        DGV2.DataSource = DGVload2()
    End Sub

And yes I have used textboxes to Edit the data, and Form1 where the initial data is saved, and to show the data in the form1 I used datagridview,and from there if you click on one row it opens up in another form2 and the data inside the textboxes to edit the selected row.The form2 also have a datagridview.

[G_Waddell]

WOW thats a lot of code... Your opening file stream your runnning updates the lot... Not sure why your opening a file stream though.

Anyway i'll give you a basic break down of what I would do:

On my form have the datagrid with a column for each field in the Employee table hiding any columns of fields I didn't want the users to change. I'd have the edit mode set to edit programatically and Multiselect to False.

Also On my form I'd put a hidden textbox for the EmployeeID and textboxes for any Employee Fields that the user can update.

Then I'd add an edit button when the user hits the button, the routine checks for a selected row in the datagrid - it then takes the field values from the selected row (selectedrows(0)) and populates the textboxes including the the employee ID value to the hidden employee ID box

Next Id have a save changes button and this would carryout the actual update. It would read the values from the textboxes and place them into my update statement ensuring the Where clause in the query got the employee ID value from the hidden textbox (not Datagrid) It would then reload the changed data into the datagrid.

[Begginnerdev]

You might also want to look into using a data adapter do reduce the amound of code you have.

For example:

Private Sub UpdateData()
    'You can place your select statment here...
    'Don't worry about selecting certian columns... you will only edit the ones you want in the code below.

    Dim da As New OleDB.OleDBDataAdapter(New OleDB.OleDBCommand("SELECT * FROM MyTable WHERE Column=Value",myConnection))
    Dim ds As New DataSet

    Try
        'If your connection is not open, do so before calling this.
        da.Fill(ds,"MyTable")

        If IsNothing(ds.Tables("MyTable")) = False Then
            'If you create a new row - you call insert, if you modfiy an existing row - call update.
            Dim dr As DataRow = ds.Tables("MyTable").Rows.Add

            With dr
                'Place your statements here.
                'Example:
                dr("Column1") = TextBox1.Text
            End With

            'If Update
            da.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand
            'If Insert
            da.InsertCommand = New OleDb.OleDbCommandBuilder(da).GetInsertCommand
            'NOTE - A PRIMARY KEY MUST BE PRESENT IN THE TABLE.

            da.Update(ds.Tables("MyTable"))               
        Else
            MsgBox("Could not retreive table data!" & vbcrlf & "Process aborted!")
            Exit Sub
        End If

    Catch ex As Exception
        MsgBox(ex.ToString())
    Finally
        'Can close here if needed/wanted
        myConnection.Close()
        da.Dispose()
        ds.Dispose()
    End Try
End Sub

[chdboy]

Can I use Adapters and datasets with datagridview for saving data into the database?

[Begginnerdev]

Most definitely.

See this.

[chdboy]

 Private Sub UpdateData()
        Dim fstream As FileStream
        Dim imgdata As Byte()
        Dim data As Byte()
        Dim finfo As FileInfo
        finfo = New FileInfo(Txtfilepath1.Text)
        Dim numbyte As Long
        Dim br As BinaryReader
        numbyte = finfo.Length
        fstream = New FileStream(Txtfilepath1.Text, FileMode.Open, FileAccess.Read)
        br = New BinaryReader(fstream)
        data = br.ReadBytes(numbyte)
        imgdata = data
        Dim img2 As Double
        img2 = calculate_size(finfo)
        If img2 <= 35 Then
            MessageBox.Show("This is a correct file size")
            Dim con = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersDanialdocumentsvisual studio 2010ProjectsESI_PF_Payroll_V1ESI_PF_Payroll_V1Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
            con.Open()
            'Don't worry about selecting certian columns... you will only edit the ones you want in the code below.
            Dim da As New SqlDataAdapter(New SqlCommand("SELECT * FROM Employee WHERE EmployerID=1", con))
            Dim ds As New DataSet
            Try
                'If your connection is not open, do so before calling this.
                da.Fill(ds, "Employee")
                If IsNothing(ds.Tables("Employee")) = False Then
                    'If you create a new row - you call insert, if you modfiy an existing row - call update.
                    Dim dr As DataRow = ds.Tables("Employee").Rows.Add
                    With dr
                        'Place your statements here.
                        'Example:
                        dr("Firstname") = TextBox1.Text
                        dr("Lastname") = TextBox2.Text
                        dr("Fathername") = TextBox3.Text
                        dr("Nominename") = TextBox4.Text
                        dr("Gender") = TextBox5.Text
                        dr("Address") = TextBox6.Text
                        dr("Pincode") = TextBox7.Text
                        dr("Contactnumber") = TextBox8.Text
                        dr("DOB") = DateTimePicker1.Text
                        dr("City") = TextBox9.Text
                        dr("Bankdetails") = TextBox10.Text
                        dr("Companyname") = TextBox11.Text
                        dr("Designation") = TextBox12.Text
                        dr("ESINO") = TextBox13.Text
                        dr("PFNO") = TextBox14.Text
                        dr("Basicsalary") = TextBox15.Text
                        dr("DOJ") = DateTimePicker2.Text
                        dr("DOL") = DateTimePicker3.Text
                        dr("HRA") = TextBox16.Text
                        dr("BasicofPF") = TextBox17.Text
                        dr("Conv") = TextBox18.Text
                        dr("Loanamount") = TextBox19.Text
                        dr("Bonus") = Txtbonus.Text
                        dr("Otherded") = TextBox21.Text
                        dr("Imagedata") = imgdata
                        dr("Imagepath") = Txtfilepath1.Text
                        dr("Imagename") = Txtfilename.Text
                        dr("Grosssalary") = TxtGross.Text
                        dr("Remark") = RTB1.Text
                    End With
                    'If Update
                    da.UpdateCommand = New SqlClient.SqlCommandBuilder(da).GetUpdateCommand
                    'If Insert
                    'da.InsertCommand = New SqlCommandBuilder(da).GetInsertCommand
                    'NOTE - A PRIMARY KEY MUST BE PRESENT IN THE TABLE.
                    da.Update(ds.Tables("Employee"))
                Else
                    MsgBox("Could not retreive table data!" & vbCrLf & "Process aborted!")
                    Exit Sub
                End If

            Catch ex As Exception
                MsgBox(ex.ToString())
            Finally
                'Can close here if needed/wanted
                con.Close()
                da.Dispose()
                ds.Dispose()
            End Try
        End If
    End Sub

[G_Waddell]

Hi That error you have looks like you are passing a parameter into SQL that is of the wrong type or length...

SQL will try to convert but may not be able to do it e.g. passing in a string into a date if SQL can convert it then there is no error but if it can't you get that message.

[chdboy]

Yes but there is no field that exceeds the length other than discribed in the sql database ,for E,g,;My firstname is nvarchar(50) ,and I'm passing Danial to it ,no more letters and no spaces also in the textbox,and that does for other textboxes also ...no more letters or numerics that has been assigned to that perticular field.

Is there any other method to update my data then?

[chdboy]

I have made and Textbox(hidden)in my form and assigned my PK value to it and based on that,I have edited my where clause with Fuction I made.Now it works fine.