Error 0x80040E14?

[chickytoo]

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.
request2.asp, line 139

for i=1 to request.form("h idmasterlistno" ).count

error code here
Set rs = conn.execute( "Select masterlist_no , doc_num , rev , title from masterlist where masterlist_no = " &request.form(" hidmasterlistno ")(i)& "." )


next

[chickytoo]

any expert please help, really appreciated

i try to do some testing, which is print out the sql statement, shown below.

Select masterlist_no , doc_num , rev , title from masterlist where masterlist_no = 1111, 2222, 3333. Select masterlist_no , doc_num , rev , title from masterlist where masterlist_no = 1111, 2222, 3333. Select masterlist_no , doc_num , rev , title from masterlist where masterlist_no = 1111, 2222, 3333.

i dont know how to fix it, anyone can give me some idea? thanks

[JamieHowarth0]

Hi chickytoo,

If I have the following code:
[code=html]
<form action="nextpag e.asp" method="POST" name="myform">
<input type="text" name="myinput1" />
<input type="submit" name="mysubmit" /></form>
[/code]

I would use this ASP code:
[code=asp]
<% Request.Form("m yinput1") %>
[/code]
to retrieve the value that the user had inputted.

If you try this code:
[code=asp]
<%
strSQL = "SELECT masterlist_no , doc_num , rev , title from masterlist WHERE "

For I = 0 To (Request.Form.C ount - 1)
If I <>Request.Form. Count - 1) Then
strSQL = strSQL & "masterlist _no = " & Request.Form(I) & " OR"
Else
strSQL = strSQL & "masterlist _no = " & Request.Form(I) & ";"
End If
%>
[/code]

This will loop through your form values, as your current code is looping through an item's values when SQL requires a condition to separate values (notice I have put OR in the criteria).

If you were to add Response.Write( strSQL) you would see that your code would come up like so:

[code=sql]SELECT masterlist_no , doc_num , rev , title from masterlist WHERE masterlist_no = 1111 OR masterlist_no = 2222 OR masterlist_no = 3333;[/code]

There is a very good reference on the Forms collection in ASP at W3School's website for your future reference.

Hope it helps.

medicineworker

[chickytoo]

[code=asp]sql1 = "Select masterlist_no , doc_num , rev , title from masterlist where"
for m=1 to (request.form(" hidmasterlistno ").count - 1)
if m <> (request.form(" hidmasterlistno ").count - 1) then

sql1 = sql1 & " masterlist_no = " &request.Form(" hidmasterlistno ")(m) & " or"
else
sql1 = sql1 & " masterlist_no = " &request.Form(" hidmasterlistno ")(m) & " ;"
end if

response.write( sql1)
rs1.open sql1, conn1 [/code]

i try to modify your script and implement to mine because my previous page got a few input item, so i have to choose one form input item and loop through it. (your script assume there's only one form input item)

after run the code, same error again

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near ','.

i guess the error lies on these script?

[code=asp]sql1 = sql1 & " masterlist_no = " &request.Form(" hidmasterlistno ")(m) & " or"
else
sql1 = sql1 & " masterlist_no = " &request.Form(" hidmasterlistno ")(m) & " ;" [/code]

thanks a lot, i appreciate if you or other experts could give me some idea again ^^

[chickytoo]

i print out the query, and it looks like

[code=sql]Select masterlist_no , doc_num , rev , title from masterlist where masterlist_no = 1111, 2222, 3333 or masterlist_no = 1111, 2222, 3333 ;[/code]

anyone can help? thanks a lot

[jhardman]

so you have a set of inputs with the same name followed by a number (hidmasterlistn o1, hidmasterlistno 2, hidmasterlistno 3) ? Try [code=asp]request.form("h idmasterlistno" &m)[/code] I don't know if that solves the whole problem...

Jared

[chickytoo]

so you have a set of inputs with the same name followed by a number (hidmasterlistn o1, hidmasterlistno 2, hidmasterlistno 3) ? Try [code=asp]request.form("h idmasterlistno" &m)[/code] I don't know if that solves the whole problem...

Jared

thanks for your answer, but i didnt use same name for the form input item, i use the same name "hidmasterlistn o" cos i use loop to run the form(cos i need to use loop to retrieve data from database), so all the name for that form item is same.

is there other way to fix my problem? thanks again

[jhardman]

...is there other way to fix my problem? thanks again

I'm sure there is some way to fix your problem, but I'm not clear what the problem is. If you were to put this at the top of the page that queries the db, [code=asp]for each x in request.form
response.write x & ": " & request.form(x) & "<br>" & vbNewLine
next[/code]what is the output?

If you were to hard-code the query, how would it look?

Jared

[chickytoo]

hidmasterlistno : 1111, 2222, 3333, 1111, 2222, 3333, 1111, 2222, 3333
hiddocnum: AIR/EM8614/002, BR1, Cb-M311-06
hidrev: , ,
hidtitle: MICRO-M+EmulatorX2(16 16M PCI+EX2), Add 1 varistor, Removal of EZ Sticker
external: example1, example2, example3
qty: 3, 2, 1
purpose: 1, 2, 3
submit: submit

this is it, sorry that my script looks messy
thanks again to allocate time to help me ^^

i have tried to use other form item "hiddocnum" , but it comes out this error when i try to retrive and display it

Microsoft VBScript runtime (0x800A000D)
Type mismatch

[JamieHowarth0]

Hi chickytoo,

I think your SQL should look more like this:

[code=SQL]
SELECT masterlist_no, doc_num, rev, title FROM masterlist WHERE masterlist_no = 1111 OR masterlist_no = 2222 OR masterlist_no = 3333;
[/code]
Note that I have a) removed spaces between column names and commas, and b) changed your SQL criteria to what it should be.

Hope it helps.

medicineworker

[jhardman]

2 more questions :
1- why does the first input have 9 values (it looks like it only needs 3, or does it indeed need all nine)?
2- How should the query look?

Jared

[chickytoo]

2 more questions :
1- why does the first input have 9 values (it looks like it only needs 3, or does it indeed need all nine)?
2- How should the query look?

Jared

it looks like over loop when i use this script shown below

[code=asp]for i=1 to request.form("c hk").count
Set rs = conn.execute( "Select masterlist_no ,doc_num,rev,ti tle from masterlist where masterlist_no = "&request.form( "chk")(i)&" ." )

<input name="hidmaster listno" type="hidden" value="<% =request.form(" chk") %>">

next[/code]

thanks jared and medicineworker spending so much time on my problems, really appreciated