Delete By Id

bfsog

Well-known member
Joined
Apr 21, 2005
Messages
50
Location
UK
Programming Experience
5-10
Hi, I have a table with a column called id, which is an integer.

I am trying to delete a record by passing in the id.

I get an exception error.

VB.NET:
		Dim dst As New DataSet

		If txtIdNum.TextLength > 0 Then
			Dim id As Integer = CInt(txtIdNum.Text)

		' start of database code here
		    Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\mydb.mdb"
			Me.OleDbConnection1.Open() ' open connection to database
		    Me.OleDbSelectCommand1.CommandText = "SELECT * FROM users WHERE id = '" & txtIdNum.Text & "' LIMIT 1"
			Me.OleDbSelectCommand1.Connection = OleDbConnection1
			OleDbDataAdapter1.Fill(dst, "Users") ' exception happens here
			If dst.Tables("Users").Rows.Count = 1 Then
			    Me.OleDbDeleteCommand1.CommandText = "DELETE FROM users WHERE id = '" & txtIdNum.Text & "' LIMIT 1"
			    Me.OleDbInsertCommand1.Connection = OleDbConnection1
			    Me.OleDbInsertCommand1.ExecuteNonQuery()
				OleDbDataAdapter1.Fill(dst, "Users")

			    If dst.Tables("Users").Rows.Count = 1 Then
				    MessageBox.Show("User with id: " & txtIdNum.Text & " has been deleted")
				Else
				    MessageBox.Show("User with id: " & txtIdNum.Text & " does not exist", "AutoTime: error")
				    txtIdNum.Focus()
				End If

			End If

i am trying to check that that id exists first, and if it is delete that user..?

Any help, greatly appreciated.
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
try this:
"SELECT * FROM users WHERE id = " & txtIdNum.Text & ""
instead
"SELECT * FROM users WHERE id = '" & txtIdNum.Text & "' LIMIT 1"

sorry if i'm wrong but i did not consider it in details (your entire code) but that's what i could catch on first side and that is: you don't need single quotation if you deal with number datatype and you don't need LIMIT as you retrieve single record anyway.

Cheers ;)
 
Last edited:

bfsog

Well-known member
Joined
Apr 21, 2005
Messages
50
Location
UK
Programming Experience
5-10
Yeah that annoys me too. However, your solution did not work correctly, so I was going to tweak it a bit and then report my findings.

Well, I am still getting an error.. The error I get = http://bfsog.co.uk/imgupload/uploads/errormsg.jpg

My code (shortened version)

VB.NET:
dim id As integer = txtIdNum.Text
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM users WHERE id = '" & id & "'"
			MessageBox.Show(Me.OleDbDeleteCommand1.CommandText)
				OleDbDeleteCommand1.Connection = OleDbConnection1
				OleDbDeleteCommand1.ExecuteNonQuery()
				OleDbDataAdapter1.Fill(dst, "Users")
			If dst.Tables("Users").Rows.Count = 1 Then
				MessageBox.Show("User deleted", "AutoTime: confirmation")
			End If

It has worked before which is odd, sometimes it works but I get the error.

Thanks for any help given
 

bfsog

Well-known member
Joined
Apr 21, 2005
Messages
50
Location
UK
Programming Experience
5-10
Yep id is type integer.

It does delete the correct record. Thanks!

But... I get my error message that should be displayed when no record is deleted..

VB.NET:
		If dst.Tables("Users").Rows.Count = 1 Then 
				MessageBox.Show("User deleted", "AutoTime: confirmation")
			Else
				MessageBox.Show("No user deleted", "AutoTime: error") ' i get this message
			End If

but thanks, il try and figure this out!
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
You could try this to see if the row was deleted
VB.NET:
[size=2][color=#0000ff]Dim[/color][/size][size=2] SQLreturn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer
[size=2]SQLreturn = OleDbDeleteCommand1.ExecuteNonQuery
[size=2][color=#0000ff]
If[/color][/size][size=2][color=#000000] SQLreturn <> 1 [/color][/size][size=2][color=#0000ff]Then
[/color][/size][size=2]MessageBox.Show("No user deleted", "AutoTime: error")
[/size][size=2][color=#0000ff]Else
[/color][/size][size=2]MessageBox.Show("User deleted", "AutoTime: confirmation")
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]
[/color][/size]

It might work
[/size][/color][/size]
 
Top Bottom