insert multiple rows into SQL table

[petertran123]

Hello all,

I have 2 pages:

Firstly, the page number one is displaying 20 rows of records with checkboxes next to each row. here is a code to display a records.


& "<TD><font face=""arial"">" & vbCrlf _
& "<INPUT TYPE=""CHECKBOX"" NAME=""checkall"" VALUE=""" & CInt(rsData("TRANS_ID")) & """ checked disabled>" & vbCrlf _
& "</font></TD>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimaryNameFirst") & "</font></TD>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimaryNameLast") & "</font></TD>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimarySSN") & "</font></TD>" & vbCrlf _
& "<TD><font face=""arial"">" & vbCrlf _
& "<INPUT TYPE=""text"" NAME=""" & iCount & ".cost"" VALUE=""" & FormatCurrency(rsData("CheckAmt"),2) & """>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("CheckDate") & "</font></TD>" & vbCrlf _
& "<INPUT
& "<TD><INPUT TYPE=TEXT NAME=""EFIN"" VALUE=""" & rsData("EFIN") & """></TD>" & vbCrLf _

& "</TD></TR>" & vbCrLf _

'Move to the next record...
rsData.MoveNext
iCount = iCount + 1

'Increment the count variable

Loop


strReturn = strReturn & vbCrlf _
& "<p>" & vbCrlf
Dim ITotal
ITotal = iCount - 1
strReturn = strReturn & vbCrlf _
& "<INPUT TYPE=HIDDEN NAME=""Count"" VALUE=""" & ITotal & """>" & vbCrlf _

& "</table>" & vbCrlf _


I want to select 5 records with checkboxes, and hit submit. 5 selected records will show on page number 2. and on page two in want to have an insert statement to insert all 5 records into the SQL table as the same time. Here is my insert code, but for something reason is not working, and i have an error occured: "Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated."


strSQL = "insert into CFM (PrimarySSN, Auth1, Auth1Stat, CheckNum1, Auth2, Auth2Stat, CheckNum2, Auth3, Auth3Stat, CheckNum3, Auth4, Auth4Stat, CheckNum4, CheckType, CheckDate, EFI, PaymentID, CheckAmt) values ('" & _
strPrimarySSN & "', '" & strAuth1 & "', '" & strAuth1Stat & "', '" & strCheckAuthNum1 & "', '" & strAuth2 & "', '" & strAuth2Stat & "', '" & strCheckAuthNum2 & "', '" & strAuth3 & "', '" & strAuth3Stat & "', '" & strCheckAuthNum3 & "','" & _
strAuth4 & "', '" & strAuth4Stat & "', '" & strCheckAuthNum4 & "', '" & strCheckType & "', '" & strCheckDate & "', '" & strEFI & "', '" & strPaymentID & "', CONVERT(money, '" & strCheckAmt & "'))"


i want to loop through each element, but not sure how i make it and here is looping sample:


'Now, we want to loop through each form element
iCount = Request("Count")
Dim iLoop
For iLoop = 0 to iCount
strCost = Request(iLoop & ".Cost")
strID = Request(iLoop & ".ID")


Please advise me how to put them together and make it work. Thanks

[oracleguy]

Well, I can at least help you on your SQL error, I beleive that means one of the fields you are inserting into is smaller than the data you are trying to put into it.

Like, if MyField was a nvarchar(4) and you tried to put 5 characters into it.

[petertran123]

i double check the fieldname for each table and there is nothing wrong with it. Any ideas....

[petertran123]

for some reason it only allow me to insert one row at the time. When i selected multiple checkboxes to insert and it gives me an error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated

[petertran123]

how do i convert this code into Insert?


Dim iLoop
For iLoop = 0 to iCount
strCost = Request(iLoop & ".Cost")
strID = Request(iLoop & ".ID")

strSQL = "UPDATE Products SET UnitPrice = " & CDBl(strCost) & _
" WHERE ProductID = " & strID

objConn.Execute strSQL
Next

[petertran123]

Here what i'm trying to do. i have 2 tables in SQL:

table 1 containing 10 records or more.
table 2 is totally empty:

on page one i have a code to display the values from table 1

& "<TD><INPUT TYPE=TEXT NAME=""" & iCount & ".cost"" VALUE=""" & rsData2("BegCkNo1") & """ size=""10""></TD>" & vbCrLf _


my hidden code:


Dim ITotal
ITotal = iCount - 1
<INPUT TYPE=HIDDEN NAME=""Count"" VALUE=""" & ITotal & """>


with the code above i can pull out 10 records display within 10 textboxes (note: there are only one field name for textbox), then i will do an insertion .

an insert code:

Dim i, strCheckNumIn
iCount = Request("Count")
For iLoop = 0 to iCount
strCheckNumIn = Request.Form("CheckNum")(iLoop)
strSQL = "insert into CFM (CheckNum1) Values ('" & strCheckNumIn & ")"

Conn.Execute(strSQL)

next



Now, i'm inserting 10 records that i'm displaying to table 2. For some reason i can't get it to work and nothing inserted into database

:confused:

[petertran123]

nevermind i got it working, thanks

[whammy]

Glad you got it working. Oracleguy was right though, that error means one thing only - you're trying to insert too much data into a field that won't hold it. Doesn't have anything to do with the name of the field, just its datatype and how large you've allowed it to be.

Another tip - it's generally bad practice to insert while you're in a loop (although there ARE situations in which I do this occasionally).

A better way (although probably still not the most elegant) is probably to keep building up a SQL statement of inserts into a string, and then once you're through with your loop, run it all at once... like:

INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf'); INSERT INTO tablename (blah) VALUES ('asdf');

If you're worried about losing data in the case of a SQL Server Timeout before your INSERT statement runs, or not knowing where you left off, you can always write stuff to a text file (using FSO) during each iteration of the loop, and then delete it once the database has finally been updated. That way, if something happens before the update statement gets triggered, you can just paste what's in your text file into SQL Query Analyzer and run it.

;)

[petertran123]

thanks for your great comments Whammy, i know you are the man

Now i have a little concern to my selective: i will show you what i mean.

this is my multiple insert code:


For i = 1 to Request("CheckAll").Count
strCheckNumOut = Request.Form("BegCkNo1")(i)
strPrimarySSNOut = Request.Form("PrimarySSN")(i)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"

Conn.Execute(strSQL)
next


Conn.Close
Set Conn = Nothing


i have 10 rows to insert as the same time. i have no problem with that, unitl i decide not to insert all the rows at the same time, but a portion of it.

10 rows listing:

1
2
3
4
5
6
7
8
9
10

when i unselected the checkbox number 7 and 8. and i want to be able to insert from 1, 2, 3, 4, 5, 6, 9, 10 and of course 7 and 8 left out. but for some reason it not work as i expected, it left out only the last rows 9 and 10. Can you or someone please advise why and what is happening? thanks

[Roy Sinclair]

You are making the mistake of assuming that the value of all the checkboxes are passed to your processing, in fact the browser only sends the value of checked checkboxes, unchecked boxes do not have their values sent. You need to make sure your values passed from the checkboxes give you the information to match up the checked checkbox to it's matching field.

[whammy]

Yup, Roy is right. If you need some clarification I can show you one way we usually handle stuff like that...

[petertran123]

Thanks Roy and Whammy,
would you please whammy, can i have an example? thanks

[whammy]

Hmm... well there's a few ways you could do that, actually. The way I was thinking of probably won't work for your situation.

What I usually do is go ahead and request (for instance) 10 checkboxes, named like:

check_1
check_2
check_3
check_4

etc.

then build a string out of them, like "XX XXX X X"

Where (in the example above), 3, 7, and 9 aren't checked.

Basically just loop through them... make sense?

[petertran123]

Thanks Whammy,

According to your example: it is won't work for my situation. I sometime have more than 10 records, maybe 100, or 200. I want to be able to pull just all records by using one field name. I will play around to see if there anything else. Beside you can give me as anything you come up with, and i will be truly appreciate it.

[Roy Sinclair]

Probably the best way would be to add the key value to the "value" property of the checkbox.

IE: If your key value is "A" and your checkbox value is "33" you'd make a value for the checkbox of "A33". Then as you process the form you can parse the checkbox value into it's key field and it's value field. Then you can use the key field value to line it up with the proper input tags for the other input fields (remember the checkbox fields are only passed if they are checked but the rest of the fields are passed).

[whammy]

What Roy said. Sometimes I do that, but separated with a Pipe or Comma or something so that they are easy to split... i.e. a value of:

A|35

[petertran123]

it sounds pretty correct, can you give me some of example? Thanks

[Roy Sinclair]

HTML


<input type="checkbox" name="CheckAll" value="A-33" checked="checked" />
<input type="checkbox" name="CheckAll" value="B-34" checked="checked" />


ASP

For i = 1 to Request("CheckAll").Count
Field1 = Mid(Request("CheckAll)(i),1,1)
Field2 = Mid(Request("CheckAll)(i),3,2)
' Field 1 should now contain either A or B
' Field 2 should now have 33 if Field1 is A or 34 if Field1 is B
Next


If the user doesn't alter the BegCkNo or Primary SSN fields you can actually just replace the "A" with one of those fields and the "33" with the other. Adjust how the MID function gets each piece of the input field and you can use that directly and even eliminate the other two fields. If the user does have to manipulate either or both of those fields then I would simply give each checkbox the value of the index into the other fields which would make your code look something like this:


For i = 1 to Request("CheckAll").Count
indexvalue = Request.Form("CheckAll")(i)
strCheckNumOut = Request.Form("BegCkNo1")(indexvalue)
strPrimarySSNOut = Request.Form("PrimarySSN")(indexvalue)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next

[petertran123]

i'm trying to run the second code, and there are an error occurred:

Request object, ASP 0105 (0x80004005)
An array index is out of range.
/tic01/Includes/content_page_checks.inc, line 853


For i = 1 to Request("CheckAll").Count
indexvalue = Request.Form("CheckAll")(i)
strCheckNumOut = Request.Form("BegCkNo1")(indexvalue)
strPrimarySSNOut = Request.Form("PrimarySSN")(indexvalue)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next

[petertran123]

this is my display.asp page:



Do While Not rsData.EOF
If iCount MOD 2 = 0 then 'even row
strReturn = strReturn & vbCrlf _
& "<tr bgcolor=""#666699"">" & vbCrlf
else
strReturn = strReturn & vbCrlf _
& "<tr bgcolor=""#6666FF"">" & vbCrlf
end if

& "<TD><font face=""arial"">" & vbCrlf _
& "<INPUT TYPE=""CHECKBOX"" NAME=""chkDisplayList"" VALUE=""" & CInt(rsData("TRANS_ID")) & """ checked>" & vbCrlf _
& "</font></TD>" & vbCrlf _
ÿ& "<TD><INPUT TYPE=TEXT NAME=""BegCkNo1"" VALUE="""" size=""10"" maxlength=""7""></TD>" & vbCrLf _

