I
ina
Hello all,
I have a problem with a sql statement in vba here is my code and I do
not know why because the same query in sql server works file and the
params strRoom is well get through my function
The sub that call this function i do like this:
strRoom = GetroomNumber(cndb, strRoomCode)
Option Explicit
' Description : Get Currency from Index
' Params : the strAssetCode as argument (index InternalCode)
' Returns : an array as String
' Author : RI
' Last Update : 11.05.06
Public Function GetRoomNumber(ByVal cndb As ADODB.Connection,
strRoomCode As String) As String
On Error GoTo GetRoomNumber_Err
Dim strSQL As String 'String for SQL statement
Dim rsroom As ADODB.Recordset 'Recorset
'Set a new recorset
Set rsRoom = New ADODB.Recordset
'SQL statement
strSQL = "SELECT CURRENCY FROM INDEXCURRENCY WHERE INTERNALCODE= '" &
strRoomCode & " '"
Debug.Print strSQL 'I can see the query and if I use the SQL it works
'Recorset and Connection
rsRoom.ActiveConnection = cndb
rsRoom.Open strSQL
GetRoomNumber = rsRoom.Fields(1).Value 'GetRoom = room number
'close the recorset
rsRoom.Close
Set rsRoom = Nothing
Exit Function
GetRoomNumber_Err:
GetRoomNumber = CVErr(xlErrNA)
Exit Function
End Function
I have a problem with a sql statement in vba here is my code and I do
not know why because the same query in sql server works file and the
params strRoom is well get through my function
The sub that call this function i do like this:
strRoom = GetroomNumber(cndb, strRoomCode)
Option Explicit
' Description : Get Currency from Index
' Params : the strAssetCode as argument (index InternalCode)
' Returns : an array as String
' Author : RI
' Last Update : 11.05.06
Public Function GetRoomNumber(ByVal cndb As ADODB.Connection,
strRoomCode As String) As String
On Error GoTo GetRoomNumber_Err
Dim strSQL As String 'String for SQL statement
Dim rsroom As ADODB.Recordset 'Recorset
'Set a new recorset
Set rsRoom = New ADODB.Recordset
'SQL statement
strSQL = "SELECT CURRENCY FROM INDEXCURRENCY WHERE INTERNALCODE= '" &
strRoomCode & " '"
Debug.Print strSQL 'I can see the query and if I use the SQL it works
'Recorset and Connection
rsRoom.ActiveConnection = cndb
rsRoom.Open strSQL
GetRoomNumber = rsRoom.Fields(1).Value 'GetRoom = room number
'close the recorset
rsRoom.Close
Set rsRoom = Nothing
Exit Function
GetRoomNumber_Err:
GetRoomNumber = CVErr(xlErrNA)
Exit Function
End Function