Board index Database Programming

Data Shaping Problem

Data Shaping Problem

Post by Michael Whit » Sat, 23 Nov 2002 05:32:26



Hello all..

I have the following:

  sConn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & Settings.DatabaseName

  sSQL = "SHAPE {"
  sSQL = sSQL & "SELECT "
  sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
  sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
  sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
  sSQL = sSQL & "dep1_amount, attendant_id.attendant "
  sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate = site.site)
"
  sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
attendant_id.id_code "
  sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
  sSQL = sSQL & "} AS depositdata "

  sSQL = sSQL & "APPEND ({"
  sSQL = sSQL & "SELECT "
  sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
  sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
attendant_id.attendant "
  sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
attendant_id.id_code "
  sSQL = sSQL & "WHERE void = -1} "
  sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
  sSQL = sSQL & ") AS transdata"

The database is Access97. All date fields are defined as type Date/Time.
transdata should contain 2 records.

When I execute each query seperately in Access, the Format$() function
returns '#Error'. When this statement is executed in my app, the first
Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am also
executing the child query in another portion of my app as a simple select
and the Format$() function works properly.

Any ideas anyone?

Thanks in advance...

--
Michael White
Programmer/Analyst
Marion County, OR

 
 
 
Top

Data Shaping Problem

Post by Val Mazu » Sat, 23 Nov 2002 05:36:26


Michael,

Could it be NULL value? Not sure but Format$ could fail in case if value is
NULL.

--
Val Mazur
Microsoft MVP


> Hello all..

> I have the following:

>   sConn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & Settings.DatabaseName

>   sSQL = "SHAPE {"
>   sSQL = sSQL & "SELECT "
>   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
>   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
>   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
>   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
>   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
site.site)
> "
>   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> attendant_id.id_code "
>   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
>   sSQL = sSQL & "} AS depositdata "

>   sSQL = sSQL & "APPEND ({"
>   sSQL = sSQL & "SELECT "
>   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
>   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> attendant_id.attendant "
>   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
> attendant_id.id_code "
>   sSQL = sSQL & "WHERE void = -1} "
>   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
>   sSQL = sSQL & ") AS transdata"

> The database is Access97. All date fields are defined as type Date/Time.
> transdata should contain 2 records.

> When I execute each query seperately in Access, the Format$() function
> returns '#Error'. When this statement is executed in my app, the first
> Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
also
> executing the child query in another portion of my app as a simple select
> and the Format$() function works properly.

> Any ideas anyone?

> Thanks in advance...

> --
> Michael White
> Programmer/Analyst
> Marion County, OR



 
 
 
Top

Data Shaping Problem

Post by Michael Whit » Sat, 23 Nov 2002 05:57:57


No, there are valid dates in the field. That is trapped prior to an insert.


> Michael,

> Could it be NULL value? Not sure but Format$ could fail in case if value
is
> NULL.

> --
> Val Mazur
> Microsoft MVP



> > Hello all..

> > I have the following:

> >   sConn = "Provider=MSDataShape;Data

Provider=Microsoft.Jet.OLEDB.4.0;Data

- Show quoted text -

> > Source=" & Settings.DatabaseName

> >   sSQL = "SHAPE {"
> >   sSQL = sSQL & "SELECT "
> >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> site.site)
> > "
> >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > attendant_id.id_code "
> >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> >   sSQL = sSQL & "} AS depositdata "

> >   sSQL = sSQL & "APPEND ({"
> >   sSQL = sSQL & "SELECT "
> >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > attendant_id.attendant "
> >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant =
> > attendant_id.id_code "
> >   sSQL = sSQL & "WHERE void = -1} "
> >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> >   sSQL = sSQL & ") AS transdata"

> > The database is Access97. All date fields are defined as type Date/Time.
> > transdata should contain 2 records.

> > When I execute each query seperately in Access, the Format$() function
> > returns '#Error'. When this statement is executed in my app, the first
> > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
> also
> > executing the child query in another portion of my app as a simple
select
> > and the Format$() function works properly.

> > Any ideas anyone?

> > Thanks in advance...

> > --
> > Michael White
> > Programmer/Analyst
> > Marion County, OR


 
 
 
Top

Data Shaping Problem

Post by Val Mazu » Sat, 23 Nov 2002 10:25:42


Michael,

Is it working without Format$ function? Did you try?

--
Val Mazur
Microsoft MVP


> No, there are valid dates in the field. That is trapped prior to an
insert.



> > Michael,

> > Could it be NULL value? Not sure but Format$ could fail in case if value
> is
> > NULL.

> > --
> > Val Mazur
> > Microsoft MVP



> > > Hello all..

> > > I have the following:

