|
 |
asp_databases thread: ADO, ASP, SQL2000 and other quandries
Message #1 by "Matthew Montgomery" <matt@p...> on Wed, 20 Jun 2001 18:04:37
|
|
Here goes. I'm inserting data into a parent table from a webpage so that
it will develope the new index/identity key. I need that key so I can
assign it to a variable and use it later as I populate the child tables.
When I do this in the query analyzer it works fine (and returns the ID)
I'll include the logon and stuff for anyone that wants to try this.
That returns the ID just fine. Now when I want to do it from my ASP...
<%
dim conn as server.createobject("ADODB.Connection")
dim strConn
dim strInsert
strInsert="Insert into Todd (test) Values('Test') Select @@Identity"
strConn="Provider=SQLOLEDB.1;User ID=glen;Password = glen;Initial
Catalog=sixfigure2000;Data Source=65.0.196.108;"
conn.open strConn
set rsTemp=conn.execute (strInsert)
response.write rsTemp(0)
%>
Now according to everything I know about what I know, that *should*
return the newly created ID from the row I've just inserted. The owner is
expecting lots of traffic. I dont want to risk a new row being inserted
if I do 2 separate statements (which does return the ID properly).
Any ideas?
-Matt
Message #2 by "Tomm Matthis" <matthis@b...> on Wed, 20 Jun 2001 15:08:51 -0400
|
|
What happens if you try:
set rsTemp=3Dconn.execute (strInsert)
rsTemp.NextRecordSet
response.write rsTemp(0)
????
Also, IF this is SQL, why not use a StoredProc? It will be much faster
that way.
-- Tomm
> -----Original Message-----
> From: Matthew Montgomery [mailto:matt@p...]
> Sent: Wednesday, June 20, 2001 6:05 PM
> To: ASP Databases
> Subject: [asp_databases] ADO, ASP, SQL2000 and other quandries
>
>
> Here goes. I'm inserting data into a parent table from a webpage so
that
> it will develope the new index/identity key. I need that key so I can
> assign it to a variable and use it later as I populate the child
tables.
> When I do this in the query analyzer it works fine (and returns the
ID)
> I'll include the logon and stuff for anyone that wants to try this.
>
>
>
> That returns the ID just fine. Now when I want to do it from my
ASP...
> <%
> dim conn as server.createobject("ADODB.Connection")
> dim strConn
> dim strInsert
>
> strInsert=3D"Insert into Todd (test) Values('Test') Select @@Identity"
> strConn=3D"Provider=3DSQLOLEDB.1;User ID=3Dglen;Password =3D
glen;Initial
> Catalog=3Dsixfigure2000;Data Source=3D65.0.196.108;"
>
> conn.open strConn
>
> set rsTemp=3Dconn.execute (strInsert)
>
> response.write rsTemp(0)
>
> %>
>
>
> Now according to everything I know about what I know, that *should*
> return the newly created ID from the row I've just inserted. The
> owner is
> expecting lots of traffic. I dont want to risk a new row being
inserted
> if I do 2 separate statements (which does return the ID properly).
>
>
> Any ideas?
>
> -Matt
Message #3 by "Matthew Montgomery" <matt@p...> on Thu, 21 Jun 2001 00:41:15
|
|
We got it to work but we had other recordsets open using
set rsMembers=conn.execute ("Select * from members")
when we made a new connection (Conn2) and made it return the @@identity
then it worked fine. Apparently it didnt like sharing the Conn object.
Go figure. We're going to update everything to SP's soon, but its a get-
it-up-now deal and he had written the code in asp already.
-Matt
> What happens if you try:
> set rsTemp=3Dconn.execute (strInsert)
> rsTemp.NextRecordSet
> response.write rsTemp(0)
>
> ????
>
> Also, IF this is SQL, why not use a StoredProc? It will be much faster
> that way.
>
> -- Tomm
>
>
>
> > -----Original Message-----
> > From: Matthew Montgomery [mailto:matt@p...]
> > Sent: Wednesday, June 20, 2001 6:05 PM
> > To: ASP Databases
> > Subject: [asp_databases] ADO, ASP, SQL2000 and other quandries
> >
> >
> > Here goes. I'm inserting data into a parent table from a webpage so
> that
> > it will develope the new index/identity key. I need that key so I can
>
> > assign it to a variable and use it later as I populate the child
> tables.
> > When I do this in the query analyzer it works fine (and returns the
> ID)
> > I'll include the logon and stuff for anyone that wants to try this.
> >
> >
> >
> > That returns the ID just fine. Now when I want to do it from my
> ASP...
> > <%
> > dim conn as server.createobject("ADODB.Connection")
> > dim strConn
> > dim strInsert
> >
> > strInsert=3D"Insert into Todd (test) Values('Test') Select @@Identity"
> > strConn=3D"Provider=3DSQLOLEDB.1;User ID=3Dglen;Password =3D
> glen;Initial
> > Catalog=3Dsixfigure2000;Data Source=3D65.0.196.108;"
> >
> > conn.open strConn
> >
> > set rsTemp=3Dconn.execute (strInsert)
> >
> > response.write rsTemp(0)
> >
> > %>
> >
> >
> > Now according to everything I know about what I know, that *should*
> > return the newly created ID from the row I've just inserted. The
> > owner is
> > expecting lots of traffic. I dont want to risk a new row being
> inserted
> > if I do 2 separate statements (which does return the ID properly).
> >
> >
> > Any ideas?
> >
> > -Matt
>
|
|
|
View Cart
