text file to datatable to SQL2005 table not working

  • Thread starter Thread starter r1100r98
  • Start date Start date
R

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.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
 
r1100r98 said:
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
'**********************************************************************
Click to expand...

Look as the SqlBulkCopy object.

David
 
Back
Top