[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I fill my dataset????

Posted on 2010-01-05
24
Medium Priority
?
326 Views
Last Modified: 2013-11-26
Hey experts,

Thanks in advance for all help with this problem.

The dataset is not picking up the staff table and so i am unable to add to the db while running the below code... please help!

Thanks a million
Public Class frmAddingStaff

    'Friend objConnection As New SqlClient.SqlConnection("Provider=sqloledb; Data Source=Database1.sdf;")

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


    'objConnection.connectionString = "Data Source=Database1.sdf;Initial Catalog=RmTel;Integrated Security=True;"
    'dim conn as New System.Data.SqlClient.SqlConnection
    'conn.ConnectionString = "Data Source=MAX;Initial Catalog=RmTel;Integrated
    'Security=True"
    'conn.Open()


    'Provider=SQLNCLI.1
    'Provider=sqloledb.1
    '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RoyalAbbeyBanking.mdb"

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

    Friend StaffCB As New SqlClient.SqlCommandBuilder(StaffDA)

    Friend objDataSet As New DataSet

    Friend Sub Retrieve()

        'fills and clears the dataset
        objDataSet.Clear()

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

    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


            If IsNothing(objDataSet.Tables("Staff")) Then

                MsgBox("objDataSet doesn't have table 'Staff' ")

            ElseIf IsNothing(objDataSet) Then

                MsgBox("objDataSet is Nothing - Not Defined Yet")

            Else

                '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()
            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()

    End Sub

End Class

Open in new window

0
Comment
Question by:Keirameister
  • 10
  • 8
  • 6
24 Comments
 
LVL 14

Expert Comment

by:ajitha75
ID: 26178786
To fill the dataset you need to use StaffDA.Fill(objDataSet) in the retrieve method.

Thanks
Ajitha
0
 

Author Comment

by:Keirameister
ID: 26178840
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

Open in new window

0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26178847
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 14

Expert Comment

by:ajitha75
ID: 26178874
Try this....

Thanks
Ajitha


Friend objDataSet

Friend Sub Retrieve()

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

End Sub

Open in new window

0
 

Author Comment

by:Keirameister
ID: 26178928

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

Open in new window

0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26178944
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
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26178956
I believe you missed the parenthesis, do as below,

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

Author Comment

by:Keirameister
ID: 26179006
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

Open in new window

0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179043
Yes., without filling the DataSet, how will it have data in it.
0
 

Author Comment

by:Keirameister
ID: 26179054
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

Open in new window

0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26179113
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
0
 

Author Comment

by:Keirameister
ID: 26179196
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

Open in new window

0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26179222
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


0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179243
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;")
0
 

Author Comment

by:Keirameister
ID: 26179247
Thanks Ajitha.... :-)

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

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

Author Comment

by:Keirameister
ID: 26179257
im gonna say local machine because its my laptop would i be correct in this instance??
0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26179261
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
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179265
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; ")
0
 

Author Comment

by:Keirameister
ID: 26179364
:-( :-(

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

Open in new window

0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26179449
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
0
 

Author Comment

by:Keirameister
ID: 26179515
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!!!
0
 

Author Comment

by:Keirameister
ID: 26179530
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

Open in new window

0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 26179566
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;")
0
 
LVL 14

Accepted Solution

by:
ajitha75 earned 2000 total points
ID: 26179639
Couple of things you need to fix..

1. Why the Retrieve function is not getting executed.
2. A valid connection string to connect to the server.

For now, try the Fill of Dataset in btnSave event handler and get it working fine. once that works, we can move the code to retrieve and see why its not getting called.

Make the changes to connection as mentioned by waltersnow.... and test it

Thanks
Ajitha
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question