|
 |
access_asp thread: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression
Message #1 by "sharon buggle" <s.buggle@w...> on Wed, 14 Nov 2001 21:31:09
|
|
I am getting this error below:[Microsoft][ODBC Microsoft Access Driver]
Syntax error (comma) in query expression 'dbo_Invoice_Items.invoice_number
= 1, , , , , , , , , , 1 and dbo_Invoice_Items.ctn_id
= '1234567, , , , , , , , , , ''.
/webpub/Status3.asp, line 132
i have this page with 11 rows and 4 columns which allows you to enter up
to 11 id numbers in the first two columns and when you hit the show button
the third column will have the returned recordset field for each row.
Here is code below
if Len(Request("btnShow.x")) > 0 then
response.write whatsentered
Sqlst = "SELECT dbo_Status_Codes.status_code FROM ((dbo_Invoice_Comments
INNER JOIN " & _
"dbo_Invoice_Table ON dbo_Invoice_Comments.invoice_number =
dbo_Invoice_Table.invoice_number)" & _
"INNER JOIN dbo_Invoice_Items ON dbo_Invoice_Table.status_code_id =
dbo_Invoice_Items.status_code_id)" & _
"INNER JOIN dbo_Status_Codes ON dbo_Invoice_Items.status_code_id =
dbo_Status_Codes.status_code_id "
end if
for j = 0 to (Maxrecords - 1)
whatsentered = 0
response.write "<TR>"
if Len(Request("InvoiceNum")) > 0 then
response.write "<TD align='middle'> <input type ='text' name
= 'InvoiceNum' size=12 value='" & Request("InvoiceNum") & "'>"
if whatsentered > 0 then
Sqlst = Sqlst & " and dbo_Invoice_Items.invoice_number = " &
Request.Form("InvoiceNum")
else
Sqlst = Sqlst & " where dbo_Invoice_Items.invoice_number = " &
Request.Form("InvoiceNum")
whatsentered = whatsentered + 1
end if
else
response.write "<TD align='middle'> <input type ='text' name
= 'InvoiceNum' size=12 >"
end if
response.write "</TD>"
if Len(Request("ContNum")) > 0 then
response.write "<TD align='middle'> <input type ='text' name = 'ContNum'
size=12 value=" & Request("ContNum") & ">"
if whatsentered > 0 then
Sqlst = Sqlst & " and dbo_Invoice_Items.ctn_id = '" & Request
("ContNum") & "'"
else
Sqlst = Sqlst & " where dbo_Invoice_Items.ctn_id = '" & Request
("ContNum") & "'"
whatsentered = whatsentered + 1
end if
else
response.write "<TD align='middle'> <input type ='text' name = 'ContNum'
size=12>"
end if
response.write "</TD>"
if whatsentered > 0 then
RecordCol.Open Sqlst,Mycon,2,2
response.write RecordCol("status_code")
fieldvalue = RecordCol("status_code")
response.write "<td align='middle'>" & fieldvalue
statusfield = RecordCol("status_code")
response.write "</td>"
RecordCol.close
else
response.write "<td align='middle'> </td>"
end if
response.write "<TD align=middle><SELECT name='status' value= " & Request
("status") & ">"
Sqlst = "SELECT dbo_Status_Codes.status_code FROM dbo_Status_Codes"
RecordCol.Open Sqlst,Mycon,2,2
do
Response.write "<Option value = ' "& RecordCol("status_code")& " '> " &
RecordCol("status_code") & " </option>"
RecordCol.MoveNext
loop until RecordCol.eof
response.write "</select>"
response.write "</td>"
response.write "</TR>"
RecordCol.close
next
response.write "</TABLE>"
response.write "<TABLE WIDTH=555 BORDER=0 CELLSPACING=0 CELLPADDING=1 >"
response.write "<TR>"
response.write "<TD>Comment <INPUT type ='text' size=40
maxlength=255 name='InvComt' >"
response.write "</TD>"
response.write "<TD ALIGN=right><input type ='image'
src='SiteCom.gif' name='btnShow'>"
response.write "</TD></TR>"
response.write "</TABLE>"
What am i doing wrong?
Message #2 by "Ken Schaefer" <ken@a...> on Thu, 15 Nov 2001 11:38:07 +1100
|
|
Just before you execute the SQL statement, please do this:
Response.Write(strSQL)
Response.End
and post the output to the list.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "sharon buggle" <s.buggle@w...>
Subject: [access_asp] [Microsoft][ODBC Microsoft Access Driver] Syntax error
(comma) in query expression
: I am getting this error below:[Microsoft][ODBC Microsoft Access Driver]
: Syntax error (comma) in query expression 'dbo_Invoice_Items.invoice_number
: = 1, , , , , , , , , , 1 and dbo_Invoice_Items.ctn_id
: = '1234567, , , , , , , , , , ''.
:
: /webpub/Status3.asp, line 132
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "sharon buggle" <s.buggle@w...> on Fri, 16 Nov 2001 23:50:59
|
|
> Just before you execute the SQL statement, please do this:
>
> Response.Write(strSQL)
> Response.End
>
> and post the output to the list.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "sharon buggle" <s.buggle@w...>
> Subject: [access_asp] [Microsoft][ODBC Microsoft Access Driver] Syntax
error
> (comma) in query expression
>
>
> : I am getting this error below:[Microsoft][ODBC Microsoft Access Driver]
> : Syntax error (comma) in query
expression 'dbo_Invoice_Items.invoice_number
> : = 1, , , , , , , , , , 1 and dbo_Invoice_Items.ctn_id
> : = '1234567, , , , , , , , , , ''.
> :
> : /webpub/Status3.asp, line 132
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> See this page has 11 possible queries when they enter the InvoiceNum and
the ContNum and hit the button when the table has already been written and
goes back through it checks each row to see if they have something entered
and then the Sqlst is executed then goes to the next row .... 11 times
I have changed the code some but still get the same error . When i change
the number or rows to print to 1 it works.
Here is the code
Sqlext1 = " where dbo_Invoice_Items.invoice_number = " & Request
("InvoiceNum")
Sqlext2 = "and dbo_Invoice_Items.ctn_id = '" & Request("ContNum")
& "'"
if Len(Request("btnShow.x")) > 0 then
Sqlst = "SELECT dbo_Invoice_Items.invoice_number,
dbo_Invoice_Items.ctn_id, dbo_Status_Codes.status_code" & _
" FROM dbo_Invoice_Comments INNER JOIN ((dbo_Invoice_Table INNER JOIN
dbo_Invoice_Items ON dbo_Invoice_Table.status_code_id " & _
"= dbo_Invoice_Items.status_code_id) INNER JOIN dbo_Status_Codes ON
dbo_Invoice_Items.status_code_id = dbo_Status_Codes.status_code_id)" & _
" ON dbo_Invoice_Comments.invoice_number =
dbo_Invoice_Table.invoice_number "
end if
session("ErrorMes") = ""
'if i change this variable Maxrecords to 1 it works
Maxrecords = 11
introw = 0
for j = 0 to (Maxrecords - 1)
whatsentered = 0
response.write "<TR>"
if Len(Request("InvoiceNum")) > 0 then
whatsentered = whatsentered + 1
' response.write "<TD align='middle'> <input type ='text' name
= 'InvoiceNum' size=12 value=' " & RecordCol("invoice_number") & "')>"
response.write "<TD align='middle'> <input type ='text' name
= 'InvoiceNum' size=12 value= '" & Request("InvoiceNum") & "'>"
introw = introw + 1
else
response.write "<TD align='middle'> <input type ='text' name
= 'InvoiceNum' size=12 >"
end if
response.write "</TD>"
response.write introw
'Session("ContN") = Request("ContNum")
if Len(Request("ContNum")) > 0 then
whatsentered = whatsentered + 1
response.write "<TD align='middle'> <input type ='text' name = 'ContNum'
size=12 value=' " & Request("ContNum") & "'>"
else
response.write "<TD align='middle'> <input type ='text' name = 'ContNum'
size=12 >"
end if
response.write "</TD>"
if whatsentered = 2 then
Sqlst = Sqlst + Sqlext1 + Sqlext2
RecordCol.Open Sqlst,Mycon,2,2
fieldvalue = RecordCol("status_code")
response.write "<td align='middle'>" & fieldvalue
response.write "</td>"
else
response.write "<td align='middle'> </td>"
if Len(Request("btnShow.x")) > 0 then
session("ErrorMes") = "Invoice number and Containter Id must be entered"
end if
end if
response.write "<TD align=middle><SELECT name='status' value= " & Request
("status") & ">"
'this part works
Sqlst = "SELECT dbo_Status_Codes.status_code FROM dbo_Status_Codes"
RecordCol.Open Sqlst,Mycon,2,2
do
Response.write "<Option value = ' "& RecordCol("status_code")& " '> " &
RecordCol("status_code") & " </option>"
RecordCol.MoveNext
loop until RecordCol.eof
if Len(fieldvalue) > 0 then
response.write "<option selected value = " & fieldvalue & "> " &
fieldvalue & " </option>"
end if
RecordCol.close
response.write "</select>"
response.write "</td>"
response.write "</TR>"
next
response.write "</TABLE>"
Please help, have a deadline on this
Thanks!!!!!!!!!!!
Message #4 by "Zee Computer Consulting" <zee@t...> on Fri, 16 Nov 2001 17:13:30 -0800
|
|
In your code fragment below, on the second and subsequent times through the
loop you add additional copies of your SQL substrings to the already
complete SQL string:
> for j = 0 to (Maxrecords - 1)
.
.
.
> if whatsentered = 2 then
> Sqlst = Sqlst + Sqlext1 + Sqlext2
> ' Add the line below for tracing of problem
> Response.Write( Sqlst )
> RecordCol.Open Sqlst,Mycon,2,2
.
.
.
> next j
Change it to this:
if whatsentered = 2 then
NewSqlString = Sqlst & Sqlext1 & Sqlext2
Response.Write( NewSqlString )
RecordCol.Open NewSqlString, Mycon, 2, 2
Does this help?
- Z
----- Original Message -----
From: "sharon buggle" <s.buggle@w...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, November 16, 2001 11:50 PM
Subject: [access_asp] Re: Syntax error (comma) in query expression
Message #5 by "sharon buggle" <s.buggle@w...> on Sat, 17 Nov 2001 14:06:58
|
|
> In your code fragment below, on the second and subsequent times through
the
> loop you add additional copies of your SQL substrings to the already
> complete SQL string:
>
>
> > for j = 0 to (Maxrecords - 1)
> .
> .
> .
> > if whatsentered = 2 then
> > Sqlst = Sqlst + Sqlext1 + Sqlext2
> > ' Add the line below for tracing of problem
> > Response.Write( Sqlst )
> > RecordCol.Open Sqlst,Mycon,2,2
> .
> .
> .
> > next j
>
>
> Change it to this:
>
> if whatsentered = 2 then
> NewSqlString = Sqlst & Sqlext1 & Sqlext2
> Response.Write( NewSqlString )
> RecordCol.Open NewSqlString, Mycon, 2, 2
>
>
> Does this help?
>
> - Z
>
>
>
> ----- Original Message -----
> From: "sharon buggle" <s.buggle@w...>
> To: "Access ASP" <access_asp@p...>
> Sent: Friday, November 16, 2001 11:50 PM
> Subject: [access_asp] Re: Syntax error (comma) in query expression
>
> I tried what you suggested and it did not work.
Do you have any other things i could try??
Thanks for your time.
SB
>
Message #6 by "Zee Computer Consulting" <zee@t...> on Sat, 17 Nov 2001 16:42:42 -0800
|
|
Please send the entire code again and the results from Request.Write(Sqlst).
-- Z
----- Original Message -----
From: "sharon buggle" <s.buggle@w...>
To: "Access ASP" <access_asp@p...>
Sent: Saturday, November 17, 2001 2:06 PM
Subject: [access_asp] Re: [Microsoft][ODBC Microsoft Access Driver] Syntax
error (comma) in query expression
> > In your code fragment below, on the second and subsequent times through
> the
> > loop you add additional copies of your SQL substrings to the already
> > complete SQL string:
> >
> >
> > > for j = 0 to (Maxrecords - 1)
> > .
> > .
> > .
> > > if whatsentered = 2 then
> > > Sqlst = Sqlst + Sqlext1 + Sqlext2
> > > ' Add the line below for tracing of problem
> > > Response.Write( Sqlst )
> > > RecordCol.Open Sqlst,Mycon,2,2
> > .
> > .
> > .
> > > next j
> >
> >
> > Change it to this:
> >
> > if whatsentered = 2 then
> > NewSqlString = Sqlst & Sqlext1 & Sqlext2
> > Response.Write( NewSqlString )
> > RecordCol.Open NewSqlString, Mycon, 2, 2
> >
> >
> > Does this help?
> >
> > - Z
> >
> >
> >
> > ----- Original Message -----
> > From: "sharon buggle" <s.buggle@w...>
> > To: "Access ASP" <access_asp@p...>
> > Sent: Friday, November 16, 2001 11:50 PM
> > Subject: [access_asp] Re: Syntax error (comma) in query expression
> >
> > I tried what you suggested and it did not work.
> Do you have any other things i could try??
>
>
> Thanks for your time.
> SB
> >
>
>
|
|
|
View Cart
