Dynamic parameterized queries

[GuntherKS]

I am new to VID and am confused on trying to make a parameterized query. I want to retrieve data that matches the name from a textbox. The recordset is working properly and is called rsITSxxx and it usess a SQL statement that is simply: select * from itsxxx. I also have a text box called tbNameSearch.

I tried this script:

Sub btnNameSearch_onclick()
oldSQL = rsITSxxx.getSQLText
newSQL = oldSQL & " where account_name = "
tbNameSearch.value
rsITSxxx.setSQLText(newSQL)
rsITSxxx.requery
End Sub

It doesn't work. What am I doing wrong? Is there a better way to do this?

Thanks
G

 

[crystalized]

I would create a stored procedure if you are using SQL Server and have the where condition as a parameter. Then you can set the parameter value in code.

As a rule stored procedures seem to work faster and you can use parameters in them. This means you don't have to embed so much sql into your code.

check out asptoday for articles about stored procedures.
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[GuntherKS]

unfortunately, I am not using SQL Server. All of the data is being stored in an Oracle database.

 

[crystalized]

That is okay you can make stored procedures in Oracle as well, and as far as I know you can have parameters for them. The syntax is different than for SQL Server so I probably can't help you with that.

Good luck
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde