Why am I getting this Access SQL error - BC30287: '.' expected. ?

When I try to run the page that contains this code, I get a compilation error that points to the line delimited by ***. I don't understand the error. Could someone please help me correct it?

Thank you!
Tim

    Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Handles Me.Load
 
      Dim dbPath As String = Server.MapPath("/currentDB/CVGWebsite.mdb")
        Dim strConnection As String = _
            ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & _
             ";Persist Security Info=False;")

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
      ***  " AND [Web Start] <= " & Date() & _  ***
            " AND [Web Stop] >= " & Date() & _
            " ORDER BY Make ASC, Model ASC"

        Dim conn As New OleDbConnection(strConnection)
        Dim daFeatured As New OleDbDataAdapter(strSQLFeatured, conn)

        Dim ds As New DataSet()
        daFeatured.Fill(ds, "FeaturedVehicles")
       
        If ds.Tables("FeaturedVehicles").Rows.Count > 0 Then
            featuredVehicleRepeater.Visible = True
        Else
            featuredVehicleRepeater.Visible = False
        End If

    End Sub
bigmoxyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TextReportConnect With a Mentor Commented:
Can you use the DATE() within the SQL statement, I suspect it doesn't like DATE()

     " AND [Web Start] <= DATE()" & _

Cheers, Andrew
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this:
Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = -1 & _
            " AND [Web Start] <= " & Date() & _  
            " AND [Web Stop] >= " & Date() & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
 
TextReportCommented:
missing " and if you are passing dates then you need to ensure they are in MM/DD/YYYY format with # around.
Cheers, Andrew
Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = -1" & _
            " AND [Web Start] <= #" & Format(Date(),"mm/dd/yyyy") & "#" & _  
            " AND [Web Stop] >= #" & Format(Date(),"mm/dd/yyyy") & "#" & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bigmoxyAuthor Commented:
I get the same error at the same place with both of your suggested solutions.
0
 
bigmoxyAuthor Commented:
Here is my code for your review.
Index.aspx.txt
0
 
dqmqCommented:
I think your problem is here:

Dim strConnection As String = _
            ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & _
             ";Persist Security Info=False;")

Remove the parens.
0
 
bigmoxyAuthor Commented:
I understand your point but that same connection string is used without problems in other places. Removing the parens didn't make a difference.
0
 
TextReportCommented:
I suspect it doesn't line DATE(), have you tried hardcoding a date to check the syntax

     " AND [Web Start] <= #02/03/2008#" & _

Cheers, Andrew
0
 
dqmqCommented:
Are you certain it's a compliation error?

Does this help:

           " WHERE [Web Special] = ""Yes""" & _
0
 
bigmoxyAuthor Commented:
TextReport:
Hard coding the date does work however that cannot be the permanent solution. I need to be comparing the dates in the db to the current date.

dqmq:
Yes, it's a compilation error. It's pretty obvious. See attached screen print. Also, treating Yes as a string would not be correct. Web Special is a boolean/bit field with values Yes/No. WHERE [Web Special] = Yes and [Web Special] = -1 both work.

BC30287-error.png
0
 
TextReportCommented:
If this is in ASP.NET so have you tried rightNow.ToString("MM/DD/YYYY") instead of DATE()
Cheers, Andrew
0
 
bigmoxyAuthor Commented:
Exception Details: System.Data.OleDb.OleDbException: Invalid use of '.', '!', or '()'. in query expression '[Web Special] = Yes AND [Web Start] <= Now.ToString("MM/DD/YYYY") AND [Web Stop] >= Now.ToString("MM/DD/YYYY"'.

rightNow is not a defined function so I used "Now". This is my SQL

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <= Now.ToString(""MM/DD/YYYY"")" & _
            " AND [Web Stop] >= Now.ToString(""MM/DD/YYYY"")" & _
            " ORDER BY Make ASC, Model ASC"
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this:

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <=#" &  Now.ToString(""MM/DD/YYYY"")" & "#" _
            " AND [Web Stop] >=#"& Now.ToString(""MM/DD/YYYY"")" & "#" _
            " ORDER BY Make ASC, Model ASC"
0
 
TextReportCommented:
Now.ToString is an ASP function and therefore has to be outside your SQL string so you need to do the following.
YES is not a defined constant in the JET Engine and you will need to compart a BIT field with 0 or -1 but the true definition of true is <> 0 this is then transportable between JET where -1 is true and SQL Server where 1 is true.

Cheers, Andrew
        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] <> 0" & _
            " AND [Web Start] <= #" & Now.ToString("MM/DD/YYYY") & "#" & _
            " AND [Web Stop] >= #" & Now.ToString("MM/DD/YYYY") & "#" & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
 
bigmoxyAuthor Commented:
I get several syntax errors in VS 2008, see screen print - syntax-error.png
syntax-error.png
0
 
TextReportCommented:
You have moved the Now.ToString to outside your SQL String therefore you don't need 2 " you need just 1
Cheers, Andrew
0
 
bigmoxyAuthor Commented:
Sorry wasn't thinking... Here's my code now and a new error.

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <=#" & Now.ToString("MM/DD/YYYY") & "#" & _
            " AND [Web Stop] >=#" & Now.ToString("MM/DD/YYYY") & "#" & _
            " ORDER BY Make ASC, Model ASC"

query-error.png
0
 
bigmoxyAuthor Commented:
I overlooked this suggestion but that is the one that works! Thank you very much for staying with me on this!

Regards,
Tim
0
 
TextReportCommented:
Just to finish off teh Now.ToString it looks like the flexibility of this was our downfall. It looks like it is case dependant so you can try. I would suggest it is probably worth checking this out as you may need it in ASP.Net anyway.

Now.ToString("MM/dd/yyyy")

Cheers, Andrew
0
 
bigmoxyAuthor Commented:
Yes, that works too! Thank you Andrew!

Regards,
Tim
0
All Courses

From novice to tech pro — start learning today.