Data Type Mismatch in DAO


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Data Type Mismatch in DAO

  1. #1
    Shlomo Guest

    Data Type Mismatch in DAO

    The SQL in the following code works on Query Analyzer but in VB it gives
    over a data mismatch type. Actually if I use aliases i get a syntax error on
    the FROM clause. ( this did not happen in Query Analyzer. If I use a simple
    SQL Statement it works, however.

    It breaks up at the end when I try to open up the Recordset.

    Dim dbs As Database, qdf As QueryDef
    Dim strConnect As String,
    Dim strSQL As String

    strConnect =
    "ODBC;DSN=*****;UID=*******;PWD=********!;DATABASE=*********;Address=***.***
    *.****;1433"

    Set dbs = DBEngine.Workspaces(0).OpenDatabase(*******, _
    False, False, strConnect)

    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strConnect

    strSQL = "select artrans.CustomerID, name, Transdate, ArAmount "
    strSQL = strSQL + "from artrans inner join customer "
    strSQL = strSQL + "on customer.customerID = artrans.customerID "
    strSQL = strSQL + "where customer.customerID = 12196 "
    strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "
    strSQL = strSQL + "AND ArAmount >= 0"
    qdf.SQL = strSQL
    qdf.ReturnsRecords = True



    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

    _____________

    It seems that using the Where clause kills it cause even the following does
    not work:

    strSQL = "select top 100 name from customer where customerID = 12235"

    whereas the same Query minus the where part of it goes well.

    Thanks For your help!!





  2. #2
    huibert Guest

    Re: Data Type Mismatch in DAO


    >strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "


    1) Single Quotes are used for strings, not for dates! your string should
    look like this:
    strSQL = strSQL & "AND Transdate between #08/01/00# and #09/01/01# "
    DAO (Jet Engine) uses pound signs around dates. Note that the date is always
    mm/dd/yyyy (i think) regardless of your regional settings!

    2) Don't use '+' for string concatenation. It is bad coding practice and
    will not compile in future versions of VB. It can lead to subtle bugs if
    you mix variants and numeric data in your concatenation

    HTH
    huiberrt

  3. #3
    Shlomo Guest

    Re: Data Type Mismatch in DAO

    Scratch that, you a genius! I tried putting single quotes around
    CustomerID - (I should really check to see what it is for sure! we know what
    what assuming makes! -I didn't design it) and it worked. well, well, well,
    Thanks - now i just have tomake sure it works with the joins.

    You can't do aloasing with VB DAO?
    "huibert" <hvandeursen@wanadoo.nl> wrote in message
    news:3b98814c$1@news.devx.com...
    >
    > >strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "

    >
    > 1) Single Quotes are used for strings, not for dates! your string should
    > look like this:
    > strSQL = strSQL & "AND Transdate between #08/01/00# and #09/01/01# "
    > DAO (Jet Engine) uses pound signs around dates. Note that the date is

    always
    > mm/dd/yyyy (i think) regardless of your regional settings!
    >
    > 2) Don't use '+' for string concatenation. It is bad coding practice and
    > will not compile in future versions of VB. It can lead to subtle bugs if
    > you mix variants and numeric data in your concatenation
    >
    > HTH
    > huiberrt




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center