Trying to pass a CSV File to SQL Server database

C

Chris

Hi,
I am having some trouble here. I have the fill code. I get
all the textboxes, but no data is sent to the database.
Any ideas?


Dim strFileName As String = "pos.txt"
Dim strFilePath As String = "C:\pos\"
Dim ds As New DataSet


Try
Dim f As System.IO.File
If f.Exists(strFilePath & strFileName) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
strFilePath & ";Extended
Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection
(ConStr)
Dim da As New OleDb.OleDbDataAdapter
("Select * from " & _
strFileName, ConStr)

da.Fill(ds, "TextFile")


ds.Tables(0).Columns(0).ColumnName
= "POS_Store_Number"
ds.Tables(0).Columns(1).ColumnName
= "POS_UPC_Number"
ds.Tables(0).Columns(2).ColumnName
= "POS_Issue_Number"
ds.Tables(0).Columns(3).ColumnName
= "POS_Quantity_Sold"
ds.Tables(0).Columns(4).ColumnName
= "POS_Sale_Date"
ds.Tables(0).Columns(5).ColumnName
= "POS_Cover_Price"
ds.Tables(0).Columns(6).ColumnName
= "POS_Issue_Year"

ds.Tables(0).Columns(7).ColumnName
= "POS_Vendor_Number"
ds.Tables(0).Columns(8).ColumnName
= "POS_Transaction_Date"

MsgBox(ds.Tables("TextFile").Rows.Count)

End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

'DataGrid1.DataSource = ds.Tables(0)


Dim adoStr As String = "Server=svrprod01
\enterprise;Database=POS;User
ID=sa;Password=test;Trusted_Connection=False"


Dim adoConn As New SqlConnection(adoStr)
Dim adoDA As New SqlDataAdapter
Dim adoCmdInst As New SqlCommand



adoConn.Open()

adoCmdInst.Connection = (adoConn)








adoCmdInst.CommandText = "INSERT INTO
Pos_Data_File (POS_Store_Number, POS_UPC_Number," & _
" POS_Issue_Number, POS_Quantity_Sold,
POS_Sale_Date, POS_Cover_Price, POS_Issue_Year," & _
" POS_Vendor_Number, POS_Transaction_Date) VALUES
(@Pos_Store_Number, @POS_UPC_Number," & _
" @POS_Issue_Number, @POS_Quantity_Sold,
@POS_Sale_Date, @POS_Cover_Price, @POS_Issue_Year," & _
" @POS_Vendor_Number, @POS_Transaction_Date)"





adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Store_Number", SqlDbType.Char,
4, "POS_Store_Number"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_UPC_Number", SqlDbType.VarChar,
15, "POS_UPC_Number"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Issue_Number", SqlDbType.Char,
3, "POS_Issue_Number"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Quantity_Sold", SqlDbType.VarChar,
10, "POS_Quantity_Sold"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Sale_Date", SqlDbType.NChar, 8, "POS_Sale_Date"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Cover_Price", SqlDbType.NChar,
10, "POS_Cover_Price"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Issue_Year", SqlDbType.Char, 4, "POS_Issue_Year"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Vendor_Number", SqlDbType.NChar,
9, "POS_Vendor_Number"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Transaction_Date", SqlDbType.NChar,
8, "POS_Transaction_Date"))




adoDA.InsertCommand = adoCmdInst


adoDA.Update(ds, "TextFile")


adoConn.Close()


MsgBox("Done")
 
G

Guest

Wrong newsgroup, please try

http://communities2.microsoft.com/c...aspx?dg=microsoft.public.sqlserver.programmin

Patrick Rous
Microsoft MVP - Terminal Serve
http://www.workthin.co

----- Chris wrote: ----

Hi
I am having some trouble here. I have the fill code. I get
all the textboxes, but no data is sent to the database.
Any ideas


Dim strFileName As String = "pos.txt
Dim strFilePath As String = "C:\pos\
Dim ds As New DataSe


Tr
Dim f As System.IO.Fil
If f.Exists(strFilePath & strFileName) The
Dim ConStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" &
strFilePath & ";Extended
Properties=""Text;HDR=No;FMT=Delimited\""
Dim conn As New OleDb.OleDbConnectio
(ConStr
Dim da As New OleDb.OleDbDataAdapte
("Select * from " &
strFileName, ConStr

da.Fill(ds, "TextFile"


ds.Tables(0).Columns(0).ColumnName
= "POS_Store_Number
ds.Tables(0).Columns(1).ColumnName
= "POS_UPC_Number
ds.Tables(0).Columns(2).ColumnName
= "POS_Issue_Number
ds.Tables(0).Columns(3).ColumnName
= "POS_Quantity_Sold
ds.Tables(0).Columns(4).ColumnName
= "POS_Sale_Date
ds.Tables(0).Columns(5).ColumnName
= "POS_Cover_Price
ds.Tables(0).Columns(6).ColumnName
= "POS_Issue_Year

ds.Tables(0).Columns(7).ColumnName
= "POS_Vendor_Number
ds.Tables(0).Columns(8).ColumnName
= "POS_Transaction_Date

MsgBox(ds.Tables("TextFile").Rows.Count

End I
Catch ex As Exceptio
MessageBox.Show(ex.ToString
End Tr

'DataGrid1.DataSource = ds.Tables(0


Dim adoStr As String = "Server=svrprod0
\enterprise;Database=POS;User
ID=sa;Password=test;Trusted_Connection=False


Dim adoConn As New SqlConnection(adoStr
Dim adoDA As New SqlDataAdapte
Dim adoCmdInst As New SqlComman



adoConn.Open(

adoCmdInst.Connection = (adoConn








adoCmdInst.CommandText = "INSERT INTO
Pos_Data_File (POS_Store_Number, POS_UPC_Number," &
" POS_Issue_Number, POS_Quantity_Sold,
POS_Sale_Date, POS_Cover_Price, POS_Issue_Year," &
" POS_Vendor_Number, POS_Transaction_Date) VALUES
(@Pos_Store_Number, @POS_UPC_Number," &
" @POS_Issue_Number, @POS_Quantity_Sold,
@POS_Sale_Date, @POS_Cover_Price, @POS_Issue_Year," &
" @POS_Vendor_Number, @POS_Transaction_Date)





adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Store_Number", SqlDbType.Char,
4, "POS_Store_Number")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_UPC_Number", SqlDbType.VarChar,
15, "POS_UPC_Number")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Issue_Number", SqlDbType.Char,
3, "POS_Issue_Number")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Quantity_Sold", SqlDbType.VarChar,
10, "POS_Quantity_Sold")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Sale_Date", SqlDbType.NChar, 8, "POS_Sale_Date")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Cover_Price", SqlDbType.NChar,
10, "POS_Cover_Price")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Issue_Year", SqlDbType.Char, 4, "POS_Issue_Year")
adoCmdInst.Parameters.Add(New SqlParamete
("@POS_Vendor_Number", SqlDbType.NChar,
9, "POS_Vendor_Number"))
adoCmdInst.Parameters.Add(New SqlParameter
("@POS_Transaction_Date", SqlDbType.NChar,
8, "POS_Transaction_Date"))




adoDA.InsertCommand = adoCmdInst


adoDA.Update(ds, "TextFile")


adoConn.Close()


MsgBox("Done")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CSV File to database. 3
how to create table in sql server from either a datagridview or excelfile 1

Top