rsData.MoveNext
iCount = iCount + 1

'Increment the count variable

Loop


insert.asp page:


SET Conn = GetDBConnection(DATABASE_CONNECT_STRING)
strSQL = "Select * FROM CFM where EFI =" & Session("EFIN")



For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)
strCheckNumOut = Request.Form("BegCkNo1")(iLoop)
strPrimarySSNOut = Request.Form("PrimarySSN")(iLoop)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next


when i run the code and an error occurred at:
Request object, ASP 0105 (0x80004005)
An array index is out of range.
/tic01/Includes/content_page_checks.inc, line 825 which is line


strCheckNumOut = Request.Form("BegCkNo1")(iLoop)

[Roy Sinclair]

Add a line:

response.write("iloop=" & iloop & "<br />") just before the line where it's failing. Then you can see what it's trying to use as a subscript.

[petertran123]

Nothing happens as i can see, and the same error has occurred:
Error Type:
Request object, ASP 0105 (0x80004005)
An array index is out of range.


For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)

response.write("iloop=" & iloop & "<br />")

strCheckNumOut = Request.Form("BegCkNo1")(iLoop)
strPrimarySSNOut = Request.Form("PrimarySSN")(iLoop)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next


it works if i took out line:

iLoop = Request.Form("chkDisplayList")(i) and replace


