CSV File to database.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have the following text file

1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,20031222
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,20031222
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,20031223
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,20031222
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,20031223
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,20031222

I am trying to send this file to a database on sql server. I have the following code. I get all the message boxes when I press the command button 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.OleDbConnection(ConStr
Dim da As New OleDb.OleDbDataAdapter("Select * from " &
strFileName, ConStr

da.Fill(ds, "TextFile"


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

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

'DataGrid1.DataSource = ds.Tables(0) >>>>>>>>>>>>>>UP TO HERE WORKS FINE. IT SHOWS UP IN THE GRI

Dim adoStr As String = "Server=nysvrprod01\enterprise;Database=POS;User ID=sa;Password=0cram;Trusted_Connection=False

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


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)

adoConn.Open(

adoCmdInst.Connection = (adoConn

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 = adoCmdIns

adoDA.Update(ds.Tables(0)

adoConn.Close(

MsgBox("Done")
 
Chris:

Right before you call adoDA.Update, insert this line of code:
Debug.Assert(ds.HasChanges) . If you don't see a big ugly assertion box,
then the rowstate of those records isn't being marked as Inserted which
appears to be the problem.
Chris said:
Hi,
I have the following text file:

1120,011522169050,01,+0000001,031221,0005.95,2003,8331308,20031222
9924,014305689050,01,+0000001,031221,0005.95,2003,8331308,20031222
1883,012152869050,01,+0000001,031222,0005.95,2003,8331308,20031223
1896,017202869050,01,+0000001,031221,0005.95,2003,8331308,20031222
1896,072211119050,01,+0000001,031222,0005.95,2003,8331308,20031223
1908,028502869050,01,+0000001,031221,0005.95,2003,8331308,20031222

I am trying to send this file to a database on sql server. I have the
Click to expand...
following code. I get all the message boxes when I press the command button
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")


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

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

'DataGrid1.DataSource = ds.Tables(0) >>>>>>>>>>>>>>UP TO HERE
Click to expand...
WORKS FINE. IT SHOWS UP IN THE GRID
Dim adoStr As String = "Server=nysvrprod01\enterprise;Database=POS;User
ID=sa;Password=0cram;Trusted_Connection=False"


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




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


adoConn.Open()

adoCmdInst.Connection = (adoConn)


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

I'm am not with Bill on this one, I gave you a complete sample how to do it.
Did you look at the long help I did give you? In this is also why this will
not work.

After the part from the first sample (that is untill where your problem
works) I see nothing back from the rest I did write.

Cor
 
Back
Top