469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

text file to datatable to SQL2005 table not working

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

Jun 9 '06 #1
1 2587

"r1100r98" <pa*****@gmail.com> wrote in message
news:11*********************@y43g2000cwc.googlegro ups.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.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
'************************************************* *********************


Look as the SqlBulkCopy object.

David

Jun 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Chris | last post: by
8 posts views Thread by John Wildes | last post: by
11 posts views Thread by scorpion53061 | last post: by
4 posts views Thread by Amit Maheshwari | last post: by
9 posts views Thread by =?Utf-8?B?anAybXNmdA==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.