14 Replies - 10582 Views - Last Post: 16 March 2013 - 04:51 AM Rate Topic: ***** 1 Votes

#1 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Update database from datagridview in VB.NET

Posted 16 March 2013 - 02:46 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Update database from datagridview in VB.NET

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:01 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:12 AM

View Postandrewsw, 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..
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:16 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:19 AM

View Postandrewsw, 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.. :(/>
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:46 AM

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).
Was This Post Helpful? 1
  • +
  • -

#7 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 03:55 AM

Oh GOD.. now my table updated. But all the rows are same value in the table.. Why like this???
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:15 AM

View Postthanzeem7, 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.
Was This Post Helpful? 0
  • +
  • -

#9 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:16 AM

View Postandrewsw, on 16 March 2013 - 04:11 AM, said:

View Postthanzeem7, 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.
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:18 AM

Did they all originally have the same date?
Was This Post Helpful? 0
  • +
  • -

#11 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:20 AM

View PostCharlieMay, on 16 March 2013 - 04:18 AM, said:

Did they all originally have the same date?


Yea...
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:22 AM

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

Was This Post Helpful? 0
  • +
  • -

#13 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:27 AM

View PostCharlieMay, 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..

Posted Image
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:50 AM

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.
Was This Post Helpful? 1
  • +
  • -

#15 thanzeem7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 14-January 13

Re: Update database from datagridview in VB.NET

Posted 16 March 2013 - 04:51 AM

MY DEAR FRIENDS FINALLY I SOLVE THIS ISSUE... I JUST ADD ONE MORE CONDITION IN WHERE CLAUSE THANKS FOR SUPPORTING ME :bananaman: :clap:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1