VBScript/SQL question 1

[jisoo22]

Hello people!

Ok, here's a little problem that I've been having with in VBScript and SQL. I have multiple text fields where I will be inputting serial numbers or "rug IDs" followed by a submit button. Upon hitting the button, this VBScript is supposed to connect to the database and do the following steps:
1. Call up a field from the database called "BaleNum" or bale number.
2. Add 1 to the pre-existing "BaleNum".

3. Take in all the serial numbers and enter them as new records in the field called "VendorRugID". At the same time, the Bale number or "BaleNum" is assigned to that serial number.

So in essence, there are multiple rugs that each have a serial number. Each serial number is assigned a bale number (hence like bales of hay, bales of rugs). Unfortunately I run into a little problem when I try to test it out...the browser (IE 5.5) says I have this problem:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/rugs/test.asp, line 32

Can anyone help me? The coding is as follows! Thank you!

<%
Dim i, v1, v2, v3, r
Set MM_insertCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_insertCmd.ActiveConnection = &quot;dsn=Glen;&quot;
If len(Request.QueryString(&quot;rugID&quot) > 0 then
strSelect = &quot;SELECT MAX(BaleNum) FROM Test&quot;
strSelect = &quot;UPDATE Test SET BaleNum = '&quot;&strSelect&&quot;+1'&quot;
End If


For i = 1 To 3
r = Cstr(Request.Form(&quot;rugID&quot; & i ))

If r <> &quot;&quot; Then

v1 = &quot;INSERT Test(VendorRugID, BaleNum) VALUES('&quot;&r&&quot;', '&quot;&strSelect&&quot;')&quot;
MM_insertCmd.CommandText = v1
MM_insertCmd.Execute

End If
Next
%>

 

[link9]

do this, and post your result:

response.write &quot;INSERT Test(VendorRugID, BaleNum) VALUES('&quot;&r&&quot;', '&quot;&strSelect&&quot;')&quot;

You may not even have to post it -- it might be obvious -- the problem, but I think it's safe to say we'd need to see the output from that statement to go further, what with your nested queries and such --

this i can certainly point out --

r = Cstr(Request.Form(&quot;rugID&quot; & i ))

should be:

r = Cstr(Request.Form(&quot;rugID&quot & i )

only your form element name should go inside those parens -- post back what you get from that response.write, and someone here will track it down for ya.


Paul Prewett

 

[Mighty]

As link9 points out, it is a good idea to print out the SQl statement and verify that it is correct. However, there is a slight modification that you should make:

v1 = &quot;INSERT INTO Test (VendorRugID, BaleNum) VALUES('&quot;&r&&quot;', '&quot;&strSelect&&quot;')&quot;
Mise Le Meas,

Mighty

 

[jisoo22]

Muchos gracias people =) I appreciate the help very much!

 

[jisoo22]

Hi again,

The last stuff I asked about works out great! Now I have one small snag. I run into a syntax error when I run this statement:

Recordset1.Source = &quot;SELECT MAX(BaleNum) AS maxBale FROM Test WHERE ConsignNum = SELECT MAX(ConsignNum)&quot;

What is the appropriate way of calling up the maximum value of the &quot;ConsignNum&quot; database field in this statement? I've tried several variations but they all lead up to the same thing. Help!


Thank You,
Jisoo22

 

[link9]

Recordset1.Source = &quot;SELECT MAX(BaleNum) AS maxBale FROM Test WHERE ConsignNum = (SELECT MAX(ConsignNum) FROM Text)&quot;

should do it.

 

[jisoo22]

Great! Seems like you have all the answers =) Thanks link9! Now I have one last question, it's one of those annoying logic errors. It's based on the original code up above but slightly modified so that it actually WORKS lol. Unfortunately when I check the database after I run it, the results aren't the only things in there. Here is the modified code:
<%@LANGUAGE=&quot;VBSCRIPT&quot;%>
<%
set Recordset1 = Server.CreateObject(&quot;ADODB.Recordset&quot

Recordset1.ActiveConnection = &quot;dsn=database;&quot;
Recordset1.Source = &quot;SELECT MAX(BaleNum) AS maxBale FROM Test WHERE ConsignNum = (SELECT MAX(ConsignNum) FROM Test)&quot;

Dim i, strSelect, q, r, v1,
Set MM_insertCmd = Server.CreateObject(&quot;ADODB.Command&quot
MM_insertCmd.ActiveConnection = &quot;dsn=database;&quot;
Recordset1.Open
q =cStr((Recordset1.Fields.Item(&quot;maxBale&quot.Value)+1)

For i = 1 To 30
r = Cstr(Request.Form(&quot;rugID&quot; & i ))

If r <> &quot;&quot; Then
v1 = &quot;INSERT INTO Test(VendorRugID, BaleNum) VALUES('&quot;+r+&quot;','&quot;+q+&quot;')&quot;
MM_insertCmd.CommandText = v1
MM_insertCmd.Execute
End If

Next
%>

<% Response.Redirect(&quot;

http://localhost/rugs/consign.asp&quot;)

%>

After this code is run, I get some funny results in the fields of the database. For instance, if I type in &quot;30&quot; and &quot;31&quot; in the form of the webpage, this script will indeed place them in the appropriate serial number field(aka VendorRugID) as well as insert a number for the &quot;baleNum&quot; field. But it looks like it loops around and starts the process over again from zero. Here's an example of the results:

Fields: VendorRug ID BaleNum
30 2
0 1
31 2
0 1

Can anyone tell me why it gives me that extra row in between while the loop goes about its business?

Thanks!
Jisoo22

 

[jisoo22]

Nevermind! I got it figured out, silly me hehe.

Thanks anyway!