Inserting into sql server using parameterized - get new column value

[ryan.mclean]

Hi all, I am new to using sql server and parameterized sql. I am
hoping to be returned the value of a column that has been inserted.
Here is my statement

strSqlInsetrtTrack = _
"INSERT INTO TRACK (CASE_NUM, CERT_NUM, CLMT_NUM, BROKER_NAME, " + _
"CONTRACT_SEQ, TRACK_COMMENTS, DATE_OVER_SPEC, LAST_TOUCHED) " + _
"VALUES ('" + strCase + "','" + strCert + "'," + _
"'" + strClmt + "',@BrokerName,'" + strContractSeq +
"',@TrackComments," + _
"'" + txtDateOverSpec.Text + "',GETDATE()) " + _
"SET @TrackId = TRACK_ID"

Then I assign the parameters and I thought I could obtain the TRACK_ID
from the newly inserted row, which is an auto-enumerated field.
Anyway, here are the parameters:

With comInsertTrack
.Parameters.Add("@BrokerName", SqlDbType.Char, 50)
.Parameters.Add("@TrackComments", SqlDbType.VarChar, 500)
.Parameters("@BrokerName").Value = txtBrokerName.Text
.Parameters("@TrackComments").Value = txtTrackComments.Text
.Parameters.Add("@TrackId", SqlDbType.Int)
.Parameters("@TrackId").Direction = ParameterDirection.ReturnValue
.ExecuteNonQuery()

strTrackid = CType(.Parameters("@TrackId").Value, String)

End With

I'm not even sure this is waht was intended by the returnvalue
enumerator . . . it would be really cool if it would work

The errror is:

SqlException: Must declare the variable '@TrackId'

On the .ExecuteNonQuery() line.

Thank you for any assistance. Have a great day!

Ryan

 

[Mark Rae]

Thank you for any assistance. Have a great day!

http://www.akadia.com/services/dotnet_autoincrement.html

 

[ryan.mclean]

Hi Mark, thanks for the article. I actually saw that one. The
comments were not very descriptive and they were using stored
procedures. I thought they would be treated differently. I also
looked at this article:

http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlParameterClass.aspx

I didn't really understand what "SET @Identity = @@Identity" meant.
I've looked at the msdn docs, but they are not very helpful.

I hope I'm not being dense Anyway, I'll take a closer look at the
article. In the mean time, if anyone can give some more information, I
would greatly appreciate it.

Thanks again!
Ryan

 

[Mark Rae]

I didn't really understand what "SET @Identity = @@Identity" meant.

It meant set the local variable @Identity to the system variable @@Identity,
which equates to the most recent autoincrement value under the current login
to SQL Server.

 

[ryan.mclean]

Ah, I was confused because the auto 'number' uses the newid() sql
server function. This is expecting an int . . . I feel so dumb. So
then if using a command object, how is the value retrieved? Should I
be using a dataadapter? I tried:

strTrackid = CType(.Parameters("@TrackId").Value, String)

Which didn't work. I could try to put the command into dataadapter and
create a datatable . . . not quite sure what to go from here. Maybe
what I want is not possible and I'll just have to query after the
insert. Or it could be that I’ve had too much coffee and I’m
missing something obvious ïŠ

Thanks again for your help.

Ryan

 

[Guest]

Ryan, Mark has the answer for you. We are both guessing that TRACK_ID is an
auto-increment column in Acess and and identity column in sql server. When
you get a chance, get to the MS web site and download/install the SQL Server
Books online. The transact-sql section is the reference guide you are looking
for. (If you are hard disk limited, you can run the web version.)...Chuck

 

[ryan.mclean]

Hi Chuck, it's actually a column in sql server. The getid thingy is
sort of like using a trigger and sequence in oracle. Then in oracle I
would call the currentval and get what the last sequence number was. I
was hoping to use these output parameters to occomplish this.

I'll just have to do a little research to see if the @@indentity thingy
will work

Thank you both for your help and have a great night!
Ryan

 

[Bruce Barker]

output parameters only work with stored proc calls, you must select the id
(assuming it ths an identity column):

'* bad code as it allows sql injection

strSqlInsetrtTrack = _
"INSERT INTO TRACK (CASE_NUM, CERT_NUM, CLMT_NUM, BROKER_NAME, " + _
"CONTRACT_SEQ, TRACK_COMMENTS, DATE_OVER_SPEC, LAST_TOUCHED) " + _
"VALUES ('" + strCase + "','" + strCert + "'," + _
"'" + strClmt + "',@BrokerName,'" + strContractSeq + "',@TrackComments," +
_
"'" + txtDateOverSpec.Text + "',GETDATE()) " + _
"select scope_identity() as id"
With comInsertTrack
.Parameters.Add("@BrokerName", SqlDbType.Char, 50)
.Parameters.Add("@TrackComments", SqlDbType.VarChar, 500)
.Parameters("@BrokerName").Value = txtBrokerName.Text
.Parameters("@TrackComments").Value = txtTrackComments.Text
trackid = .ExecuteScaler()

-- bruce (sqlwork.com)

 

[ryan.mclean]

Hi Bruce, thanks for the reply. the excecutescaler is what I was
looking for

By the way, in what way is the insert "bad code." The fields that I am
using parameters are the only free-form textboxes. I thought this was
the only way sql injection could occur. The other fields are numeric
only (or dates), which I validate.

I am new to protecting against sql injection, please explain.

Thanks again!
Ryan

 

[ryan.mclean]

Nevermind, if I was to use the scaler, I would simply query for the id
that is auto-generated . . . thanks anyway, you still answered my
original question, so thank you.

If you would be so kind, I would still appreciate you answering my
question about sql injection.

Thanks and have a nice night.
Ryan

 

[Alan Silver]

If you would be so kind, I would still appreciate you answering my

question about sql injection.

As it happens, I saw these two links just the other day that opened my
eyes as to the extent of the injection problem. Read them both VERY
carefully, then go and rewrite al your apps!! Remember, even one little
opening is enough for someone to get in.

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