Problem returning identity from SQL Server when string contains semicolon

[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))

 

[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

 

[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)

 

[Dan]

Both of these suggestions were a big help.
Thanks