[02/03] Updating datasource from dataset

[pvbangera]

Hi all,

On a button click event, I have written the following the code to update the database:

Dim r As DataRow, MyDataAdapter as OleDbDataAdapter
        MyDataAdapter = New OleDbDataAdapter
        MyDataAdapter.SelectCommand = New OleDbCommand("select * from Results where code = '" & Code & "'", cnMyConnection)
        dsMyDataSet = New DataSet
        MyDataAdapter.Fill(dsMyDataSet)
        With dsMyDataSet.Tables(0)
            If .Rows.Count = 0 Then
                r = .NewRow
                r("Code") = Code
                r("First Name") = txtFName.Text
                r("Right issue") = txtLName.Text
                .Rows.Add(r)
            Else
                .Rows(0).Item("First Name") = txtFName.Text
                .Rows(0).Item("Last Name") = txtLName.Text
            End If
            Try
                MyDataAdapter.Update(dsMyDataSet)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End With

When the Update method is called, it gives me error: "Invalid INSERT INTO statement".

Am I doing anything wrong?

Pls guide

[robymix]

From your code, if dsMyDataSet.Tables(0) has no rows, you add a new row. If it has at least a row, you modified the first row.

You need to provide an INSERT command and an UPDATE command.

A CommandBuilder object may do the trick if you don't want to write the INSERT and UPDATE commands yourself.

[pvbangera]

Will you please provide me with the code?

THx in advance

[pvbangera]

How to use the commandbuilder?

Or is there a way thru which I can update db without using dataset?

[robymix]

have a look at this:

http://msdn.microsoft.com/library/de...andbuilder.asp

[pvbangera]

But...what's wrong in my code above?

[jmcilhinney]

You cannot get data from a data source without a SELECT statement. How can you save new data to a data source without an INSERT statement? Unless you provide some SQL code that controls how the data is inserted where is it going to go? You should read some ADO.NET tutorials. There are a number of links in my signature.

[pvbangera]

After reading an msdn article I amended my code as below:

Dim r As DataRow, MyDataAdapter as OleDbDataAdapter
        MyDataAdapter = New OleDbDataAdapter
        MyDataAdapter.SelectCommand = New OleDbCommand("select * from Results where code = '" & Code & "'", cnMyConnection)
        [B]Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(daResults)[/B]
        dsMyDataSet = New DataSet
        MyDataAdapter.Fill(dsMyDataSet)
        With dsMyDataSet.Tables(0)
            If .Rows.Count = 0 Then
                r = .NewRow
                r("Code") = Code
                r("First Name") = txtFName.Text
                r("Right issue") = txtLName.Text
                .Rows.Add(r)
            Else
                .Rows(0).Item("First Name") = txtFName.Text
                .Rows(0).Item("Last Name") = txtLName.Text
            End If
            Try
                MyDataAdapter.Update(dsMyDataSet)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End With

I have used the commandbuilder as shown in the example in msdn. But failed to understand where is it used. There was just a declartion statement. The CB object was not used anywhere else in the code.

Now, my application generates the following error:

Update unable to find TableMapping['Results'] or DataTable 'Results'.

Pls bare with me. I am stuck to this prob since 3 days and I cannot proceed with my work before solving this one.

[jmcilhinney]

You are supposed to pass your DataAdapter object to the CommandBuilder constructor, so your code should be this:

MyDataAdapter = New OleDbDataAdapter
        MyDataAdapter.SelectCommand = New OleDbCommand("select * from Results where code = '" & Code & "'", cnMyConnection)
        Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder([B][U]MyDataAdapter[/U][/B])

This creates the link between the CommandBuilder and the DataAdapter so that when you call the Update method of your DataAdapter the CommandBuilder will automatically generate the DeleteCommand, InsertCommand and UpdateCommand as required.

This is why you shouldn't just copy code examples. They don't explain why things happen the way they do. You should have read the help topic for the OleDbCommandBuilder class and it would have told you how it works and how to use it.

[pvbangera]

Sorry, my mistake... I typed it wrong in the prev post. But in my code its like this:

Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(MyDataAdapter)

.

I didnt copy the code from the help file.

Still the appln is giving me the erorr:
When I call the Update method as follows:

MyDataAdapter.Update(MyDataSet, "Results")

it gives me the error:

Update unable to find TableMapping['Results'] or DataTable 'Results'

And when I call it as:

MyDataAdapter.Update(MyDataSet)

it gives me error:

Invalid syntax in INSERT INTO statement

[pvbangera]

Is there any way thru which I can view the SQL statement generated by the CommandBuilder object, while calling the DataAdapter's Update method?

[jmcilhinney]

Have you read about the OleDbCommandBuilder yet? I'd say not or you probably would have seen mention of its GetInsertCommand method. This stuff is all there on MSDN waiting to be read.

I'd guess that your problem is that you haven't named the DataTable when you've created it. Change this:

MyDataAdapter.Fill(dsMyDataSet)
'...
MyDataAdapter.Update(dsMyDataSet)

to this:

MyDataAdapter.Fill(dsMyDataSet, "Results")
'...
MyDataAdapter.Update(dsMyDataSet, "Results")

and see if it fixes your issue.

[pvbangera]

I tried your suggestion. But it still gives me the error:

Syntax error in INSERT INTO statement

Is there any way thru which I can view the SQL statement generated by the CommandBuilder object?

[pvbangera]

I viewed the SQL statement generated by CommandBuilder object:

INSERT INTO Results( Code, First Name , Last Name) VALUES ( ? , ?, ?)

What could be the prob? Is it coz of the space within the FieldName??

[pvbangera]

I tried removing the spaces in the FieldName, but its not solving the prob

[robymix]

But...what's wrong in my code above?

Your code has no INSERT and no UPDATE commands.

[robymix]

sorry, my earlier post was late.

if a coulumn name has spaces, you need to enclose it within square braces, eg. [First Name]. Alternatively, consider using column names without spaces, the underscore character may be a good replacement.

hope this will solve your problem.

[jmcilhinney]

Firstly, you should never use column names with spaces in them. It's just inviting disaster. If you change the names of your columns in the database to remove the spaces it should work. Having said that, if you recode your query and use the actual column names with square brackets instead of the wildcard then the CommandBuilder should hopefully pick that up and do the same:[CODE]SELECT

, [First Name], [Last Name] FROM Results

Also, you might think about coding your non-query commands yourself. The CommandBuilder is a convenience but problems like this are one of the reasons that doing things yourself is better than relying on autogeneration. The CommandBuilder also has various other limitations that make it unsuitable for a great many situations.