> > >   sConn = "Provider=MSDataShape;Data
> Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=" & Settings.DatabaseName

> > >   sSQL = "SHAPE {"
> > >   sSQL = sSQL & "SELECT "
> > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > site.site)
> > > "
> > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > attendant_id.id_code "
> > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > >   sSQL = sSQL & "} AS depositdata "

> > >   sSQL = sSQL & "APPEND ({"
> > >   sSQL = sSQL & "SELECT "
> > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > attendant_id.attendant "
> > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON trans.attendant
=
> > > attendant_id.id_code "
> > >   sSQL = sSQL & "WHERE void = -1} "
> > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > >   sSQL = sSQL & ") AS transdata"

> > > The database is Access97. All date fields are defined as type
Date/Time.
> > > transdata should contain 2 records.

> > > When I execute each query seperately in Access, the Format$() function
> > > returns '#Error'. When this statement is executed in my app, the first
> > > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I am
> > also
> > > executing the child query in another portion of my app as a simple
> select
> > > and the Format$() function works properly.

> > > Any ideas anyone?

> > > Thanks in advance...

> > > --
> > > Michael White
> > > Programmer/Analyst
> > > Marion County, OR


 
 
 
Top

Data Shaping Problem

Post by Michael Whit » Sun, 24 Nov 2002 01:44:34


Yes, if I just retrieve the value as is without format$ it works.

I just tried the query in Access2K and I'm getting "undefined function
'format$' in expression.

Michael


> Michael,

> Is it working without Format$ function? Did you try?

> --
> Val Mazur
> Microsoft MVP



> > No, there are valid dates in the field. That is trapped prior to an
> insert.



> > > Michael,

> > > Could it be NULL value? Not sure but Format$ could fail in case if
value
> > is
> > > NULL.

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > Hello all..

> > > > I have the following:

> > > >   sConn = "Provider=MSDataShape;Data
> > Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=" & Settings.DatabaseName

> > > >   sSQL = "SHAPE {"
> > > >   sSQL = sSQL & "SELECT "
> > > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count, "
> > > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > > site.site)
> > > > "
> > > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > > attendant_id.id_code "
> > > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > > >   sSQL = sSQL & "} AS depositdata "

> > > >   sSQL = sSQL & "APPEND ({"
> > > >   sSQL = sSQL & "SELECT "
> > > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site, "
> > > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > > attendant_id.attendant "
> > > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON
trans.attendant
> =
> > > > attendant_id.id_code "
> > > >   sSQL = sSQL & "WHERE void = -1} "
> > > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > > >   sSQL = sSQL & ") AS transdata"

> > > > The database is Access97. All date fields are defined as type
> Date/Time.
> > > > transdata should contain 2 records.

> > > > When I execute each query seperately in Access, the Format$()
function
> > > > returns '#Error'. When this statement is executed in my app, the
first
> > > > Format$() succeeds, but the one in the SHAPE APPEND clause fails. I
am
> > > also
> > > > executing the child query in another portion of my app as a simple
> > select
> > > > and the Format$() function works properly.

> > > > Any ideas anyone?

> > > > Thanks in advance...

> > > > --
> > > > Michael White
> > > > Programmer/Analyst
> > > > Marion County, OR


 
 
 
Top

Data Shaping Problem

Post by Val Mazu » Sun, 24 Nov 2002 01:52:48


Michael,

Just check which values you have in that column in child recordset. Maybe
there is something which cannot be converted into specified format,
otherwise it could be a bug

--
Val Mazur
Microsoft MVP


> Yes, if I just retrieve the value as is without format$ it works.

> I just tried the query in Access2K and I'm getting "undefined function
> 'format$' in expression.

> Michael



> > Michael,

> > Is it working without Format$ function? Did you try?

> > --
> > Val Mazur
> > Microsoft MVP



> > > No, there are valid dates in the field. That is trapped prior to an
> > insert.



> > > > Michael,

> > > > Could it be NULL value? Not sure but Format$ could fail in case if
> value
> > > is
> > > > NULL.

> > > > --
> > > > Val Mazur
> > > > Microsoft MVP



> > > > > Hello all..

> > > > > I have the following:

> > > > >   sConn = "Provider=MSDataShape;Data
> > > Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > > Source=" & Settings.DatabaseName

