error while trying to insert data into SQL database

Simon1203

Member
Joined
Nov 23, 2006
Messages
6
Location
Bedford
Programming Experience
Beginner
Hi all

I am creating a windows app which interacts with a SQL database so data is contantly being called and written to the database.

I have a maddening problem with this set of code and it is driving me up the wall!

When I run my build and call the function below I get an Object reference not set to an instance of the object error. The line of code that is causing the error I have higlighted below in red. The error does not tell me which object is causing the problem.

Can anyone help me with why this is happening? I have an exact copy of the code inserting data into a different dataset and table (within the same SQL database) and it works fine so I am really bemused as to why this code fails! I have been checking through my books and I have created the XML schema correctly and the dataset is being correctly generated from the schema and called correctly so really at a loss as to why I am getting the error.

Can anyone help?

VB.NET:
[I]Public Function AddPupilAddress()

        objAddress = New MIWSBLPupilBrowserand_Details.MIWSBLPupilDetails(strCompany, strApplication)

        'get a new address dataset
        objAddressData = objAddress.GetNewAddressDS

        'initialize a datarow object from the address dataset

        [COLOR=Red]Dim objDataRow As DataRow = objAddressData.Tables("PupilAddressDetails").NewRow[/COLOR]

        'Set the values in the datarow
        objDataRow.Item("addressID") = Guid.NewGuid()
        objDataRow.Item("flatnumber") = AddressForm.txtFlatNumber.Text
        objDataRow.Item("flatblock") = AddressForm.txtFlatBlock.Text
        objDataRow.Item("housenumber") = AddressForm.txtHouseNumber.Text
        objDataRow.Item("buildingname") = AddressForm.txtBuildingName.Text
        objDataRow.Item("roadname") = AddressForm.txtRoadNameStreet.Text
        objDataRow.Item("towncity") = AddressForm.txtTownCity.Text
        objDataRow.Item("county") = AddressForm.txtCounty.Text
        objDataRow.Item("country") = AddressForm.cboCountry.SelectedIndex
        objDataRow.Item("postcode") = AddressForm.txtPostcode.Text


        'Add the Data Row to the Dataset
        objData.Tables("PupilAddressDetails").Rows.Add(objDataRow)

        'add the pupil to the the database
        Try
            Call objAddress.AddPupilAddress(objData)

        Catch ExceptionErr As Exception
            Throw New System.Exception
        End Try

        'Cleanup
        Call AddressForm.ClearAddress()

    End Function
[/I]
 
Last edited:
In your function

VB.NET:
[I]objAddressData = objAddress.GetNewAddressDS[/I]
Had you initialise your objAddressData.Tables("PupilAddressDetails") table?

Thanks for the reply. I believe that I have initialised the objAddressData but I may be wrong. Below is the Variable declarations for the Form and also the bussiness logic and Data Access code from 2 classes which is being called:

Variable Declarations:
VB.NET:
[I]Private intIndex As Integer
    Public objPupils As MIWSBLPupilBrowserand_Details.MIWSBLPupilDetails
    Public objAddress As MIWSBLPupilBrowserand_Details.MIWSBLPupilDetails
    Public objContacts As MIWSBLContactDetails.MIWSBLContactDetails
    Public objlookup As MIWSBLLookups.MIWSBLLookups
    Private objData As DataSet
    Private objAddressData As DataSet
    Private objPupilDS As DataSet
    Private objContactDS As DataSet
    Private objlookupDS As DataSet
    Public strCompany As String = "ECS"
    Public strApplication As String = "MIWS"
    Private strActiveScreen As String = "Pupils"
    Private strAppTitle As String
    Private imgCurrentNavImage As Image
    Private Const ImageSelected As Integer = 0
    Private Const ImageUnselected As Integer = 1
    Private Const ImageHighlighted As Integer = 2
    Private Const ImagePupils As Integer = 7
    Private Const ImagePreAdmissionPupils As Integer = 8
    Private Const ImageStaff As Integer = 9
    Private Const ImageContacts As Integer = 10
    Private Const ImageDoctors As Integer = 11
    Private Const ImagePreAdmissionGroups As Integer = 12
    Dim AddressForm As AddressDetails
    Public FormAddress As AddressDetails[/I]
