OleDBDataAdapters

Hi guys

Im trying to produce a form that shows messages given certain criteria as defined in public variables previsouly in my vb code.

However, when i've tried to create my form using the dataadapter wizard, i've found that it doesnt like my pre-defined variables, and only pure SQL statements.

eg:

select * from emp where empID=" & MyEmpIdVar & ";
will cause error

select * from emp where empID=2;
wont cause error

any suggestions for a work around are kindly welcome!

Chris

"select * from emp where empID=" & MyEmpIdVar.ToString() & ";"

what is your MyEmpIdVar data type?

you could always try LIKE instead of = if you use any % or _ in your research.

or if it's not exactly in the same format.

Its numeric. I believe its something to do with the wizard itself, since I have the same code elsewhere on different forms and it works fine.

Thing is tho, I could always change it outside of the wizard screen, but the statement appears in that "windoze generated code" that im not allowed to touch.

Any one got other suggestions?

I am not very sure but I have read in the book when using variables in the query in data adapter try with @variablename

Example:

Select * form tablename where (ProductName LIKE @productName + '%')

ok, there is a followup to this:

I've noticed that in the the commandtext within the data adapter brings you to a "query builder" styled screen.

If i enter my SQL statement by hand (it gives the option to build it for you), and i enter:

select * from emp where empNumber= " & myvar &";

it'll change it (after closing of course to make you think its done nothing wrong) to:

select * from emp where empNumber = ' & myvar & ';

Of course, running this will make it freak out, since the variable is incorrectly declared, so I try:

select * from emp where empNumber = ' " & myvar & " ';
to which it freaks at the number being the wrong data type.

I've checked the code as to seeing where it stores the SQL statement, and (conveniently) is held in the windows generated region, and hence non-touchable.

I also tried to manually change the sql statement (against advice from windows) and for the the initial run compilation, worked fine. There after, the SQL statement is erased, and we're back to square 1.

So. Suggestions anyone?

I do touch the IDE generated code alot. It is touchable and changable as long as you know what you are doing.

I think I know what is wrong with your sql


if you just type
select * from emp where empID=" & MyEmpIdVar & ";
into the Command Text property box, it will be translated to
"select * from emp where empID="" & MyEmpIdVar & "";"
and when you run it, the sql text sent to OLEDB engine will be
select * from emp where empID=" & MyEmpIdVar & ";


you should (re-)assign the sql command text to your SqlCommand.CommandText in code view
syntax should be similar to
selectCmd.CommandText = "select * from emp where empID=" & MyEmpIdVar & ";"
so the command Text will be translated into:
"select * from emp where empID=2;"
the the sql text sent to OLEDB engine will be
select * from emp where empID=2;

(selectCmd should be the selection command which appears on your adapter's property)



However, often with an adapter, I would do it in other way, using parameters. And it is quite lengthy to show you how to do it, and I'm at work. So you might have to browse the "help", MSDN, or ask someone else.