Problem with OdbcDataReader only returning 32 Columns per ROw

Problem with OdbcDataReader only returning 32 Columns per ROw

Post by QW5kcmV3IF » Sat, 08 Apr 2006 22:45:01


i I am having a major problem with a program I am writing to import an
Access DB into SQL Server.

I have a large Access table of approx 93 columns and 3100 rows. I can
import this data into a dataset and handle it that way but I am trying to use
an OdbcDataReader in order to speed up the import (i.e. only accessing the
table one row at a time)

I have written the following code but when I come to pull out -
drCmd1.GetValue(32).toString() although the Immediate Window says that the
DataReader knows there is supposed to by 93 values, every value from 32
onwards is showings as <indefined value>. Is there a limitation on how much
data per row/columns per row the datareader can pull back? is this a
limitation of odbc? My code is copied below - please can anyone help?

Thanks
Andrew


static void DataImport()
{
mpyapftdb.DataBaseImport.DAL.DataAccess dlDAL = new
mpyapftdb.DataBaseImport.DAL.DataAccess();
OdbcConnection oConn = new
OdbcConnection(ConfigurationSettings.AppSettings["AccessConnection"]);
try
{
Console.WriteLine("Data Import Started at " +
System.DateTime.Now.ToString());
OdbcCommand cmdMax = new OdbcCommand("Select Max(FireTestNumber) from
Masterdatabase", oConn);
oConn.Open();
OdbcDataReader drCmdMax = cmdMax.ExecuteReader(CommandBehavior.SingleRow);
drCmdMax.Read();
string sMaxFTNo = drCmdMax.GetValue(0).ToString();
drCmdMax.Close();
oConn.Close();

string strCmd1 = @"Select FireTestNumber, Date, FurnaceNumber,
SpecimenNumber, Secref, GenericType, Material, Batch, Wrkcard, BatchA,
BatchB, SubWt, MatWt, SubThic, MatThic, [139CRise], [180CRise], [400C],
[538CEdges], [550C], [620C], [%DI], CharYeild, Length, Substrate, HPa,
Reinforcement, Depth, FireType, PurposeOfTest, Description, Furnace, TCNo1,
TCNo2, TCNo3, TCNo4, TCNo5, TCNo6, TCNo7, TCNo8, TCPosition1, TCPosition2,
TCPosition3, TCPosition4, TCPosition5, TCPosition6 , TCPosition7,
TCPosition8, Ave50, Ave100, Ave150, Ave200, Ave250, Ave300, Ave350, Ave400,
Ave450, Ave500, Ave550, Ave600, Ave620, Ave650, HTC50, HTC100, HTC150,
HTC200, HTC250, HTC300, HTC350, HTC400, HTC450, HTC500, HTC550, HTC600,
HTC620, HTC650, Observations, Chard1, Chard2, Chard3, Chard4, Chard5, Chard6,
Chard7, Chard8, Chard9, Chard10, Chard11, Chard12, PostThic, PreThic, PreWt,
PostWt From Masterdatabase WHERE FiretestNumber Between 0 AND 4412 ORDER BY
FiretestNumber, SpecimenNumber";
string strCmd2 = @"Select FireTestNumber, Date, FurnaceNumber,
SpecimenNumber, Secref, GenericType, Material, Batch, Wrkcard, BatchA,
BatchB, SubWt, MatWt, SubThic, MatThic, [139CRise], [180CRise], [400C],
[538CEdges], [550C], [620C], [%DI], CharYeild, Length, Substrate, HPa,
Reinforcement, Depth, FireType, PurposeOfTest, Description, Furnace, TCNo1,
TCNo2, TCNo3, TCNo4, TCNo5, TCNo6, TCNo7, TCNo8, TCPosition1, TCPosition2,
TCPosition3, TCPosition4, TCPosition5, TCPosition6 , TCPosition7,
TCPosition8, Ave50, Ave100, Ave150, Ave200, Ave250, Ave300, Ave350, Ave400,
Ave450, Ave500, Ave550, Ave600, Ave620, Ave650, HTC50, HTC100, HTC150,
HTC200, HTC250, HTC300, HTC350, HTC400, HTC450, HTC500, HTC550, HTC600,
HTC620, HTC650, Observations, Chard1, Chard2, Chard3, Chard4, Chard5, Chard6,
Chard7, Chard8, Chard9, Chard10, Chard11, Chard12, PostThic, PreThic, PreWt,
PostWt From Masterdatabase WHERE FiretestNumber Between 5341 AND " +
sMaxFTNo + " ORDER BY Firet
 
 
 

1. Select which column is returned per row

2. Sum per row and Average per column from a second sheet.

OK. I have 4 colums in my table on sheet 2.
In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it
contains anything) add columns 2, 3, and 4 together for that row. Then
Average these sums in one total.


1 2 3 4
------------------------
A|Client1|10|20|30
B|Client2|10|20|30
C| |10|20|30
D|Client4|10|20|30

We should get the following.

A| 10+20+30=60
B| 10+20+30=60
C| 0 (Because column 1 is empty)
D| 10+20+30=60
-------------------------
60
+60
+60
-----
180 / 3=60
Average score or each row, 60.

Does that make sense, and if so, how can I make it happen?

3. Image size : 2Gb per row or per column?

4. 32 x 32 icons returned by ExtractAssociatedIcon() look poor

5. Problem when trying to convert one column with multiple rows to one row with multiple column

6. one row per item > one row per order

7. how can I return entire row for column value returned

8. find Max value in row--return value in same column, different row

9. Return as single row from multiple rows in a column

10. Returning single column, multiple rows in a single row

11. Returning Multiple Rows based on 1 rows column integer content.

12. select case return rows in one row (how to group by three column)

13. Returning Multiple Rows based on 1 rows column integer content

14. How can I Return a selected row without the Frist column (ID autonumber column) ??

15. delete columns / upon hitting enter, return cursor to next row column A