Queennie L
asked on
Import from comma delimited text file into SQL Server 2008 R2 table using VB.net 2010
I have an error(see attached word file) importing from comma delimited text file into SQL Server 2008 R2 table using VB.net 2010.
Here's the code:
Thank you.
Error.docx
Here's the code:
Dim FileName As String = "textfile.txt"
Dim txtConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\network\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""
Dim cn As New OleDbConnection(txtConn)
Dim cmd As New OleDbCommand("SELECT * FROM " & FileName, cn)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=Main;Initial Catalog=Main;Persist Security Info=True;User ID=sa;Password=helloworld"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
bulkCopy.DestinationTableName = "dbo.tblImport"
Try
objDR = cmd.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
Thank you.
Error.docx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Kyle:
cn is to connect to text file
SQLconn is to connect to SQL Server table.
Am I doing it wrong?
cn is to connect to text file
SQLconn is to connect to SQL Server table.
Am I doing it wrong?
You were doing it right . . . my comment was before you fixed it. You needed to add cn.open and open both connections.
as for why you're getting null:
Do you have the file open? First try seeing if you have anything in the reader before writing.
A good example:
https://msdn.microsoft.com/en-us/library/txy68e2d(v=vs.110).aspx
as for why you're getting null:
Do you have the file open? First try seeing if you have anything in the reader before writing.
A good example:
https://msdn.microsoft.com/en-us/library/txy68e2d(v=vs.110).aspx
ASKER
No Answer?????
Do you have the file open? Also first try making sure you have info in the reader before doing the bulk update.
ASKER
Thank you.
ASKER
I added cn.open and worked perfectly
Dim cn As New OleDbConnection(txtConn)
cn.Open()
But when inserted to the SQL table is NULL.
I don't why is not inserting txt data to sql table.
Thank you.