Passing ADODB Parameters to SQL Query in VB6
481021Sep 14 2006 — edited Sep 24 2006Hi,
I have an application in VB6 that uses Microsoft ODBC for Oracle driver.
I have this query to be fired into the ORACLE 9i database thru this application
SELECT DECODE(SIGN(LENGTH(&MYSTR)-4),1,SUBSTR(&MYSTR,LENGTH(&MYSTR)-4),&MYSTR) FROM DUAL
Purpose of the query is to return last 5 charactres of an input string.
I follow this VB6 code ...
Dim objRS As New ADODB.Recordset, objCmd As New ADODB.Command, inputValue As String, outputValue As String
inputValue = "ABCDEFGHIJK"
objCmd.ActiveConnection = "DSN=MYORA;Password=ORAPWD;User ID=ORAUSER"
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT DECODE(SIGN(LENGTH(&MYSTR)-4),1,SUBSTR(&MYSTR,LENGTH(&MYSTR)-4),&MYSTR) FROM DUAL"
objCmd.Parameters.Append objCmd.CreateParameter("&MYSTR", adVarChar, adParamInput, 200, inputValue)
Set objRS = objCmd.Execute
outputValue = objRS.Fields(0).Value
But it says ORA-01008: not all variables bound
Help....
Thx