String and int Issue

[Bonzol]

vb.net 2003 1.1 web application

Hi there.

Atm I am using this SQL string

SQL = "SELECT Addresses." + tab2LookupCol + " FROM Addresses INNER JOIN
Clients ON Addresses.ID=Clients.AddID WHERE (((Clients." + tab1CheckCol
+ ")='" + checkval + "'))"

However, it will not work where '"checkval"' is a number? I require
int's and strings to be entered in to this variable? Is there a simple
way to get around this?

This data is comming from a databse, so I was thinking maybe I could
use an if statment to check the type of the column and then change the
SQL string based on that, unless anyone can think of a differnt
solution, does anyone know the syntax to check the type of a column?

thanx in advance

 

[Cor Ligthert [MVP]]

Bonzol,

Than use the Visual Basic concatenate char & instead of the VisualBasic Add
char +.
(The + will to concatenate to but can give problems)

(In the SQL part it has to be +)

I hope this helps,

Cor

 

[Guest]

well :-|

SQL = "SELECT Addresses." + tab2LookupCol + " FROM Addresses INNER JOIN
Clients ON Addresses.ID=Clients.AddID WHERE (((Clients." + tab1CheckCol +
")='" + checkval + "'))"

if the variabel column tab1CheckCol is pointing to a numeric column in the
sql database then you should write out without the ''

Alphanumeric column

SQL = "SELECT Addresses." + tab2LookupCol + " FROM Addresses INNER JOIN
Clients ON Addresses.ID=Clients.AddID WHERE (((Clients." + alphanumericCol
+ ")='" + checkval + "'))"

numeric column

SQL = "SELECT Addresses." + tab2LookupCol + " FROM Addresses INNER JOIN
Clients ON Addresses.ID=Clients.AddID WHERE (((Clients." + numericCol +
")=" + checkval + "))"


regards

Michel Posseth [MCP]

 

[R. MacDonald]

Hello, Bonzol,

If I understand correctly, you want to use the same statement to
generate a query applicable to either the case that tab1CheckCol refers
to a text field or a numeric field. So you need to be able to determine
the field type at run time in order to decide whether or not to include
quotation marks around the field.

I am only familiar with Access. There, I believe that you can find the
field type through the TableDefs collection of the database object.
From memory, it was something like:

db.TableDefs(table).Fields(field).DataType

It's probably also possible (but likely different) for other DBs. What
are you using?

Cheers,
Randy

 

[Jim Wooley]

You might be able to kill 2 birds with one stone by using parameterized queries.
Try the following:

SQL = "SELECT Addresses." + tab2LookupCol + _
" FROM Addresses INNER JOIN " & _
"Clients ON Addresses.ID=Clients.AddID " & _
"WHERE (((Clients." & tab1CheckCol & ")=@ColumnVal & "))"
CMD.CommandText = SQL
CMD.Parameters.Add("@ColumnVal", checkval) 'With VB2005 use AddWithValue

This will take care of the differences between the value types as you don't
have to worry about escaping the string value with the single quote. It will
also take care of the option for SQL injection.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx