June 27, 2003 at 12:49 pm
Does anyone know of a way to insert multiple rows of data at one time from a form? I would like to be able to enter the name, address and phone number of multiple individuals at one time. This is what I've tried, but it's not working..... Thanx
For I = 1 To RecCount
RegQry = "insert into Test (name, address, phone)"
RegQry = RegQry & " values ('" & request("name")(I) & "','"
RegQry = RegQry & request("address")(I) & "','"
RegQry = RegQry & request("phone")(I) & "'"
Registration.Execute(RegQry)
Next
Edited by - hitek2000 on 06/27/2003 12:50:09 PM
June 27, 2003 at 12:54 pm
Best way to DEBUG is to Response.Write the RegQry string. Go ahead and post the result of this code:
For I = 1 To RecCount
RegQry = "insert into Test (name, address, phone)"
RegQry = RegQry & " values ('" & request("name")(I) & "','"
RegQry = RegQry & request("address")(I) & "','"
RegQry = RegQry & request("phone")(I) & "'"
Response.Write RegQry & "<br>"
'Registration.Execute(RegQry)
Next
June 27, 2003 at 1:07 pm
Thanx for the help.... I'm getting a different error.... here it is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xda0 Thread 0xa90 DBC 0x10a6004 Jet'.
I'm using MS Access, here is the connection string..... do I need adovbs?
<%Response.buffer = True%>
<%
Set registration = Server.CreateObject("ADODB.Connection")
registration.Open"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\MSP\Database\mspsl.mdb;"
%>
June 27, 2003 at 1:17 pm
Have you set up a valid DSN on the server for MS Jet 4.0?
June 27, 2003 at 9:04 pm
As far as inserting multiple records, there are at least three ways to do it:
1 - Do what you're doing, execute one insert query for each record
2 - Build up a longer string comprised of multiple inserts each separated by a semi colon, then execute once outside the loop
3 - Build up an XML doc, send that to the server, use OpenXML inside a proc to do the insert.
Note that you're not checking for embedded single quotes, so if you get a value like O'hara your insert will fail. This technique also leaves you vulnerable to a sql injection attack, if you use a stored proc you can eliminate that risk.
Andy
June 27, 2003 at 11:12 pm
Thanx Andy...... Do you know where I can find some examples?
June 28, 2003 at 1:15 am
insert into tblCustPreference(
custguid,
name,
) SELECT
x.custguid,
x.name,
FROM OPENXML (@xdoc, '*/cust/coldata',1)
WITH (
custguid uniqueidentifier,
name varchar(25),
) as x
June 28, 2003 at 9:19 am
Thanx kraks... I haven't used XML before. does it have to be used with a stored procedure? Can this be used with MS Access also?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply