Friday, May 8, 2020

Inserting New Record into MS Access Database using VB.Net, concatenating a string variable into the SQL Command

The following is an example of inserting a record into the ItemMaster table where field 'Description' will have a value of 'Cardinal' put in it.

Note that a String variable is created with the value of Cardinal and the SQL String is parced together with this information. Note the component of the SQL string: '" + strDesc + "'  This is where the string variable strDesc is concatenated into the string. The final SQL string looks like:

INSERT INTO ItemMaster (Description) VALUES ('Cardinal')

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Dim strDesc As String = "Cardinal"
            Dim command As New OleDb.OleDbCommand("INSERT INTO ItemMaster (Description) VALUES ('" + strDesc + "')"cnn)
            command.ExecuteNonQuery()
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
End Module

No comments:

Post a Comment

Horizontal Scrolling for Code

Horizontal scrolling can be obtained by using a tag as follows using  style="overflow: auto;"          Dim   connStr   As   Stri...