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

T

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.Text & "',"
fNewsSubBy = "'" & txtNewsSubBy.Text & "',"
fNewsHdr = "'" & txtNewsHdr.Text & "',"
fNewsDesc = "'" & txtNewsDesc.Text & "'"

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



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

Try
cmd.ExecuteScalar()



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

End Sub
******
-----Original Message-----
Have you added an output parameter and assigned @@IDENTITY to it? I think
that should do it. Alternatively, you could use ExecuteScalar and get the
only SELECTed row, @@IDENTITY.
-mike
MVP




.
Click to expand...
..
 
C

Chris R. Timmons

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.Text & "',"
fNewsSubBy = "'" & txtNewsSubBy.Text & "',"
fNewsHdr = "'" & txtNewsHdr.Text & "',"
fNewsDesc = "'" & txtNewsDesc.Text & "'"

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



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

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

End Sub
******
Click to expand...

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/cpconretrievingidentityorautonumbervalues.asp

or

http://tinyurl.com/w5z5



Advanced SQL Injection:

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





Hope this helps.

Chris.
 
Top