delete rows

Question

what is the code to delete a row from a database, the binding navegater has the delete function, but when i select a record to delete and press the delete box on the navegater i get an error telling me a must have a delelet command. what does theat look like, my database is working fine until i want to delete one.

 

thanks

Answer 1

a delete command looks like this:

"DELETE FROM tableName WHERE [field] = someValue"

which will delete a record on a condition that matches. usually you would delete a record by ID but not always - here is an example:

"DELETE FROM tableName WHERE [ID] = IDValueHere"

to delete ALL records:

"DELETE FROM tableName"

Answer 2

can i itercept the row counter and delete the rowcount

Answer 3

not sure I understand what you mean..can you explain further?

Answer 4

Public Class seeall

Dim see2 As New seeall2

this sub shows you all the records in your dataset, you can see them by clicking the arrow back n forth, if i want to delete one( which i do) then the one that is being viewed is the one selected for deletion.

or do i just empty the box and make it nul

Private Sub seeall_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'MusicDataSet2.records' table. You can move, or remove it, as needed.

Me.RecordsTableAdapter.Fill(Me.MusicDataSet2.records)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim rowsaffected As Integer ' this is just a row counter

Me.Hide()

RecordsBindingSource.EndEdit() ' this will send data back to table adapter the rowsaffected is just my way to see the integer that is returned.

rowsaffected = RecordsTableAdapter.Update(MusicDataSet2.records)' when i get here i get an unhandled exception telling me it needs a delelet command. so im unclear as to what i have missed out or not told it to do

MsgBox(rowsaffected) ' this shows the user how many rows have been affected

 

End Sub

Private Sub RecordsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RecordsBindingNavigatorSaveItem.Click

Me.Validate()

Me.RecordsBindingSource.EndEdit()

Me.RecordsTableAdapter.Update(Me.MusicDataSet2.records)

End Sub

Private Sub BindingNavigatorMovePreviousItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMovePreviousItem.Click

 

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnquit.Click

Me.Hide()

view.Show()

End Sub

End Class

 

Answer 5

yes you can, its the same thing - when you do an Update() on the dataAdapter, it returns back the number of rows effected by the result so you can use this also when deleting a record.

You can also do this using the "Raw" approach, using the SqlCommand classes and executenonquery, which returns back the number of records effected by the command.

however using the DataAdapter approach, when you update/delete/insert records, it will return back the number of rows effected by the command/transaction.

Answer 6

can you give me an example of what the code would look like and where you might out it on the code i sent please

Answer 7

you have done it already

Dim rowsaffected as Integer = 0

rowsaffected = RecordsTableAdapter.Update(MusicDataSet2.records)

 

this will return back the number of rows affected by the update command. So if you wanted to delete a row then you need a delete command to be given to the dataAdapter (RecordsTableAdapter.DeleteCommand)

SQL:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx

OleDB:

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.deletecommand.aspx

 

 

Answer 8

i have done that too and it gives me an error dlete command needed, as though ihave to  delete a spacific row of data. for example if a wanted to delete a record that was on row 10, the project needs to know that i want to delete row 10. it this im not sure about.

this is my delete sub

Public Class deleteit

Private Sub deleteit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'MusicDataSet21.records' table. You can move, or remove it, as needed.

Me.RecordsTableAdapter1.Fill(Me.MusicDataSet21.records)

 

End Sub

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

End Sub

Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

End Sub

Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTNEND.Click

Dim ans As Integer

something has to go here to tell the sub which i want to delete

RecordsBindingSource.EndEdit()RecordsTableAdapter1.Update(MusicDataSet21.records)

 

 

 

End Sub

Private Sub RecordsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RecordsBindingNavigatorSaveItem.Click

Me.Validate()

Me.RecordsBindingSource.EndEdit()

Me.RecordsTableAdapter1.Update(Me.MusicDataSet21.records)

End Sub

End Class

Answer 9

(delete from recordstableadapter1)where (song)= newsong

this gives me a syntax error, whats wrong with it, and could this be the way to delet my row