469,271 Members | 1,049 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

DataAdapter select assuming integer

How do I tell DataAdapter that Column 2 and 3 are string and not integer?
Following is the example data that comes from the .csv file

FEDERAL TAX,1084,0000
COREHCR,1084,0000
CLIENT P,1084,0000

The select is:

Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
"CorelandGLConversion.csv", conn)

The problem is that it thinks the 3rd column is an integer and changes the
0000 to 0.

Can I tell it in the Select statement that the column is a varChar? I can't
do it after reading in the data as the data will already have changed the
0000 to 0 at that point.

Thanks,

Tom.
Mar 28 '07 #1
4 1486
"tshad" <t@home.comwrote in message
news:u5*************@TK2MSFTNGP05.phx.gbl...
How do I tell DataAdapter that Column 2 and 3 are string and not integer?
Following is the example data that comes from the .csv file

FEDERAL TAX,1084,0000
COREHCR,1084,0000
CLIENT P,1084,0000

The select is:

Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
"CorelandGLConversion.csv", conn)

The problem is that it thinks the 3rd column is an integer and changes the
0000 to 0.

Can I tell it in the Select statement that the column is a varChar? I
can't do it after reading in the data as the data will already have
changed the 0000 to 0 at that point.
I tried to set up the table as:

Dim DTGLConversion as DataTable

DTGLConversion.Columns.Add("F1", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F2", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F3", System.Type.GetType("System.String"))

da2.Fill(DTGLConversion)
DataSetObj.Tables.Add(DTGLConversion)

This sets up the columns as strings in the DTGLConversion Table and adds it
to the Dataset but it seems the Select in the DataAdapter must have already
converted the string "0000" to integer 0 and then when I filled the
DataTable it reconverted it to "0".

How do I fix this?

Thanks,

Tom
Mar 28 '07 #2
On Mar 28, 5:11 am, "tshad" <t...@home.comwrote:
"tshad" <t...@home.comwrote in message

news:u5*************@TK2MSFTNGP05.phx.gbl...


How do I tell DataAdapter that Column 2 and 3 are string and not integer?
Following is the example data that comes from the .csv file
FEDERAL TAX,1084,0000
COREHCR,1084,0000
CLIENT P,1084,0000
The select is:
Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
"CorelandGLConversion.csv", conn)
The problem is that it thinks the 3rd column is an integer and changes the
0000 to 0.
Can I tell it in the Select statement that the column is a varChar? I
can't do it after reading in the data as the data will already have
changed the 0000 to 0 at that point.

I tried to set up the table as:

Dim DTGLConversion as DataTable

DTGLConversion.Columns.Add("F1", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F2", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F3", System.Type.GetType("System.String"))

da2.Fill(DTGLConversion)
DataSetObj.Tables.Add(DTGLConversion)

This sets up the columns as strings in the DTGLConversion Table and adds it
to the Dataset but it seems the Select in the DataAdapter must have already
converted the string "0000" to integer 0 and then when I filled the
DataTable it reconverted it to "0".

How do I fix this?

Thanks,

Tom- Hide quoted text -

- Show quoted text -
Try to use schema.ini file

http://msdn.microsoft.com/library/de...a_ini_file.asp

I think it should be similar to this

[CorelandGLConversion.csv]
Format=Delimited(;)
ColNameHeader=True
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char

and place that file in the same folder as the CSV file

Mar 28 '07 #3
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@y66g2000hsf.googlegr oups.com...
On Mar 28, 5:11 am, "tshad" <t...@home.comwrote:
>"tshad" <t...@home.comwrote in message

news:u5*************@TK2MSFTNGP05.phx.gbl...


How do I tell DataAdapter that Column 2 and 3 are string and not
integer?
Following is the example data that comes from the .csv file
FEDERAL TAX,1084,0000
COREHCR,1084,0000
CLIENT P,1084,0000
The select is:
Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
"CorelandGLConversion.csv", conn)
The problem is that it thinks the 3rd column is an integer and changes
the
0000 to 0.
Can I tell it in the Select statement that the column is a varChar? I
can't do it after reading in the data as the data will already have
changed the 0000 to 0 at that point.

