Click here to Skip to main content
11,647,170 members (68,352 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET MS-Access
my INSERT and DELETE command is working but UPDATE command is not. whats wrong.. Frown | :(
 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
 
        'INSERT record
        Connect()
 
        sql = "SELECT (sname) FROM tblsample WHERE sname = @sname"
        cmd = New OleDbCommand(sql, conn)
 
        cmd.Parameters.Add(New OleDbParameter("@sname", OleDbType.VarChar, 255)).Value = TextBox1.Text
 
        Dim rec = cmd.ExecuteScalar
 
        If rec = TextBox1.Text Then
            MessageBox.Show("Name already exist.", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
 
        conn.Close()
        conn.Dispose()
        cmd.Dispose()
 
        Try
 
            Connect()
 
            Trans = conn.BeginTransaction
 
            sql = "INSERT INTO tblsample(sname,contact) VALUES(@sname,@contact)"
 
            cmd = New OleDbCommand(sql, conn, Trans)
 
            cmd.Parameters.Add(New OleDbParameter("@sname", OleDbType.VarChar, 255)).Value = TextBox1.Text
 
            cmd.Parameters.Add(New OleDbParameter("@contact", OleDbType.VarChar, 255)).Value = TextBox2.Text
 
            cmd.ExecuteNonQuery()
 
            Trans.Commit()
 
            conn.Close()
            conn.Dispose()
            cmd.Dispose()
 
            MessageBox.Show("Saved.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Call loadrec()
        Catch ex As Exception
            Trans.Rollback()
            MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
 
    End Sub
 
 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
 
        'DELETE record
        Try
            Connect()
 
            Trans = conn.BeginTransaction
 
            sql = "DELETE FROM tblsample WHERE ID = @ID"
 
            cmd = New OleDbCommand(sql, conn, Trans)
 
            cmd.Parameters.Add(New OleDbParameter("@ID", OleDbType.Integer)).Value = DataGridView1.SelectedCells(0).Value
 
            cmd.ExecuteNonQuery()
 
            Trans.Commit()
 
            conn.Close()
            conn.Dispose()
            cmd.Dispose()
 
            Call loadrec()
 
            MessageBox.Show("DELETED!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
        Catch ex As OleDb.OleDbException
            Trans.Rollback()
            MessageBox.Show(ex.Message, "DATABASE ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex As Exception
            Trans.Rollback()
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
 
 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
 
        'UPDATE record
        Try
            Connect()
 
            Trans = conn.BeginTransaction
 
            sql = "UPDATE tblsample SET sname = @sname,contact = @contact WHERE contact = @ID"
 
            cmd = New OleDbCommand(sql, conn, Trans)
 
            cmd.Parameters.Add(New OleDbParameter("@sname", OleDbType.VarChar, 255)).Value = TextBox1.Text
            cmd.Parameters.Add(New OleDbParameter("@contact", OleDbType.VarChar, 255)).Value = TextBox2.Text
            cmd.Parameters.Add(New OleDbParameter("@ID", OleDbType.Integer)).Value = DataGridView1.SelectedCells(0).Value
 
            cmd.ExecuteNonQuery()
 
            Trans.Commit()
 
            conn.Close()
            conn.Dispose()
            cmd.Dispose()
            Call loadrec()
            MessageBox.Show("UPDATED!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
        Catch ex As Exception
            Trans.Rollback()
            MessageBox.Show(ex.Message)
        End Try
 
    End Sub
Posted 12-Aug-12 8:47am
Edited 12-Aug-12 10:23am
v3
Comments
Sandeep Mewara at 12-Aug-12 15:12pm
   
1. Do you get any error? If so, please share.
2. What do you see when you DEBUG?
bluecondor7 at 12-Aug-12 15:29pm
   
there is no error. the code will continue and saying that the record is updated.
bluecondor7 at 12-Aug-12 16:26pm
   
sorry sir, i put wrong question, i mean is the UPDATE command, not the DELETE command. i updated my question now. sorry
Philip Stuyck at 12-Aug-12 17:18pm
   
is this a sql server database ? Because not all databases support named parameters. Most expect ? iso @paramname.
bluecondor7 at 12-Aug-12 20:05pm
   
sir, it's ms access database.
Philip Stuyck at 13-Aug-12 1:47am
   
updated the soluton accordingly
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If nothing is deleted it must be because your Id is wrong. Are you sure the parameter is filled in correctly and that this value actually exists.
ExecuteQuery has a return value with the number of updates that have been done. Very usefull information during debugging!

Also check your sql syntax, but normally if it is wrong exceptions should be thrown. Since this is ms access your named parameters don't work, this only works in sql server. Better read this link too :
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access[^]

Please mark this as a solution if this solves your problem.
  Permalink  
v2
Comments
bluecondor7 at 12-Aug-12 16:26pm
   
sorry sir, i put wrong question, i mean is the UPDATE command, not the DELETE command. i updated my question now. sorry
Philip Stuyck at 12-Aug-12 17:25pm
   
well, my comment still applies.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Please check the following line while debbuging, are you getting any value in DataGridView1.SelectedCells(0).Value.

cmd.Parameters.Add(New OleDbParameter("@ID", OleDbType.Integer)).Value = DataGridView1.SelectedCells(0).Value

Thanks
Ashish
  Permalink  
Comments
singhritu at 13-Aug-12 12:13pm
   
my 5!
Rahul Rajat Singh at 14-Aug-12 0:44am
   
good answer. +5.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

thank you very much to you all.. problem solved with the help of your suggestions and advice. i change the cmd.parameters.add ...etc. in correct order based in the order of my fields, then it works. thanks!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

There will be problem only in this line
sql = "DELETE FROM tblsample WHERE ID = @ID"
check the @ID value whether you input it rightly or exist correctly in your database..
  Permalink  
Comments
bluecondor7 at 12-Aug-12 16:26pm
   
sorry sir, i put wrong question, i mean is the UPDATE command, not the DELETE command. i updated my question now. sorry
ridoy at 13-Aug-12 3:21am
   
There will be only problem in here "WHERE contact = @ID" of your update statement.Check your @ID value of your database.Update can fail here if you use such type of value that doesn't exist in your database.So carefully look out this point.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Check your update query carefully:
sql = "UPDATE tblsample SET sname = @sname,contact = @contact WHERE contact = @ID"
Quote:
contact = @contact
WHERE
contact = @ID

You are updating the same ID field which you are providing as condition. Check your table, otherwise use this:
sql = "UPDATE tblsample SET sname = @sname,contact = @contact WHERE ID = @ID"


--Amit
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 jyo.net 500
1 F-ES Sitecore 370
2 DamithSL 335
3 OriginalGriff 322
4 CPallini 290
0 OriginalGriff 1,277
1 jyo.net 994
2 DamithSL 946
3 Sergey Alexandrovich Kryukov 853
4 CPallini 760


Advertise | Privacy | Mobile
Web01 | 2.8.150804.2 | Last Updated 13 Aug 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100