CODEWITHSUNDEEP
sqlvb.netdatagridview
Newbie matt
Newbie matt

Reputation: 59

Update Command in Datagridview to database

i've been working so far with my database issue, i can't proceed with a new module for the reason that i can't update my database through datagridview

can somebody check on my codes why i can't update my database in datagridview using a button click?

here's how i had my datagrid 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, who_updated, claim_no,  year_student from tobee.EBD_BILLHISTORY where CLAIM_NO like '" + txtClaimno.Text + "'"
    'cmd.CommandText = "select  a.period, a.VOUCH_AMT, a.INDIVIDUAL_AMT, a.check_no, a.bal_ent, a.D_MAILED, a.DIR_NO from tobee.EBD_BILLHISTORY a left join carlos.claims_waivee b on a.CLAIM_NO = b.claim_no where b.CLAIM_NO like '" + claimno.ToString + "'"
    Dim dt As New DataTable
    'Dim ds As New DataSet
    da = New SqlDataAdapter
    da.SelectCommand = cmd
    da.Fill(dt)
    Me.DataGridView1.DataSource = dt
    Me.DataGridView2.DataSource = dt
    cmdconn.Close()
End Sub

now i want to modify some rows within that datagridview and update changes that i made into database with a buttonclick

here's my button click code:

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;"
    Dim cmd2 As New SqlCommand

    Try
        Using connection As New SqlConnection(connstr)
            connection.Open()
            cmd2.CommandText = "update tobee.EBD_BILLHISTORY set period=@period, vouch_amt=@vouch_amt, individual_amt=@individual_amt, check_no=@check_no, d_mailed=@d_mailed, dir_no=@dir_no, who_updated=@who_updated where claim_no = '" + txtClaimno.Text + "' "

            cmd2.Parameters.Add("@period", SqlDbType.Char, 25, "period")
            cmd2.Parameters.Add(New SqlParameter("@vouch_amt", SqlDbType.Decimal) With {.Precision = 18, .Scale = 2}).Value = "vouch_amt"
            cmd.Parameters.Add(New SqlParameter("@individual_amt", SqlDbType.Decimal) With {.Precision = 18, .Scale = 2}).Value = "individual_amt"

            cmd2.Parameters.Add("@check_no", SqlDbType.Char, 15, "check_no")
            'cmd2.Parameters.Add("@d_mailed", SqlDbType.DateTime, "d_mailed")
            cmd2.Parameters.Add("@dir_no", SqlDbType.Char, 15, "DIR_NO")
            cmd2.Parameters.Add("@who_updated", SqlDbType.Char, 15, "who_updated")
            cmd2.ExecuteNonQuery()
            connection.Close()
            MsgBox("changes done")
            'MessageBox.Show("Updated Successfully!", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End Using
    Catch ex As Exception
    msgbox(ex.message)
    End Try

End Sub

the problem that occur "executenonquery : connection property has not been initialized" what could be the problem of mybutton save?(i don't know if im doing things right, did i connect these two "subs" correcty?) if you have any other suggestion on (updating) the changes i made in the datagridview pls. feel free to modify the save button :(

Upvotes: 0

Views: 1505

Answers (1)

har07
har07

Reputation: 89295

It seems that you forgot to set Connection property of cmd2. Simply add this line after connection.Open() :

cmd2.Connection = connection

UPDATE :

You can try using DataAdapter's Update() function passing DataTable as parameter to commit changes from DataTable to database :

da.Update(dt);

[MSDN Reference]

Upvotes: 1

Related Questions