Problem returning identity from SQL Server when string contains semicolon

D

Dan

I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to be
inserted. The string fields are inside single quotes in the INSERT command.
With the semicolon in the string, the record is written correctly including
the semicolon, but the identity is not returned. Without a semicolon in the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))
 
H

Hans Kesting

I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to be
inserted. The string fields are inside single quotes in the INSERT command.
With the semicolon in the string, the record is written correctly including
the semicolon, but the identity is not returned. Without a semicolon in the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber. Thanks.

lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
+ NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
MyCommand.ExecuteNonQuery()
Dim sSelect As String = "SELECT @@IDENTITY as NewID"
Dim DataSet As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(sSelect, conn)
adapter.Fill(DataSet, "Identity")
cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))


If that Contract Number contained a single quote ('), then your sql
statement would fail. Look up "SQL Injection Attack".
So use Parameters to pass those values! See MSDN for details.
This might also solve your semicolon problem.

Second remark: instead of filling an entire dataset with that single
identity value, use ExecuteScalar (and cast the "object" result to an
integer). This will return just the first column in the first row of
the first resultset.

Hans Kesting
 
B

Bruce Barker

there are a lot issues with your code:

@@IDENTITY returns the last identity assigned in the sql batch. your select
@@IDENTITY is in its own batch, so it returns null. you need to switch to
one batch.

@@identity does not return the correct value if a trigger is used which also
creates an identity. you should use scope_ideneity() instead.

your code allows sql injection, you should switch to parameters.

string sql = @"set nocount on
INSERT INTO tblVPContracts (
NonContracted, ContractNumber,POReqNumber,
ForInfoOnly, ImmediateActionReq, ModifiedBy
)
VALUES (
@NonContracted,@ContractNum,@textbox,
@ForInfoOnly,@ImmediateAction,@userid
)
select scope_identity as newid";

SqlCommand cmd = new SqlCommand(sql, conn)
cmd.Parameters.Add(@NonContracted,SqlDbType.Int,0);
....
int newId = cmd.ExecuteScaler();


-- bruce (sqlwork.com)
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top