Working with command builders

Anyone used these or had any problems with them?


currently I am doing this:
Dim myConnection As New OdbcConnection(myConnectionStr)
Dim myDataAdapter As New OdbcDataAdapter()
Dim mySelectSQL As String = "select * from misc..tbl_Alerts"

myDataAdapter.SelectCommand = New OdbcCommand(mySelectSQL, myConnection)
Dim custCB As OdbcCommandBuilder = New OdbcCommandBuilder(myDataAdapter)

This all seems pretty standard and should flow on easily,
except this next line fails
Debug.WriteLine(custCB.GetUpdateCommand.ToString)

This is the error:
"Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any modifiable columns."

Now I am logging in as the admin of the database and so have complete permissions over creating and updating anything in the database, and I am not trying to modify the table, just get the command builder to generate the sql for me.

Anyone have a similar problem?

I've used them before but I usually take them out before production if I do because it causes another trip to the db. I didn't have any problems though.

So at the moment I am creating a dataset, updating it, then trying to post back the changes in the db using the update method on the dataadappter. I guess I could loop through the changed records in the dataset and call a stored proc on the server to update the records if I got a performance block here, but it would be cool to be able to get this damn command builder to work for situations where it is more conveniant and quicker to use them.

I have now tried changing the table to allow nulls in several columns of the table, but still no luck.

I am going to try creating a table without an autoincrementing key next. It is a pretty straightforward task and ado.net should make it easier than this!

Now I have created a test table with 2 columns the 1st is an id field, the second is an integer field. The command builder will not work with this setup either with the same error!

tried it with a 2 column table with no primary keys and both cols allow nulls and still get :

Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any modifiable columns.

Arrrgghh

What is the full code for the Select command?

I've read that the command builder will not work without a primary key. You may have an autonumber but the field might not be set to primary key.

Edneeis, it is pretty tricky sql:

"select * from misc..tbl_test2"

Thnx Musician, will try again with primary key set, but I am using sybase so this may be a complicating factor.

It is something about your select statement or the primary key that is causing it not to work, that is why I asked.

I tried it with a table with 2 cols the 1st being an auto incrementing id field and the second taking an integer, you can't get much more rudimentary than this.

I can get a dataset out and loop through the records no probem but when it comes to the command builder it just spits the dummy, like I am going to in a minute! :D


I have also tweaked the select statement to this
Dim mySelectSQL As String = "select FirstCol from misc..tbl_test"

So I guess my only option is to loop through and write the sql myself, oh well I guess it will keep my sql up to scratch.

It kinda pisses me off that a lot of this technology seems to be dependant on interfacing with MS products only.