delete row vb.net and sql server-VBForums
Results 1 to 5 of 5

Thread: delete row vb.net and sql server

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    31

    delete row vb.net and sql server

    Hi guys i would like to delete row in my datagrid, however i have an auto increment in my sql db table calle (Utilisateurs)
    it will only delete row when i fix a number like
    cmd.Parameters.Add(New SqlParameter("@UserID", " 1))
    i want to delete any rows in my db. Please help?? below is my code

    Code:
     Private Sub BtnSupprimer_Click(sender As Object, e As EventArgs) Handles BtnSupprimer.Click
    
            Dim cmd As New SqlCommand
                Try
                    MyConnexion.ConnectionString = "Data Source=OTJ\SQLEXPRESS;Initial Catalog=Gestock;Integrated Security=True"
                    MyConnexion.Open()
                    cmd.Connection = MyConnexion
                    cmd.CommandText = "Delete From Utilisateurs1 where UserID=  @UserID "
                    cmd.Parameters.Add(New SqlParameter("@UserID", " 1))
                    cmd.ExecuteNonQuery()
                    If MessageBox.Show("Voulez-vous vraiment supprimer ce champ?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
    
                        MsgBox("Operation Annulée")
    
                        Exit Sub
    
                    End If
                Catch ex As Exception
                    MessageBox.Show("Error while deleting record on table..." & ex.Message, "Delete Records")
    
                Finally
    
                    MyConnexion.Close()
                End Try
                SetUpDataGridView()
                ComboType.SelectedValue = -1
                TextUsers.Text = ""
                TextPass.Text = ""
                TextUsers.Focus()
            End If
    End Sub

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,369

    Re: delete row vb.net and sql server

    If you want to remove all rows from the table, simply eliminate the Where clause and parameter, eg:
    Code:
                    cmd.CommandText = "Delete From Utilisateurs1"
                    cmd.ExecuteNonQuery()
    If you don't want to delete all rows, you need to use a Where clause that is appropriate to what you want to delete.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2016
    Posts
    31

    Re: delete row vb.net and sql server

    I user the where clause in my statement the problem is this line
    cmd.Parameters.Add(New SqlParameter("@UserID", " 1))
    I have an auto increment and i dont want to fix my userID to 1 i want it to delete any row when i click on it

  4. #4
    Addicted Member
    Join Date
    Sep 2017
    Posts
    128

    Re: delete row vb.net and sql server

    Can you not use a bindingsource?

    Example of setting up a bindingsource and removing the row from datagridview and sqltable.

    Code:
    Imports MySql.Data.MySqlClient
    Public Class DeleteExample
        Dim DT As New DataTable
        Dim BS As New BindingSource
        Private Sub DeleteExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn As New MySqlConnection("server=127.0.0.1;user id=****;password=****;persistsecurityinfo=True;database=sandbox")
                Using DA As New MySqlDataAdapter("SELECT * FROM t1", conn)
                    With DA
                        .MissingSchemaAction = MissingSchemaAction.AddWithKey
                        .FillSchema(DT, SchemaType.Source)
                        .Fill(DT)
                    End With
                    BS.DataSource = DT
                    With DataGridView1
                        Dim BtnDel As New DataGridViewButtonColumn
                        BtnDel.Name = "BtnDel"
                        .DataSource = BS
                        .Columns.Add(BtnDel)
                    End With
                End Using
            End Using
        End Sub
        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            If DataGridView1.CurrentCell.OwningColumn.Name = "BtnDel" Then
                Using conn As New MySqlConnection("server=127.0.0.1;user id=****;password=****;persistsecurityinfo=True;database=sandbox")
                    Using DA As New MySqlDataAdapter("SELECT * FROM t1", conn)
                        BS.RemoveCurrent()
                        BS.EndEdit()
                        Dim CMDBuilder As New MySqlCommandBuilder(DA)
                        DA.Update(DT)
                    End Using
                End Using
            End If
        End Sub
    End Class

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,315

    Re: delete row vb.net and sql server

    Check out DataGridVew1_UserDeletingRow in MainForm, follow the code through to Operations class to RemoveRecord. In short when pressing delete key on a row in the DataGridView I get that row's primary key (its not visible in the DataGridView) then send the primary key to RemoveRecord. You don't need to download any code, just click Browse code and review it.

    https://code.msdn.microsoft.com/MS-A...e96d88?redir=0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.