locked
delete rows RRS feed

  • 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

    Monday, October 16, 2006 11:11 AM

Answers

  • 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"

    Monday, October 16, 2006 11:34 AM

All replies

  • 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"

    Monday, October 16, 2006 11:34 AM
  • can i itercept the row counter and delete the rowcount
    Monday, October 16, 2006 12:55 PM
  • not sure I understand what you mean..can you explain further?
    Monday, October 16, 2006 1:06 PM
  • 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

     

    Monday, October 16, 2006 1:20 PM
  • 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.

    Monday, October 16, 2006 1:53 PM
  • 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
    Monday, October 16, 2006 2:02 PM
  • 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

     

     

    Monday, October 16, 2006 2:09 PM
  • 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

    Monday, October 16, 2006 2:23 PM
  • (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

    Tuesday, October 17, 2006 8:04 PM