Business Logic Functions:

VB.NET:
[I][COLOR=Red]Public Function AddPupilAddress(ByVal PupilAddressDetails As DataSet) As Boolean

        Try
            'call the data component to add the new address
            Return objMIWSDAPupils.AddPupilAddress(PupilAddressDetails)

        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, ExceptionErr.InnerException)

        End Try

    End Function[/COLOR]    

[COLOR=SeaGreen]     Public Function GetNewAddressDS() As DataSet
        Try
            'return the mainpupilsdetails schema as a dateset
            GetNewAddressDS = New PupilAddressDetails

        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, ExceptionErr.InnerException)

        End Try
    End Function[/COLOR]
[/I]Data Access Logic Function:
VB.NET:
[I]Public Function AddPupilAddress(ByVal PupilAddressDetails As DataSet) As Boolean
        Try
            MyBase.SQL = "usp_InsertPupilAddressDetails"

            'initialise the command object
            MyBase.InitializeCommand()

            'add the parameters to the parameters collection
            MyBase.AddParameter("@addressID", SqlDbType.UniqueIdentifier, 16, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("addressID"))
            MyBase.AddParameter("@housenumber", SqlDbType.BigInt, 8, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("housenumber"))
            MyBase.AddParameter("@flatnumber", SqlDbType.BigInt, 8, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("flatnumber"))
            MyBase.AddParameter("@flatblock", SqlDbType.Char, 30, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("flatblock"))
            MyBase.AddParameter("@buildingname", SqlDbType.Char, 30, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("buildingname"))
            MyBase.AddParameter("@roadname", SqlDbType.Char, 40, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("roadname"))
            MyBase.AddParameter("@towncity", SqlDbType.Char, 40, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("towncity"))
            MyBase.AddParameter("@county", SqlDbType.Char, 40, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("county"))
            MyBase.AddParameter("@country", SqlDbType.Char, 40, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("country"))
            MyBase.AddParameter("@postcode", SqlDbType.Char, 10, PupilAddressDetails.Tables("addressdetails").Rows(0).Item("postcode"))

            MyBase.ExecuteStoredProcedure()

        Catch ExceptionErr As Exception
            Throw New System.Exception(ExceptionErr.Message, ExceptionErr.InnerException)

        End Try
    End Function
[/I]I hope that it makes sense above.
 
Last edited:
While debugging in Visual Studio, if you hover over variables, the IDE will show you the values of said variable. You should be able to figure out where the Null reference is this way.

Thanks for the advice. I re-ran the app and when it hit the error I hovered over each variable to see what each contained. The variable that said it had nothing was the objDataRow. The objAddressData object does contain the PupilAddressDetails dataset so I'm a bit confused as to why this is set to nothing where in the code I have set it to equal objAddressData.

Dim objDataRow As DataRow (=nothing at run time)= objAddressData(=correct dataset schema).Tables("PupilAddressDetails").NewRo w

CAn you see why the code is doing this. I have supplied above the Business Logic and Data Logic functions that the whole process is using.

many thanks
 
Sorry, it takes me a while to figure out. I think you are not initialise your DataTable. The datatable can be initialise:

1) Doing the SQL retrieve and using the sqlAdapter to fill-up the datatable.

2) Programatically to create it, eg:

VB.NET:
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] _myDataSet.Tables.IndexOf(__TBL_BLOCK_LIST) < 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dTable = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable(__TBL_BLOCK_LIST)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] colEFlexBlock [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn("EFlexBlock", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))
dTable.Columns.Add(colEFlexBlock)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] colCrtUser [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn("Crt_Usr", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))
colCrtUser.DefaultValue = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
dTable.Columns.Add(colCrtUser)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] colCrtTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataColumn("Crt_Dt", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]))
colCrtTime.DefaultValue = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty
dTable.Columns.Add(colCrtTime)
_myDataSet.Tables.Add(dTable)
[/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]dTable = _myDataSet.Tables(__TBL_BLOCK_LIST)
dTable.Clear()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]
 
mmm I'm sure that I have because an identical set of code and functions for another part of the app works fine and this is the way I have been taught to insert data into the SQL database whilst keeping the front end blissfully unaware of the logic required to enter the data into the database.

