vb.net Trying to udate sql database from a dataset i coppied from another dataset

NCSA SCADA
NCSA SCADA used Ask the Experts™
on
I am new to this, and think I might be going about the the wrong way.  I am trying to read from one sql table (a small subset of table based on sql query) and then copy that to a new table I created.  I have been trying to do this by copying the first dataset to the second and then updating.  I get an erro that states "Update unable to find TableMapping['TempUsers'] or DataTable 'TempUsers'." when I run the code.  any help would be great
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You state that you read from one sql table and copy the 'dataset' to the second table.
Do you mean you copy the datarow you just read?
It would help if you could post your code.

Author

Commented:
I forgot to add the code
dbProvider = "provider = sqloledb;Data Source=" & VOBServer & ";Initial Catalog=Windman;User ID=user;Password=password"

con = New OleDb.OleDbConnection(dbProvider)
con.Open()
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "QueryResult")
con.Close()

Dim sql2 As String = "select * from TempUsers"
        
Dim ds2 As New DataSet
Dim da2 As OleDb.OleDbDataAdapter
da2 = New OleDb.OleDbDataAdapter(sql2, con)
con.Open()
da2.Fill(ds2, "TempUsers")
ds2 = ds.Copy
Dim cb As New OleDb.OleDbCommandBuilder(da2)
da2.Update(ds2, "TempUsers")
con.Close()
MsgBox("complete")

Open in new window

Author

Commented:
I don't know if this helps, but I create the new table just before this runs with the same column info. I have no problem creating the table
objConn.Open()
        Dim objCmd As New SqlCommand("Create Table TempUsers(ID Int,userinitials Varchar(50),userfirstname Varchar(50),usersurname Varchar(50),userorganisation Varchar(50))", objConn)
        objCmd.CommandType = CommandType.Text
        objCmd.ExecuteNonQuery()

Open in new window

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
I honestly think there must be an easier way to do this.  I just have never had a reason to try before.

Author

Commented:
any help experts

Commented:
What exactly are you trying to accomplish?
Could you create and use a DTS package instead?
Does it have to run within the program's code where other stuff happens?
Does the table always have the same structure?
The more info the better.

Author

Commented:
Here are the steps I am trying to pull off (without any code)
1) create a new table (this works)

2) read from an existing table (userinitials, userfirstname, usersurname, userorganisation - is is only   a  small subset of the table)

3) take that subset of the table and move it to the new table

Thanks again for your help

Author

Commented:
Sorry, I did not answer your questions
The new table has only an id column (int) and userinitials, userfirstname, usersurname, userorganisation columns

it does not have to run in other code.

I do not know what a DTS Package is

Thanks

Commented:
The table you copy from has exactly the same field names, types, length,etc..., correct?
So QueryResult is the subset? Could you post the select statement you are using for that?

Am I looking at this wrong or isn't TempUsers the new table which is empty?

Author

Commented:
Dim sql As String = "select userinitials, userfirstname, usersurname, userorganisation from tuser"

Author

Commented:
So when everything is said and done, I need to copy userinitials, userfirstname, usersurname, userorganisation from tuser and put in the table i created called tempusers

Commented:
So it is just one record?

Author

Commented:
no its hundreds of records just those columns are selected

Commented:
Try this (I can't try it myself since I'm on the road right now):

con = New OleDb.OleDbConnection(dbProvider)
con.Open()
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "QueryResult")
'The adapter da has the results of the query. Just use it to fill the table
Dim dt as new Datatable("TempUsers")
da.Fill(dt)            
con.Close()

Author

Commented:
I must be missing somthing here.  how do I take what is in the datatable to my new sql table

Commented:
What do you mean? If your tableadapter da contains the result of sql query "select userinitials, userfirstname, usersurname, userorganisation from tuser" then with the code above you can fill table dt with the results and name it Tempusers. You don't have to create it beforehand.
Add this to your code to make sure what I am saying is correct:

 For Each myrow As DataRow In dt.Rows
                MsgBox(myrow.Item(0).ToString)
Next

This should display the first field of every record which I believe would be userinitials in this case.

Commented:
Add it after
Dim dt as new Datatable("TempUsers")
da.Fill(dt)  
For Each myrow As DataRow In dt.Rows
                MsgBox(myrow.Item(0).ToString)
Next
But before:
con.close

Author

Commented:
I think we are missing each other here.  I need to write all the values to a new table in sql not a datatable in vb.  I created the new table at the begining of the code called TempUsers. I need that data now in the datatable (created with your help) writen to the sql table TempUsers

objConn.Open()
        Dim objCmd As New SqlCommand("Create Table TempUsers(ID Int,userinitials NVarchar(50),userfirstname NVarchar(50),usersurname NVarchar(50),userorganisation NVarchar(50))", objConn)
        objCmd.CommandType = CommandType.Text
        objCmd.ExecuteNonQuery()

