Update SQL Server DB Using Combobox Selected Value

[dwayned]

Hi I am trying to update my database based on the selected values in two comboboxes.

Combobox1 = Manufacturers
Combobox2 = Orderlists

In the App the user selects a manufacturer and then an orderlist and then presses Ok button which updates the default orderlist for the manufacturer.

I am having problems on buttonClick event which updates the data, just unsure of method, if somebody could even point me towards a good tutorial it would be great as I have searched high and low for one.

thanks very much
Dwayne

Code is as follows....

Imports System.Data
Imports System.Data.SqlClient


Public Class frmMain
    Dim ConnString As String = "Server=MACHINENAME\SQL2008EX;Database=mydb;Trusted_Connection=True;"
    Dim SQLCon As New SqlConnection(ConnString)

    Private Sub TabPage2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage2.Click

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Me.Close()

    End Sub

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim SQLCmd As String = "SELECT ManufacturerID, Name FROM Manufacturer ORDER BY Name"
        Dim da As New SqlDataAdapter(SQLCmd, SQLCon)
        Dim ds As New DataSet
        'Fill the dataset
        da.Fill(ds, "MakersList")

        With ComboBox1
            .DataSource = ds.Tables("MakersList")
            .DisplayMember = "Name"
            .ValueMember = "ManufacturerID"
            .SelectedIndex = 0
        End With

        Dim SQLCmd2 As String = "SELECT OrderListID, OrderListCode, OrderListDescription FROM OrderList ORDER BY OrderListCode"
        Dim da2 As New SqlDataAdapter(SQLCmd2, SQLCon)
        Dim ds2 As New DataSet
        'Fill the dataset
        da2.Fill(ds2, "OrderLists")

        With ComboBox2
            .DataSource = ds2.Tables("OrderLists")
            .DisplayMember = "OrderListDescription"
            .ValueMember = "OrderListCode"
            .SelectedIndex = 0
        End With

    End Sub

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

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim SQLCmd As String = "Update(Stock)SET DefaultOrderListID = '" & ComboBox2.SelectedValue & "' WHERE StockID IN(SELECT DISTINCT StockID FROM StockManufacturer WHERE ManufacturerID = '" & ComboBox1.SelectedValue & "')"
        Dim da As New SqlDataAdapter(SQLCmd, SQLCon)
        Dim ds As New DataSet
        Dim dt As New DataTable

        'Update the DB
        da.Update(dt)

        MessageBox.Show("Query Completed")
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)

    End Sub
End Class

[detoxx]

I find that combobox.SelectedValue doesnt work very well with database updates.

I personaly use combobox.SelectedItem. Which sub are you having trouble with? paste just that one sub :)

When you say updating your DB, I assume you mean adding new records?

This is how i would do it;

Imports System.Data.OleDb

Public Class Form1

Private Sub BtnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOK.Click

        ' opens database
        objconnect = New OleDbConnection
        objconnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                                      "Data Source = Northwind.mdb"

        objcommand = New OleDbCommand
        objcommand.CommandText = "INSERT INTO `Customers` (`CustomerID`, `ContactName`, `ContactTitle`, `Address`, `City`, `PostalCode`, `Phone`) VALUES  ('" & TbCusID.Text & "', '" & TbCusName.Text & "', '" & CmboConTitle.SelectedItem & "' , '" & TbAdd.Text & "' , '" & TbCity.Text & "' , '" & TbRegion.Text & "' , '" & TbPhone.Text & "');"

        objcommand.Connection = objconnect

        'Open the connection
        objconnect.Open()

        ' Execute's query
        objcommand.ExecuteNonQuery()

        ' closes connection
        objconnect.Close()

        End Sub
     End Class

[kvprajapati]

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim SQLCmd As String = "Update [Stock] SET [DefaultOrderListID] =@ordId WHERE StockID IN (SELECT DISTINCT StockID FROM StockManufacturer WHERE ManufacturerID = @manuId)"
        Dim cmd As New SqlCommand(SQLCmd, SQLCon)
        cmd.Parameters.AddWithValue("@ordId",ComboBox2.SelectedValue)
        cmd.Parameters.AddWithValue("@manuId",ComboBox1.SelectedValue)
         
        SQLCon.Open()
        cmd.ExecuteNonQuery()
        SQLCon.Close()
        MessageBox.Show("Query Completed")
    End Sub
End Class

[dwayned]

Hi adatapost,

I have tried your code but am getting error below, It is stopping on the cmd.ExecuteNonQuery() Incorrect syntax near '('.

[InsitizrT]

Try this

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim SQLCmd As String
        
        'Query
        SQLCmd = "UPDATE Stock SET"
        SQLCmd &= " DefaultOrderListID = '" & ComboBox2.SelectedValue & "'"
        SQLCmd &= " WHERE StockID IN ("
        SQLCmd &= " SELECT DISTINCT StockID"
        SQLCmd &= " FROM StockManufacturer"
        SQLCmd &= " WHERE ManufacturerID = '" & ComboBox1.SelectedValue & "')"

        Try
            SqlCon.Open() 'Connection
            Dim cmd As New SqlCommand(SQLCmd, SQLCon) 
            cmd.ExecuteNonQuery() 'Update
            MsgBox("Update Complete!", MsgBoxStyle.Information, "Update")

        Catch ex As Exception
            MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Update")
        Finally
            sqlCon.Close()
        End Try
    End Sub