«

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

<> wrote in message
news: oups.com...

> Thank you for any assistance. Have a great day!

http://www.akadia.com/services/dotne...increment.html

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/aspxt...eterClass.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

<> wrote in message
news: oups.com...

> 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.

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

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

"" wrote:

> 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/aspxt...eterClass.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
>
>

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

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)


<> wrote in message
news: oups.com...
> 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
>

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

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