how to import a csv file into database table using vb.net

[Rnt6872]

I need to import a .csv file into a firebird database. I am able to
cnnect tothe databe using:
Dim ds1 As New DataSet
Dim SQLString As String = "insert into rdstype27
(division,trans_code,DATE,TIME,dept,vend,emp_no,on line_function,sku_del_units,
sku_handling_class, sku_no, loc, qty, retail,dc_sort_code,dc)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Dim constringa As String =
"User=DBADMIN;Password=admin;Database=E:\Fireb ird
\RDS.FDB;DataSource=fs000xveuc90;Port=3050;Dialect =3;Charset=NONE;Role=user;Connection
lifetime=0;Connection timeout=15;Pooling=True;Packet Size=8192;Server
Type=0"
Dim FBcn As New
FirebirdSql.Data.FirebirdClient.FbConnection(const ringa)
Dim da As New
FirebirdSql.Data.FirebirdClient.FbDataAdapter(SQLS tring, FBcn)
Dim cmdBuilder As
FirebirdSql.Data.FirebirdClient.FbCommandBuilder = New
FirebirdSql.Data.FirebirdClient.FbCommandBuilder(d a)
Dim dtRecords1 As New DataTable
Dim InsertCmd As FirebirdSql.Data.FirebirdClient.FbCommand =
New FirebirdSql.Data.FirebirdClient.FbCommand
FBcn.Open()

Then I can parse the file using:
Using parser As New FileIO.TextFieldParser("e:\firebird
\actblrds027rl.txt")
parser.SetDelimiters(",")
Try

While Not parser.EndOfData
Me.DataGridView1.Rows.Add(parser.ReadFields())

End While
Beep()
MsgBox("Import Successfull!!")
Catch ex As Exception
MsgBox(e.ToString)
End Try

End Using
I need to get the data that is in the datagridview into the database.
Please help

[tomb]

Why don't you just open the file with a datatable, then update the
database from the dataset?

T

Rnt6872 wrote:

I need to import a .csv file into a firebird database. I am able to
cnnect tothe databe using:
Dim ds1 As New DataSet
Dim SQLString As String = "insert into rdstype27
(division,trans_code,DATE,TIME,dept,vend,emp_no,on line_function,sku_del_units,
sku_handling_class, sku_no, loc, qty, retail,dc_sort_code,dc)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Dim constringa As String =
"User=DBADMIN;Password=admin;Database=E:\Fireb ird
\RDS.FDB;DataSource=fs000xveuc90;Port=3050;Dialect =3;Charset=NONE;Role=user;Connection
lifetime=0;Connection timeout=15;Pooling=True;Packet Size=8192;Server
Type=0"
Dim FBcn As New
FirebirdSql.Data.FirebirdClient.FbConnection(const ringa)
Dim da As New
FirebirdSql.Data.FirebirdClient.FbDataAdapter(SQLS tring, FBcn)
Dim cmdBuilder As
FirebirdSql.Data.FirebirdClient.FbCommandBuilder = New
FirebirdSql.Data.FirebirdClient.FbCommandBuilder(d a)
Dim dtRecords1 As New DataTable
Dim InsertCmd As FirebirdSql.Data.FirebirdClient.FbCommand =
New FirebirdSql.Data.FirebirdClient.FbCommand
FBcn.Open()

Then I can parse the file using:
Using parser As New FileIO.TextFieldParser("e:\firebird
\actblrds027rl.txt")
parser.SetDelimiters(",")
Try

While Not parser.EndOfData
Me.DataGridView1.Rows.Add(parser.ReadFields())

End While
Beep()
MsgBox("Import Successfull!!")
Catch ex As Exception
MsgBox(e.ToString)
End Try

End Using
I need to get the data that is in the datagridview into the database.
Please help

[Cor Ligthert [MVP]]

Hi,

If you are sure that the records with the primary key don't already exist,
than you can just create SQL insert code and proces that using the
OleDbCommand.ExecuteNonQuery

Cor

"Rnt6872" <r_******@msn.comschreef in bericht
news:11**********************@v45g2000cwv.googlegr oups.com...

>I need to import a .csv file into a firebird database. I am able to
cnnect tothe databe using:
Dim ds1 As New DataSet
Dim SQLString As String = "insert into rdstype27
(division,trans_code,DATE,TIME,dept,vend,emp_no,on line_function,sku_del_units,
sku_handling_class, sku_no, loc, qty, retail,dc_sort_code,dc)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Dim constringa As String =
"User=DBADMIN;Password=admin;Database=E:\Fireb ird
\RDS.FDB;DataSource=fs000xveuc90;Port=3050;Dialect =3;Charset=NONE;Role=user;Connection
lifetime=0;Connection timeout=15;Pooling=True;Packet Size=8192;Server
Type=0"
Dim FBcn As New
FirebirdSql.Data.FirebirdClient.FbConnection(const ringa)
Dim da As New
FirebirdSql.Data.FirebirdClient.FbDataAdapter(SQLS tring, FBcn)
Dim cmdBuilder As
FirebirdSql.Data.FirebirdClient.FbCommandBuilder = New
FirebirdSql.Data.FirebirdClient.FbCommandBuilder(d a)
Dim dtRecords1 As New DataTable
Dim InsertCmd As FirebirdSql.Data.FirebirdClient.FbCommand =
New FirebirdSql.Data.FirebirdClient.FbCommand
FBcn.Open()

Then I can parse the file using:
Using parser As New FileIO.TextFieldParser("e:\firebird
\actblrds027rl.txt")
parser.SetDelimiters(",")
Try

While Not parser.EndOfData
Me.DataGridView1.Rows.Add(parser.ReadFields())

End While
Beep()
MsgBox("Import Successfull!!")
Catch ex As Exception
MsgBox(e.ToString)
End Try

End Using
I need to get the data that is in the datagridview into the database.
Please help

[aaron.kempf]

uh 'BULK INSERT'?

why use a bunhc of VB code that's going to change every 18 months?

BULK INSERT works perfectly; right?

-Aaron

[punjab_tom]

i agree dog

use BULK INSERT
it's a _LOT_ faster than inserting a single record at a time

if you don't know enough about databases; why don't you go back to
preschool