How do I fill my dataset????

To fill the dataset you need to use StaffDA.Fill(objDataSet) in the retrieve method.

Thanks
Ajitha

hey thanks so much for getting back to me so quickly...

My retrieve function now looks like below.. :-)

but i am still getting the error... Object reference not set to an instance of an object.

Have you any thoughts on what i am doing wrong??? :-(

Thanks

Friend Sub Retrieve()

        'fills and clears the dataset
        objDataSet.Clear()

        StaffDA.UpdateCommand = StaffCB.GetUpdateCommand()
        StaffDA.Update(objDataSet, "Staff")
        StaffDA.Fill(objDataSet)

    End Sub

Select allOpen in new window

Whenevr you need to have data inside a DataSet, you need to have DataTable inside DataSet. Inside a single DataSet, you can have any number of DataTable.
So, you need to create a DataTable to fill the DataSet.

You can follow the following Link for the regard..

http://www.beansoftware.com/ASP.NET-Tutorials/DataSet-DataAdapter.aspx
http://bytes.com/topic/asp-net/answers/324672-creating-dataset-programmatically

Try this....

Thanks
Ajitha

Friend objDataSet

Friend Sub Retrieve()

objDataSet = new DataSet()
objConnection.Open()
StaffDA.Fill(objDataSet)

End Sub

Select allOpen in new window

Im really sorry now and i dont mean to be pestering you...

but i have another error.... :-(

upon hitting this line.. 'Creating a new row in the Customer table
            objRowStaff = objDataSet.Tables("Staff").NewRow

the following error is returned...
Object variable or With block variable not set. - NullReferenceException was unhandled

I have attached all my code below again!

Thanks again for all your help

Public Class frmAddingStaff

    Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=Database1.sdf;Initial Catalog=RmTel;Integrated Security=True;")

    Friend StaffDA As New SqlClient.SqlDataAdapter("Select * from Staff", objConnection)

    Friend StaffCB As New SqlClient.SqlCommandBuilder(StaffDA)

    Friend objDataSet


    Friend Sub Retrieve()

        'fills and clears the dataset
        objDataSet = New DataSet()
        objConnection.Open()
        StaffDA.Fill(objDataSet)

    End Sub


    Private Sub btnSSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSSave.Click

        'validation to prevent Errors from occuring where at all possible
        If txtSFName.Text <> "" And txtSLName.Text <> "" And txtSAddress.Text <> "" And IsNumeric(txtSContactNum.Text) And _
        txtSEmailAddress.Text <> "" Then

            'Declaring(Variables)
            Dim objRowStaff As DataRow

            'Creating a new row in the Customer table
            objRowStaff = objDataSet.Tables("Staff").NewRow

            'taking the values in the form(Textboxes)... placing them in the dataset
            objRowStaff.Item("StaffFirstName") = txtSFName.Text
            objRowStaff.Item("StaffLastName") = txtSLName.Text
            objRowStaff.Item("StaffAddress") = txtSAddress.Text
            objRowStaff.Item("StaffContactNum") = txtSContactNum.Text
            objRowStaff.Item("StaffAltContactNum") = txtSAContactNum.Text
            objRowStaff.Item("StaffEmailAddress") = txtSEmailAddress.Text

            'updating the customer table
            objDataSet.Tables("Staff").Rows.Add(objRowStaff)
            StaffDA.Update(objDataSet, "Staff")

            'Messagebox
            MsgBox(objRowStaff.Item("StaffFirstName") & "" & objRowStaff.Item("StaffLastName") & " has been successfully added to Staff records.", MsgBoxStyle.OkOnly, "Completed")

            'Clearing the GUI...
            txtSFName.Text = ""
            txtSLName.Text = ""
            txtSAddress.Text = ""
            txtSContactNum.Text = ""
            txtSAContactNum.Text = ""
            txtSEmailAddress.Text = ""

            txtSFName.Focus()

        Else

            'Messagebox
            MsgBox("Please ensure you have entered an appropriate value for all required fields", MsgBoxStyle.OkOnly, "Completed")
            txtSFName.Focus()

        End If

    End Sub

    Private Sub btnSClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSClear.Click

        'Clearing the GUI...
        txtSFName.Text = ""
        txtSLName.Text = ""
        txtSAddress.Text = ""
        txtSContactNum.Text = ""
        txtSAContactNum.Text = ""
        txtSEmailAddress.Text = ""

        'sets the focus again
        txtSFName.Focus()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        'Returns the user to the main menu of the admin side
        Me.Hide()
        home.Show()

    End Sub

    Private Sub frmAddingStaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Determines what will happen when the form loads up
        Retrieve()

    End Sub

End Class

Select allOpen in new window

I guess that might be because the Table is not having Staff as table name....

Try

If objDataSet.Tables.Count > 0 Then
   objRowStaff = objDataSet.Tables[0].NewRow()
End If

I believe you missed the parenthesis, do as below,

objRowStaff = objDataSet.Tables("Staff").NewRow()

hey again.. :-)

Ok so i place the block around creating a new row into an if statement... like below however it keeps skipping to the else statement ... does this mean that my dataset still doesnt have my staff table inside of it??

Private Sub btnSSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSSave.Click

        'validation to prevent Errors from occuring where at all possible
        If txtSFName.Text <> "" And txtSLName.Text <> "" And txtSAddress.Text <> "" And IsNumeric(txtSContactNum.Text) And _
        txtSEmailAddress.Text <> "" Then

            'Declaring(Variables)
            Dim objRowStaff As DataRow

            If objDataSet.Tables.Count > 0 Then
                objRowStaff = objDataSet.Tables(0).NewRow()

                'Creating a new row in the Customer table
                'objRowStaff = objDataSet.Tables("Staff").NewRow

                'taking the values in the form(Textboxes)... placing them in the dataset
                objRowStaff.Item("StaffFirstName") = txtSFName.Text
                objRowStaff.Item("StaffLastName") = txtSLName.Text
                objRowStaff.Item("StaffAddress") = txtSAddress.Text
                objRowStaff.Item("StaffContactNum") = txtSContactNum.Text
                objRowStaff.Item("StaffAltContactNum") = txtSAContactNum.Text
                objRowStaff.Item("StaffEmailAddress") = txtSEmailAddress.Text

                'updating the customer table
                objDataSet.Tables("Staff").Rows.Add(objRowStaff)
                StaffDA.Update(objDataSet, "Staff")

                'Messagebox
                MsgBox(objRowStaff.Item("StaffFirstName") & "" & objRowStaff.Item("StaffLastName") & " has been successfully added to Staff records.", MsgBoxStyle.OkOnly, "Completed")

                'Clearing the GUI...
                txtSFName.Text = ""
                txtSLName.Text = ""
                txtSAddress.Text = ""
                txtSContactNum.Text = ""
                txtSAContactNum.Text = ""
                txtSEmailAddress.Text = ""

                txtSFName.Focus()

            Else
                MsgBox("Code has skipped to the else statement", MsgBoxStyle.OkOnly, "Completed")

                'Clearing the GUI...
                txtSFName.Text = ""
                txtSLName.Text = ""
                txtSAddress.Text = ""
                txtSContactNum.Text = ""
                txtSAContactNum.Text = ""
                txtSEmailAddress.Text = ""

                txtSFName.Focus()

            End If

        Else

            'Messagebox
            MsgBox("Please ensure you have entered an appropriate value for all required fields", MsgBoxStyle.OkOnly, "Completed")
            txtSFName.Focus()

        End If

    End Sub

Select allOpen in new window

Yes., without filling the DataSet, how will it have data in it.

i was under the impression that my retrieve function did this.. am i incorrect... ??

how do i fill the dataset?

Friend Sub Retrieve()

        'fills and clears the dataset
        objDataSet = New DataSet()
        objConnection.Open()
        StaffDA.Fill(objDataSet)

    End Sub

Select allOpen in new window

The flow is correct. During load you are filling the dataset. However, I am not sure if it is working correctly. Can you check the Tables.Count after StaffDA.Fill(objDataSet) and see if Table Count is 1.

Thanks
Ajitha

hi...

Ok so what i did was removed the retrieve function and placed it within the btnSSave.

Then immediately did a test to see if the dataset had tables... however i didnt even get that far.. i was thrown this error...

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" - SQLException was unhandled

I wonder is my connection string correct??? i have referenced connectionstrings.com and other websites but i am still unsure whether or not it is correct...

I am using visual studios - Vb.net & SQL server Express??

Here is my code again.. just in case... & Thanks so much for you patience!

Public Class frmAddingStaff

    Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=Database1.sdf;Initial Catalog=RmTel;Integrated Security=True;")

    Friend StaffDA As New SqlClient.SqlDataAdapter("Select * from Staff", objConnection)

    Friend StaffCB As New SqlClient.SqlCommandBuilder(StaffDA)

    Friend objDataSet


    'Friend Sub Retrieve()

    '    'fills and clears the dataset
    '    objDataSet = New DataSet()
    '    objConnection.Open()
    '    StaffDA.Fill(objDataSet)

    'End Sub


    Private Sub btnSSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSSave.Click

        'fills and clears the dataset
        objDataSet = New DataSet()
        objConnection.Open()
        StaffDA.Fill(objDataSet)

        If objDataSet.Tables.Count > 0 Then
            MsgBox("objDataset has no tables", MsgBoxStyle.OkOnly, "Completed")

        Else

            'validation to prevent Errors from occuring where at all possible
            If txtSFName.Text <> "" And txtSLName.Text <> "" And txtSAddress.Text <> "" And IsNumeric(txtSContactNum.Text) And _
            txtSEmailAddress.Text <> "" Then

                'Declaring(Variables)
                Dim objRowStaff As DataRow

                'If objDataSet.Tables.Count > 0 Then
                objRowStaff = objDataSet.Tables("Staff").NewRow()

                'Creating a new row in the Customer table
                'objRowStaff = objDataSet.Tables("Staff").NewRow

                'taking the values in the form(Textboxes)... placing them in the dataset
                objRowStaff.Item("StaffFirstName") = txtSFName.Text
                objRowStaff.Item("StaffLastName") = txtSLName.Text
                objRowStaff.Item("StaffAddress") = txtSAddress.Text
                objRowStaff.Item("StaffContactNum") = txtSContactNum.Text
                objRowStaff.Item("StaffAltContactNum") = txtSAContactNum.Text
                objRowStaff.Item("StaffEmailAddress") = txtSEmailAddress.Text

                'updating the customer table
                objDataSet.Tables("Staff").Rows.Add(objRowStaff)
                StaffDA.Update(objDataSet, "Staff")

                'Messagebox
                MsgBox(objRowStaff.Item("StaffFirstName") & "" & objRowStaff.Item("StaffLastName") & " has been successfully added to Staff records.", MsgBoxStyle.OkOnly, "Completed")

                'Clearing the GUI...
                txtSFName.Text = ""
                txtSLName.Text = ""
                txtSAddress.Text = ""
                txtSContactNum.Text = ""
                txtSAContactNum.Text = ""
                txtSEmailAddress.Text = ""

                txtSFName.Focus()

                'Else
                '    MsgBox("Code has skipped to the else statement", MsgBoxStyle.OkOnly, "Completed")

                '    'Clearing the GUI...
                '    txtSFName.Text = ""
                '    txtSLName.Text = ""
                '    txtSAddress.Text = ""
                '    txtSContactNum.Text = ""
                '    txtSAContactNum.Text = ""
                '    txtSEmailAddress.Text = ""

                '    txtSFName.Focus()

                'End If

            Else

                'Messagebox
                MsgBox("Please ensure you have entered an appropriate value for all required fields", MsgBoxStyle.OkOnly, "Completed")
                txtSFName.Focus()

            End If

        End If

    End Sub

    Private Sub btnSClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSClear.Click

        'Clearing the GUI...
        txtSFName.Text = ""
        txtSLName.Text = ""
        txtSAddress.Text = ""
        txtSContactNum.Text = ""
        txtSAContactNum.Text = ""
        txtSEmailAddress.Text = ""

        'sets the focus again
        txtSFName.Focus()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        'Returns the user to the main menu of the admin side
        Me.Hide()
        home.Show()

    End Sub

    'Private Sub frmAddingStaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    '    'Determines what will happen when the form loads up
    '    Retrieve()

    'End Sub

End Class

Select allOpen in new window

Your connection string is not correct...

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

The datasource should have your Database IP Address. and Initial Catalog the name of the Database.

-Ajitha

Does your SQL Server lies in local machine or remote machine??

By the way, try to give the serverIP at Data Source.

Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=serverIP;Initial Catalog=RmTel;Integrated Security=True;")

Thanks Ajitha.... :-)

