How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:

SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
 Field2 LIKE '%NEW%' OR
 Field3 LIKE '%NEW%' OR
 Field4 LIKE '%NEW%'

Regards,

How to code ASP.NET page with return value Store Procedure?

Does anyone know how to call a SQL store procedure that return a value to the page?

I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.

Are there a similar article somewhere?

Thank you all!

check out the docs about using ExecuteScalar() Method.|||

Using ExecuteScalar() requires a separate procedure call. I would like to use the same procedure call that to insert a record into a table and also return an ID back. My store procedure look like this:

sqlString = "EXEC spInsertRow 'parm1','parm2','parm3', @.ReturnID OUTPUT"

Thanks

|||

In the end of your sproc:

SELECT @.ReturnID = @.@.IDENTITY

|||

I suggested ExecuteScalar assuming you are doing some insert and want to return the ID.

If that is not the case you can use the OUTPUT parameters..

Dim res as integer

myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.returnId"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)

res = mycommand.Parameters("@.result").Value

|||

Thank you. It works

|||

Can you share your general procedure. I'm having the same issue and have tested many ways. Still not working.

See posthttp://forums.asp.net/1178243/ShowPost.aspx

Thanks