Anomoly - cannot use the word "Size" in a SELECT s

Results 1 to 2 of 2

Thread: Anomoly - cannot use the word "Size" in a SELECT s

  1. #1
    Join Date
    Dec 1969

    Default Anomoly - cannot use the word "Size" in a SELECT s

    Has anyone else ever seen this anomaly?<BR><BR>I have experienced this problem in a much larger application but scaled it down here for the purposes of this post.<BR><BR>The problem is this:<BR>I cannot use the word "Size" as part of a SQL "SELECT" statement in a recordset that is part of a DSN-Less connection.<BR><BR>In this case, there is an MS Access database called "testdatabase.mdb" and a table in testdatabase.mdb called "TestTable", and a single field in TestTable called "Size"<BR>I populated the field Size in TestTable with the values "Small", "Medium", and "Large"<BR><BR>I have included in the code two options for the Select statement, one uses the "*" options for ALL columns, and the other specifies the column "Size"<BR><BR>This only fails in a DSN-Less connection, as show in the example below, it works if you define a DSN.<BR><BR>Furthermore, the "Select * From TestTable" string works but "Select Size From TestTable" does not.<BR><BR>In my case, I need to specify the Size column because I need to be able to do a "GROUP BY" on this column.<BR><BR>Is there any explanation for this? Is there any solution for this? Currently my solution has been to change the name of the field in the database from "Size" to "Size1"<BR>----------------------------------------------------------<BR>&#060;%<BR>Option Explicit<BR><BR>&#039; Declarations<BR> Dim dcnDB, strDBLoc, sql, rs<BR><BR>&#039; Open Database Connection (DSN-Less)<BR> Set dcnDB = Server.CreateObject("ADODB.Connection")<BR> strDBLoc = Server.MapPath("testdatabase.mdb")<BR> dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & strDBLoc<BR> dcnDB.Open<BR>%&#062; <BR>&#060;html&#062;<BR>&#060;head&#062;<BR>&#06 0;/head&#062;<BR>&#060;body&#062;<BR>&#060;%<BR>&#039 ; The following sql statement works using a DSN connection <BR>&#039; but has problems using a DSN-less connection<BR> sql = "SELECT Size FROM TestTable"<BR> <BR>&#039; The following sql statement works fine in both DSN and DSN-less connections<BR> &#039; sql = "SELECT * FROM TestTable"<BR><BR>&#039; Set Recordset<BR> Set rs = Server.CreateObject("ADODB.Recordset")<BR> rs.Open sql, dcnDB, 3, 3<BR><BR>&#039; Loop through sizes and format output<BR> rs.MoveFirst<BR> Do Until rs.EOF<BR> Response.Write rs("Size") & "<BR>"<BR> rs.MoveNext<BR> Loop<BR><BR>&#039; Close Variables<BR> rs.Close<BR> dcnDB.Close<BR> Set dcnDB = Nothing<BR>%&#062;<BR>&#060;/body&#062;<BR><BR>&#060;/html&#062;<BR>

  2. #2
    Join Date
    Dec 1969

    Default AnomAly, by the way...

    And it&#039;s not an anomaly.<BR><BR>Probably your DSN connection is via an Access driver. Your DSN-less is via the JET OLE DB drivers. And Jet SQL has more keywords than standard Access SQL. Including, of course, SIZE.<BR><BR>So do the same thing we all do when we have a keyword as a field name (or have spaces in a field name): Enclose the name in [...]<BR><BR>sql = "SELECT [Size] FROM TestTable"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts