data not updated in sql after doing some changes in the datagridview

[matt_8]

hello everyone, im pretty new in this language (vb.net) so i have difficulties understanding its function, right now i am able to populate my database to datagridview,
i've been looking for some related answers. but found no luck. so i decided to post it in here.

so i have this problem..

    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;"

        cmdconn = New SqlConnection
        cmd = New SqlCommand
        cmdconn.ConnectionString = connstr 'sqlstr
        cmd.Connection = cmdconn
        cmdconn.Open()

        Dim period, VOUCH_AMT, INDIVIDUAL_AMT, check_no, D_MAILED, DIR_NO, who_updated As String
        For i As Integer = 0 To Me.DataGridView2.Rows.Count - 1



            With Me.DataGridView2.Rows(i)

                If IsDBNull(.Cells(0).Value()) OrElse .Cells(0).Value() Is Nothing OrElse .Cells(0).Value().ToString().Trim() = "" Then
                    period = ""
                Else
                    period = .Cells(0).Value()
                End If
                If IsDBNull(.Cells(1).Value()) OrElse .Cells(1).Value() Is Nothing OrElse .Cells(1).Value().ToString().Trim() = "" Then
                    VOUCH_AMT = "0"
                Else
                    VOUCH_AMT = .Cells(1).Value()
                End If
                If IsDBNull(.Cells(2).Value()) OrElse .Cells(2).Value() Is Nothing OrElse .Cells(2).Value().ToString().Trim() = "" Then
                    INDIVIDUAL_AMT = "0"
                Else
                    INDIVIDUAL_AMT = .Cells(2).Value()
                End If
                If IsDBNull(.Cells(3).Value()) OrElse .Cells(3).Value() Is Nothing OrElse .Cells(3).Value().ToString().Trim() = "" Then
                    check_no = ""
                Else
                    check_no = .Cells(3).Value()
                End If
                If IsDBNull(.Cells(4).Value()) OrElse .Cells(4).Value() Is Nothing OrElse .Cells(4).Value().ToString().Trim() = "" Then
                    D_MAILED = ""
                Else
                    D_MAILED = .Cells(4).Value()
                End If
                If IsDBNull(.Cells(5).Value()) OrElse .Cells(5).Value() Is Nothing OrElse .Cells(5).Value().ToString().Trim() = "" Then
                    DIR_NO = ""
                Else
                    DIR_NO = .Cells(5).Value()
                End If
                If IsDBNull(.Cells(6).Value()) OrElse .Cells(6).Value() Is Nothing OrElse .Cells(6).Value().ToString().Trim() = "" Then
                    who_updated = ""
                Else
                    who_updated = .Cells(6).Value()
                End If


            End With

            cmd.CommandText = "insert into tobee.EBD_BILLHISTORY(period, vouch_amt, individual_amt, check_no, d_mailed, dir_no, who_updated)values" & _
                "('" & period.Replace("'", "''") & "'," & VOUCH_AMT & "," & INDIVIDUAL_AMT & ",'" & check_no.Replace("'", "''") & "','" & D_MAILED.Replace("'", "''") & "', '" & DIR_NO.Replace("'", "''") & "','" & who_updated.Replace("'", "''") & "')"
            cmd.ExecuteNonQuery()
            MsgBox("Saved")
        Next
        cmdconn.Close()

    End Sub

all code works fine up to end of the process.
however, the changes that i've made in the datagridview
was not being reflected in the database.
based from the code above, what i wanna do is to insert a new row.
is there something that im missing? or is there any other way to manipulate
my data in vb.net (by means of dgv or listview(im not pretty familiar with listview) )

and 1 thing i want to add, how can i insert data, and update the table in 1 command button, i believe that the code above is only intended for inserting new data (if im correct )(how about adding a new row, and editing the current row.. that is what im trying to do now, but dont have any idea how to do it for now..) pls help me as it will make progress to my current school project. ty.in advance

[G_Waddell]

Hi,

How are you initially populating the datagrid? Are you using a DataAdapter?
If so you can specify the Update method for it as shown on the link I have provided.

As for your code, because you are "firing off" the command with an execute non query and then changing the command each time the loop runs, you will have to redeclare the command each time you go through the loop:

 Dim ConnStr as string ="Your connections string"
 Dim cmdConn as sqlconnection = new sqlconnection
 cmdConn.ConnectionString = ConnStr
 Dim cmd as sqlcommand
'dim your period stuff -note I haven't actually done anything with the command
'Now your loop 
For i as integer = 0 to datagridview2.rows.count-1
    'do your thing with the rows and columns
    'check your coinnection is open
    if cmdConn.State <> Open then cmdConn.open        
    'Now set your command up
    cmd = new sqlcommand()
    cmd.CommandText = "Your command text - I'm not typing all that"
    cmd.Connection = cmdConn
    cmd.executenonquery
    cmd.dispose

 Next

[matt_8]

thank you for the answer.. im using data adapter to pull that table into the datagrid. here's how it looks like.

 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, who_updated, year_student 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.DataGridView2.DataSource = dt
        cmdconn.Close()

    End Sub

[matt_8]

now, what i'm gonna do next?

[G_Waddell]

Well,

You could put the example I've given you into your save button click event.. OR
look at the update method for datadaptor in the link I gave you.