dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2016
    Posts
    60

    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,845

    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
    Member
    Join Date
    Dec 2016
    Posts
    60

    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
    Hyperactive Member
    Join Date
    Sep 2017
    Posts
    429

    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,339

    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


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.