For i = 1 to Request("chkDisplayList").Count
strCheckNumOut = Request.Form("BegCkNo1")(i)
strPrimarySSNOut = Request.Form("PrimarySSN")(i)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next


But, i will run into the same situation of:

when unselected the checkboxes number 7 and 8. and want to be abled to insert from 1, 2, 3, 4, 5, 6, 9, 10 and of course 7 and 8 left out. but for some reason it not work as i expected, it left out only the last rows 9 and 10.

[Roy Sinclair]

Then try this code:


For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)

response.write("iloop=" & iloop & "<br />")

'strCheckNumOut = Request.Form("BegCkNo1")(iLoop)
'strPrimarySSNOut = Request.Form("PrimarySSN")(iLoop)
'strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
'Conn.Execute(strSQL)
next


You need to see what's being processed and that means using simple debugging techniques like this to see what's happening. In this case it's complaining about the subscript being used so we'll want to look at the values of the subscript first.

[petertran123]

this is a whole picture of what i'm trying to do:

first.asp


Dim iCount
iCount = 0
Do While Not rsData.EOF
& "<INPUT TYPE=""CHECKBOX"" NAME=""checkall"" VALUE=""" & CInt(rsData("TRANS_ID")) & """>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimarySSN") & "</font></TD>" & vbCrlf _

& "<INPUT TYPE=HIDDEN NAME=""PrimarySSN"" VALUE=""" & rsData("PrimarySSN") & """>" & vbCrLf _
rsData.MoveNext
iCount = iCount + 1
Loop



second.asp

strCheckAll = Request.Form("checkall")
strPrimarySSN = Request.Form("PrimarySSN")



if strCheckAll = "" then
strReturn = strReturn & vbCrlf _
& "<p>You did not select any items to update.</p>" & vbCrlf
Else
SET Conn = GetDBConnection(DATABASE_CONNECT_STRING)
strSQL ="Select * FROM CHK WHERE TRANS_ID IN (" & strCheckAll & ")" & " ORDER BY " & sOrderBy & "" ' Order by PrimarySSN DESC"
Set rsData = conn.Execute(strSQL)


Dim iCount
iCount = 0
Do While Not rsData.EOF

& "<INPUT TYPE=""CheckBox"" NAME=""chkDisplayList"" VALUE=""" & CInt(rsData("TRANS_ID")) & """ checked>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimarySSN") & "</font></TD>" & vbCrlf _
& "<INPUT TYPE=HIDDEN NAME=""PrimarySSN"" VALUE=""" & rsData("PrimarySSN") & """>" & vbCrLf _
& "<TD><INPUT TYPE=TEXT NAME=""BegCkNo1"" VALUE="""" size=""10"" maxlength=""7"" onChange=""fillTextboxes(this);""></TD>" & vbCrLf _
rsData.MoveNext
iCount = iCount + 1
Loop


