|
 |
asp_databases thread: Adding data to more than one table?
Message #1 by "Frode" <fstroemm@o...> on Tue, 31 Jul 2001 17:02:27
|
|
I want to know if it's possible to add data to more than one table using
just one connection/execute command.
I have several tables and I'm not sure how to do this an easy way.
Here's my first table:
adminID = request.form("aperson")
overskrift = request.form("overskrift")
nyhet = request.form("melding")
dato = date
klokkeslett = time
tabkol = "[adminID] , [overskrift] , [nyhet] , [dato] , [klokkeslett]"
feltverdi = "'" & adminID & "',"
feltverdi = feltverdi & "'" &overskrift & "',"
feltverdi = feltverdi & "'" & nyhet & "',"
feltverdi = feltverdi & "'" & dato & "',"
feltverdi = feltverdi & "'" & klokkeslett & "'"
admquery = "insert into tabnyheter (" & tabkol & ") values (" & feltverdi
& ")"
conn.execute admquery
conn.close
Thanks.
- Frode.
Message #2 by "Dallas Martin" <dmartin@z...> on Tue, 31 Jul 2001 12:31:08 -0400
|
|
Why don't you try it?
Remember to separate ALL SQL statements with a semi-colon;
----- Original Message -----
From: "Frode" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, July 31, 2001 5:02 PM
Subject: [asp_databases] Adding data to more than one table?
> I want to know if it's possible to add data to more than one table using
> just one connection/execute command.
>
> I have several tables and I'm not sure how to do this an easy way.
>
> Here's my first table:
>
> adminID = request.form("aperson")
> overskrift = request.form("overskrift")
> nyhet = request.form("melding")
> dato = date
> klokkeslett = time
>
> tabkol = "[adminID] , [overskrift] , [nyhet] , [dato] , [klokkeslett]"
>
> feltverdi = "'" & adminID & "',"
> feltverdi = feltverdi & "'" &overskrift & "',"
> feltverdi = feltverdi & "'" & nyhet & "',"
> feltverdi = feltverdi & "'" & dato & "',"
> feltverdi = feltverdi & "'" & klokkeslett & "'"
>
> admquery = "insert into tabnyheter (" & tabkol & ") values (" & feltverdi
> & ")"
>
> conn.execute admquery
> conn.close
>
>
> Thanks.
>
> - Frode.
Message #3 by "Tomm Matthis" <matthis@b...> on Tue, 31 Jul 2001 14:02:13 -0400
|
|
You can do it either using:
1. ADO Command Objects calling Stored Procs (the best IMHO)
or
2. Concatenating multiple insert statements in the passed sql query
string via a semicolon:
strSQL =3D"INSERT INTO myTable (fld1, fld2, etc) VAlUES (val1, val2,
etc);"
strSQL =3D strSQL & "INSERT INTO myTable2 (fld1, fld2, etc) VALUES
(val1, val2, etc)"
connection.execute strSQL
Hope that helps.
-- Tomm
> -----Original Message-----
> From: Frode [mailto:fstroemm@o...]
> Sent: Tuesday, July 31, 2001 5:02 PM
> To: ASP Databases
> Subject: [asp_databases] Adding data to more than one table?
>
>
> I want to know if it's possible to add data to more than one table
using
> just one connection/execute command.
>
> I have several tables and I'm not sure how to do this an easy way.
>
> Here's my first table:
>
> adminID =3D request.form("aperson")
> overskrift =3D request.form("overskrift")
> nyhet =3D request.form("melding")
> dato =3D date
> klokkeslett =3D time
>
> tabkol =3D "[adminID] , [overskrift] , [nyhet] , [dato] ,
[klokkeslett]"
>
> feltverdi =3D "'" & adminID & "',"
> feltverdi =3D feltverdi & "'" &overskrift & "',"
> feltverdi =3D feltverdi & "'" & nyhet & "',"
> feltverdi =3D feltverdi & "'" & dato & "',"
> feltverdi =3D feltverdi & "'" & klokkeslett & "'"
>
> admquery =3D "insert into tabnyheter (" & tabkol & ") values (" &
feltverdi
> & ")"
>
> conn.execute admquery
> conn.close
>
>
> Thanks.
>
> - Frode.
>
Message #4 by "Frode" <fstroemm@o...> on Tue, 31 Jul 2001 22:47:58
|
|
Thanks, tried something like #2 ...
ztittel = Request.Form("tittel")
zbilde = Request.Form("bilde")
strSQL ="INSERT INTO khitfilm (tittel) values (ztittel);"
strSQL = strSQL & "INSERT INTO khitbilde (bilde) values (zbilde);"
set conn = Server.CreateObject ("ADODB.Connection")
conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=c:\datastores\test.mdb"
conn.execute strSQL
conn.close
This gives me...
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of
SQL statement.
also... the first table uses autonumber while the second one doesn't..
I guess I have to get the number added in #1 and add it to #2 but is it
possible to retrieve that number before I execute?
Thanks.
- Frode.
> You can do it either using:
> 1. ADO Command Objects calling Stored Procs (the best IMHO)
>
> or
> 2. Concatenating multiple insert statements in the passed sql query
> string via a semicolon:
> strSQL =3D"INSERT INTO myTable (fld1, fld2, etc) VAlUES (val1, val2,
> etc);"
> strSQL =3D strSQL & "INSERT INTO myTable2 (fld1, fld2, etc) VALUES
> (val1, val2, etc)"
> connection.execute strSQL
>
> Hope that helps.
>
> -- Tomm
>
> > -----Original Message-----
> > From: Frode [mailto:fstroemm@o...]
> > Sent: Tuesday, July 31, 2001 5:02 PM
> > To: ASP Databases
> > Subject: [asp_databases] Adding data to more than one table?
> >
> >
> > I want to know if it's possible to add data to more than one table
> using
> > just one connection/execute command.
> >
> > I have several tables and I'm not sure how to do this an easy way.
> >
> > Here's my first table:
> >
> > adminID =3D request.form("aperson")
> > overskrift =3D request.form("overskrift")
> > nyhet =3D request.form("melding")
> > dato =3D date
> > klokkeslett =3D time
> >
> > tabkol =3D "[adminID] , [overskrift] , [nyhet] , [dato] ,
> [klokkeslett]"
> >
> > feltverdi =3D "'" & adminID & "',"
> > feltverdi =3D feltverdi & "'" &overskrift & "',"
> > feltverdi =3D feltverdi & "'" & nyhet & "',"
> > feltverdi =3D feltverdi & "'" & dato & "',"
> > feltverdi =3D feltverdi & "'" & klokkeslett & "'"
> >
> > admquery =3D "insert into tabnyheter (" & tabkol & ") values (" &
> feltverdi
> > & ")"
> >
> > conn.execute admquery
> > conn.close
> >
> >
> > Thanks.
> >
> > - Frode.
> >
|
|
|
View Cart
