how to update, insert records through datagridview to sql using data adapter or other method in vb.net

Question

hello best programmers in the world i need your expert advise and assistance with regards to my project. I am trying to insert and update my table through datagridview by clicking a command button, i have my datagridview properties set to editmode : editprogrammatically,

here's the code, of where i pulled up my datagridview content:

Public Sub loaddgvfrm3()
    cmdconn = New SqlConnection
    cmd = New SqlCommand
    cmdconn.ConnectionString = sqlstr
    cmdconn.Open()
    cmd.Connection = cmdconn
    cmd.CommandText = "select  period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED,           DIR_NO from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + txtClaimno.Text + "'"


    Dim dt As New DataTable
    da = New SqlDataAdapter
    da.SelectCommand = cmd
    da.Fill(dt)
    Me.DataGridView1.DataSource = dt
    Me.DataGridView2.DataSource = dt
    cmdconn.Close()

End Sub

now i have my command buttons here here's the add button: (im prefering to add a row within the selected table)

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click

End Sub

here's the Edit button:

Private Sub btnEditmain_Click(sender As Object, e As EventArgs) Handles btnEditmain.Click

    ''Form1.ShowDialog()
    'DataGridView2.AllowUserToAddRows = True
    ''DataGridView2.BeginEdit(True)
    'btnSave.Enabled = True

End Sub and here's the save button that should save all changes that i have done,

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    Dim connstr As String = "server=midtelephone\sqlexpress; database=testdb; user= sa; password=sa;"

End Sub

i left command button contents empty because i need to create a new method of inserting, updating the row. because the one that i have earlier was a fail, although it inserts the data in the sql, but not in its appropriate row, take a look at here: Data inserted from datagridview not updated in SQL Server , instead it creates another row which is not connected with '" + txtClaimno.Text + "'" (stated from above) so what happens is that it stacks a new row with no connected owner from another table(claim_no < this claim_no is connected from another table as a fk in the database but a primary key in (billhistory table)) can you pls help me get rid of this problem as i am having a hard time moving to the next phase of our project? im just a high school student, so pls bear with me :) i'll appreciate if u give comments / answers regarding my question :) thanks in adv.

my mentor advised me to use data adapter accept changes stuff, but i don't know how to implement such stuffs. pls help me thank you :)

Refer this link : codeproject.com/Articles/667599/Add-Edit-update-Delete-gridview — Dilip Suvagiya
oh my.. im quite confused with the syntax for i am not familiar with asp.net :( im really not familiar with Visual studio, i only understand a lil'bit. :( — Newbie matt
I would use xml and grab it I all in one go to the database, I personally like this trade off but others would rather loop through the rows and call a save function each time. — zaggler

AHMAD SUMRAIZ AHMAD SUMRAIZ · Accepted Answer · 2014-02-14T06:42:29

Use Event to get data from gridview on cellConttent Click and these values to a query or Stored procedure.

 private void grdCampaignStats_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

                //I want to get value of SomeColumn of grid view having datatype String 
                string SomeVariabel = grd["CoulmnNameinGRID", e.RowIndex].Value.ToString();

           // Make it according to Vb it is C# code 
        }

how to update, insert records through datagridview to sql using data adapter or other method in vb.net

1 Answers