[thanzeem7]

Update database from datagridview in VB.NET. i have a datagridview in my form. Data read from database to datagridview is working properly. But i cant update the cahanges. I use this code for update the database.

Private Sub BTNUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNUPDATE.Click
    DTPEDITAT.Value = Format(DTPEDITAT.Value, "dd/MM/yyyy")
    Dim query As SqlCommand
    getConnect()
    Try
        Conn.Open()
        Dim id As Integer
        Dim status As Double
        Dim name As String
        Dim ateditdate As String
        Dim remark As String
        ateditdate = DTPEDITAT.Value.ToString()
        For x As Integer = 0 To Me.ATCEDITGRID.Rows.Count - 1
            id = 0
            status = 2
            name = ""
            remark = ""
            id = Me.ATCEDITGRID.Rows(x).Cells(0).Value
            name = Me.ATCEDITGRID.Rows(x).Cells(1).Value
            If Me.ATCEDITGRID.Rows(x).Cells(2).EditedFormattedValue = "Present" Then
                status = 1
            ElseIf Me.ATCEDITGRID.Rows(x).Cells(2).EditedFormattedValue = "Halfday" Then
                status = 0.5
            ElseIf Me.ATCEDITGRID.Rows(x).Cells(2).EditedFormattedValue = "Absent" Then
                status = 0
            Else
                status = 2
            End If
            If Me.ATCEDITGRID.Rows(x).Cells(3).EditedFormattedValue = "" Then
                remark = ""
            Else
                remark = Me.ATCEDITGRID.Rows(x).Cells(3).EditedFormattedValue
            End If
            Dim strSQL As String = "UPDATE ATTENDANCE SET [EMP_ID]=@EMP_ID,[EMP_NAME]=@EMP_NAME,[AT_STATUS]=@AT_STATUS,[AT_REMARK]=@AT_REMARK WHERE AT_DATE='" & ateditdate & "'"
            query = New SqlCommand(strSQL, Conn)
            query.Parameters.AddWithValue("@EMP_ID", id)
            query.Parameters.AddWithValue("@EMP_NAME", name)
            query.Parameters.AddWithValue("@AT_STATUS", status)
            query.Parameters.AddWithValue("@AT_REMARK", remark)
            query.ExecuteNonQuery()
        Next x
        Conn.Close()
        MessageBox.Show("Updated Successfully!", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information)
        BTNCLEAR.PerformClick()
    Catch ex As SqlException
        MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
    End Try
End Sub

The problem is there is no changes in Database. I am using SQL database.

[andrewsw]

You are using Parameters, but not for the AT_DATE. Make this a parameter as well and you can probably set this parameter directly to DTPEDITAT.Value and the problem will probably go away (and you won't need the additional apostrophes around this value either).

The problem you currently have is probably related to the format you are attempting to use for the date-value - it needs to be a format acceptable to whichever database you are using.

If you are executing an UPDATE statement within a loop then you will also need to Clear() the parameters each time.

This post has been edited by andrewsw: 16 March 2013 - 03:02 AM

[thanzeem7]

andrewsw, on 16 March 2013 - 03:01 AM, said:

You are using Parameters, but not for the AT_DATE. Make this a parameter as well and you can probably set this parameter directly to DTPEDITAT.Value and the problem will probably go away (and you won't need the additional apostrophes around this value either).

The problem you currently have is probably related to the format you are attempting to use for the date-value - it needs to be a format acceptable to whichever database you are using.

If you are executing an UPDATE statement within a loop then you will also need to Clear() the parameters each time.

Actually the date is string value.. and can u give me an example for clear(). I am new in VB.NET..

[andrewsw]

query.Parameters.Clear()

If the date is stored in the database as a string data-type then you'll need to ensure that the date-value you supply (as a string) matches exactly.

[thanzeem7]

andrewsw, on 16 March 2013 - 03:16 AM, said:

query.Parameters.Clear()

If the date is stored in the database as a string data-type then you'll need to ensure that the date-value you supply (as a string) matches exactly.

I change my Query like

Dim strSQL As String = "UPDATE ATTENDANCE SET [EMP_ID]=@EMP_ID,[EMP_NAME]=@EMP_NAME,[AT_STATUS]=@AT_STATUS,[AT_REMARK]=@AT_REMARK WHERE [AT_DATE]=@AT_DATE"
                query = New SqlCommand(strSQL, Conn)
                query.Parameters.AddWithValue("@EMP_ID", id)
                query.Parameters.AddWithValue("@EMP_NAME", name)
                query.Parameters.AddWithValue("@AT_DATE", ateditdate)
                query.Parameters.AddWithValue("@AT_STATUS", status)
                query.Parameters.AddWithValue("@AT_REMARK", remark)
                query.ExecuteNonQuery()
                query.Parameters.Clear()

Still same.. />

[andrewsw]

I would test it by printing out id, name, etc., to make sure the are valid values, and use AT_DATE = '2013/03/10' or whatever date value I know to exist in the database.

I would also construct the SQL statement (using the values printed out above) and execute it within the database tools (MSSQL Management Studio or similar).

[thanzeem7]

Oh GOD.. now my table updated. But all the rows are same value in the table.. Why like this???

[andrewsw]

thanzeem7, on 16 March 2013 - 03:55 AM, said:

Oh GOD.. now my table updated. But all the rows are same value in the table.. Why like this???

I assume you kept a copy of the table?

You'll need to explain what has happened in more detail.

Erm, they would be the same value (as the set of values from your last iteration of x) because your only criteria is based on the AT_DATE - which you only set once.

[thanzeem7]

andrewsw, on 16 March 2013 - 04:11 AM, said:

thanzeem7, on 16 March 2013 - 03:55 AM, said:

Oh GOD.. now my table updated. But all the rows are same value in the table.. Why like this???

I assume you kept a copy of the table?

You'll need to explain what has happened in more detail.

I change the WHERE condition in my query to Parameter like [AT_DATE]=@AT_DATE..
After i try to update. All the rows in the table updated to the value of First row.

[CharlieMay]

Did they all originally have the same date?

[thanzeem7]

CharlieMay, on 16 March 2013 - 04:18 AM, said:

Did they all originally have the same date?

Yea...

[CharlieMay]

You told the database to update all rows with that date that contained that date.

This post has been edited by CharlieMay: 16 March 2013 - 04:22 AM

[thanzeem7]

CharlieMay, on 16 March 2013 - 04:22 AM, said:

You told the database to update all rows with that date that contained that date.

Yes..
My table have different ids, names and status. After update my table look like this..

[CharlieMay]

When you update a row(s) the criteria you specify has to make that row (or group of rows) unique. A single date column is probably not going to be a good determining factor for uniqueness in a row.

Do you happen to have a screenshot of the table with how it originally looked before the update (from your backup)?

The AT_ID is a Primary Key and will never be allowed to be the same as any other row as it must remain unique.

Without knowing exactly what your intent was, it would be hard for me to say that you could just update based off the primary key. But, it appears you will need to specify more conditions in your WHERE clause to ensure that you update the row you intended.

[thanzeem7]

MY DEAR FRIENDS FINALLY I SOLVE THIS ISSUE... I JUST ADD ONE MORE CONDITION IN WHERE CLAUSE THANKS FOR SUPPORTING ME