Upd SQL Srvr table with text file

[Marc Miller]

I'm trying to update (insert into) an SQL Server table with a dataset
that I filled from an Odbc dataadapter. I must be dumber than
a rock, but I can't figure it out nor can I find any examples on the
web.

Could someone pleeeeze point me in the right direction?

Tks,
M. Miller

 

[Nick Malik [Microsoft]]

Post what you have already. It will help us to get you the rest of the way
there.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.

 

[Marc Miller]

Nick,

Thank you for your response and interest in my problem. The code that I am
using to retrieve the text follows. I have tried all sorts of ways to
get the data set from the text retrieval into a data set to update the SQL
Server table, but to not avail.

I appreciate your help.

Marc Miller
'--------------------------------
Dim ConnectionString As String = "Driver={Microsoft Text Driver (*.txt;
*.csv)};DBQ=i:\groups\oracle\empldata\"

Dim conn As Odbc.OdbcConnection = New Odbc.OdbcConnection(ConnectionString)
Dim ds As DataSet = New DataSet

conn.Open()

Dim da As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter("select * from
empsdata.dat", conn)

Try

da.Fill(ds, "empsdata")

'''''' The datagrid is just for verifying the data grab.

dg.SetDataBinding(ds, "empsdata")

'''''''

'''' The next step is to load it into an SQL Server Table.

Catch ex As OdbcException

MessageBox.Show(ex.Message)

End Try

conn.Close()

 

[Nick Malik [Microsoft]]

Hi Marc,

There is nothing wrong, in theory, with using the same dataset object to
read from one database and write to another. However, you have to translate
your field and table names.

Once you read the dataset, you will need to create a table mapping to change
the column and field names for output.
http://msdn.microsoft.com/library/e...ughmappingdatasourcetablestodatasettables.asp

I'll be honest with you... this approach is for experts. While you end up
with less code, it is not something that can be easily explained.

If you do not feel comfortable with the level of detail in the
documentation, or the references to XML Schemas, you may be better off
simply using your dataset as an input mechanism, and then, taking each row
from the input and submitting a stored procedure call to write the data, one
row at a time, to the target database. It's more code, but it's more
achievable, and you'll understand it when you are done.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.