Syntax Error (missing Operator) in query expression '07/08/2011 12:00:00 PM'.

Amanjot

Hi, I am trying to populate access database with a small program in VB; however, getting the syntax error "Syntax Error (missing Operator) in query expression '07/08/2011 12:00:00 PM'." If I remove the date filed in the database, the program works fine. Therefore, the problem seems in the date field. In access database, the format of the date field is General Here is the code:

Dim mdate As Date
mdate = "07/08/2011 12:00:00"
Dim cmd As String = "insert into rawClimateData VALUES (" & mdate & ", 12:50 , " & TippingBucket & " , " & Precip & " , " & AirTemp & " , " & SolarRad & " , " & Evap & " , " & windSpeed & " , " & windDirection & " , " & SoilTemp & " , " & SnowDepth & " , " & RH & " , " & BaroPressure & " , " & BatteryVoltage & " , " & SoilMoisture & ")"
MsgBox("cmd= " & cmd)
dbCmd = New OleDbCommand(cmd, myConnection)
dbCmd.ExecuteScalar()

Any help would be greatly appreciated. Thanks, Amanjot

AspDotNetDev

For the love of Bobby Tables, don't create a query by incorporating the parameters using string concatenation. Use the Parameters collection. That will also solve your date issue (I am NOT going to tell you how to fix this without the parameters collection). Also, in any INSERT statement, you should specify which parameters you are inserting, in case the table changes.

AspDotNetDev

Also, the reason you should not use string concatenation is because of SQL injection attacks (not to mention it causes errors like the one you've come across). Just a few minutes ago somebody posted in the Lounge about a SQL injection attack getting some people into trouble.

Luc Pattyn

Right, however not the best example, the way I understood that linked message, SQL injection attacks can be almost good. :doh:

Luc Pattyn [My Articles] Nil Volentibus Arduum

Amanjot

Thanks very much for the quick reply. I believe you could sense that I am a rusted programmer. Could you explain little bit on what you mean by parameters collection!!

AspDotNetDev

The link I sent you to shows examples of how to use parameters. Basically, a SQL query can have parameters. Just like a function with parameters, you pass values to the SQL query... these are parameters. Rather than create the entire query including the values, you can put placeholders where the values go and then pass the command that was created with that parameterized query the parameter values. The link above shows how do do that. Google should be able to answer any more questions you have.

AspDotNetDev

Most SQL injection attacks can be good... for somebody. :)

Amanjot

Thanks for the link. It was good to get updated with the parameter collection stuff. You are right, now it makes the code more flexible. However, I still have issues with the date. The program works fine if I don't "insert into" dataTime field but when I include this field, I get syntax error. Any tips for that?

Dim mdate As Date
mdate = Convert.ToDateTime("07/08/2011 12:00:00")
Dim dbCmd As OleDbCommand = New OleDbCommand

' Parameter collection
Dim parameter As New OleDbParameter()
dbCmd.Parameters.Add("@dateTime", OleDbType.Date).Value = mdate
dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket

            Dim cmd As String = "insert into rawClimateData (dateTime, TippingBucket) VALUES (" & mdate & " , " & TippingBucket & ")"

dbCmd = New OleDbCommand(cmd, myConnection)
dbCmd.ExecuteScalar()

AspDotNetDev

You are creating the command twice. You are starting to create the command the right way, but then you create it again the wrong way.

Amanjot

Thanks for catching that. I have improved code further, however, still no success. The same error is popping up: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error (missing operator) in query expression '07/07/2011 11:00:00 AM'. Here is how the code looks now: accessDataFile = AccessFileTextBox.Text Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim mdateTime As Date = "07/07/2011 11:00:00" Dim Precip As Single = 3.2 Dim TippingBucket As Integer = 2 Dim cmd As String = "insert into rawClimateData (mdateTime, TippingBucket, Precip) VALUES (" & mdateTime & " , " & TippingBucket & " , " & Precip & ")" Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection) MsgBox(cmd) ' Parameter collection dbCmd.Parameters.Add("@Precip", OleDbType.Single).Value = Precip dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket dbCmd.Parameters.Add("@mdateTime", OleDbType.DBDate).Value = mdateTime dbCmd.ExecuteScalar()

AspDotNetDev

You still aren't getting the point of parameters. You are 1) adding the parameters and 2) adding the values into the command string. You are duplicating your effort, and if you looked at that Bobby Tables example or Googled "SQL injection" you'd know why you shouldn't be adding values to the command string using string concatenation. Look at another example. Read that article. Also, pay close attention to what is placed in the command string... you'll notice it isn't the actual values. You should also notice from the reading material that Access accepts 2 different formats for parameters in the command string that you can choose from.

Amanjot

Thanks for your help. The program finally works. The last surprise I had was that when the parameters are added, they need to be in the same sequence as they are in the dataBase. Here is the final code: accessDataFile = AccessFileTextBox.Text Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessDataFile Try Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim mdateTime As Date = "07/07/2011 11:00:00" Dim Precip As Single = 3.2 Dim TippingBucket As Integer = 2 Dim cmd As String = "insert into rawClimateData (mdateTime,TippingBucket, Precip) VALUES(@mdateTime, @TippingBucket, @Precip)" Dim dbCmd As OleDbCommand = New OleDbCommand(cmd, myConnection) MsgBox(cmd) ' Parameter collection dbCmd.Parameters.Add("@mdateTime", OleDbType.Date).Value = mdateTime 'Convert.ToDateTime(mdateTime) dbCmd.Parameters.Add("@TippingBucket", OleDbType.Integer).Value = TippingBucket dbCmd.Parameters.Add("@Precip", OleDbType.Single).Value = Precip dbCmd.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message) End Try

AspDotNetDev

Looks much better, I'm glad to see you figured it out! :thumbsup:

Hypermommy

The only reason I've continued building SQL strings instead of using parameters is because I have often found it helpful, when having a problem, to put a breakpoint after the SQL is built but before it's executed. Then I'll cut the fully built SQL string and plop it in my database window so the database can give me a clearer understanding of what is going wrong. I presume there's a way to do this with parameters... to see what the whole SQL statement is and attempt execution in the database... but I've never had time to dig up how to do it. So now with this thread, I just thought I'd ask how that can be done. Thanks.

Hypermommy

AspDotNetDev

Yep, SQL Server Profiler can intercept all calls to a SQL database. It can either show them on screen in a table or it can store them into a file or database table (which you can then query against). There is all kinds of filtering available to only intercept the calls you are interested in. It's one of the tools that gets installed with SQL Server Management Studio.

Hypermommy

Aiiieeee! I've seen that, now that you mention it. Guess I need to make myself a note to use it more often. Now if you'll excuse me I'm off to investigate it some more. Thanks!

Hypermommy

Hypermommy

Really dumb question time here.... we're using SQL Server Management Studio Express here. My guess is the reason I'm not seeing Profiler is because this is Express and as such is not as complete as the full SSMS? :rolleyes: Would you figure that's what it is or maybe I'm just not seeing it?

Hypermommy

AspDotNetDev

I think you are correct. I did a search and came across this. It is apparently a tool that acts like SQL Server Profiler, but that works with SQL Server Management Studio Express.

Hypermommy

Looks like what I need. Thanks!!! :-D

Hypermommy