Insert Statement

[jodi1982]

I am trying to insert alot of new data into a database.

I have used INSERT ...VALVES on a smaller query but this table has 32 feilds and it keeps erroring.

I use a stringbuilder to build the statment which seems correct.

I was wondering is there an easier way to do this giving each field a valve then updating i.e

username = textbox1.text
modify_date = data.now

so I can list all the fields and assign the commands then run the insert.

 

[jodi1982]

I tired

        DataRow = DataSet.Tables(0).Rows.Add
        DataRow.Item("test_id") = ""
        DataRow.Item("test") = ""
        DataSet.Tables(0).Rows.Add(DataRow)
        DataRow.AcceptChanges()

But I get the error 'This row already belongs to this table.'


I created a basic table called test, containing test_id, and test to try and get this to work but still no luck.

 

[jmcilhinney]

First of all this:

DataRow = DataSet.Tables(0).Rows.Add

should be this:

DataRow = DataSet.Tables(0).NewRow()

Secondly, get rid of this:

DataRow.AcceptChanges()

or you'll never be able to save that row to the database.

 

[jodi1982]

thank you. I edited the code now, and it runs without error but does not seem to update the database

The code is now.

DataRow = DataSet.Tables(0).NewRow
        DataRow.Item("test_id") = ""
        DataRow.Item("test") = ""
        DataSet.Tables(0).Rows.Add(DataRow)

I call set the dataset before hand by calling a class.

sql string = "SELECT * FROM test"

    Public Sub FillSave(ByVal sqlstring As String)
        If sConn.State <> ConnectionState.Open Then
            sConn.Open()
        End If

        DataAdapter.SelectCommand = New OleDbCommand(sqlstring, sConn)
        CommandBuilder = New OleDb.OleDbCommandBuilder(DataAdapter)

        DataSet.Clear()
        DataAdapter.Fill(DataSet)

    End Sub

Any Ideas.

 

[jmcilhinney]

What part of that code is supposed to save the data to the database? It doesn't happen by magic. I'll give you a hint. You get the data from the database by calling the Fill method of the DataAdapter. Maybe the DataAdapter can be used to save the changes too.

 

[cjard]

jodi; youre using .NET 2
If you do your data access properly, then the generated TableAdapter will have DBDirect methods, you can just call in a loop:

myTableAdapter.Insert(valuue1, value2 ... valueX)


ont know what a tableadapter is? Read the DW2 link in my signature

 

[jodi1982]

Thank you so much, all working ok now and so much easier than the way I was doing it before.

 

[jodi1982]

Can I use this style of function to update/edit exisiting records?