help in executing a query

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.

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?

Hi,

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

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

Code:

---

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 '.

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.