The reason why I'm using an XML schema to build a DATASET is that if changes need to be made as to what data is to be inserted into the database (and believe me there are regular changes) then using a Schema means that I just adjust the schema to include the new items and then slightly alter the front end code to include the new items.
by using the datatable method above I would have to alter a lot more of the code to include any changes.. or at least thats my perspective on it.

Also using a dataset I find it better for performance as it does not need to have a constant connection to the database open. (I think lol)

any suggestions about other methods on using a DATASET? or any ways to work out why this error is happening?
 
Well, the problem maybe that objAddress is not a dataset... it is of type MIWSBLPupilBrowserand_Details.MIWSBLPupilDetails .... so I can only assume that .Tables is where the error occurrs.

-tg
 
mmm I'm sure that I have because an identical set of code and functions for another part of the app works fine and this is the way I have been taught to insert data into the SQL database whilst keeping the front end blissfully unaware of the logic required to enter the data into the database.

You cant really say "oh, i copied and pasted this from another part of the program where it works perfectly so it must work perfectly in this context too" - copy/paste assumptions are one of the biggest introducers of subtle bugs...

The reason why I'm using an XML schema to build a DATASET is that if changes need to be made as to what data is to be inserted into the database (and believe me there are regular changes) then using a Schema means that I just adjust the schema to include the new items and then slightly alter the front end code to include the new items.
by using the datatable method above I would have to alter a lot more of the code to include any changes.. or at least thats my perspective on it.

Its something of a mystery to me, after going to the effort of creating and using a typed dataset, that you would go and use it in generic fashion..

Try
objAddressData.PupilAddressDetails

Instead of:
objAddressData.Tables("PupilAddressDetails")


Things get a lot clearer because the intellisense is able to work out what youre talking about! Try it and see - youll see column definitions, names, types etc- it can really help prevent runtime errors of stuffing a string into a boolean..


Also using a dataset I find it better for performance as it does not need to have a constant connection to the database open. (I think lol)
Not strictly true - there are a constant number of lingering connections open to the database that are re-used as and when the .NET framework feels like it. How it manages them is not our concern, but suffice to say its like a pool of hire cars.. when you Open() a connection (after the first time) a connection is leased from the pool, rather than constructed from nothing, used and when Close()d or Dispose()d it is returned to the pool rather than being destroyed (it would be very inefficient to scrap and rebuild a car after every journey)

any suggestions about other methods on using a DATASET?

Few suggestions on dataset as well as other aspects:

dont prefix your variable names with "obj" - in an OO programming language like .NET, everything is an object (apart from primitives like int, boolean) so youre not really adding any value with that prefix. You would be better suffixing the variable with something that indicates its type:

addressDataDS for example


-

As noted before - youve gone to the effort of creating a typed dataset, dont access it in untyped fashion:

untyped= DirectCast(myDataSet.Tables("MyTableName").Columns("MyBooleanColumn"), DataColumn).DefaultValue = "wrongly try to put a string in a boolean"
typed= myDataSet.MyTableName.MyBooleanColumn.DefaultValue = "oops, thats not a boolean!"

Only in the second case will the IDE warn you before your program crashes at runtime.


Same for the NewRow stuff:

Dim ro as MyTypedDataRow <-- look, it's a TYPED data row.. this is cool because:
ro = MyTypedDataSet.MyTypedDataTable.NewMyTypedDataRowRow()
ro.MyBooleanColumn = true
ro.MyIntColumn = 1
ro.MyStringColumn = "hello world"



definitely better than this:

Dim ro as DataRow
ro = MyTypedDataSet.Tables("MyTypedDataTableName").NewRow()
ro.Items("MyBooleanColumm") = false
ro.Items("MyIntColem") = 1
ro.Items("MyStirngCollum") = "oops i made a typo in all the column names"

youll have to wait till runtime to find those bugs... :)


-

When you want to find out soemthing that is null, use the debugger!

so your line:
Dim objDataRow As DataRow = objAddressData.Tables("PupilAddressDetails").NewRow

it crashes.. no problems. Open the immediate window and type:

