Re: msde 2000 INNER JOIN problems

steve wrote on Wed, 20 Jul 2005 21:31:53 +1000:

> Hi All
>
> I am using VB6 and MSDE 2000
>
> I previously used Access 2000 and used the following ado sql to create a
> temp table in access
> (Invoice table contains Invoice data such as Invoice number, date etc.
> Idetail table contains details of the invoice individual items e.g product
> code, Invoice Number etc) The link is the Invoice Number field in each
> table (Master/Detail)
>
> sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp IN '" & App.path & "\data\tramcarstemp.mdb'
> FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num
> where invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "# and
> #" & Format(DT2.Value, "m-d-yyyy") & "#"
> cn.execute sql
>
> This worked fine in Access 2000
>
> I am upgrading to MSDE2000 and using the following code in TSql
>
> Private Sub maketable()
> Dim a, fso As New FileSystemObject
> Dim x As Integer
>
> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
>
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'true'") a.WriteLine ("GO")
> a.WriteLine ("USE " & "Tramcars")
> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp")
> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
> invoice.inv_num")
> a.WriteLine ("where invoice.inv_date between '" &
> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
> a.WriteLine ("GO")
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'false'") a.WriteLine ("GO")
> a.Close
>
> Set a = Nothing
>
> End Sub
>
> The table is created but the inserted invoice dates are only ever 1 of 2
> dates

Change you date format to a one that isn't region setting specific, eg.

YYYYMMDD

Depending on the region settings, something like 6-12-2005 could be
interpreted as either 6th December 2005 or 12th June 2005.

> Also when I read from the table to graph the data with
> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
> [prod_code] = '" &
> frmorderhistory.maingrid.TextMatrix(frmorderhistory.maingrid.Row, 0) & "'
> group by [inv_date]"
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
>
> There are several records in the recordset but calling rs.recordcount =
> -1, which didn't happen in Access 2K

When a server side cursor is used you will get -1 for the recordcount (it
indicates that there are records, but the count is unknown as not all of the
records have been returned yet, records are returned as requested to save on
immediate resource usage on the server). If you require the recordcount, use
a client side cursor which will cause all records to be sent to the
recordset when the Open method.

rs.CursorLocation = adUseClient
rs.Open sql, cn, adOpenDynamic, adLockReadOnly


Dan


.

Relevant Pages

  • Re: Insert, Delete, and Update - best practices advice please
    ... and handles only a small subset of the SQL keywords, functions, etc. ... The table GrowthInv is derrived from the above datasources by doing a SELECT ... those "fields" and represent the Invoice Number of an invoice and the ... as Customer Name and Salesperson but I can get those easily from Goldmine's ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert, Delete, and Update - best practices advice please
    ... and handles only a small subset of the SQL keywords, functions, etc. ... The table GrowthInv is derrived from the above datasources by doing a SELECT ... those "fields" and represent the Invoice Number of an invoice and the ... as Customer Name and Salesperson but I can get those easily from Goldmine's ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with sql statment - ASAP please
    ... The form is not a sub form, ... the user enter the invoice number,InvoiceDate and company ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: msde 2000 INNER JOIN problems
    ... >> (Invoice table contains Invoice data such as Invoice number, ... >> cn.execute sql ... > When a server side cursor is used you will get -1 for the recordcount (it ... > save on immediate resource usage on the server). ...
    (microsoft.public.data.ado)
  • Re: Please explain this update query?!?
    ... that update everytime a payment is made against an invoice. ... >> This update query works fine as it updates the values that I want it to, ... >> however, I seem to having a problem with multiple transactions, ie. ... > The way to get summations is thru SELECT queries that use the SQL ...
    (microsoft.public.access.queries)