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

Discussion in 'ASP .Net' started by tom c, Sep 6, 2006.

  1. tom c

    tom c Guest

    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?
    tom c, Sep 6, 2006
    #1
    1. Advertising

  2. tom c

    Tim_Mac Guest

    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" <> wrote in message
    news:...
    >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?
    >
    Tim_Mac, Sep 6, 2006
    #2
    1. Advertising

  3. tom c

    tom c Guest

    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" <> wrote in message
    > news:...
    > >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?
    > >
    tom c, Sep 6, 2006
    #3
  4. tom c

    Tim_Mac Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> >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?
    >> >

    >
    Tim_Mac, Sep 6, 2006
    #4
  5. tom c

    tom c Guest

    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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > >> news:...
    > >> >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?
    > >> >

    > >
    tom c, Sep 6, 2006
    #5
  6. tom c

    Tim_Mac Guest

    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" <> wrote in message
    news:...
    >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" <> wrote in message
    >> news:...
    >> > 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" <> wrote in message
    >> >> news:...
    >> >> >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?
    >> >> >
    >> >

    >
    Tim_Mac, Sep 6, 2006
    #6
  7. tom c

    tom c Guest

    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" <> wrote in message
    > news:...
    > >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" <> wrote in message
    > >> news:...
    > >> > 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" <> wrote in message
    > >> >> news:...
    > >> >> >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?
    > >> >> >
    > >> >

    > >
    tom c, Sep 6, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Do
    Replies:
    1
    Views:
    326
    Natty Gur
    Nov 16, 2003
  2. Simon Kennedy
    Replies:
    0
    Views:
    329
    Simon Kennedy
    Jan 30, 2004
  3. charles
    Replies:
    3
    Views:
    469
    charles
    Mar 15, 2006
  4. BS
    Replies:
    0
    Views:
    121
  5. CreepieDeCrapper

    code behaves differently on different servers

    CreepieDeCrapper, Apr 5, 2005, in forum: Javascript
    Replies:
    5
    Views:
    126
    Grant Wagner
    Apr 7, 2005
Loading...

Share This Page