I tried to set up the table as:

Dim DTGLConversion as DataTable

DTGLConversion.Columns.Add("F1", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F2", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F3", System.Type.GetType("System.String"))

da2.Fill(DTGLConversion)
DataSetObj.Tables.Add(DTGLConversion)

This sets up the columns as strings in the DTGLConversion Table and adds
it
to the Dataset but it seems the Select in the DataAdapter must have
already
converted the string "0000" to integer 0 and then when I filled the
DataTable it reconverted it to "0".

How do I fix this?

Thanks,

Tom- Hide quoted text -

- Show quoted text -

Try to use schema.ini file

http://msdn.microsoft.com/library/de...a_ini_file.asp

I think it should be similar to this

[CorelandGLConversion.csv]
Format=Delimited(;)
ColNameHeader=True
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char

and place that file in the same folder as the CSV file
It almost works. It does give me everything. But it doesn't split each
line into 3 columns.

It makes each line one columns and ignores the comma separators. If I
change the name of the file in the schema file (so that it doesn't use it),
it splits each line (row) into 3 columns.

column 1 = FEDERAL TAX,1084,0000
column 2 = Null
column 3 = Null

If I leave out the schema.ini file (or just change the file name in the
file), I get:

column 1 = FEDERAL TAX
column 2 = 1084
column 3 = 0

Thanks,

Tom
Mar 28 '07 #4
Found it.

I needed to have:

Format=CSVDelimited

in my file.

Thanks,

Tom
"tshad" <t@home.comwrote in message
news:Op**************@TK2MSFTNGP06.phx.gbl...
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@y66g2000hsf.googlegr oups.com...
>On Mar 28, 5:11 am, "tshad" <t...@home.comwrote:
>>"tshad" <t...@home.comwrote in message

news:u5*************@TK2MSFTNGP05.phx.gbl...

How do I tell DataAdapter that Column 2 and 3 are string and not
integer?
Following is the example data that comes from the .csv file

FEDERAL TAX,1084,0000
COREHCR,1084,0000
CLIENT P,1084,0000

The select is:

Dim da2 As New OleDb.OleDbDataAdapter("Select * from " &
"CorelandGLConversion.csv", conn)

The problem is that it thinks the 3rd column is an integer and changes
the
0000 to 0.

Can I tell it in the Select statement that the column is a varChar? I
can't do it after reading in the data as the data will already have
changed the 0000 to 0 at that point.

I tried to set up the table as:

Dim DTGLConversion as DataTable

DTGLConversion.Columns.Add("F1", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F2", System.Type.GetType("System.String"))
DTGLConversion.Columns.Add("F3", System.Type.GetType("System.String"))

da2.Fill(DTGLConversion)
DataSetObj.Tables.Add(DTGLConversion)

This sets up the columns as strings in the DTGLConversion Table and adds
it
to the Dataset but it seems the Select in the DataAdapter must have
already
converted the string "0000" to integer 0 and then when I filled the
DataTable it reconverted it to "0".

How do I fix this?

Thanks,

Tom- Hide quoted text -

- Show quoted text -

Try to use schema.ini file

http://msdn.microsoft.com/library/de...a_ini_file.asp

I think it should be similar to this

[CorelandGLConversion.csv]
Format=Delimited(;)
ColNameHeader=True
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char

and place that file in the same folder as the CSV file

It almost works. It does give me everything. But it doesn't split each
line into 3 columns.

It makes each line one columns and ignores the comma separators. If I
change the name of the file in the schema file (so that it doesn't use
it), it splits each line (row) into 3 columns.

column 1 = FEDERAL TAX,1084,0000
column 2 = Null
column 3 = Null

If I leave out the schema.ini file (or just change the file name in the
file), I get:

column 1 = FEDERAL TAX
column 2 = 1084
column 3 = 0

Thanks,

Tom


Mar 28 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Eric A. Johnson | last post: by
2 posts views Thread by jonefer | last post: by
2 posts views Thread by tshad | last post: by
3 posts views Thread by Newbie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.