Problem with SQL Statement

[SonicBoomAu]

Hi All,

 

This is a snippet of my code. What I am trying to do is use strTableQuery to assign the table name I want to search through. It could be SystemName, Type, etc. Basically any of the table names.

 

When I run this the value of strTableQuery isn't being set into the strSqlQuery.

 

I.E. During Runtime

strSqlQuery = "SELECT * from PermanentAssets WHERE strTableQuery = '*'"

where I want it to equal.

strSqlQuery = "SELECT * from PermanentAssets WHERE WallPortNo = '*'"

 

Is it possible to do this? If so what am I doing wrong?

 

Thank you in advance for your help.

 

Dim strSqlQuery As String
Dim strTableQuery As String

strTableQuery = "WallPortNo"
strSqlQuery = "SELECT * from PermanentAssets WHERE strTableQuery = '*'"

' Retrieve all the Permanent Asset Information
daDataAdapter = New OleDb.OleDbDataAdapter(strSqlQuery, cnAdoNetConnection)

[eramgarden]

You have:

 

strTableQuery = "WallPortNo"

strSqlQuery = "SELECT * from PermanentAssets WHERE strTableQuery = '*'"

 

 

You need to change that to the variable:

 

strTableQuery = "WallPortNo"

strSqlQuery = "SELECT * from PermanentAssets WHERE " + strTableQuery + "= '*'"

[SonicBoomAu]

That works well.

 

Ok next question then. If I want to set the '*'"

 

strSqlQuery = "SELECT * from PermanentAssets WHERE " + strTableQuery + "= '*'"

 

to a string how would I do that.

[SonicBoomAu]

Not to worry figured it out with a bit of playing around.

 

strSqlQuery = "SELECT * from PermanentAssets WHERE " + strTableQuery + "= '" + strTableTest + "'"

 

Thanks for your help.

[PlausiblyDamp]

You would probably find using either a stored procedure or a parameterised query would be both easier and more secure / robust. Search these forums and you should find a few examples / reasons why.