By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 1,009 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

MS Access + C# OdbcDataReader -- Unknown SQL type - 0.

P: n/a
Hello,

I am writing an app that is retrieving data via an ODBC connection to
an Access database. I am returning many columns, and it seems that for
certain columns that I try to read, I get the following error:

"Unknown SQL type - 0."

Am I doing a cast wrong somewhere? Are null values affecting the data
reader? Any help would be appreciated.

Here is the SQL:

"SELECT Invoice_Header.Document AS TransID, 1 AS TransType, 0 AS
BatchID, Invoice_Header.Customer AS CustID, Null AS ShipToID, Null AS
ShipToName, Null AS ShipToAddr1, Null AS ShipToAddr2, Null AS
ShipToCity, Null AS ShipToRegion, 'USA' AS ShipToCountry, Address.Zip
AS ShipToPostalCode, Invoice_Header.Ship_Via AS ShipVia,
Invoice_Header.Terms AS TermsCode, Null AS TaxableYN,
Invoice_Header.Document AS InvcNum, Null AS WhseId, Null AS OrderDate,
Null AS ShipNum, Null AS ShipDate, Invoice_Header.Document_Date AS
InvcDate, Null AS Rep1ID, 0 AS Rep1Pct, Null AS Rep2ID, 0 AS Rep2Pct,
'TRUE' AS TaxOnFreight, 0 AS TaxClassFreight, 0 AS TaxClassMisc, Null
AS PostDate,
IIf(Len([Fiscal_Period].[Name])=5,Mid([Fiscal_Period].[Name],6,1),Mid([Fiscal_Period].[Name],6,2))
AS GLPeriod, Fiscal_Year.Name AS FiscalYear, Invoice_Header.Tax_Code AS
TaxGrpID, Invoice_Header.Taxable_Amt AS TaxSubtotal, 0 AS
NonTaxSubtotal, Sum(Tax_Detail.Tax_Amt) AS SalesTax, 0 AS Freight, 0 AS
Misc, 0 AS TotPmtAmt, Invoice_Header.Taxable_Amt AS TaxSubtotalFgn, 0
AS NonTaxSubtotalFgn, 0 AS SalesTaxFgn, 0 AS FreightFgn, 0 AS MiscFgn,
0 AS TotCostFgn, 0 AS TotPmtAmtFgn, 0 AS PrintStatus, Null AS
CustPONum, Format(5,'00') AS DistCode, 'DOLLAR' AS CurrencyID,
Invoice_Header.Currency_Conv_Rate AS ExchRate, Null AS DiscDueDate,
Null AS NetDueDate, 0 AS DiscAmt, 2 AS SumHistPeriod, 0 AS TaxAmtAdj, 0
AS TaxAmtAdjFgn, 1 AS TaxAdj, Null AS TaxLocAdj, 0 AS TaxClassAdj, Null
AS BillingPeriodFrom, 'NEW' AS PMTransType, 'IT' AS ProjItem, Null AS
BillingPeriodThru, 0 AS BillingFormat FROM (Address RIGHT JOIN
(Invoice_Header LEFT JOIN (Fiscal_Period LEFT JOIN Fiscal_Year ON
Fiscal_Period.Fiscal_Year = Fiscal_Year.Fiscal_Year) ON
Invoice_Header.Period = Fiscal_Period.Fiscal_Period) ON Address.Address
= Invoice_Header.Ship_To) INNER JOIN (Invoice_Detail LEFT JOIN
Tax_Detail ON cstr(Invoice_Detail.Invoice_Detail) =
Tax_Detail.Owner_ID) ON Invoice_Header.Document =
Invoice_Detail.Document GROUP BY Invoice_Header.Document, 1, 0,
Invoice_Header.Customer, Null, Null, Null, Null, Null, Null, 'USA',
Address.Zip, Invoice_Header.Ship_Via, Invoice_Header.Terms, Null,
Invoice_Header.Document, Null, Null, Null, Null,
Invoice_Header.Document_Date, Null, 0, Null, 0, 'TRUE', 0, 0, Null,
IIf(Len([Fiscal_Period].[Name])=5,Mid([Fiscal_Period].[Name],6,1),Mid([Fiscal_Period].[Name],6,2)),
Fiscal_Year.Name, Invoice_Header.Tax_Code, Invoice_Header.Taxable_Amt,
0, 0, 0, 0, Invoice_Header.Taxable_Amt, 0, 0, 0, 0, 0, 0, 0, Null,
Format(5,'00'), 'DOLLAR', Invoice_Header.Currency_Conv_Rate, Null,
Null, 0, 2, 0, 0, 1, Null, 0, Null, 'NEW', 'IT', Null, 0,
Tax_Detail.Owner_Type HAVING (((Invoice_Header.Document_Date) Between
#10/1/2006# AND #10/30/2006#) AND ((Tax_Detail.Owner_Type)=0));"

Thanks,
Barret

Oct 30 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I would say try your query inside of Access in the query builder and
debug it there. If the query builder complains, then you have a problem
with your query. If the query builder does not complain then the
problem is somewhere else. Are you opening your datareader before
retrieving values?

If dataReader.HasRows Then
dataReader.Read
txt1.Text = dataReader(0).ToString
txt2.Text = dataReader(1).ToString
...

--use brackets { } and semicolons ; for C#
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.