|
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
|
|
|
|
|
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.
|
|
|
|
|
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!!
|
|
|
|
|
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.
Somebody in an online forum wrote: INTJs never really joke. They make a point. The joke is just a gift wrapper.
|
|
|
|
|
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
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()
|
|
|
|
|
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()
|
|
|
|
|
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
|
|
|
|
|
Looks much better, I'm glad to see you figured it out! 
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
|
|
|
|
|
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
|
|
|
|
|
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?  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.
|
|
|
|
|
Looks like what I need. Thanks!!! 
Hypermommy
|
|
|
|
|
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.
Somebody in an online forum wrote: INTJs never really joke. They make a point. The joke is just a gift wrapper.
|
|
|
|
|
Right, however not the best example, the way I understood that linked message, SQL injection attacks can be almost good.
|
|
|
|
|
Most SQL injection attacks can be good... for somebody. 
Somebody in an online forum wrote: INTJs never really joke. They make a point. The joke is just a gift wrapper.
|
|
|
|
|
Hello fellow programmers,
I have reached a bump in the road that I am unable to get over. I would appreciate some assistance. Currently, I am upgrading an old app in VB6 to VS2008. The function in question is using the object data type for 2 parameters. After reading on this site the warning about boxing and unboxing of object data types and how they can increase time I was wondering if I could alleviate that by overloading the function or making it generic.
Initially I wanted to overload the function. However, with all the possible data types that can be accepted I found out that there are 26 possible combinations of all the parameters that needed to be overloaded. This does not seem to me an ideal way to go so I started looking into generics.
Here comes my problem. Before messing around with the original code I created a separate program to simulate what I need to do to change the code. I ran into some problems that I am unable to figure out what to do. I will post the code momentarily. If any of you fellow programmers don't think this is a valid way to go please let me know why. I am open to suggestions.
Note: This is placed inside a module.
Public Function ReportGeneric(Of Class_Objects, GUI_Input)(ByRef value1 As Class_Objects, ByRef value2 As GUI_Input) As Boolean
Dim status As Boolean
Try
If TypeOf value2 Is System.Windows.Forms.ComboBox Then
setComboBoxWithInfo(value1.ReturnsImportantInfo())
ElseIf TypeOf value2 Is System.Windows.Forms.TextBox Then
setTextBoxWIthInfo(value1.ReturnsImportantInfo())
End If
status = True
Catch ex As Exception
MsgBox("Good job you broke the software!")
status = false
End Try
Return status
End Function
There are many errors that I receive that pertain to the class_objects members. Should I put constraints on the class_objects? Am I going about this the wrong way? Any info you can send my way would be greatly appreciated. If you would like me to clarify anything let me know.
Thanks!
|
|
|
|
|
After further investigation I have tried to add a constraint to see if this will help my problem. I tried the following code below.
Public Function ReportGeneric(Of Class_Objects As {clsMyClass1, clsMyClass2}, GUI_Input)(ByRef value1 As Class_Objects, ByRef value2 As GUI_Input) As Boolean
...
Both classes will have the same member function as shown in my original post. But the compiler error I receive is "Type parameter 'Class_Objects' can only have one constraint that is a class." This does not bode with me to well. It seems as though generics well not help me to achieve reduce copying my function and overloading it. As I mentioned before I would really like to steer away from copying my function 26 times and overloading it to capture all possible combinations of different parameters. This will make it hard to maintain code down the line.
Is something amiss here? Have I overlooked something?
Thanks.
One more thing I would like to say is yes I can use an object data type and be done with this, but the point was to focus upon speed by preventing boxing and unboxing. Any info you can send my way to shed some light would be greatly appreciated.
|
|
|
|
|
Clark Kent123 wrote: "Type parameter 'Class_Objects' can only have one constraint that is a class."
That limit doesn't exist for Interfaces. Create an Interface or multiple Interfaces for your classes to implement, even if it's a one interface to one class ratio.
|
|
|
|
|
Thanks Dave for that piece of advice. That looks like my only option at this point.
Now I just have to figure out the logistics of what needs to be done. Thanks again. 
|
|
|
|
|
Hi every one I am New using VB and also new in this Web site I created and VB Application the Read, add, and delete Printers records from an Access database. But the Application don't know when the Printer is Online or Offline I have to manually update that. I want that the be automatically and this is what I have so far but is not saving it...Please help me
Private Sub SplashForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
PFCdbconnection = New OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source =F:\Device Files\MFC\Common\Shared\Printer Control\PFC.mdb;jet OLEDB:Database Password=password;")
PFCdbconnection.Open()
printerscommand(0) = New OleDbCommand("SELECT Printer,IPAddress,OffLine,Online FROM PrinterInfo ORDER by ID", PFCdbconnection)
printersAdapter(0) = New OleDbDataAdapter()
printersAdapter(0).SelectCommand = printerscommand(0)
PrintersTable(0) = New DataTable
printersAdapter(0).Fill(PrintersTable(0))
PrintersManager(0) = DirectCast(Me.BindingContext(PrintersTable(0)), CurrencyManager)
Dim MaxRow As Integer
MaxRow = PrintersTable(0).Rows.Count
Dim RPosition As Integer = 0
Do Until RPosition = MaxRow
PrintersTable(0).DefaultView.Sort = "Printer"
PrintersManager(0).Position = RPosition
PrinterIPTextBox.DataBindings.Add("Text", PrintersTable(0), "printer")
If My.Computer.Network.Ping(PrinterIPTextBox.Text) Then
OnlineRadioButton.Checked = True
Else
OfflineRadioButton.Checked = True
End If
PrintersManager(0).EndCurrentEdit()
Dim CurrentInfoData As DataRow = PrintersTable(0).Rows(PrintersTable(0).Rows.Count - 1)
If RPosition = -1 Then
CurrentInfoData.Item("Online") = OnlineRadioButton.Checked
CurrentInfoData.Item("offline") = OfflineRadioButton.Checked
Dim PrinterInfoCom As New OleDbCommandBuilder(printersAdapter(0))
printersAdapter(0).Update(PrintersTable(0))
End If
PrinterIPTextBox.DataBindings.Clear()
RPosition = RPosition + 1
Loop
End Sub
modified 20-Oct-11 11:04am.
|
|
|
|
|
I would move the printer status to a background worker that monitors for a changing status e.g. going offline/online etc. then message this to the rest of the application.
That way you could set it off running at the start and forget about it, and only concern yourself with a state change.
Also ping is not a reliable method of determining the printers availability. The network interface could still happily respond to ping events, but the main printer is in fact dead.
You would be better to read the printer status directly using WMI.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
