I get an error for inserting data into SQL Server via VB.Net

Question

I need to put some data into my SQL Server database using VB.net forms and vb language.

I already have a similar SQL request who is working but this time I get crazy errors.

This is the code who is inserting the data into the database:

Dim strIns As String = "INSERT INTO Entreprise (adresseClient, villeClient, cpClient, telClient, identClient, nomEntreprise, secteurEntreprise, dateCreationEntreprise) VALUES ('"
      strIns &= txtAdresse.Text & ", " & txtVille.Text & ", " & txtCp.Text & ", " & txtNumero.Text & ", " & txtID.Text & ", " & txtNom.Text & ", " & txtSecteur.Text & ", " & txtDateCreation.Text & "')"

Dim con As New SqlConnection
Dim cmd As New SqlCommand

Try
   con.ConnectionString = "Data Source= DESKTOP - KBTD2C1 \ MYDATABASE;Initial Catalog=MyDatabse; Integrated Security=SSPI;"
   con.Open()

   cmd.Connection = con
   cmd.CommandText = strIns

   cmd.ExecuteNonQuery()

Catch ex As Exception
    MessageBox.Show("Error While inserting record On table..." & ex.Message, "Insert Records")
Finally
    con.Close()
End Try

I get this error:

Error while inserting record on table: Syntax error near 'Infinite'

This is the SQL command I generate in strIns:

I don't find out where is the syntax error ?

Here is my SQL Server database table:

Answer 1

There are pieces here that replies/comments prior to this are enforced e.g. use parameters.

There are comments sprinkled throughout the code but a few things first.

It's a good idea to separate user interface from data operations thus the code below is a class which in the form an instance of the class is created, calling the AddNewRecord and passing back the newly created record's primary key.

Note how I created the SQL statement, much better than concatenating strings which is good for Framework 3.5 or higher.

Hope this helps.

Imports System.Data.SqlClient
''' <summary>
''' Created with Framework 4.5 under
''' VS2015
''' </summary>
Public Class DataOperations
    Private Server As String = "DESKTOP - KBTD2C1 \ MYDATABASE"
    Private Catalog As String = "MyDatabse"
    Private ConnectionString As String = ""
    Private mException As Exception
    ''' <summary>
    ''' If AddNewRecord returns false check this for
    ''' the exception thrown.
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property Exception As Exception
        Get
            Return mException
        End Get
    End Property
    ''' <summary> 
    ''' Setup the connection string 
    ''' </summary> 
    Public Sub New()
        ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
    End Sub
    ''' <summary>
    ''' I don't know you data types for fields, last one
    ''' seemed like a date so I cast the arguments in as string
    ''' exception for the last one.
    ''' 
    ''' Example call would be to pass in your values. Last argument
    ''' pass in a defined Integer e.g. Dim Id As Integer = 0
    ''' If this function returns true then the variable Id will contain
    ''' the new primary key value for the newly created record.
    ''' </summary>
    ''' <param name="adresseClient"></param>
    ''' <param name="villeClient"></param>
    ''' <param name="cpClient"></param>
    ''' <param name="telClient"></param>
    ''' <param name="identClient"></param>
    ''' <param name="nomEntreprise"></param>
    ''' <param name="secteurEntreprise"></param>
    ''' <param name="dateCreationEntreprise"></param>
    ''' <param name="NewIdentifier"></param>
    ''' <returns></returns>
    Public Function AddNewRecord(
        ByVal adresseClient As String,
        ByVal villeClient As String,
        ByVal cpClient As String,
        ByVal telClient As String,
        ByVal identClient As String,
        ByVal nomEntreprise As String,
        ByVal secteurEntreprise As String,
        ByVal dateCreationEntreprise As Date,
        ByRef NewIdentifier As Integer) As Boolean

        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                '
                ' INSERT record then get the record's newly generated primary key
                ' (assuming the primary key is auto-incrementing int)
                '
                cmd.CommandText =
                    <SQL>
                        INSERT INTO Entreprise 
                        (
                            adresseClient,
                            villeClient,
                            cpClient,
                            telClient, 
                            identClient, 
                            nomEntreprise,
                            secteurEntreprise,
                            dateCreationEntreprise
                        ) 
                        VALUES 
                        (
                            @adresseClient,
                            @villeClient,
                            @cpClient,
                            @telClient, 
                            @identClient, 
                            @nomEntreprise,
                            @secteurEntreprise,
                            @dateCreationEntreprise
                        ); 
                        SELECT CAST(scope_identity() AS int);
                    </SQL>.Value

                cmd.Parameters.AddWithValue("@adresseClient", adresseClient)
                cmd.Parameters.AddWithValue("@villeClient", villeClient)
                cmd.Parameters.AddWithValue("@cpClient", cpClient)
                cmd.Parameters.AddWithValue("@telClient", telClient)
                cmd.Parameters.AddWithValue("@identClient", identClient)
                cmd.Parameters.AddWithValue("@nomEntreprise", nomEntreprise)
                cmd.Parameters.AddWithValue("@secteurEntreprise", secteurEntreprise)
                cmd.Parameters.AddWithValue("@dateCreationEntreprise", dateCreationEntreprise)

                Try
                    cn.Open()
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    Return True
                Catch ex As Exception
                    mException = ex
                    Return False
                End Try
            End Using
        End Using
    End Function
End Class

EDIT: Mock up of usage

Public Class example
    Private dt As DataTable
    Public Sub New()
        dt = New DataTable
        dt.Columns.Add(New DataColumn With {.ColumnName = "id", .DataType = GetType(Integer), .AutoIncrement = True})
        dt.Columns.Add(New DataColumn With {.ColumnName = "adresseClient", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "villeClient", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "cpClient", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "telClient", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "identClient", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "nomEntreprise", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "secteurEntreprise", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "dateCreationEntreprise", .DataType = GetType(Date)})
    End Sub
    Public Sub demo()
        Dim ops As New DataOperations
        Dim id As Integer = 0
        If ops.AddNewRecord("sasas", "sdsd", "fgfgf", "wew", "asd", "cvb", "xv", Now, id) Then
            dt.Rows.Add(New Object() {id, "sasas", "sdsd", "fgfgf", "wew", "asd", "cvb", "xv", Now})
        End If
    End Sub
End Class

Answer 2

It looks like you are not applying single quote " ' " around each of the values you are passing. You are simply concatenating the textbox's value with the query string.

With current code your SQL query will become something like this:

INSERT INTO Entreprise (adresseClient, villeClient) Values ('ABC, XYZ')

But after applying " ' " around values it will become :

INSERT INTO Entreprise (adresseClient, villeClient) Values ('ABC', 'XYZ')

which is the correct version.

Also I would say not to concatenate the SQL Query as string as wit will cause SQL injection, please use Query parameters instead.

You can also share the error details here, which will help us to analyze the bug