Syntax Error (missing Operator) in query expression '07/08/2011 12:00:00 PM'.
-
Amanjot wrote:
Dim dbCmd As OleDbCommand = New OleDbCommand
Amanjot wrote:
dbCmd = New OleDbCommand(cmd, myConnection)
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
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()
-
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()
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
-
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
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
-
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
Looks much better, I'm glad to see you figured it out! :thumbsup:
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
-
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
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
-
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
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
-
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
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
-
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
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
-
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
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
-
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.
Somebody in an online forum wrote:
INTJs never really joke. They make a point. The joke is just a gift wrapper.
Looks like what I need. Thanks!!! :-D
Hypermommy