third.asp


For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)

strCheckNumOut = Request.Form("BegCkNo1")(iLoop)
strPrimarySSNOut = Request.Form("PrimarySSN")(iLoop)
strSQL = "insert into CFM (PrimarySSN, CheckNum1) Values ('" & _
strPrimarySSNOut & "', '" & strCheckNumOut & "')"
Conn.Execute(strSQL)
next


this is how i run into situation: An array index is out of range at line

strCheckNumOut = Request.Form("BegCkNo1")(iLoop). Please tell me what is wrong. Thanks

[petertran123]

For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)

response.write("iloop=" & iloop & "<br />")

Next

this code is giving me a list of checkboxes ID like

10
20
30
40

[petertran123]

i test a simple code and result comes out as follow:


For i = 1 to Request("chkDisplayList").Count
strCheckAll = Request("chkDisplayList")(i)
strPrimarySSNOut = Request("PrimarySSN")(i)
strSQL = "insert into CFM (PrimarySSN, TRANS_ID ) Values ('" & strPrimarySSNOut & "', '" & strCheckAll & "')"
Conn.Execute(strSQL)
next

Set rsData = Nothing
Conn.Close
Set Conn = Nothing


5 rows with checkboxes:

[I]Trans_ID[I]
1
2
3
4
5

when i unchecked a row 4th and 1,2,3,5 will be inserted into database, that is fine....and it works. But the PrimarySSN field is not work as i expected.

[I]PrimarySSN[I]

111-11-1111
222-22-2222
333-22-3333
444-44-4444
555-55-5555

with row 4th left out i expected items will be inserted into database as follow
111-11-1111
222-22-2222
333-22-3333
555-55-5555

but, it not do like it. it inserted:
111-11-1111
222-22-2222
333-22-3333
444-44-4444

and left out 555-55-5555, which not make any sence to me. Would you please help me. Thanks so much

[Roy Sinclair]

Originally posted by petertran123
For i = 1 to Request("chkDisplayList").Count
iLoop = Request.Form("chkDisplayList")(i)

response.write("iloop=" & iloop & "<br />")

Next

this code is giving me a list of checkboxes ID like

10
20
30
40

These values should be
1
2
3
4

Then you could use them properly as subscripts to the correct checkAll and SSN values. The fact that they are 10 times the needed value is why you got the subscript error in the earlier tests.

[petertran123]

i'm sorry for missing typo the ID should be

1
2
3
4
5

then how do i fix this problem.. for ID's field to match PrimarySSN's field? According to my last question

