Reputation: 363
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
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
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')
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
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