Problem with sql statement in vba
 

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

Problem with sql statement in vba
 

When you create your SQL statement strSQL, is the extra space after
strRoomCode intentional? (i.e. where you have & strRoomCode & " '").
--
- K Dales


"ina" wrote:

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

Problem with sql statement in vba
 

no I do not do intentionally the extra space and the strRoomCode coming
from the sub from where I call my function

strRoomCode is a String

strRoom = GetroomNumber(cndb, strRoomCode)

Problem with sql statement in vba
 

That would explain why the query fails, then. Remove the extra space.
--
- K Dales


"ina" wrote:

no I do not do intentionally the extra space and the strRoomCode coming
from the sub from where I call my function

strRoomCode is a String

strRoom = GetroomNumber(cndb, strRoomCode)

Problem with sql statement in vba
 

Hello Dales thanks,

It was not the space but I do not why I change the "currency" by *

strSQL = "select distinct * from indexCurrency where internalcode= '" &
strRoomCode & "'"

and it works.

Ina