Still Need Help with pulling Record ID for new record and assigning it to a var

[Tony]

Thanks for writing back and don't laugh at what I am
about to post I am new to this type of stuff....this is
what I have tried and it isn't working(please note in the
try section I was just trying to see if it was grabbing
my variable or not any suggestions or hints would be
greatly appreciated:

********
Private Sub AddArticle()

Dim sql As String
Dim cmd As SqlCommand
Dim sb As StringBuilder
Dim Values As ArrayList

Dim fNewsDate As String
Dim fNewsSubBy As String
Dim fNewsHdr As String
Dim fNewsDesc As String
Dim NewID


fNewsDate = "'" & txtNewsDate.Tex t & "',"
fNewsSubBy = "'" & txtNewsSubBy.Te xt & "',"
fNewsHdr = "'" & txtNewsHdr.Text & "',"
fNewsDesc = "'" & txtNewsDesc.Tex t & "'"

sql = "SET NOCOUNT ON; Insert INTO [News]
(NewsDate,NewsS ubBy,NewsHdr,Ne wsDesc) Values " & "(" &
fNewsDate & fNewsSubBy & fNewsHdr & fNewsDesc & ");SELECT
@@IDENTITY AS myID FROM News;"



cmd = New SqlCommand(sql, conSCGC)
conSCGC.Open()

Try
cmd.ExecuteScal ar()



NewID = cmd.ExecuteScal ar("myID")
Response.Write( NewID)
Catch
Response.Write( sql)
Finally
conSCGC.Close()
End Try

End Sub
******[color=blue]
>-----Original Message-----
>Have you added an output parameter and assigned[/color]
@@IDENTITY to it? I think[color=blue]
>that should do it. Alternatively, you could use[/color]
ExecuteScalar and get the[color=blue]
>only SELECTed row, @@IDENTITY.
>-mike
>MVP
>
><anonymous@dis cussions.micros oft.com> wrote in message
>news:047f01c3b 07e$9651b190$a3 01280a@phx.gbl. ..[color=green]
>> Thanks but I still have a question..I already had the[/color][/color]
sql[color=blue][color=green]
>> part that wasn't the issue but when I click on the[/color][/color]
button[color=blue][color=green]
>> to submit the link how do I assign the newly added[/color][/color]
record[color=blue][color=green]
>> id to a variable so I can then display a confirm page
>> with a link to the users newly added record page? I
>> right now am using the executeNonQuery but it just adds
>> the record and doesn't return any value.
>>
>> Thanks
>>[color=darkred]
>> >-----Original Message-----
>> >In the command that inserts, after the insert, you can[/color]
>> do SELECT @@IDENTITY[color=darkred]
>> >and that will give you the ID of the last row[/color][/color][/color]
inserted.[color=blue][color=green][color=darkred]
>> >
>> >-mike
>> >MVP
>> >
>> >"Tony Stoker" <anonymous@disc ussions.microso ft.com>[/color]
>> wrote in message[color=darkred]
>> >news:0e7e01c3a ebf$98f55f70$a5 01280a@phx.gbl. ..
>> >> I have a .Net web app that adds a record to a SQL
>> >> database. After the user adds their record I want[/color][/color][/color]
to[color=blue][color=green][color=darkred]
>> >> have a link that will link them to their new record![/color]
>> The[color=darkred]
>> >> recordID is a AutoNumber in the SQL server...
>> >>
>> >> How do I return the recordID after I have added the
>> >> record?
>> >
>> >
>> >.
>> >[/color][/color]
>
>
>.
>[/color]
..

[Chris R. Timmons]

Re: Still Need Help with pulling Record ID for new record and assigning it to a var

"Tony" <anonymous@disc ussions.microso ft.com> wrote in
news:04e701c3b1 4c$05ac61c0$a00 1280a@phx.gbl:
[color=blue]
> Thanks for writing back and don't laugh at what I am
> about to post I am new to this type of stuff....this is
> what I have tried and it isn't working(please note in the
> try section I was just trying to see if it was grabbing
> my variable or not any suggestions or hints would be
> greatly appreciated:
>
> ********
> Private Sub AddArticle()
>
> Dim sql As String
> Dim cmd As SqlCommand
> Dim sb As StringBuilder
> Dim Values As ArrayList
>
> Dim fNewsDate As String
> Dim fNewsSubBy As String
> Dim fNewsHdr As String
> Dim fNewsDesc As String
> Dim NewID
>
>
> fNewsDate = "'" & txtNewsDate.Tex t & "',"
> fNewsSubBy = "'" & txtNewsSubBy.Te xt & "',"
> fNewsHdr = "'" & txtNewsHdr.Text & "',"
> fNewsDesc = "'" & txtNewsDesc.Tex t & "'"
>
> sql = "SET NOCOUNT ON; Insert INTO [News]
> (NewsDate,NewsS ubBy,NewsHdr,Ne wsDesc) Values " & "(" &
> fNewsDate & fNewsSubBy & fNewsHdr & fNewsDesc & ");SELECT
> @@IDENTITY AS myID FROM News;"
>
>
>
> cmd = New SqlCommand(sql, conSCGC)
> conSCGC.Open()
>
> Try
> cmd.ExecuteScal ar()
> NewID = cmd.ExecuteScal ar("myID")
> Response.Write( NewID)
> Catch
> Response.Write( sql)
> Finally
> conSCGC.Close()
> End Try
>
> End Sub
> ******[/color]

Tony,

Executing multiple SQL statements at one time is something that's
best done in a stored procedure. It will be easier to get the
@@IDENTITY value back this way.

Another potential problem is an SQL Injection Attack. This is due to
using string concatenation to build the INSERT statement. The use of
SQLParameter objects can prevent that kind of attack.


Retrieving Identity or Autonumber Values:

http://msdn.microsoft.com/library/en-

us/cpguide/html/cpconretrieving identityorauton umbervalues.asp

or

http://tinyurl.com/w5z5

Advanced SQL Injection:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Hope this helps.

Chris.
-------------
C.R. Timmons Consulting, Inc.

http://www.crtimmonsinc.com/