Most Valuable Expert 2012
Top Expert 2014

Commented:
DataTable would only update changes to the table which was part of the original query. You can not use this technique to add records to a new table and then use the dataadapter.update to save changes. I think you can loop through the datatable and then execute a sqlcommand to insert each row to the new table. Something like

For i as integer = 0 to dTable.Rows.Count - 1
      dbcmd.commandtext = "Insert Into table(column) Values(" & dTable.Rows(i).Item(0) & ")"
      dbcmd.executenonquery()
Next
Most Valuable Expert 2012
Top Expert 2014

Commented:
>DataTable would only update changes to the table...
DATAADAPTER would only update...

Author

Commented:
That helps. I was thinking I could add or copy everything at one time.  I think I see what I need to do now.  I will give it a try in the morning thanks

Commented:
The code I gave you works (I tried it myself) and it doesn't have an update statement.
You just fill the temp table with the contents of the adapter.
I thought you needed that for the program to do some processing.
I am unclear about what you need.
You mentioned you create the table just before the code runs.
Did you mean you create the table once and then it is permanently in your database?
If not and you only need it while your program runs my code will work:
con = New OleDb.OleDbConnection(dbProvider)
con.Open()
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "QueryResult")
'The adapter da has the results of the query. Just use it to fill the table
Dim dt as new Datatable("TempUsers")
da.Fill(dt)      
' Do your processing here.      
con.Close()
 
You will be able to use TempUsers table with the records from your query to do whatever.
If  it is a permanent table and you want to 'update' the records in it everytime the program runs:
- Do you want to update the existing records?
- Do you want to add new records?
- Both?
- Do you want to clear existing records and just use the queried records?

Author

Commented:
13598 thanks for the reply.  This is a sql table that does need to be updated with the information from the da
Thanks again

Commented:
Then going back to your original code (which for some reason I have trouble following the logic)
please try to insert this into the last part:
Dim cb As New OleDb.OleDbCommandBuilder(da2)
 === Insert this here ==>   da2.UpdateCommand = cb.GetUpdateCommand()
da2.Update(ds2, "TempUsers")
con.Close()
MsgBox("complete")

Post any errors you get.

Author

Commented:
I recieved this
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Commented:
Your select query needs to return the primary key of the table. Can you change it to do so?
Dim sql As String = "select userinitials, userfirstname, usersurname, userorganisation from tuser"
needs to have it at the beginning, i.e. if the name of the primary key is ID.
Dim sql As String = "select ID,  userinitials, userfirstname, usersurname, userorganisation from tuser"

Commented:
Try adding this line:

Dim cb As New OleDb.OleDbCommandBuilder(da2)
da2.UpdateCommand = cb.GetUpdateCommand()
 === Insert this here ==>   da2.InsertCommand = cb.GetInsertCommand()
da2.Update(ds2, "TempUsers")
con.Close()
MsgBox("complete")

My bad. The very first time the table is 'updated' it requires an insert.

Author

Commented:
figured out the primary key issue. now I get this
Update unable to find TableMapping['TempUsers2'] or DataTable 'TempUsers2'.

Commented:
Can you post your current code starting at the Select statement?

Commented:
You can try adding this before filling:
da2.TableMappings.Add("Table","TempUsers" )

But looking at your original code, if you are filling the dataset with the results of the query, why are you then doing a dataset copy?
Why are you using two dataadapters? You usually want to use the same dataadpater to do your select and update.

Author

Commented:
everything has gone to chet.  There must be a way to do this.  Any chance you could show me how you would aproach this problem rather than trying to fix my mess.  I am realy stuck an frustrated.

This is all I want to do
1) create a new table in a 2000 sql database - ( this works)
2) read information from an existing table - (this works)
3) take the information from the existing table and write it to the new table
     a) there might be hundreds of records moved from the existing table to the new table

Thanks so much for your time on this
Commented:
If you don't have to use oledb you can use this. It will create the table for you too:
Imports System.Data.SqlClient

Dim sql As String = "select ID,  userinitials, userfirstname, usersurname, userorganisation Into TempUsers from tuser"

            Dim SQLConn As New SqlConnection()
             Dim SQLCmd As New SqlCommand()
             SQLConn.ConnectionString = "Server=" & sServer & ";Database=" & sData & ";uid=" & sUser & ";pwd=" & sPwd & ""
            SQLConn.Open()
           SQLCmd.Connection = SQLConn
             SQLCmd.CommandText = sql
            SQLCmd.ExecuteNonQuery()
            SQLConn.Close()

Author

Commented:
Perfect that was exaclty what I needed.  Thanks so much for you time on this

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial