help in executing a query

[kaustav]

Hi,

I was trying to list all details for names beginning with a particular character from the emp table in a data report. I am trying to pass this ename as a parameter from a textbox within a form. The query i wrote in the command was -

select * from emp where ename like '?%'

I have assigned the parameter fields as varchar in the parameters tab of the command object in the data environment.

The code for viweing the report is -

Private Sub Command1_Click()

If DataEnvironment1.rsCommand1.State = adStateOpen Then
DataEnvironment1.rsCommand1.Close
End If

DataEnvironment1.Command1 trim(Text1.Text)
Set DataReport1.DataSource = DataEnvironment1
DataReport1.Show

End Sub

Now when i enter S in the textbox and press the button, i get this error -

Run-time error 3708
Application defined or object defined error.


Any ideas how to fix this. The same query works in Oracle. Thanks.

[shunt]

What database are you querying? Oracle?

When you say the same query works in Oracle, do you mean that you type "select * from emp where ename like 'S%'" into sqlplus and it works?

[kaustav]

Hi,

yeah exactly, the same query works in SQL* Plus, however I am unable to make this work in VB using the Data Environment.

[shunt]

the reason is that msado puts the ' character in for you when you supply a varchar character. Try using this query:

select * from emp where ename like ? || '%'

The || is a concatenation in sql. Since you have specified that the ? is a varchar, you do not need to surround it with '.

[kaustav]

Hi,

thanks for your suggestion, tried it, but that didnt work to, it gives the same error. There must be some way around I guess.