none
Delete Row from SQL in VB.NET form

    คำถาม

  • Hi, im trying to delete a row from a small table on SQL from within a form in my app.

    Ive googled around a lot and dont seem to find a clear answer, the one on msdn was a bit vague and i couldnt get it to work. i Also tried using command  (as in example: http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/78f72403-509f-466c-9cb1-0f37ea5ce161)


    but i cant get that to work, plus id rather not have the connection string there anyway, whats the point when im using table adpaters?

    I have a query on the dataset called UserDeleteQuery:


    DELETE FROM Users
    WHERE     (ID = @ID)
    
    
    



    And the code im using on the button click:



        Private Sub DeleteBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBtn.Click
    
         Dim USERID As String = USerIDTb.Text
            Dim UserToDeleteRow As WDA_SQLDataSet.UsersRow
                    Try
                If MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.No Then
    
                    Exit Sub
    
                End If
    UserToDeleteRow = UsersTableAdapter.UserDeleteQuery("USERID")
    
            UserToDeleteRow.Delete()
            Me.UsersTableAdapter.Update(Me.WDA_SQLDataSet.Users)
                MsgBox("Delete successful")
    
                'Update the Gridview on the Admin form
                Administration.UsersTableAdapter.Fill(Administration.WDA_SQLDataSet.Users)
                Me.Close()
            Catch ex As Exception
    
                MessageBox.Show("Error while deleting record on table: " & ex.Message, "Delete Records", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
            End Try
        End Sub
    
    
    



    But this gives error "Value of type 'integer' cannot be converted to 'WDA.NET.WDA._SQLDataset.UsersRow'."
    However... "USERID" is a string. On the Table "ID" is an autonumber & Primary key.


    Ive also tried using the auto generated "delete" like so:


        Private Sub DeleteBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBtn.Click
            Try
    
                If MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.No Then
                    Exit Sub
                End If
    
              Me.UsersTableAdapter.DELETE(UserIdTb.Text, UsernameTb.Text, InitialsTb.Text, PasswordTb.Text, firstChkbx.Checked, secondChkbx.Checked, thirdChkBx.Checked, fourthChkbx.Checked, fithChkBx.Checked, UsernotesRTB.Text) 
    MsgBox("Delete successful") Me.Close() Catch ex As Exception MessageBox.Show("Error while deleting record on table: " & ex.Message, "Delete Records", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) End Try End Sub


    However when i run the program and try to delete a user i get the error "Conversion from String "" to type 'integer' is not valid" - and id rather just delete the row by its ID, i dont care what it contains. (i expect the "" is from a password text box thats blank)


    Could someone point out where im going wrong?
    Thanks in advance.

    FJ
    • แก้ไขโดย FthrJACK 3 กรกฎาคม 2552 13:13
    3 กรกฎาคม 2552 13:04

คำตอบ

  • ive solved it, i was about to delete the thread but saw your helpfull replies so i wont delete :)

    using:
        Private Sub DeleteBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBtn.Click
            Try
                If MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.No Then
                    Exit Sub
                End If
    
                           Me.UsersTableAdapter.UserDeleteQuery(USerIDTb.Text)
                MsgBox("Delete successful")
                'Update the gridview on the admin form
                Administration.UsersTableAdapter.Fill(Administration.WDA_SQLDataSet.Users)
                Me.Close()
    
            Catch ex As Exception
                MessageBox.Show("Error while deleting record on table: " & ex.Message, "Delete Records", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End Try
        End Sub
    Reason it wouldnt work before, i guess, was because the useridTb was set to be not visible, so it couldnt read the text from it. seems to work fine now... i hid that text box behind a richtext box, so it cant be seen anyway. probably best to retrieve the ID from the database programatically though, but this works for me at the minute.

    thanks for the help guys.

    • ทำเครื่องหมายเป็นคำตอบโดย Riquel_DongModerator 9 กรกฎาคม 2552 5:09
    3 กรกฎาคม 2552 13:33

ตอบทั้งหมด

  • The answer is in the error message you recieve.  If Users.ID field is type Integer, then you will need to specify this in your code.  Untested by you could try this....

    Change:

    Dim USERID As String = USerIDTb.Text

    To:

    Dim USERID As Integer = CInt(USerIDtb.Text)
    jfc
    3 กรกฎาคม 2552 13:22
  • ive since found that the useridTB.txt - which is in a text box thats set to be not visible, Has to be visible. The text doesent seem to be read otherwise. So i hid it behind another control for now.

    Now the query seems to run, i get no errors..... but the record doesent delete!
    if i run that  query directly on the table from vithin SQL manager or in VS, and replace '@ID' with an id number, then it will delete!

    so im stumped good and proper now.

    That's because in SQL Mgr you are passing in an Integer (@ID) into the query.  Your code is trying to pass in a String.
    jfc
    3 กรกฎาคม 2552 13:26
  • ive solved it, i was about to delete the thread but saw your helpfull replies so i wont delete :)

    using:
        Private Sub DeleteBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteBtn.Click
            Try
                If MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.No Then
                    Exit Sub
                End If
    
                           Me.UsersTableAdapter.UserDeleteQuery(USerIDTb.Text)
                MsgBox("Delete successful")
                'Update the gridview on the admin form
                Administration.UsersTableAdapter.Fill(Administration.WDA_SQLDataSet.Users)
                Me.Close()
    
            Catch ex As Exception
                MessageBox.Show("Error while deleting record on table: " & ex.Message, "Delete Records", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End Try
        End Sub
    Reason it wouldnt work before, i guess, was because the useridTb was set to be not visible, so it couldnt read the text from it. seems to work fine now... i hid that text box behind a richtext box, so it cant be seen anyway. probably best to retrieve the ID from the database programatically though, but this works for me at the minute.

    thanks for the help guys.

    • ทำเครื่องหมายเป็นคำตอบโดย Riquel_DongModerator 9 กรกฎาคม 2552 5:09
    3 กรกฎาคม 2552 13:33