asked on

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

Guy Hengel [angelIII / a3]

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

TextReport

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

bigmoxy

ASKER

I get the same error at the same place with both of your suggested solutions.

bigmoxy

ASKER

Here is my code for your review.
Index.aspx.txt

dqmq

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.

bigmoxy

ASKER

I understand your point but that same connection string is used without problems in other places. Removing the parens didn't make a difference.

TextReport

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

dqmq

Are you certain it's a compliation error?

Does this help:

" WHERE [Web Special] = ""Yes""" & _

bigmoxy

ASKER

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

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TextReport

If this is in ASP.NET so have you tried rightNow.ToString("MM/DD/YYYY") instead of DATE()
Cheers, Andrew

bigmoxy

ASKER

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"

Scott McDaniel (EE MVE )

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"

TextReport

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

bigmoxy

ASKER

I get several syntax errors in VS 2008, see screen print - syntax-error.png
syntax-error.png

TextReport

You have moved the Now.ToString to outside your SQL String therefore you don't need 2 " you need just 1
Cheers, Andrew

bigmoxy

ASKER

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

bigmoxy

ASKER

I overlooked this suggestion but that is the one that works! Thank you very much for staying with me on this!

Regards,
Tim

TextReport

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

bigmoxy

ASKER

Yes, that works too! Thank you Andrew!

Regards,
Tim