OleDbDataAdapter created in code behaves differently from one created by wizard.

  • Thread starter Thread starter tom c
  • Start date Start date
T

tom c

I create 2 data OleDbDataAdapters, one with the wizard, and one in
code. I know the adapter created in code is OK because I use it to
fill a data table.

However, when I try to use the same SQL insert statement in the two
adapters, the adapter created with the wizard works fine, but the
adapter created in code gives me an error "Object reference not set to
an instance of an object". The code is below. What am I doing wrong?

'OleDbDataAdapter1 is created by the wizard. daA is created in code.

sqlA = "Select ID1, ID2 from CrossRef"
Dim daA As OleDbDataAdapter = New OleDbDataAdapter(sqlA, ConnA)
OleDbDataAdapter1.InsertCommand.CommandText = SQL 'this works fine
daA.InsertCommand.CommandText = SQL 'this gives an error

What am I missing?
 
if you look in the designer, and click on the daA, expand the InsertCommand
property and i'll bet it is empty. you can choose the correct OleDbCommand
object from the menu. then you won't get a NULLreferenceException when you
access daA.InsertCommand.CommandText.

it should be clear from the Exception that the InsertCommand property of the
daA object is set to Null, so when you try and access the CommandText
property of a null object, you get the exception.
a good idea in this case is to debug the code and when the exception
happens, examine the object in question. you'll see in the debugger
<undefined> (i think) next to any null properties.

hope this helps
tim
 
Thanks so much Tim.

You are correct. I used the debugger, set a break point, right clicked
daA, did a "Quick Watch", and I could see that insertcommand is set to
nothing.
I didn't know you could do that.

What I still don't understand is how I cerate the insert command.

How do I do that? What was left out of my original code that is needed
to create the insert, update and delete commands?

Tom
 
hi tom,
no problem. debugging is a truly essential skill for a developer, i'm glad
you got it going. whenever anything goes wrong in your code now, you can
jump right in with the debugger and identify the problem point quickly.

usually what happens with the dataAdapters is you configure them in the VS
designer. if you choose the SQL statement option, then it will
automatically generate OleDbCommand objects for Update,Delete,Insert and
Select, and attach them correctly to the DataAdapter.
if you choose Stored Procedures then you have to instruct VS which sproc
should be used for each of the 4 commands. if you leave out any of the 4
command types, then there will be no OleDbCommand object for that function
type (Insert/Update etc).

if you then look in the code behind, you will see 4 OleDbCommand objects
called OleDbSelectCommand1, OleDbInsertCommand1 etc.

you can re-configure the dataadapter by right-clicking it in the VS
designer.
alternatively you can expand the InsertCommand property of the dataAdapter
in the VS designer and choose 'new' from the menu. then you can set up the
command as you like, setting the connection, commandText properties etc.

does that make sense?
tim
 
I have a lot to learn.

When you create the dataadapter with the designer, how do you see the
code in code behind? I am using Visual Web Developer 2005 Express
edition.

When you say:
usually what happens with the dataAdapters is you configure them in the VS
designer.
Click to expand...

do you mean the wizard that comes up when you drag the dataadaper
control on to the form?

I thought that is gave you more control to actually write the code
instead of using the wizard, but maybe that is old fashioned.

Thanks again
Tom
 
hi tom,
i'm not too familiar with the Visual Web Developer, but i would say the
wizard is close enough to what i was describing. Visual Studio also has a
wizard to configure the dataadapter.

you can certainly configure the DataAdapter in your code, and i wouldn't say
it's old-fashioned by any means. it's just that you need to know more about
what goes on 'under the hood' if you do want to hand-write the code. the
wizard will create all the objects it needs, and set all the properties that
need to be set, and create proper typed parameters for all the commands. if
you do it by hand, then you need to know how to do all these things
yourself.

As a brief example: OleDbDataAdapter is a class. it has a property called
InsertCommand of type OleDbCommand. unless you set an OleDbCommand object
for this property then you can't use it! (because it is null). all
properties are null unless something is assigned to them (either by you in
your code, or the property may be initialised by the class itself).

personally i'd recommend using the wizard because it is easier to update
later on if you change your database. then you can just re-run the wizard,
instead of having to trawl through 100 lines of code and manually identify
the parameters that have changed for your Select, Insert, Delete and Update
commands. it's always nice to move higher up the food chain so to speak, by
writing less code, but as you rightly point out, it is good to know what is
happening underneath.

hope this helps
tim
 
Back
Top