CSV File to database.

[Chris]

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;Us er 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")

[William Ryan eMVP]

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" <de*****@yahoo.com> wrote in message
news:95**********************************@microsof t.com...

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 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 WORKS FINE. IT SHOWS UP IN THE GRID

Dim adoStr As String = "Server=nysvrprod01\enterprise;Database=POS;Us er
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 (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 = adoCmdInst
adoDA.Update(ds.Tables(0))
adoConn.Close()
MsgBox("Done")

[Chris]

Hi
Thanks for responding. I did see a big message box. Any ideas

Thanks

[Cor]

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