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.SqlClient
Imports System.Data.OleDb
Imports System.Net
Imports System.IO
Imports SYGNeT3.clsFTP
Imports System.Text
Imports System.Net.Sockets

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

Public Shared Function ReadOGText() As Boolean
        Dim MyTime As System.DateTime = "00:00:00"
        Console.WriteLine("Start " & Now())
        Dim ctr As Int32 = 0
        Dim strMsg As String = ""
        Dim RetVal As Integer = 0
        Dim ConStr As String = GetConnectionString()
        Dim myConnection As New SqlConnection(ConStr)
        If Not (myConnection.State = ConnectionState.Open) Then
myConnection.Open()
        Dim sSQL As String = "DELETE FROM tblItems" ' Clean out table
before load from text file
        Dim myCommand As New SqlCommand(sSQL, myConnection)
        RetVal = myCommand.ExecuteNonQuery()
        Dim ds As New DataSet()
        Dim dt As New DataTable("temp")
        Dim da As New SqlClient.SqlDataAdapter("SELECT * FROM
tblItems", ConStr)
        ds.Clear()
        da.FillSchema(ds, SchemaType.Mapped, "temp")
        Dim path As String = AppPath(True) & "Incoming\OG.txt"
        Dim sR As IO.StreamReader =
System.IO.File.OpenText(path.ToString)
        Dim fileline As String = ""
        Try
            Do While sR.Peek <> -1
                fileline = sR.ReadLine
                Dim dr As DataRow = ds.Tables("temp").NewRow
                dr("CompanyNumber") =
Convert.ToInt32(fileline.Substring(0, 4)) ' 0-4
                dr("PriceGroup") =
Convert.ToInt16(fileline.Substring(5, 7)) '5-11
                dr("ItemCategoryCode") =
Convert.ToInt32(fileline.Substring(12, 5)) '12-16
                dr("ItemNumber") =
Convert.ToInt32(fileline.Substring(17, 9)) '17-25
                dr("ItemDescription") = fileline.Substring(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.WriteLine("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.ExecuteNonQuery()
                'ds.HasChanges() 'false
                'da.Fill(ds, "temp")
                'da.Update(ds, "tblItems")
            Catch e As Exception
                'TODO: PutInfo() add error message here
                Console.WriteLine(e.Message)
            End Try
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            If Not (myConnection.State = ConnectionState.Closed) Then
myConnection.Close()
            sR.Close()
            sR = Nothing
            GC.Collect()
            ReadOGText = True
        End Try
        Console.WriteLine("End " & Now())
    End Function
End Class

Show quote Hide quote Look as the SqlBulkCopy object.

David

Threading
Export CR to Excel problem
Last Modified date for the Source code.
Launch new instance of IE??
Evaluating threads in an application
Help with Datagridviews
dll & Web Service in VB.Net 2005 Stored Procedure
Data Reader
VB.NET/2005 Application crash
problem in crystal report