Inserting into sql server using parameterized - get new column value

Discussion in 'ASP .Net' started by ryan.mclean@gmail.com, May 18, 2005.

  1. Guest

    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
     
    , May 18, 2005
    #1
    1. Advertising

  2. Mark Rae Guest

    Mark Rae, May 18, 2005
    #2
    1. Advertising

  3. Guest

    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
     
    , May 18, 2005
    #3
  4. Mark Rae Guest

    <> wrote in message
    news:...

    > 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.
     
    Mark Rae, May 18, 2005
    #4
  5. Guest

    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
     
    , May 18, 2005
    #5
  6. Re: Inserting into sql server using parameterized - get new column

    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/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
    >
    >
     
    =?Utf-8?B?Y2h1Y2sgcnVkb2xwaA==?=, May 18, 2005
    #6
  7. Guest

    Re: Inserting into sql server using parameterized - get new column

    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
     
    , May 18, 2005
    #7
  8. Bruce Barker Guest

    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:...
    > 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
    >
     
    Bruce Barker, May 19, 2005
    #8
  9. Guest

    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
     
    , May 19, 2005
    #9
  10. Guest

    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
     
    , May 19, 2005
    #10
  11. Alan Silver Guest

    >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


    --
    Alan Silver
    (anything added below this line is nothing to do with me)
     
    Alan Silver, May 25, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page