> > > > >   sSQL = "SHAPE {"
> > > > >   sSQL = sSQL & "SELECT "
> > > > >   sSQL = sSQL & "Format$(dep_date,'mmm dd yyyy') AS depositdate, "
> > > > >   sSQL = sSQL & "gate, sname as sitename, rec_count, void_count, "
> > > > >   sSQL = sSQL & "cash_rec_count, check_rec_count, visa_rec_count,
"
> > > > >   sSQL = sSQL & "dep1_amount, attendant_id.attendant "
> > > > >   sSQL = sSQL & "FROM (deposit INNER JOIN site ON deposit.gate =
> > > > site.site)
> > > > > "
> > > > >   sSQL = sSQL & "LEFT JOIN attendant_id ON deposit.attendant =
> > > > > attendant_id.id_code "
> > > > >   sSQL = sSQL & "WHERE dep_date = #" & ClosingDate & "#"
> > > > >   sSQL = sSQL & "} AS depositdata "

> > > > >   sSQL = sSQL & "APPEND ({"
> > > > >   sSQL = sSQL & "SELECT "
> > > > >   sSQL = sSQL & "Format$(tdate,'mmm dd yyyy') AS transdate, site,
"
> > > > >   sSQL = sSQL & "rec_num, acc_num, total_chrg, paymnt_typ, reason,
> > > > > attendant_id.attendant "
> > > > >   sSQL = sSQL & "FROM trans LEFT JOIN attendant_id ON
> trans.attendant
> > =
> > > > > attendant_id.id_code "
> > > > >   sSQL = sSQL & "WHERE void = -1} "
> > > > >   sSQL = sSQL & "RELATE depositdate TO transdate, gate TO site"
> > > > >   sSQL = sSQL & ") AS transdata"

> > > > > The database is Access97. All date fields are defined as type
> > Date/Time.
> > > > > transdata should contain 2 records.

> > > > > When I execute each query seperately in Access, the Format$()
> function
> > > > > returns '#Error'. When this statement is executed in my app, the
> first
> > > > > Format$() succeeds, but the one in the SHAPE APPEND clause fails.
I
> am
> > > > also
> > > > > executing the child query in another portion of my app as a simple
> > > select
> > > > > and the Format$() function works properly.

> > > > > Any ideas anyone?

> > > > > Thanks in advance...

> > > > > --
> > > > > Michael White
> > > > > Programmer/Analyst
> > > > > Marion County, OR


 
 
 
Top

Data Shaping Problem

Post by Giuseppe Briott » Fri, 06 Dec 2002 07:13:11


Hi,
try to remove the $.

Moreover, what did you mean with "...but the one in the SHAPE APPEND clause
fails."?

Bye

Giuseppe

 
 
 
Top

1. ABOUT DEFINE DATA SHAPE PROBLEM

how to Define a Multi key Relation ??
..
Child2 RELATE VEHICLE_NO TO VEHICLE_NO , INVOICE_NO TO INVOICE_NO
POSSIBLE??

I WANT ADD ONE INVOICE_NO HAVE RELATE WITH CHILD2 AND CHILD1 HOW TO
DEFINE?

strShape = SHAPE APPEND
New adVarWChar(11) As INVOICE_NO,
New adDate As TRANS_DATE,
New adVarWChar(8) As MEMBER_CODE,
New adVarWChar(40) As MEMBER_NAME,
New adVarWChar(40) As MEMBER_ADDR1,
New adVarWChar(40) As MEMBER_ADDR2,
New adVarWChar(40) As MEMBER_ADDR3,
New adVarWChar(40) As MEMBER_ADDR4,
New adVarWChar(20) As MEMBER_CONTACT,
New adCurrency As INVOICE_AMOUNT,
     New adCurrency As DUE_DAYS,
     New adCurrency As OVERDUE_AMOUNT,
     New adCurrency As INVOICE_BALANCE,
     New adCurrency As CREDIT_TERM,
    ((SHAPE APPEND
             New adVarWChar(11) As INVOICE_NO,
             New adVarWChar(7) As VEHICLE_NO,
   ((SHAPE APPEND
             New adVarWChar(11) As INVOICE_NO,
New adVarWChar(8) As CARD_NO,
New adVarWChar(7) As VEHICLE_NO,
New adVarWChar(8) As STATION_SHORT_NAME,
New adDate As TRANS_DATE,
New adDate As TRANS_TIME,
New adCurrency As SALES_VOLUME,
New adCurrency As SALE_RATE,
New adCurrency As SALES_AMOUNT)
AS Child2 RELATE VEHICLE_NO TO VEHICLE_NO))
AS Child1 RELATE INVOICE_NO To INVOICE_NO)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Middleware --- polling or triggering

3. ADO Data Shaping Problem...

4. DDE Programming

5. Data shaping problem using stored procedures!

6. Legal Question

7. DATA SHAPING PROBLEM

8. New Server

9. VB6 Question re: Data Shaping relations and Shape query

10. Shapes shapes shapes

11. SHAPE in SHAPE problem........

12. SHAPE in SHAPE problem...

13. Problem with data shaping APPEND command against SQL Server 2000


All times are UTC
Board index