[whammy]

I'm not sure I follow the problem anymore. :|

[petertran123]

Hi whammy,

this is what i'm trying to do: i'm running into situation of inserting records in SQL database.

my code as follows:


For i = 1 to Request("chkDisplayList").Count

strCheckAll = Request.Form("chkDisplayList")(i)
strPrimarySSNOut = Request.Form("PrimarySSN")(i)

response.write("strCheckAll=" & strCheckAll & "<br />")
response.write("strPrimarySSNOut=" & trPrimarySSNOut "<br />")

Next



i have a display.asp page's showing
5 or more records. There are a checkboxe for each record:

CheckBox
[I]Trans_ID[I] [I]PrimarySSN[I]
1------------------------ 111-11-1111
2------------------------ 222-22-2222
3------------------------ 333-22-3333
4------------------------ 444-44-4444
5------------------------ 555-55-5555


when i unchecked a row number 4th and 1,2,3,5 will be inserted into database, that is fine....and it works for Trans_ID field. But the PrimarySSN field is not work as i expected.

when i unchecked a row 4th and the others 4 items will be inserted into database as follows

1 111-11-1111
2 222-22-2222
3 333-22-3333
5 555-55-5555

but, it not work like it. it inserted:
1 111-11-1111
2 222-22-2222
3 333-33-3333
5 444-44-4444

it seems to me the PrimarySSN field reset from 1 to 4 and not work related to Trans_ID field

it lefts out 555-55-5555, which is a last row of it. beside, No matter what i unckecked row 1 or row 3, it always left out the last row of PrimarySSN's field. Would you please take a look and tell me what is wrong. Hope you understand my problem .!

[whammy]

Can you post the HTML code you're using for your checkboxes? :)

[petertran123]

this is my HTML code



if strCheckAll = "" then
strReturn = strReturn & vbCrlf _
& "<p>You did not select any items to update.</p>" & vbCrlf
Else
SET Conn = GetDBConnection(DATABASE_CONNECT_STRING)
strSQL ="Select * FROM CHK WHERE TRANS_ID IN (" & strCheckAll & ")" & " ORDER BY " & sOrderBy & "" ' Order by PrimarySSN DESC"
Set rsData = conn.Execute(strSQL)


Dim iCount
iCount = 0
Do While Not rsData.EOF

& "<INPUT TYPE=""CheckBox"" NAME=""chkDisplayList"" VALUE=""" & CInt(rsData("TRANS_ID")) & """ checked>" & vbCrlf _
& "<TD><font face=""arial"" size=""2"" color=""white"">" & rsData("PrimarySSN") & "</font></TD>" & vbCrlf _
& "<INPUT TYPE=HIDDEN NAME=""PrimarySSN"" VALUE=""" & rsData("PrimarySSN") & """>" & vbCrLf _
& "<TD><INPUT TYPE=TEXT NAME=""BegCkNo1"" VALUE="""" size=""10"" maxlength=""7"" onChange=""fillTextboxes(this);""></TD>" & vbCrLf _
rsData.MoveNext
iCount = iCount + 1
Loop
End If

[whammy]

I'm at work right now, but when I get home I'll take a look at this for you. :)

[whammy]

Ok, I think what Roy was trying to tell you was to put the SSN and the ID in the field's value, like:

& "<INPUT TYPE=""CheckBox"" NAME=""chkDisplayList"" VALUE=""" & CInt(rsData("TRANS_ID")) & "|" & rsData("PrimarySSN") & """ checked>" & vbCrlf

Now, when you post the checkbox to the next form, you'll have BOTH values, like:

23|3453453455,24|1231231233

Then you can split them up or whatnot to insert them into the database... does that help?

[petertran123]

this is not what i'm looking for, but thanks anyway. I will find out other solution..thanks again Whammy

[whammy]

Hmm... I'm trying to think of one, but I am just brain dead right now after all of the programming I've been doing at work (and I'm also trying to figure out a problem with BETWEEN using dates in SQL (not to mention in a stored procedure, but that is irrelevant) that I have to fix on Sunday, and I have a date tonight!).

I will try and look at this again Sunday afternoon if I have a chance, my Saturday is totally booked as well... :(

Hopefully you'll figure it out before then - just think about how checkboxes work, and you should arrive at a solution yourself.

[petertran123]

Thanks Whammy, just wanted to let you know that. I got it's working and everything is fine now :thumbsup: thanks again.

[whammy]

Glad you figured it out. ;)