By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,824 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,824 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Sep 6 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>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?

Sep 6 '06 #2

P: n/a
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
Tim_Mac wrote:
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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
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?
Sep 6 '06 #3

P: n/a
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


"tom c" <to******@gmail.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
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
Tim_Mac wrote:
>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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googleg roups.com...
>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?

Sep 6 '06 #4

P: n/a
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.
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
Tim_Mac wrote:
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


"tom c" <to******@gmail.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
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
Tim_Mac wrote:
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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
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?
Sep 6 '06 #5

P: n/a
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


"tom c" <to******@gmail.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.com...
>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.

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
Tim_Mac wrote:
>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


"tom c" <to******@gmail.comwrote in message
news:11*********************@i3g2000cwc.googlegro ups.com...
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
Tim_Mac wrote:
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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googleg roups.com...
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?


Sep 6 '06 #6

P: n/a
Thanks again Tim. I will just use the wizard for now.



Tim_Mac wrote:
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


"tom c" <to******@gmail.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.com...
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.
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
Tim_Mac wrote:
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


"tom c" <to******@gmail.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
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
Tim_Mac wrote:
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
"tom c" <to******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
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?
Sep 6 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.