0

With a huge thanks to preciousbetine I can now select a name from a ComboBox which will display the other column entries in TextBoxes, but now I'm trying to figure out how to delete the row from the database and update it immediately within the ComboBox after clicking Button1, my current code does nothing(for Button1).

Public Class Form1
Dim dt As New DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim con As New SqlConnection("Data Source=xxxx\sqlexpress;Initial Catalog=ltLeavers;Integrated Security=True")

    Dim da As New SqlDataAdapter("SELECT * FROM dbo.ltData", con)
    da.Fill(dt)
    ComboBox1.DisplayMember = "DISPLAY_NAME"
    ComboBox1.DataSource = dt

End Sub

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

    Dim con As New SqlConnection("Data Source=xxxxx\sqlexpress;Initial Catalog=ltLeavers;Integrated Security=True")
    Dim del As New SqlDataAdapter("DELETE * FROM dbo.ltData where DISPLAY_NAME='" & ComboBox1.SelectedIndex & "'", con)

    MsgBox("Thank your for your submission.", MsgBoxStyle.OkOnly, "Success!")

End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    TextBox1.Text = CStr(dt.Rows(ComboBox1.SelectedIndex)("EMAIL_ADDRESS"))
    TextBox3.Text = CStr(dt.Rows(ComboBox1.SelectedIndex)("OFFICE"))
    TextBox2.Text = CStr(dt.Rows(ComboBox1.SelectedIndex)("DEPARTMENT"))
    TextBox12.Text = CStr(dt.Rows(ComboBox1.SelectedIndex)("NEG_ID"))
CyronDnB
  • 35
  • 6
  • 2
    Please do NOT use any of the code in the accepted answer in the link from @ChetanRanpariya. You need to learn how to parameterize your queries. This is wide open to sql injection. http://bobby-tables.com/ – Sean Lange Jan 31 '19 at 14:00
  • Is DISPLAY_NAME the primary key in the database? – Mary Feb 01 '19 at 00:43
  • Thanks @SeanLange for pointing that out.. I just deleted the old comment.. For the OP you can refer to the answer of [this](https://stackoverflow.com/questions/6413938/sql-delete-command) question to understand how to use SQLConnection and SQLCommand to perform DELETE operation on a table in SQL Server database. – Chetan Feb 01 '19 at 03:40
  • @Mary Yes that's correct, it's the primary key. – CyronDnB Feb 01 '19 at 09:32
  • @SeanLange thank you so much for the information, I know next to nothing regarding SQL but will be sure to look into how to prevent an SQL Injection before distributing this application. – CyronDnB Feb 01 '19 at 09:33

2 Answers2

0

Try replacing

 ComboBox1.SelectedIndex

to

 ComboBox1.SelectedText
Rajbir Singh
  • 101
  • 1
  • 6
  • Thank you for responding. I amended the code to the following: `Dim con As New SqlConnection("Data Source=xxxxx\sqlexpress;Initial Catalog=ltLeavers;Integrated Security=True")` but unfortunately this did not delete a row in the database / table – CyronDnB Jan 31 '19 at 14:32
0

Hope this will help

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim con As New SqlConnection("Data Source=xxxxx\sqlexpress;Initial Catalog=ltLeavers;Integrated Security=True")
    con.Open()

    strSQL = "DELETE * FROM dbo.ltData where DISPLAY_NAME='" & ComboBox1.SelectedText & "'"

    Dim com As New SqlCommand(strSQL, con)

    com.ExecuteNonQuery()

    MsgBox("Thank your for your submission.", MsgBoxStyle.OkOnly, "Success!")
End Sub
Rajbir Singh
  • 101
  • 1
  • 6