Multi Row Insert?

  • 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

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

    %>

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

  • 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/

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

  • insert into tblCustPreference(

    custguid,

    name,

    ) SELECT

    x.custguid,

    x.name,

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

    WITH (

    custguid uniqueidentifier,

    name varchar(25),

    ) as x

  • 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