?objdatarow
Nothing <-- its nothing.. ok, it hasnt been assigned - the call to function didnt complete
?objAddressData
Nothing <-- ohhh, our dataset is nothing! no wonder it crashed when we try to call a method on it!

So go single step into the function that is supposed to return a nice working data set. Go examine it...



-

More suggestions:

VB.NET:
Public Function AddPupilAddress(ByVal PupilAddressDetails As DataSet) As Boolean
 
        Try
            'call the data component to add the new address
            Return objMIWSDAPupils.AddPupilAddress(PupilAddressDetails)
 
        [B]Catch ExceptionErr As Exception[/B]
[B]           Throw New System.Exception(ExceptionErr.Message, ExceptionErr.InnerException)[/B]
 
        End Try
 
    End Function

Ermm.. Why do you catch an exception only to go and throw it again? You havent changed the type of exception here or done any processing of it.. dont bother with this try/catch at all if youre only going to create another identical exception from this exception and throw that.

Especially do not do this:

VB.NET:
[I]       Catch ExceptionErr As Exception[/I]
[I]           Throw New System.Exception[/I]
[I]       End Try[/I]
You catch one exception with a potentially meaningful error message, and re-throw a blank, no-message exception. The simple rule is, if youre going to use the word "Throw" in a "Catch" block, consider whether that catch block should be there at all..


-

VB.NET:
     Public Function GetNewAddressDS() As DataSet
        Try
            'return the mainpupilsdetails schema as a dateset
            [B]GetNewAddressDS = New PupilAddressDetails[/B]

Decide on a consistent way of doing your returns.. In the last example it was:
Return (value)

In this one it's
FunctionName = (value) [and then wait for function to end]


I recommend you settle on "return" as a way of exiting subs, functions and "return (value)" to return values (from functions) because:
a) its the way most other languages operate
b) its cleaner


-

Private Const ImageSelected As Integer = 0
Private Const ImageUnselected As Integer = 1
Private Const ImageHighlighted As Integer = 2
Private Const ImagePupils As Integer = 7
Private Const ImagePreAdmissionPupils As Integer = 8
Private Const ImageStaff As Integer = 9
Private Const ImageContacts As Integer = 10
Private Const ImageDoctors As Integer = 11
Private Const ImagePreAdmissionGroups As Integer = 12

This is what we now call an Enum:

VB.NET:
  Private Enum ImageIndexes As Integer
   ImageSelected = 0
   ImageUnselected = 1
   etc...
  End Enum

Enums are cool because they are type safe and they m,ean something. You can store the string in a database and get VB to parse it back into an enum value for you. They give meaning to your code instead of all these random magic numbers flying around
If you create a method that requires an enum:

Public Sub SetImage(idx as ImageIndexes)

now we cant just say:
setImage(4) <--magic number!

before.. there was nothing stopping us doing that, with just raw constant ints. Look Enums up some time.. handy things..

-

re "data access logic function"

--> the idea of parameterized queries is partly to protect against sql injection hacking (which yours does) and partly that you buiild them once, set all the parameter types once, and then reuse the command jsut by changing the values and running it again (which you dont do)
Your code, builds the command, parameters etc, loads of new objects.. runs the query then throws it all away again.. You mentioned efficiency as part of your notion of typed datasets, so surely a constant logic of creating an destroying large numbers of objects that are optimized for preservation, will be of interest :D


-

Do not use the "Call" keyword - it is no longer needed

-

Last but not least.. i know it looks like i hammered on just about every aspect of the posted code.. hope you didnt get offended by it! :)
 
hehe I'm not offended. In fact thank you very much for typing all that advice out, it's really appreciated :D and it's certainly given me a lot to think on. This project is a mamouth one and I haven't done any program since the mid 90's (C++) so learning VB.Net is a big thing for me and any help I can get on optimising and coming up to date with the technology I am grateful to get.

I'm sure I will be posting up more in these forums as time goes by :)

Again thanks for the help. Hopefully now I can get rid of this error.
 
Its something of a mystery to me, after going to the effort of creating and using a typed dataset, that you would go and use it in generic fashion..

Try
objAddressData.PupilAddressDetails

Instead of:
objAddressData.Tables("PupilAddressDetails")


Things get a lot clearer because the intellisense is able to work out what youre talking about! Try it and see - youll see column definitions, names, types etc- it can really help prevent runtime errors of stuffing a string into a boolean..

