SELECT @@IDENTITY question

Have a look at the SCOPE_IDENTITY() function as opposed to @@IDENTITY. I
always prefer to use this method.

Pat.


"Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
news:e9ooij$7ek$2@forums.macromedia.com...
> Its ok i sorted it out.
> I was being a bit DIM
>
> :-)
>
> Andy
>
>
>
> "Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
> news:e9olu8$3vh$2@forums.macromedia.com...
>> I'm using this now but getting another error
>>
>>
>> Error Type:
>> Microsoft VBScript runtime (0x800A01F4)
>> Variable is undefined: 'commInsert'
>>
>>
>> <% Option Explicit %>
>> <%
>> Dim firstname, lastname, company
>> 'Receiving values from Form, assign the values entered to variables
>> firstname = Request("first_name")
>> lastname = Request("last_name")
>> company = Request("company")
>> %>
>> <%
>> Set commInsert = Server.CreateObject("ADODB.Connection")
>> commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> Source=D:\WebSite_folders\mysite\mydb.mdb; " ' Replace with your OLE DB
>> connection string.
>> commInsert.Execute "INSERT INTO customers(firstname,lastname,companyname)
>> values ('" & firstname & "', '" & lastname & "', '" & company & "')" '
>> Execute the insert command
>> Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' Create a
>> recordset
>> and SELECT the new Identity
>> intNewID = rsNewID(0) ' Store the value of the new identity in variable
>> intNewID
>> rsNewID.Close
>> Set rsNewID = Nothing
>> commInsert.Close
>> Set commInsert = Nothing
>> %>
>>
>>
>> "Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
>> news:e9ojtk$1ia$2@forums.macromedia.com...
>> > How do i use the SELECT @@IDENTITY with the code below?
>> >
>> > Andy
>> >
>> >
>> > <%
>> > 'declare your variables
>> > Dim firstname, lastname, company
>> > Dim sConnString, connection, sSQL
>> > 'Receiving values from Form, assign the values entered to variables
>> > firstname = Request("first_name")
>> > lastname= Request("last_name")
>> > company =Request("company")
>> >
>> > 'declare SQL statement that will query the database
>> > sSQL = "INSERT into customers (firstname, lastname, companyname) values
>> ('"
>> > & firstname & "', '" & lastname & "', '" & company & "')"
>> >
>> > 'define the connection string, specify database
>> > 'driver and the location of database
>> > sConnString="Provider=Microsoft.Jet.OLEDB.4.0;Data
>> > Source=D:\WebSite_folders\mysite\mydb.mdb; "
>> > 'create an ADO connection object
>> > Set connection = Server.CreateObject("ADODB.Connection")
>> >
>> > 'Open the connection to the database
>> > connection.Open(sConnString)
>> >
>> > 'execute the SQL
>> > connection.execute(sSQL)
>> >
>> >
>> >
>> > response.write "The form information was inserted successfully."
>> > 'Done. Close the connection object
>> > connection.Close
>> > Set connection = Nothing
>> > %>
>> >
>> >
>>
>>
>
>

Its ok i sorted it out.
I was being a bit DIM

:-)

Andy



"Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
news:e9olu8$3vh$2@forums.macromedia.com...
> I'm using this now but getting another error
>
>
> Error Type:
> Microsoft VBScript runtime (0x800A01F4)
> Variable is undefined: 'commInsert'
>
>
> <% Option Explicit %>
> <%
> Dim firstname, lastname, company
> 'Receiving values from Form, assign the values entered to variables
> firstname = Request("first_name")
> lastname = Request("last_name")
> company = Request("company")
> %>
> <%
> Set commInsert = Server.CreateObject("ADODB.Connection")
> commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=D:\WebSite_folders\mysite\mydb.mdb; " ' Replace with your OLE DB
> connection string.
> commInsert.Execute "INSERT INTO customers(firstname,lastname,companyname)
> values ('" & firstname & "', '" & lastname & "', '" & company & "')" '
> Execute the insert command
> Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' Create a recordset
> and SELECT the new Identity
> intNewID = rsNewID(0) ' Store the value of the new identity in variable
> intNewID
> rsNewID.Close
> Set rsNewID = Nothing
> commInsert.Close
> Set commInsert = Nothing
> %>
>
>
> "Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
> news:e9ojtk$1ia$2@forums.macromedia.com...
> > How do i use the SELECT @@IDENTITY with the code below?
> >
> > Andy
> >
> >
> > <%
> > 'declare your variables
> > Dim firstname, lastname, company
> > Dim sConnString, connection, sSQL
> > 'Receiving values from Form, assign the values entered to variables
> > firstname = Request("first_name")
> > lastname= Request("last_name")
> > company =Request("company")
> >
> > 'declare SQL statement that will query the database
> > sSQL = "INSERT into customers (firstname, lastname, companyname) values
> ('"
> > & firstname & "', '" & lastname & "', '" & company & "')"
> >
> > 'define the connection string, specify database
> > 'driver and the location of database
> > sConnString="Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=D:\WebSite_folders\mysite\mydb.mdb; "
> > 'create an ADO connection object
> > Set connection = Server.CreateObject("ADODB.Connection")
> >
> > 'Open the connection to the database
> > connection.Open(sConnString)
> >
> > 'execute the SQL
> > connection.execute(sSQL)
> >
> >
> >
> > response.write "The form information was inserted successfully."
> > 'Done. Close the connection object
> > connection.Close
> > Set connection = Nothing
> > %>
> >
> >
>
>

I'm using this now but getting another error


Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'commInsert'


<% Option Explicit %>
<%
Dim firstname, lastname, company
'Receiving values from Form, assign the values entered to variables
firstname = Request("first_name")
lastname = Request("last_name")
company = Request("company")
%>
<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\WebSite_folders\mysite\mydb.mdb; " ' Replace with your OLE DB
connection string.
commInsert.Execute "INSERT INTO customers(firstname,lastname,companyname)
values ('" & firstname & "', '" & lastname & "', '" & company & "')" '
Execute the insert command
Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' Create a recordset
and SELECT the new Identity
intNewID = rsNewID(0) ' Store the value of the new identity in variable
intNewID
rsNewID.Close
Set rsNewID = Nothing
commInsert.Close
Set commInsert = Nothing
%>


"Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
news:e9ojtk$1ia$2@forums.macromedia.com...
> How do i use the SELECT @@IDENTITY with the code below?
>
> Andy
>
>
> <%
> 'declare your variables
> Dim firstname, lastname, company
> Dim sConnString, connection, sSQL
> 'Receiving values from Form, assign the values entered to variables
> firstname = Request("first_name")
> lastname= Request("last_name")
> company =Request("company")
>
> 'declare SQL statement that will query the database
> sSQL = "INSERT into customers (firstname, lastname, companyname) values
('"
> & firstname & "', '" & lastname & "', '" & company & "')"
>
> 'define the connection string, specify database
> 'driver and the location of database
> sConnString="Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=D:\WebSite_folders\mysite\mydb.mdb; "
> 'create an ADO connection object
> Set connection = Server.CreateObject("ADODB.Connection")
>
> 'Open the connection to the database
> connection.Open(sConnString)
>
> 'execute the SQL
> connection.execute(sSQL)
>
>
>
> response.write "The form information was inserted successfully."
> 'Done. Close the connection object
> connection.Close
> Set connection = Nothing
> %>
>
>