Category: SQL

Categories
SQL

Get the ID of the Inserted row

One way of getting the ID of the row you just inserted into a table would be to run another sql statement following the insert one. Use an aggreagate statement something like SELECT MAX (CusID from tblCustomers.

You can also get it using the insert statement with the OUTPUT clause…

INSERT INTO tblCustomers (FirstName, LastName) OUTPUT INSERTED.CusID
VALUES (‘Fred’, ‘Bloggs’)

(Assuming CusID is the primary key field in the table.)

If you’re running the insert in an application, the command needs to ExecuteScalar rather than just Execute…

In C#:

Return (Int32)cmd.ExecuteScalar();

Categories
SQL

Parameters with Wildcards

Be careful not to fall into the trap of adding the wildcard to the sql. It needs to be added to the parameter…

SELECT * FROM tblCustomers WHERE LastName LIKE '%mit%'

will return rows where the LastName field contains “mit”, at the beginning, end or anywhere with in the last name as there are 2 wildcards.

If you want to be able to specify the search criteria at run time, i.e. not just “mit”, replace the hard coded search with a parameter like @Search…

SELECT * FROM tblCustomers WHERE LastName LIKE '%@Search%'

If you are using something like a Sqlcommand in asp.net you will supply the parameter to it through perhaps a TextBox control so you would think you should do this…

Cmd.Parameters.Add("@Search", SqlDbType.NvarChar).Value = TextBoxSearch.Text

BUT THIS IS WRONG AND WON’T WORK!!!

The sql should not contain the wildcards. It should be :

SELECT * FROM tblCustomers WHERE LastName LIKE @Search

and the parameter should have the the wildcards:

Cmd.Parameters.Add(“@Search”, SqlDbType.NvarChar).Value = “%” + TextBoxSearch.Text + “%”