locked
Variables in TableAdapter queries... RRS feed

  • Question

  • The short of my question is "how do I go about declaring a variable in OLEDB?"

    The long story: I've got a connection to an Access file, and I'm trying to set up a fill query for a TableAdapter, and it won't let me put a variable in my "WHERE" clause! When I first try to add the query, the statement builder just gives a standard SELECT statement; I then try to add "WHERE myField = @myVar", and it tells me "Error in WHERE clause near '@'. Unable to parse query text". There is no apparent error in the statement, at least from my knowledge of SQL, so I figure it's because there's a different way to declare variables in OLEDB.

    Thanks!

    Friday, April 14, 2006 2:11 AM

Answers


  • If you're using OLEDB, and adding parameters to a Command Parameters collection, then the parameter placeholder should be a question mark and not a named variable:

    "WHERE myField = ?"

    Under ADO.NET OLEDB uses the ordinal position of each parameter in the SQL statement and inserts values based upon the corresponding positions in the Parameters collection.

    If you're inserting a code variable value into the SQL statement then it should look like the following:

    "WHERE myField = '" & myVar & "''    ...for a string value

    "WHERE myField = " & myVar  ...for a numeric value

    "WHERE myField = #" & myVar & "#"  ...for a date value

    Personally I would recommend using the OLEDB Command object (w/parameters) so that you don't have to bother with the various data type syntax as listed in the above three SQL statement examples.

     

    Friday, September 15, 2006 1:17 PM
  • Dont know if this will help you but I found this post

     

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586271&SiteID=1

     

     

    Friday, September 15, 2006 12:48 PM
  • This is spelled out pretty well at the following link

     (having had the same issue as yourself I saw your post during my web searches to resolve the issue)

    http://msdn2.microsoft.com/en-us/library/k14b54kc.aspx

    Friday, September 15, 2006 1:30 PM

All replies

  • Dont know if this will help you but I found this post

     

    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=586271&SiteID=1

     

     

    Friday, September 15, 2006 12:48 PM

  • If you're using OLEDB, and adding parameters to a Command Parameters collection, then the parameter placeholder should be a question mark and not a named variable:

    "WHERE myField = ?"

    Under ADO.NET OLEDB uses the ordinal position of each parameter in the SQL statement and inserts values based upon the corresponding positions in the Parameters collection.

    If you're inserting a code variable value into the SQL statement then it should look like the following:

    "WHERE myField = '" & myVar & "''    ...for a string value

    "WHERE myField = " & myVar  ...for a numeric value

    "WHERE myField = #" & myVar & "#"  ...for a date value

    Personally I would recommend using the OLEDB Command object (w/parameters) so that you don't have to bother with the various data type syntax as listed in the above three SQL statement examples.

     

    Friday, September 15, 2006 1:17 PM
  • This is spelled out pretty well at the following link

     (having had the same issue as yourself I saw your post during my web searches to resolve the issue)

    http://msdn2.microsoft.com/en-us/library/k14b54kc.aspx

    Friday, September 15, 2006 1:30 PM
  • If you find the page in MSDN titled "Parameters COllection Editor" it suggests the appropriate parameter substitution character to use for the various adapters (OLE, SQL, Oracle) ? % : respectively. The Tools-Options setting seems to have no effect

    However even following this does not work. I tried using ?  since I am using OLEDB dor an Access database.

    I have also tried generating all the commands first using a query with no parameters and then later adding them. The second time around I get IErrorInfo getDescription failed with e_fail (0x80004005)

    Friday, September 4, 2009 6:33 PM
  • OK that last error was because one of my parameters was "language" which is a SQL keyword  and must be enclosed in []

    Now that I am using ? as parameter placeholders the wizard completes . HOWEVER the generated code (Dataset1.designer.vb) does not have an INSERT or DELETE method although it does have 4 overloads for UPDATE. Any ideas?
    Friday, September 4, 2009 6:54 PM
  • In a DataSet's TableAdapter , for a Access database connexion , you use the below query if you need a filter parameter .

    select var1,var2 from Table where var1 = ?

    Cheers !

    • Proposed as answer by Eid Almuabark Tuesday, August 30, 2011 7:33 AM
    Sunday, November 28, 2010 1:09 AM
  • In a DataSet's TableAdapter , for a Access database connexion , you use the below query if you need a filter parameter .
    select var1,var2 from Table where var1 = ?

    Cheers !

    This is The best Great Answer

     

    thank u

    Tuesday, August 30, 2011 7:34 AM