How to add item selected in combo box to data table ?

Question

I want user to select an item of his choice from drop down combobox and ADD to database thru save button.

Answer 1

Hello @Sheelnath Kekre

The following code sample has data operations performed in a class rather than a form as it's best to separate data operations from form operations as this keeps your form code clean. The function which inserts a new record also gets the new primary key (all tables in a database table should have a primary key as this allows for things like searching for information based on a key rather than something like a first name for instance which can change over time). Private and public methods and properties are setup as shared so there is no need to instantiate them e.g. Dim Operations as New Operations but instead simply use them. This is done in VS2019.

Full source code can be downloaded or inspected in the following GitHub repository.

Form code

ComboBox name is CompanyNameComoboBox, the Import statement points to the class to perform data operations in a folder named Classes

Imports SqlSamples.Classes  
  
Public Class Form1  
    Private Sub InsertButton_Click(sender As Object, e As EventArgs) Handles InsertButton.Click  
  
        If CompanyNameComoboBox.SelectedIndex > -1 Then  
            Dim primaryKey = Operations.InsertItem(CompanyNameComoboBox.Text)  
            If Operations.HasException Then  
                MessageBox.Show($"Adding record failed{vbTab}{Operations.LastException.Message}")  
                Exit Sub  
            End If  
        End If  
  
    End Sub  
End Class  

Data class

Note the insert method returns an Integer which I talked about above. Note the use of a parameter for the Command, without a parameter if the value being inserted has a single apostrophe this causes unbalanced string and a runtime exception is thrown while with parameter the single apostrophe is escaped.

Imports System.Data.SqlClient  
  
Namespace Classes  
    Public Class Operations  
  
        Private Shared _connectionString As String =  
                           "Data Source=.\SQLEXPRESS;" &  
                           "Initial Catalog=NorthWindAzureForInserts;" &  
                           "Integrated Security=True"  
  
        Public Shared Function InsertItem(companyName As String) As Integer  
  
            Dim newPrimaryKey As Integer = -1  
  
            Dim insertStatement =  
                    "INSERT INTO dbo.Customers (CompanyName) VALUES (@CompanyName);" &  
                    "SELECT CAST(scope_identity() AS int);"  
  
            Using cn As New SqlConnection With {.ConnectionString = _connectionString}  
  
                Using cmd As New SqlCommand With {.Connection = cn, .CommandText = insertStatement}  
                    cmd.Parameters.AddWithValue("@CompanyName", companyName)  
  
                    Try  
                        cn.Open()  
                        newPrimaryKey = CInt(cmd.ExecuteScalar())  
                    Catch ex As Exception  
                        HasException = True  
                        LastException = ex  
                    End Try  
                End Using  
  
            End Using  
  
            Return newPrimaryKey  
  
        End Function  
        Public Shared LastException As Exception  
        Public Shared HasException As Boolean  
    End Class  
End Namespace  

Answer 2

Hi @Sheelnath Kekre ,
Here's an example of saving selected item in combo box to database.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        Dim insertStr As String  
        Try  
            insertStr = ComboBox1.SelectedItem.ToString  
        Catch ex As NullReferenceException  
            MessageBox.Show("please select an item in combo box")  
            Return  
        End Try  
        Dim connString As String = "your connection string"  
        Using con As SqlConnection = New SqlConnection(connString)  
            Dim cmdText = "INSERT INTO yourTable(Column1,ComboboxColumn) VALUES ('valueofColumn','" & insertStr & "')"  
            Dim cmd As SqlCommand = New SqlCommand(cmdText, con)  
            con.Open()  
            cmd.ExecuteNonQuery()  
        End Using  
    End Sub  

Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.