Multi Row Insert?

Question

Multi Row Insert?

hitek2000

SSC Veteran

Points: 244
More actions
June 27, 2003 at 12:49 pm

#81086

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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

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

hitek2000 SSC Veteran Points: 244 More actions · Answer 2

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;"

%>

jpipes SSChampion Points: 12230 More actions · Answer 3

Have you set up a valid DSN on the server for MS Jet 4.0?

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

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

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn

hitek2000 SSC Veteran Points: 244 More actions · Answer 5

Thanx Andy...... Do you know where I can find some examples?

kraks SSC Veteran Points: 295 More actions · Answer 6

insert into tblCustPreference(

custguid,

name,

) SELECT

x.custguid,

x.name,

FROM OPENXML (@xdoc, '*/cust/coldata',1)

WITH (

custguid uniqueidentifier,

name varchar(25),

) as x

hitek2000 SSC Veteran Points: 244 More actions · Answer 7

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?

Multi Row Insert?

Cookies on SQLServerCentral