SQL Syntax Error

kbooker23

New member
Joined
Dec 15, 2006
Messages
4
Programming Experience
1-3
hello, i am currently writing a program that will requires to read from a mySQL database and write the data to a Excel file. I have the writting to the excel file, but my select statement gives me a sql syntax eror. my code looks like this :
VB.NET:
Dim strSQL As String     'Select statment
Dim startDate As String  'start date - 1 string for sql statment
Dim endDate As String    'end date - 1 string for sql statment
startDate = startDateTimePicker.Value.AddDays(-1).ToString("MM/dd/yy")
endDate = endDateTimePicker.Value.ToString("MM/dd/yy")            
strSQL = "SELECT Format(wab_answers.answerTime,'mm/dd/yyyy') as [Date], Count(Format(wab_answers.answerTime,'mm/dd/yyyy')) as [Count] FROM wab_answers WHERE wab_answers.answerTime > #" + startDate + " 11:59:59 PM# And wab_answers.answerTime < #" + endDate + " 11:59:59 PM# And wab_answers.wabScreen = 1 OR wab_answers.wabScreen = 2 GROUP BY Format(wab_answers.answerTime,'mm/dd/yyyy');"
            If (MessageBox.Show(strSQL, "TEST", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No) Then
                End
            End If
            Dim bdwsmtCon As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.2.5;port=3306;database=bdwsmt;option=3;pwd=password;uid=username;")
            Dim bdwsmtCommande As New Odbc.OdbcCommand(strSQL, bdwsmtCon)
            bdwsmtCommande.Connection.Open()
            Dim bdwsmtReader As Odbc.OdbcDataReader = bdwsmtCommande.ExecuteReader(CommandBehavior.CloseConnection)
It's the last line that gives me the syntax error. I used a message box to show me the query before i used it to make sure it's exactly how i wanted it. and i also put that query into access to see if that gave me and syntax error and it ran perfectly. I was hoping maby some had an idea to get it to work??
 
I tried a bunch of different things to get that sql error to go away and non of them worked. So i re factored my project and made the the sql statement vary simple. Yet i still have one problem. Here's the code
VB.NET:
Dim bdwsmtCon As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.2.5;port=3306;database=bdwsmt;pwd=knicks24;uid=tmstms;")
Dim bdwsmtCommande As New Odbc.OdbcCommand
Dim bdwsmtReader As Odbc.OdbcDataReader
curDate=startDateTimePicker.Value.AddDays(dblStartDay).ToString("M/d/yyyy")
endDate=startDateTimePicker.Value.AddDays(dblEndDay).ToString("M/d/yyyy")
While (curDate <> Format(endDateTimePicker.Value.AddDays(1), "M/d/yyyy"))
   strSQL = "SELECT Count(*) AS MyCount FROM wab_answers WHERE       answerTime > #" & endDate & " 11:59:59 PM# AND answerTime < #" & curDate & " 11:59:59 PM# AND wabScreen=1;"
   bdwsmtCommande = New Odbc.OdbcCommand(strSQL, bdwsmtCon)
   bdwsmtCommande.Connection.Open()
  bdwsmtReader=bdwsmtCommande.ExecuteReader(CommandBehavior.CloseConnection)
sheet.Cells(lngRow, 4) = curDate
   While bdwsmtReader.Read
      sheet.Cells(lngRow, 5) = bdwsmtReader.GetValue(0)
   End While
   curDate=startDateTimePicker.Value.AddDays(dblStartDay).ToString("M/d/yyyy")
   endDate=startDateTimePicker.Value.AddDays(dblEndDay).ToString("M/d/yyyy")
   bdwsmtCon.Close()
   strSQL = "SELECT Count(*) AS MyCount FROM wab_answers WHERE  answerTime > '" & endDate & " 11:59:59 PM' AND answerTime < '" & curDate & " 11:59:59 PM' AND wabScreen=2;"
   bdwsmtCon = New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.2.5;port=3306;database=bdwsmt;option=3;pwd=pw;uid=ui;")
   bdwsmtCommande = New Odbc.OdbcCommand(strSQL, bdwsmtCon)
   bdwsmtCommande.Connection.Open()                       bdwsmtReader=bdwsmtCommande.ExecuteReader(CommandBehavior.CloseConnection)
   While bdwsmtReader.Read
      sheet.Cells(lngRow, 7) = bdwsmtReader.GetValue(0)
   End While
   bdwsmtCon.Close()
   lngRow += 1
   dblStartDay += 1
   dblEndDay += 1
   curDate=startDateTimePicker.Value.AddDays(dblStartDay).ToString("M/d/yyyy")
   endDate=startDateTimePicker.Value.AddDays(dblEndDay).ToString("M/d/yyyy")
End While
I put the ' ' around the Date and time the i was comparing and that didn't give me and sql error but it did always give me a 0 for MyCount. And in access it has to be # # around the date and time for it to not give any sql errors? I'm not to sure how to get this to work the way i want. i used msgbox to show my sql statment and it looks like how i want it to look like. Any thoughts?
 
If everyone would use parameters then none of this formatting stuff would ever be an issue, e.g.
VB.NET:
Dim adp As New SqlDataAdapter("SELECT * FROM Person WHERE DateOfBirth = @DateOfBirth", con)

adp.SelectCommand.Parameters.AddWithValue("@DateOfBirth", myDateTimePicker.Value.Date)
As you see, you never have to worry about format because you never convert the Date object to a string. This is the proper way to insert any values into an SQL statement.

Now, I've used SqlClient but the same principle applies to all ADO.NET. You can do the same with OdbcClient but I'm not sure what prefix MySQL uses for parameters. I know it's not "@" like SQL Server. I've got a feeling that it's ":" but that might be Oracle. It shouldn't be too hard to find out though.
 
So that code would work to pull out all date from the database that have the same date as my datetimepicker? Even if there's a time attached to the date in the database?
 
So that code would work to pull out all date from the database that have the same date as my datetimepicker? Even if there's a time attached to the date in the database?
No. If you want to get all records where the date part of a datetime value is a particular value then you may be able to use an SQL/database function, although I'm not aware of one for SQL Server. Alternatively you could use a date range:
VB.NET:
Dim adp As New SqlDataAdapter("SELECT * FROM MyTable WHERE MyDateTime [U]>=[/U] @StartDate AND MyDateTime [U]<[/U] @EndDate", con)

adp.SelectCommand.Parameters.AddWithValue("@StartDate", myDateTimePicker.Value.Date)
adp.SelectCommand.Parameters.AddWithValue("@EndDate", myDateTimePicker.Value.Date.AddDays(1))
Take particular note of the operators used.
 
Back
Top