[2005] Update table question

[penguin5000]

Hello,
I am developing a small app that connects to Informix databases, and allows the user to type an SQL statement into a TextBox, and display the results in a DataGridView. This is fine.
However, what I want the user to be able to do is update individual cells in the grid, press update, and update the underlying table.
Hard coding specific update commands is ok, but if the user enters a command in the TextBox along the lines of ....

SELECT name, address FROM table WHERE name = "Joe"

... how would I get it write the appropriate changes to 'table'?

[jmcilhinney]

Create a DataAdapter with the query string and then use a CommandBuilder to automatically generate the non-query commands. You will need to set the MissingSchemaAction property of the adapter to AddWithKey.

[penguin5000]

Thanks for your reply.
Unfortunately, I keep getting, "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." on my line:

myDataAdapter.Update(ds, "Table")

I'm assuming that I've got a problem with the order in which I'm putting things together, but I can't figure out where the problem is:

            myCommand.CommandText = Me.txtSQLStatement.Text

            myDataAdapter = New OleDbDataAdapter(myCommand.CommandText, conn)

            myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

            myCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

            myDataAdapter.Fill(ds, "Table")

I believe that I need to run the MissingSchemaAction line before the Fill?

[jmcilhinney]

What does your query look like?

[penguin5000]

Select * From Comp

[jmcilhinney]

That seems very odd. There is also a peculiarity in your code. You're creating a Command, setting its CommandText and then creating a DataAdapter without using the Command. What is that Command for? Either use it as the SelectCommand of the DataAdapter or get rid of it altogether. Fix that and then see if you still get the same error.

[penguin5000]

Unfortunately, I get the same problem either way.

The code currently looks like:

myCommand.Connection = conn
myCommand.CommandText = Me.txtSQLStatement.Text

myDataAdapter.SelectCommand = myCommand

myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

'myDataAdapter = New OleDbDataAdapter(Me.txtSQLStatement.Text, conn)

myCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataAdapter.Fill(ds, "Table")

But I get the same problem if the code is:

'myCommand.Connection = conn
'myCommand.CommandText = Me.txtSQLStatement.Text

'myDataAdapter.SelectCommand = myCommand

myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

myDataAdapter = New OleDbDataAdapter(Me.txtSQLStatement.Text, conn)

myCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataAdapter.Fill(ds, "Table")

[jmcilhinney]

Is the SelectCommand.CommandText of your DataAdapter definitely a valid query when you call the Update method?

[penguin5000]

Yes, it seems to be:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

    MsgBox("'" & myDataAdapter.SelectCommand.CommandText.ToString & "'")

    myDataAdapter.Update(ds, "Table")

    MsgBox("Done")

End Sub

Displays, "'SELECT * FROM COMP'", before it throws the error.

[jmcilhinney]

And this is definitely the same DataAdapter object you called Fill on previously?

[penguin5000]

Yes - its the same adapter - though it was using the same name as one used earlier. So, just to be sure, I created a new one - myGridViewDataAdapter - and I still get the same error.

Beginning to wonder if this is something to do with Informix?

[Hack]

Beginning to wonder if this is something to do with Informix?

After reading through this thread, if you have followed jmcilhinney's suggestions, and it seems you have, but it is still blowing up, then that would be my guess.

[penguin5000]

I've re-written part of this to use the IBM.Data.Informix namespace, and the error message has changed to, "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information" when it hits this line:

myGridViewDataAdapter.Update(myDataSet, "Table")

in the btnUpdate_Click event.

The code used to fill the DataSet is now:

        myIfxConnection = New IfxConnection("Database=" & myDatabase & _
                                            ";Server=" & myServer & _
                                            ";User ID=username;Password=password")

        myGridViewDataAdapter = New IfxDataAdapter

        myIfxCommand = New IfxCommand(strSQLCommand, myIfxConnection)

        myGridViewDataAdapter.SelectCommand = myIfxCommand

        myIfxBuilder = New IfxCommandBuilder(myGridViewDataAdapter)

        myGridViewDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        myDataSet = New DataSet

        myIfxConnection.Open()

        myGridViewDataAdapter.Fill(myDataSet, "Table")

        myIfxConnection.Close()

Any advice is really appreciated.

[jmcilhinney]

So the obvious question is "does your SelectCommand return any key column information?". If your table has a primary key, that column (or columns) is returned by the query and you've set the MissingSchemaAction property to AddWithKey then the CommandBuilder should do its thing. If all those things are true and it still doesn't work then you're just going to have to build the non-query commands yourself.

[penguin5000]

build the non-query commands yourself

Gulp!

Unfortunately, I didn't build the database - so I'll have to dig deeper to see if there are any primary keys. Informix is horrible (IMO)!

I have noticed that there are certain hidden columns with Informix databases - one of which is called 'RowID'. This is unique, and seems to be something that Informix creates for itself (?).

Do you know of a way of assigning a primary key AFTER the Fill? I could probably code it all in such a way that 'RowID' is always collected.

[jmcilhinney]

You can assign to the DataTable's PrimaryKey property once the columns have been added. I don't know that that will help with the CommandBuilder though because it doesn't know anything about the DataTable. The CommandBuilder only knows about the DataAdapter. Maybe the DataTable schema will be used when you call Update on the adapter, maybe not. Only one way to find out.