What exactly is myServerAddress... how would i locate that...??

I am working off my laptop from college at the moment!!

im gonna say local machine because its my laptop would i be correct in this instance??

in that case you can use Localhost.. like below

Data Source=Localhost;Initial catalog=RmTel;Integrated Security=SSPI

That is assuming the app & db are running off your laptop.

Thanks
Ajitha

where is ur server, is that remotely located?? then you should use as follows..,

Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=serverIP;Initial Catalog=RmTel;Integrated Security=True; User Id=serverUsername; pwd=serverPassword; Database=databseName; ")

:-( :-(

Im back to this error again.....

"Object variable or With block variable not set" - NullReferenceException was unhandled!

so i placed the if statement around the code again to see and it appears the dataset is still not filling correctly.../at all!! :-(

Public Class frmAddingStaff

    Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=Localhost;Initial catalog=RmTel;Integrated Security=SSPI; Initial Catalog=Database1.sdf;")

    Dim StaffDA As New SqlClient.SqlDataAdapter("Select * from Staff", objConnection)

    Dim StaffCB As New SqlClient.SqlCommandBuilder(StaffDA)

    Dim objDataSet


    Sub Retrieve()

        'fills and clears the dataset
        objDataSet = New DataSet()
        objConnection.Open()
        StaffDA.Fill(objDataSet)

    End Sub


    Private Sub btnSSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSSave.Click

        'validation to prevent Errors from occuring where at all possible
        If txtSFName.Text <> "" And txtSLName.Text <> "" And txtSAddress.Text <> "" And IsNumeric(txtSContactNum.Text) And _
        txtSEmailAddress.Text <> "" Then

            'Declaring(Variables)
            Dim objRowStaff As DataRow

            If objDataSet.Tables.Count > 0 Then

                'Creating a new row in the Customer table
                objRowStaff = objDataSet.Tables("Staff").NewRow()

                'taking the values in the form(Textboxes)... placing them in the dataset
                objRowStaff.Item("StaffFirstName") = txtSFName.Text
                objRowStaff.Item("StaffLastName") = txtSLName.Text
                objRowStaff.Item("StaffAddress") = txtSAddress.Text
                objRowStaff.Item("StaffContactNum") = txtSContactNum.Text
                objRowStaff.Item("StaffAltContactNum") = txtSAContactNum.Text
                objRowStaff.Item("StaffEmailAddress") = txtSEmailAddress.Text

                'updating the customer table
                objDataSet.Tables("Staff").Rows.Add(objRowStaff)
                StaffDA.Update(objDataSet, "Staff")

                'Messagebox
                MsgBox(objRowStaff.Item("StaffFirstName") & "" & objRowStaff.Item("StaffLastName") & " has been successfully added to Staff records.", MsgBoxStyle.OkOnly, "Completed")

                'Clearing the GUI...
                txtSFName.Text = ""
                txtSLName.Text = ""
                txtSAddress.Text = ""
                txtSContactNum.Text = ""
                txtSAContactNum.Text = ""
                txtSEmailAddress.Text = ""

                txtSFName.Focus()
            Else
                MsgBox("no tables in dataset", MsgBoxStyle.OkOnly, "Completed")

            End If


        Else

            'Messagebox
            MsgBox("Please ensure you have entered an appropriate value for all required fields", MsgBoxStyle.OkOnly, "Completed")
            txtSFName.Focus()

        End If


    End Sub

    Private Sub btnSClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSClear.Click

        'Clearing the GUI...
        txtSFName.Text = ""
        txtSLName.Text = ""
        txtSAddress.Text = ""
        txtSContactNum.Text = ""
        txtSAContactNum.Text = ""
        txtSEmailAddress.Text = ""

        'sets the focus again
        txtSFName.Focus()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        'Returns the user to the main menu of the admin side
        Me.Hide()
        home.Show()

    End Sub

    Private Sub frmAddingStaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Determines what will happen when the form loads up
        Retrieve()

        'setting the focus
        txtSFName.Focus()

    End Sub

End Class

Select allOpen in new window

Can you check if during load the Retrieve method is getting called and also try adding the below line in the last line of Retrieve function and see the result?

txtSFName.Text = objDataSet.Tables.Count

Thanks
Ajitha

I placed the line.... txtSFName.Text = objDataSet.Tables.Count at the end of the retrieve function and ran the project to see if the textbox was populated.... but it wasnt so it doesnt appear to be calling the form load!!

So i called the Retrieve() function at the beginning of the btnSSave statements and was thrown an error

..... A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)..... - SQLException was unhandled!!

:-(

could it still be my connection?? im sooo confused!!

Thanks for all your help though its great!!!

ps this is my code right now!! :-)

Public Class frmAddingStaff

    Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=Localhost;Initial catalog=RmTel;Integrated Security=SSPI; Initial Catalog=Database1.sdf;")

    Dim StaffDA As New SqlClient.SqlDataAdapter("Select * from Staff", objConnection)

    Dim StaffCB As New SqlClient.SqlCommandBuilder(StaffDA)

    Dim objDataSet


    Public Sub Retrieve()

        'fills and clears the dataset
        objDataSet = New DataSet()
        objConnection.Open()
        StaffDA.Fill(objDataSet)
        'txtSFName.Text = objDataSet.Tables.Count

    End Sub


    Private Sub btnSSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSSave.Click

        'calling the retrieve function
        Retrieve()

        'validation to prevent Errors from occuring where at all possible
        If txtSFName.Text <> "" And txtSLName.Text <> "" And txtSAddress.Text <> "" And IsNumeric(txtSContactNum.Text) And _
        txtSEmailAddress.Text <> "" Then

            'Declaring(Variables)
            Dim objRowStaff As DataRow

            If objDataSet.Tables.Count > 0 Then

                'Creating a new row in the Customer table
                objRowStaff = objDataSet.Tables("Staff").NewRow()

                'taking the values in the form(Textboxes)... placing them in the dataset
                objRowStaff.Item("StaffFirstName") = txtSFName.Text
                objRowStaff.Item("StaffLastName") = txtSLName.Text
                objRowStaff.Item("StaffAddress") = txtSAddress.Text
                objRowStaff.Item("StaffContactNum") = txtSContactNum.Text
                objRowStaff.Item("StaffAltContactNum") = txtSAContactNum.Text
                objRowStaff.Item("StaffEmailAddress") = txtSEmailAddress.Text

                'updating the customer table
                objDataSet.Tables("Staff").Rows.Add(objRowStaff)
                StaffDA.Update(objDataSet, "Staff")

                'Messagebox
                MsgBox(objRowStaff.Item("StaffFirstName") & "" & objRowStaff.Item("StaffLastName") & " has been successfully added to Staff records.", MsgBoxStyle.OkOnly, "Completed")

                'Clearing the GUI...
                txtSFName.Text = ""
                txtSLName.Text = ""
                txtSAddress.Text = ""
                txtSContactNum.Text = ""
                txtSAContactNum.Text = ""
                txtSEmailAddress.Text = ""

                txtSFName.Focus()
            Else
                MsgBox("no tables in dataset", MsgBoxStyle.OkOnly, "Completed")

            End If


        Else

            'Messagebox
            MsgBox("Please ensure you have entered an appropriate value for all required fields", MsgBoxStyle.OkOnly, "Completed")
            txtSFName.Focus()

        End If


    End Sub

    Private Sub btnSClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSClear.Click

        'Clearing the GUI...
        txtSFName.Text = ""
        txtSLName.Text = ""
        txtSAddress.Text = ""
        txtSContactNum.Text = ""
        txtSAContactNum.Text = ""
        txtSEmailAddress.Text = ""

        'sets the focus again
        txtSFName.Focus()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        'Returns the user to the main menu of the admin side
        Me.Hide()
        home.Show()

    End Sub

    Private Sub frmAddingStaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Determines what will happen when the form loads up
        Retrieve()

        'setting the focus
        txtSFName.Focus()

    End Sub

End Class

Select allOpen in new window

This will not work, your Initial Catalog is twice here, what is RmTel? and Database1.sdf? among which the database name should be written in Initial Catalog. not the following..,

Dim objConnection As New System.Data.SqlClient.SqlConnection("Data Source=Localhost;Initial catalog=RmTel;Integrated Security=SSPI; Initial Catalog=Database1.sdf;")