What causes SqlCommandBuilder to not build commands?

[Guest]

Language is C++ and my code is pretty much right out of the documentation
example, yet I'm not ending up with any generated commands. In the following,
assume the relevant objects are members of a form class and this is happening
on the form's load event:

m_SqlConnection = gcnew SqlConnection( strConnection );
m_SqlAdapter = gcnew SqlDataAdapter( strQuery, m_SqlConnection );
m_SqlCommandBuilder = gcnew SqlCommandBuilder( m_SqlAdapter );
m_DataSet = gcnew DataSet();
m_SqlAdapter->Fill( m_DataSet, "SomeTable" );

My dataset is correctly populated; I can see the data both in the debugger
and in the controls I bind to the dataset. My adapter, however, has
"undefined value" for every command except the SelectCommand, which I
provided myself in the constructor with strQuery.

Does anyone know why this might be the case? No exceptions are thrown from
the above code so there's no indication why the thing failed.

 

[Guest]

And if it's relevant, the string for the Select command is only pulling from
one table:

SELECT * FROM SomeTable WHERE SomeField='x' AND SomeOtherField='Y'

I've also tried simplifying it to:

SELECT * FROM SomeTable

with the same results.

 

[Cor Ligthert [MVP]]

Scott,

I am not sure of this, however can you try to change the position of the
CommandBuilder behind the Fill. I thought that your schema was by an non
strongly typed dataset not yet populated.

Can you reply, than I have that information as well.

Cor

 

[W.G. Ryan - MVP]

Scott - the most likely culprit is lack of a key on the table. If you don't
have a key, then any of the autogenerate features (CommandBuilders, the Data
Adapter Configuration Wizard etc) can't gen the update logic. I suspect
this is probably the source of your problem. There are those (such as
myself ;-) ) that are of the school of thought that every table should have
a key although admittedly it's not always 'necessary' to have a key on
every table. However if you want to use a CommandBuilder, you're going to
need one so you may want to create a key on an existing column(s) or create
a new key column to support your needs here. Then there's the other
approach of getting rid of the commandbuilder altogether (check out Weaning
Developers from the CommandBuilder at www.betav.com -> Articles -> MSDN for
an in depth explanation why). Since you asked why this isn't working not
how you should update your db, I'll spare you the anti-commandbuilder rant
;-)

 

[Marina]

The adapter doesn't actually get the commands assigned to it. It gets them
from the commandbuilder as necessary. There are methods on the
commandbuilder to get samples of what kind of command might get generated
that you can use to take a look.

I don't see where your code might even fail, since you are never issuing any
updates. You are only getting data, at which point why bother creating a
command builder?

 

[Guest]

Rants are more than welcome. We make games here so this .Net/database stuff
is new and frightening to me I wouldn't be touching it at all but it
seemed like the best tech to use for a particular tool we're writing
(project/task management stuff; it's like watching paint dry, only less fun)

Anyway, the table in question does have a primary key defined, an int field
that serves as an autoincrementing ID. Identity spec, seed and increment are
set on the DB side. If I use the designer to create a dataset and hook it up
to a simple grid view that seems to work ok, which leads me to believe the DB
is set up right.

Unfortunately my needs are a little more complex than slapping it into a
grid at design time. I need to generate at least the Select at runtime based
on the user to pull a custom list, and for usability purposes the data
displayed in this view is split between a few different controls. This much
at least is working fine and was actually easy to set up. The next step was
updating the source when changes are made on the client, which is where it
falls down (thus answering Marina's question).

I've tried setting the MissingSchemaAction::AddWithKey as well as explicitly
calling FillSchema() but I still can't get the bloody thing to give me any
commands. I've also copied the Update statement from the generated dataset
and explicitly added it as the Update command in the programagically created
one, but it doesn't actually update. That I don't know what to make of but
since assuming the fetal position and crying isn't an option I'm going with
the theory that I missed a key step somewhere.

 

[Guest]

Interesting. When I copy the update command text from a generated set and
manually set it up as the update command, what I get is a bunch of "must
declare the scalar variable @varname" exceptions when it's called.

 

[Guest]

Ok, I'm with the program now (no pun intended).

Just copying the command text doesn't take all the magic with it that the
generated version took care of, such as parameter definitions. I'm still not
sure why the builder didn't work for me but what it generates looks like a
nightmare I once had anyway. Thanks for the help and the pointer to your
article; I'm sold.

--
Scott Bruno
Shiny Entertainment

Interesting. When I copy the update command text from a generated set and
manually set it up as the update command, what I get is a bunch of "must
declare the scalar variable @varname" exceptions when it's called.

 

[W.G. Ryan - MVP]

Scott - drop me a line if you would WilliamRyan at gmaildotcom b/c I want to
take a look at your code. I've been trying to replicate the problem and
can't seem to.

Ok, I'm with the program now (no pun intended).

Just copying the command text doesn't take all the magic with it that the
generated version took care of, such as parameter definitions. I'm still
not
sure why the builder didn't work for me but what it generates looks like a
nightmare I once had anyway. Thanks for the help and the pointer to your
article; I'm sold.