DataType Problem!

[Arpan]

A MS-Access database table has 4 columns named Col1, Col2, Col3 & Col4.
The datatype of Col1 is Number & the datatype of Col2, Col3 & Col4 are
Text.

In my ASP application, the column name as well as the record in a SQL
query gets generated dynamically, something like this:

<%
Dim strColumn, strRecord
strColumn=Request.QueryString("col")
strRecord=Request.QueryString("rec")

Dim strSQL
strSQL="SELECT * FROM MyTable WHERE " & strColumn & "='" & strRecord &
"'"
..............
..............
%>

Note that both the column name as well as the record are getting
generated dynamically.

Now if the value of strColumn is either Col2, Col3 or Col4, then
everything's fine but if the value of strColumn is Col1, then the
single quotes in the SQL query results in a datatype mismatch error
since strRecord gets enclosed within single quotes. For e.g. with
strColumn=Col1, assume that the value of strRecord is 10. Then the SQL
query becomes

SELECT * FROM MyTable WHERE Col1='10'

Now since the datatype of Col1 is Number, the above SQL query generates
the datatype mismatch error since 10 is enclosed within single quotes!

Any workaround to overcome this?

Thanks,

Arpan

 

[Brendan Reynolds]

VBA code follows, I'll have to leave the translation into ASP syntax to you!


If strColumn = "Col1" Then
strDelim = "'"
Else
strDelim = ""
End If
strSQL = "SELECT * FROM MyTable WHERE " & strColumn & " = " & strDelim &
strRecord & strDelim

If for whatever reason hard-coding the column name as above is a problem for
you, then you'll need to get the column name from the user, then open a
recordset returning that column (use a WHERE clause that will return no
records) and examine the Type property of the column. What follows is VBA
code in Access, again I'll have to leave the translation to ASP to you ...

Public Function GetDelim(ByVal ColName As String) As String

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT " & ColName & " FROM tblTest WHERE True = False"
.Open
Select Case .Fields(ColName).Type
Case adDate
GetDelim = "#"
Case adVarWChar, adLongVarWChar
GetDelim = "'"
Case Else
GetDelim = vbNullString
End Select
.Close
End With

End Function

 

[Tom Wickerath]

Hi Arpan,

I'm not familiar with ASP. Do you know if it supports an IF...THEN...ELSE
construct? If so, you might try something like the following:


<%
Dim strColumn, strRecord
strColumn=Request.QueryString("col")
strRecord=Request.QueryString("rec")

Dim strSQL

IF strColumn = "Col1" THEN
strSQL="SELECT * FROM MyTable WHERE " & strColumn & "=" & strRecord
ELSE
strSQL="SELECT * FROM MyTable WHERE " & strColumn & "='" & strRecord & "'"
END IF

...............
...............
%>



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

A MS-Access database table has 4 columns named Col1, Col2, Col3 & Col4.
The datatype of Col1 is Number & the datatype of Col2, Col3 & Col4 are
Text.

In my ASP application, the column name as well as the record in a SQL
query gets generated dynamically, something like this:

<%
Dim strColumn, strRecord
strColumn=Request.QueryString("col")
strRecord=Request.QueryString("rec")

Dim strSQL
strSQL="SELECT * FROM MyTable WHERE " & strColumn & "='" & strRecord &
"'"
...............
...............
%>

Note that both the column name as well as the record are getting
generated dynamically.

Now if the value of strColumn is either Col2, Col3 or Col4, then
everything's fine but if the value of strColumn is Col1, then the
single quotes in the SQL query results in a datatype mismatch error
since strRecord gets enclosed within single quotes. For e.g. with
strColumn=Col1, assume that the value of strRecord is 10. Then the SQL
query becomes

SELECT * FROM MyTable WHERE Col1='10'

Now since the datatype of Col1 is Number, the above SQL query generates
the datatype mismatch error since 10 is enclosed within single quotes!

Any workaround to overcome this?

Thanks,

Arpan