#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Sheffield, UK
    Posts
    94
    Rep Power
    11

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Brisbane, Australia
    Posts
    50
    Rep Power
    11
    "select * from emp where empID=" & MyEmpIdVar.ToString() & ";"

    what is your MyEmpIdVar data type?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Montreal, Canada
    Posts
    486
    Rep Power
    11
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Sheffield, UK
    Posts
    94
    Rep Power
    11
    Originally posted by oni9
    "select * from emp where empID=" & MyEmpIdVar.ToString() & ";"

    what is your MyEmpIdVar data type?
    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?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Virginia
    Posts
    19
    Rep Power
    0
    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 + '%')
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Sheffield, UK
    Posts
    94
    Rep Power
    11
    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?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Brisbane, Australia
    Posts
    50
    Rep Power
    11
    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.
    Last edited by oni9; October 14th, 2003 at 06:32 PM.

IMN logo majestic logo threadwatch logo seochat tools logo