text file to datatable to SQL2005 table not working

[r1100r98]

I am having a problem moving the data from a datatable to the SQL2005
table (using VB2005). See code below. The SQL2005 table is empty, the
datatable is being filled from a text file, not from the SQL2005 table.
I have tried various ways, but the SQL table is not updating. Help
would be appreciated.
Code is below
Thanks

Imports System
Imports System.Data
Imports System.Data.Sql Client
Imports System.Data.Ole Db
Imports System.Net
Imports System.IO
Imports SYGNeT3.clsFTP
Imports System.Text
Imports System.Net.Sock ets

Public Class Utility
'************** **********
' other stuff here
'************** ***********

Public Shared Function ReadOGText() As Boolean
Dim MyTime As System.DateTime = "00:00:00"
Console.WriteLi ne("Start " & Now())
Dim ctr As Int32 = 0
Dim strMsg As String = ""
Dim RetVal As Integer = 0
Dim ConStr As String = GetConnectionSt ring()
Dim myConnection As New SqlConnection(C onStr)
If Not (myConnection.S tate = ConnectionState .Open) Then
myConnection.Op en()
Dim sSQL As String = "DELETE FROM tblItems" ' Clean out table
before load from text file
Dim myCommand As New SqlCommand(sSQL , myConnection)
RetVal = myCommand.Execu teNonQuery()
Dim ds As New DataSet()
Dim dt As New DataTable("temp ")
Dim da As New SqlClient.SqlDa taAdapter("SELE CT * FROM
tblItems", ConStr)
ds.Clear()
da.FillSchema(d s, SchemaType.Mapp ed, "temp")
Dim path As String = AppPath(True) & "Incoming\OG.tx t"
Dim sR As IO.StreamReader =
System.IO.File. OpenText(path.T oString)
Dim fileline As String = ""
Try
Do While sR.Peek <> -1
fileline = sR.ReadLine
Dim dr As DataRow = ds.Tables("temp ").NewRow
dr("CompanyNumb er") =
Convert.ToInt32 (fileline.Subst ring(0, 4)) ' 0-4
dr("PriceGroup" ) =
Convert.ToInt16 (fileline.Subst ring(5, 7)) '5-11
dr("ItemCategor yCode") =
Convert.ToInt32 (fileline.Subst ring(12, 5)) '12-16
dr("ItemNumber" ) =
Convert.ToInt32 (fileline.Subst ring(17, 9)) '17-25
dr("ItemDescrip tion") = fileline.Substr ing(26,
30).TrimEnd '26-55

'************** *************** *************** ***********
' the other 400 chars work as well, removed for example
'************** *************** *************** ************
dr("New") = True
dr("Date") = Now()
ds.Tables("temp ").Rows.Add (dr)
ctr += 1
Loop
Console.WriteLi ne("Rows = " & CStr(ctr))
'************** *************** *************** *************** ***********

' This where I am having a problem, getting the data from the temp
table to the
' table in the SQL2005 mdf. I am using VB2005
'************** *************** *************** *************** ***********

Try
sSQL = "INSERT INTO TBLITEMS "
sSQL &= "SELECT temp.* "
sSQL &= "FROM temp;"
Dim myCommand2 As New SqlCommand(sSQL , myConnection)
RetVal = myCommand2.Exec uteNonQuery()
'ds.HasChanges( ) 'false
'da.Fill(ds, "temp")
'da.Update(ds, "tblItems")
Catch e As Exception
'TODO: PutInfo() add error message here
Console.WriteLi ne(e.Message)
End Try
Catch ex As Exception
Console.WriteLi ne(ex.Message)
Finally
If Not (myConnection.S tate = ConnectionState .Closed) Then
myConnection.Cl ose()
sR.Close()
sR = Nothing
GC.Collect()
ReadOGText = True
End Try
Console.WriteLi ne("End " & Now())
End Function
End Class

[David Browne]

"r1100r98" <pa*****@gmail. com> wrote in message
news:11******** *************@y 43g2000cwc.goog legroups.com...

I am having a problem moving the data from a datatable to the SQL2005
table (using VB2005). See code below. The SQL2005 table is empty, the
datatable is being filled from a text file, not from the SQL2005 table.
I have tried various ways, but the SQL table is not updating. Help
would be appreciated.
Code is below
Thanks

Imports System
Imports System.Data
Imports System.Data.Sql Client
Imports System.Data.Ole Db
Imports System.Net
Imports System.IO
Imports SYGNeT3.clsFTP
Imports System.Text
Imports System.Net.Sock ets

Public Class Utility
'************** **********
' other stuff here
'************** ***********

Public Shared Function ReadOGText() As Boolean
Dim MyTime As System.DateTime = "00:00:00"
Console.WriteLi ne("Start " & Now())
Dim ctr As Int32 = 0
Dim strMsg As String = ""
Dim RetVal As Integer = 0
Dim ConStr As String = GetConnectionSt ring()
Dim myConnection As New SqlConnection(C onStr)
If Not (myConnection.S tate = ConnectionState .Open) Then
myConnection.Op en()
Dim sSQL As String = "DELETE FROM tblItems" ' Clean out table
before load from text file
Dim myCommand As New SqlCommand(sSQL , myConnection)
RetVal = myCommand.Execu teNonQuery()
Dim ds As New DataSet()
Dim dt As New DataTable("temp ")
Dim da As New SqlClient.SqlDa taAdapter("SELE CT * FROM
tblItems", ConStr)
ds.Clear()
da.FillSchema(d s, SchemaType.Mapp ed, "temp")
Dim path As String = AppPath(True) & "Incoming\OG.tx t"
Dim sR As IO.StreamReader =
System.IO.File. OpenText(path.T oString)
Dim fileline As String = ""
Try
Do While sR.Peek <> -1
fileline = sR.ReadLine
Dim dr As DataRow = ds.Tables("temp ").NewRow
dr("CompanyNumb er") =
Convert.ToInt32 (fileline.Subst ring(0, 4)) ' 0-4
dr("PriceGroup" ) =
Convert.ToInt16 (fileline.Subst ring(5, 7)) '5-11
dr("ItemCategor yCode") =
Convert.ToInt32 (fileline.Subst ring(12, 5)) '12-16
dr("ItemNumber" ) =
Convert.ToInt32 (fileline.Subst ring(17, 9)) '17-25
dr("ItemDescrip tion") = fileline.Substr ing(26,
30).TrimEnd '26-55

'************** *************** *************** ***********
' the other 400 chars work as well, removed for example
'************** *************** *************** ************
dr("New") = True
dr("Date") = Now()
ds.Tables("temp ").Rows.Add (dr)
ctr += 1
Loop
Console.WriteLi ne("Rows = " & CStr(ctr))
'************** *************** *************** *************** ***********

' This where I am having a problem, getting the data from the temp
table to the
' table in the SQL2005 mdf. I am using VB2005
'************** *************** *************** *************** ***********

Look as the SqlBulkCopy object.

David