Delete By Id

[bfsog]

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.

		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]

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

 

[kulrom]

Btw, please let us know do we help you out ... i noted that you don't post anything after your problems are resolved ... it could be very frustrating for the guy who has replied to you ... thanks in advance

Cheers

 

[bfsog]

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)

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

 

[kulrom]

dim id As integer = Val(txtIdNum.Text) 'or put Ctype(txtIdNum.Text, Integer)

now remove single quotes around 'id' as it's probably number (let me know what is the data type of id)

Me.OleDbDeleteCommand1.CommandText = "DELETE FROM users WHERE id = " & id & ""

Cheers

 

[bfsog]

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..

		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]

You could try this to see if the row was deleted

[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]

 

[kulrom]

I think you have wrong approach for that problem ...
Cheers

 

[kulrom]

Yes, levyuk gave you good point ... So, you need to check Whether it's empty table or not .... Cheers



note: Sorry levyuk it seems like we have replied in same time ... and if I saw your post, I probably wouldn't have replied ... cheers