Have tried this and the inteli sense underligns it and gives the message "PupilAddressDetails" is not a member of system.data.dataset. I'm using VS2003 so could this be the problem? (I'm downloading VS2005 at the moment but it's gonna take a while)

When you want to find out soemthing that is null, use the debugger!

so your line:
Dim objDataRow As DataRow = objAddressData.Tables("PupilAddressDetails").NewRo w

it crashes.. no problems. Open the immediate window and type:

?objdatarow
Nothing <-- its nothing.. ok, it hasnt been assigned - the call to function didnt complete
?objAddressData
Nothing <-- ohhh, our dataset is nothing! no wonder it crashed when we try to call a method on it!

So go single step into the function that is supposed to return a nice working data set. Go examine it...
Given this a go and below is what I got for the 2 objects in the line of code:

VB.NET:
?objDataRow
Nothing
?objAddressData
{MIWSBLPupilBrowserand_Details.PupilAddressDetails}
    [MIWSBLPupilBrowserand_Details.PupilAddressDetails]: {MIWSBLPupilBrowserand_Details.PupilAddressDetails}
    CaseSensitive: False
    Container: Nothing
    DataSetName: "PupilAddressDetails"
    DefaultViewManager: {System.Data.DataViewManager}
    DesignMode: False
    EnforceConstraints: True
    ExtendedProperties: {System.Data.PropertyCollection}
    HasErrors: False
    Locale: {System.Globalization.CultureInfo}
    Namespace: "http://www.tempuri.org/PupilAddressDetails.xsd"
    Prefix: ""
    Relations: {System.Data.DataRelationCollection.DataSetRelationCollection}
    Site: Nothing
    Tables: {System.Data.DataTableCollection}
As far as I can tell the objDataRow object is not being populated. I have no clue as to why but I am going to be re-writing this function as I need to give it it's own Business logic class and Data access class but any suggestions until then would be greatly appreciated.
 
Have tried this and the inteli sense underligns it and gives the message "PupilAddressDetails" is not a member of system.data.dataset. I'm using VS2003 so could this be the problem?

If you tell the IDE its an instance of DataSet then thats all the IDE knows. Quick OO lesson:

Class Shape
Class Square Inherits From Shape
Class Square has extra method: TurnRectangluar


Dim sq as Square = New Square
sq.TurnRectangular() 'success!


Dim sh as Shape = New Square
sh.TurnRectangular() 'shape does not contain a definition for TurnRectangluar
DirectCast(sh, Square).TurnRectangular() 'success


Your customised Square is presented inside a box with "Contents: Shape" on the outside. The IDE doesnt look inside the box, it looks at the box and decides its just a boring shape.

Naturally, you either cast the shape every time (to open the box) or you make a square and dont put it in a box.

Does this help you realise the solution?

(I'm downloading VS2005 at the moment but it's gonna take a while)

Im afraid VS2005 doesnt open the box either

Given this a go and below is what I got for the 2 objects in the line of code:

VB.NET:
?objDataRow
Nothing
?objAddressData
{MIWSBLPupilBrowserand_Details.PupilAddressDetails}
As far as I can tell the objDataRow object is not being populated.

True, but its not the cause of the problem.. an exception is occurring somewhere that prevents the object from populating. It isnt the object being nothing (null) that is causing the NullReference.

did you also try:
?objAddressData.Tables("PupilAddressDetails")

see what that gives.. You cant format your hard disk from the immediate window so have more of a play with it! :)


I have no clue as to why but I am going to be re-writing this function as I need to give it it's own Business logic class and Data access class but any suggestions until then would be greatly appreciated.
Well, if you cant figure it out, re-writing is an option, if a little unsavoury one :) Try and work out what is null first..
Its really simple actually...

Everywhere you see a dot . it means that some method/sub/function/property of that object is going to be called. If the object (everything before the dot) is nothing, bang.

so in this line:

objDataRow As DataRow = objAddressData.Tables("PupilAddressDetails").NewRow()

there are 2 dots. either objAddressData is nothing, or the result of objAddressData.Tables("PupilAddressDetails") is nothing. Go investigate
 
Back
Top