CODEWITHSUNDEEP
sqlvbaexcel-2010
User
User

Reputation: 363

VBA SQL Error in insert

I have an error with my SQL insert statement. The error is

Syntax error (missing operator) in query expression

My SQL code:

INSERT INTO [table1] ('Row 1', 'Row 2', 'Row 3') 
VALUES (2018-2019, Data2, Data3)

Data2 and Data3 are strings

Here is the code I used to generate my sql string

Just to try and get it working I replaced the variables with string litterals but I would like to move the variables back in.

I got several errors

First is expecting 3 paramaters Second missing query operator

Calling Functionm I checked the data give me what I am looking for

createSQLforInsert tableName, pkData(0, 0), pkData(0, 1), FiscalYear

Public Function createSQLforInsert(tableName As String, dType As String, role 
As String, FiscalYear As String)
Dim SQL As String
Dim tempString As String
tableName = tableName & "$"
tempString = ""
'SQL = "INSERT INTO [" & tableName & "] ([Fiscal Year], [Type], [role]) "
SQL = "INSERT INTO [" & tableName & "] ([FiscalYear], [Type], [Role]) "
SQL = SQL & "VALUES ([" & FiscalYear & "], [" & dType & "], [" & role & "]) "

Debug.Print SQL

insertQuery SQL
End Function

Here is my current query

INSERT INTO [Data-People$] ([FiscalYear], [Type], [Role]) VALUES ([2018-2019], [RA], [Department Data])

This gives me an error expecting three parameters too few parameters.

Upvotes: 0

Views: 397

Answers (3)

Cetin Basoz
Cetin Basoz

Reputation: 23867

As I said in my other reply, use parameters, never attempt to create SQL statements like that concatenating strings.

Public Function createSQLforInsert(tableName As String, dType As String, role 
  As String, FiscalYear As String)
  Dim SQL As String
  Dim tempString As String
  tableName = tableName & "$"
  tempString = ""
  SQL = "INSERT INTO [" & tableName & "] ([Fiscal Year], [Type], [role]) " & _
        " VALUES (@data1, @data2, @data3)"
  Debug.Print SQL

'insertQuery SQL
End Function

I commented out "insertQuery SQL" because its code is missing and you would need to pass @data1, 2 and 3's values to there if they are not constructed in that "insertQuery" method. This method does a very unnecessary thing IMHO and just build an INSERT statement. You could simply type this where it is needed.

EDIT: Anyway I am giving a sample with parameters:

    Public Function insertFiscalData(tableName As String, dType As String, role As String, FiscalYear As String)
      Dim SQL As String

      SQL = "INSERT INTO [" & tableName & "$] ([Fiscal Year], [Type], [role]) " & _
            " VALUES (@p1, @p2, @p3)"

     Dim oConnection As ADODB.Connection
     Dim oCommand As ADODB.Command
     Set oConnection = New ADODB.Connection
     Set oCommand = New ADODB.Command

    oConnection.ConnectionString = "Provider=... fill correct conn info here"

  oConnection.Open
  oCommand.ActiveConnection = oConnection
  oCommand.CommandText = SQL

  oCommand.Parameters.Append oCommand.CreateParameter("@p1", adVarChar )
  oCommand.Parameters.Append oCommand.CreateParameter("@p2", adVarChar )
  oCommand.Parameters.Append oCommand.CreateParameter("@p3", adVarChar )

  oCommand.Parameters("@p1").Value = FiscalYear
  oCommand.Parameters("@p2").Value = dType
  oCommand.Parameters("@p3").Value = role

  oCommand.execute
  oConnection.Close

    End Function

Upvotes: 0

Vityata
Vityata

Reputation: 43595

If the Data2 and Data3 are strings, then they should be passed with ':

CREATE TABLE table11 (
    [Row 1] varchar(255),
    [Row 2] varchar(255),
    [Row 3] varchar(255),
);

INSERT INTO table11 ([Row 1], [Row 2], [Row 3]) 
VALUES ('2018-2019', 'Data 2', 'Data 3')

enter image description here

Note: Lots of people would probably advise not to put space in the names of the columns. E.g. use CamelCase or "_".

Concerning putting the sql into VBA - the ' should not be a problem:

Public Sub TestMe()
    Dim sql As String
    sql = "INSERT INTO table11 ([Row 1], [Row 2], [Row 3]) " & _
                "VALUES ('2018-2019', 'Data 2', 'Data 3')"
    MsgBox sql
End Sub

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23867

Column names are not strings (2018-2019 you meant int -1):

INSERT INTO [table1] ([Row 1], [Row 2], [Row 3]) 
VALUES (2018-2019, @Data2, @Data3)

Data2 and Data3 should be passed as parameters.

If you meant you want to literally insert 'Data 2' and 'Data 3':

INSERT INTO [table1] ([Row 1], [Row 2], [Row 3]) 
VALUES (2018-2019, 'Data 2', 'Data 3')

Note: Do not put quotes around 2018-2019 if you didn't mean '2018-2019' string literal.

Upvotes: 2

Related Questions