Query with quotation mark.

I have problem with sql query with aspnet.

In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark =>' .

Any suggestions? Thanks.

Use a parameterized query instead of building a SQL String to execute. If you must continue building a SQL string, then you need to replace all the quotes in any string field with two quotes.

Example:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text & "'"

You can change that to:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text.replace("'","''") & "'"

or:

dim conn as new sqlconnection(configurationmanager.connectionstrings("{your key here").ConnectionnString)

conn.open

dim cmd as new sqlcommand("SELECT * FROM table WHEREfield1=@.field1",conn)

cmd.parameters.add(new sqlparameter("@.field1",sqldbtype.varchar))

cmd.parameter("@.field1").value=textbox1.text

' Execute the following line if you want a datareader

dim dr as sqldatareader=cmd.executedatareader

' Execute the following 4 lines if you want a dataset

dim ds as new dataset

dim da as sqldataadapter=new sqldataadapter(cmd)

da.fill(ds)

conn.close

|||

a parameterized query is definately the way to go.

If you continue to use concatenated SQL statements, you are opening yourself up to sql injection attacks.

|||Another option is to use a Stored Procedure - this way you don't have to write an SQL statement at all in your web application. You also gain a performance benefit since the Procedure is already compiled in SQL Server.

dim objCmd as sqlcommand
dim objRdr as sqldatareader

objCmd = new sqlcommand(spNameHere, dbConnObjectHere)
objCmd.commandType = commandtype.storedprocedure
objCmd.parameters.Add("@.SearchField", textBox1.text)

objRdr = objCmd.executeReader

When you use the parameters collection in this way there is no need to worry about single quotes, percent signs, or other characters messing with your query. You can add as many parameters as you need to.|||

rich freeman wrote:

You also gain a performance benefit since the Procedure is already compiled in SQL Server.

Beginning with SQL Server 2000, the execution plan of a parameterized sql statement is also cached just like with a stored proc. Therefore performance differences between stored procs and parameterized sql statements should not be used as a significant factor when choosing one technique over the other.

|||

Thanks you everybody for the reply. All the replies are really informative (thumbs up!)! With the suggestions from you all, I have finally solved the problem. Thanks a lot!!!

Query with another query input parameter

Dear Friends,

I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.

SELECT DIR FROM DIRECCAO

BIG QUERY with DIR input parameter.

How can I do?

Thanks.

SELECT @.DIR = DIR FROM DIRECCAO

EXEC BIG_QUERY @.DIR

HTH,

Babu

|||

IT WORKS AND THE QUERY IS:

ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]

AS

DECLARE @.DIR nvarchar(10)

SELECT @.DIR = DIR_NOME FROM Direccao

EXECUTE dbo.GD_SP_FACTURA_ValorTotal @.DIR

BUT How can I SUM all the values returned by the BIGQuery?

THANKS!!

|||How can I have the sum and it's possible to return a list of all values returned by the bigQuery? THANKS!!!|||

Could anyone help me?

Thanks!

Query to Search all fields in simple table

I am trying to write a simple search page that will searchall the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*

FROM Table

WHERE * = @.USERINPUT

But thats not working. Can someone help.? Thanks..

Not a simple task, but this should get you started.

SELECT *

FROM Table

WHERE field1 LIKE '%' + @.UserInput + '%' OR field2 LIKE'%'+@.UserInput+'%' OR...

sql