I have been struggling with inserting a row into a simple bridge table for the past while and though this would be dead simple in VB6 this process in .Net is getting me down.

Thanks to Bmains I finally got a different error message that makes some sense but no light is shining on the problem with my ocde as far as I can tell.

 The message now is:

System.InvalidOperationException: Missing the datacolumn "Survey_Id" in the datatable "Bridge" for the sourcecolumn "survey_id"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub linkProject(ByVal surveyid As Integer)
        Dim querystring As String
        Dim myrow As DataRow
        Dim bridgeinsert As String

        querystring = "Select organisation_id_fk,Survey_id_fk from surveyorganisationbridge"
        bridgeinsert = "Insert into surveyorganisationbridge (Organisation_Id_Fk,Survey_Id_Fk) values (?,?) "

        OledbDataConn.Open()


        daSurvey.SelectCommand.commantext = querystring
        daSurvey.Fill(dsSurvey, "Bridge")

        myrow = dsSurvey.Tables("Bridge").NewRow
        myrow("Organisation_Id_Fk") = CInt(Me.tvPostField.SelectedNode.Tag)
        myrow("Survey_Id_Fk") = CInt(surveyid)

        dsSurvey.Tables("Bridge").Rows.Add(myrow)

        daSurvey.Update(dsSurvey, "Bridge")

        OledbDataConn.Close()

    End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
But there isn't such a column in the table! Both columns are foreign keys for the ids of two other tables.

The dataadapter and datset are global and I have popuated the access data base table with survey information prior to inserting the row in the brige table. Also the organisation id in the orgainsation table exists already.

There is something going on that I do not understand. I have tried to do this 4 different ways (mapping tables, making associations etc) all without success. I am able to insert data into a table but there is something else going on.

Help please.

Guy Buller
Project Module Leader
Geological
Survey of Canada

__________________
Guy

After more hair pulling I got the answer....not what I expected and proves to me why dotNet, like all other wizards, are evil!

To solve my problem I had to create a new instance of the command builder object to "clean out" the commands that were built automatically by the application.

Thus the line

dim cmdBuilder as New OledbcommandBuilder(dsSurveys) is the answer.

Guy Buller
Project Module Leader
Geological
Survey of Canada

.NET is actually quite not evil if you don't use the "wizards".

daSurvey.Update(dsSurvey, "Bridge")

by using the Update u must have ur own InsertCommand in ur DataAdapter.
eg.

OleDbCommand com;
com = new OleDbCommand();

com.Parameters.Add("@ID", OleDbType.VarChar , 50, "DeptID");

com.CommandText = "Insert into MS_Test (ID) Values (@ID)";

da.InsertCommand = com;



~ Human Knowledge Belongs to the World !

It's not an evil, it's just u are not used to it only.

~ Human Knowledge Belongs to the World !