Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Database navigation

I am creating a front end for a database.  My knowledge is fairly basic in this area.  I have for example got about 5 tables.  I have one table with client details on.  This will populate fields on the left hand side of my application.  For every client there is usually more than  one record.  My database needs on the left hand side to navigate through the client records, and the right hand fields updating as you scroll through.  Once you have settled on the client record, you need to be able to cycle through the individual clients records.  (does that make sense)?  Would I do this with two different datasets?  Because if I need two sets of navigation buttons I am going to need to datasets arent I?
0
matt_swinburne
Asked:
matt_swinburne
  • 9
  • 7
1 Solution
 
TorrwinCommented:
Yes, I would use two different datasets.  The first one will scroll through the different clients.  Then, once you pick your client, you will use that information to fill a second dataset containing all of the individual records for that client.

Then, if you want to scroll through the clients just increment or decrement the index of the first dataset.
If you want to scroll through a specific clients records, just increment or decrement the index of the second dataset.

Does that make sense?
0
 
matt_swinburneAuthor Commented:
Sounds interesting, not sure exactly on the last bit but you have answered my question for now, so thank you.
0
 
matt_swinburneAuthor Commented:
Just tried that.  However it takes about 2 mins to load the datain each time.  I am not sure how to make sure that the dataset only returns the single clients details and not all of the clients.  
Any hints?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
TorrwinCommented:
Ok, well its hard to tell without seeing any code.  Can you post your code?
0
 
matt_swinburneAuthor Commented:
This is what im using so far although it is by no means final;

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dv = DataSet11.Tables("client").DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)
        dv2 = DataSet11.Tables("document").DefaultView
        cm2 = CType(Me.BindingContext(dv), CurrencyManager)

        'Fill Database
        DbClient.Fill(DataSet11, "client")
        DbFeeEarner.Fill(DataSet11, "feeearner")

        'Bind Textboxes Client
        TxtTitle.DataBindings.Add("Text", dv, "cli_title")
        TxtForename.DataBindings.Add("Text", dv, "cli_forname")
        TxtSurname.DataBindings.Add("Text", dv, "cli_surname")
        TxtAddress1.DataBindings.Add("Text", dv, "cli_add1")
        TxtAddress2.DataBindings.Add("Text", dv, "cli_add2")
        TxtAddress3.DataBindings.Add("Text", dv, "cli_add3")
        TxtTown.DataBindings.Add("Text", dv, "cli_town")
        TxtCounty.DataBindings.Add("Text", dv, "cli_county")
        TxtCountry.DataBindings.Add("Text", dv, "cli_country")
        TxtPostCode.DataBindings.Add("Text", dv, "cli_postcode")
        TxtPhoneHome.DataBindings.Add("Text", dv, "cli_hometel")
        TxtPhoneWork.DataBindings.Add("Text", dv, "cli_worktel")
        TxtPhoneMobile.DataBindings.Add("Text", dv, "cli_mobiletel")
        TxtEmail.DataBindings.Add("Text", dv, "cli_emailtel")
        TxtFax.DataBindings.Add("Text", dv, "cli_faxtel")
        TxtAIM.DataBindings.Add("Text", dv, "cli_aimref")

        'Bind ComboBoxes Client
        CmbPartner.DataBindings.Add("Text", dv, "fe_name")
        CmbFeeEarner.DataBindings.Add("Text", dv, "fe_name1")

        'Bind Textboxes Document
        CmbDocDesc.DataSource = DataSet11.Tables("document")
        CmbDocDesc.DisplayMember = "doc_description"
        CmbDocDesc.ValueMember = "cli_seq"
        CmbDocDesc.DataBindings.Add("selectedvalue", dv2, "cli_seq")

        CmbDocFileDate.DataSource = DataSet11.Tables("document")
        CmbDocFileDate.DisplayMember = "doc_filedate"
        CmbDocFileDate.ValueMember = "cli_seq"
        CmbDocFileDate.DataBindings.Add("selectedvalue", dv2, "cli_seq")

        CmbFiled.DataSource = DataSet11.Tables("document")
        CmbFiled.DisplayMember = "doc_filedby"
        CmbFiled.ValueMember = "cli_seq"
        CmbFiled.DataBindings.Add("selectedvalue", dv2, "cli_seq")

        CmbStorageLocation.DataSource = DataSet11.Tables("document")
        CmbStorageLocation.DisplayMember = "doc_description"
        CmbStorageLocation.ValueMember = "cli_seq"
        CmbStorageLocation.DataBindings.Add("selectedvalue", dv2, "cli_seq")

    End Sub

    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst1.Click, BtnLast1.Click, BtnRight1.Click, BtnLeft1.Click
        Select Case sender.Name

            Case "BtnFirst1"

                cm.Position = 0

            Case "BtnLeft1"

                cm.Position -= 1

            Case "BtnRight1"

                cm.Position += 1

            Case "BtnLast1"

                cm.Position = dv.Count - 1


        End Select

    End Sub

    Private Sub NavigationButtons2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst2.Click, BtnLast2.Click, BtnRight2.Click, BtnLeft2.Click
        Select Case sender.Name
            Case "BtnFirst2"

                cm2.Position = 0

            Case "BtnLeft2"

                cm2.Position -= 1


            Case "BtnRight2"

                cm2.Position += 1


            Case "BtnLast2"

                cm2.Position = dv2.Count - 1

        End Select

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DbDocType.Fill(DataSet11, "doctype")
        DbDocument.Fill(DataSet11, "document")
        DbLocation.Fill(DataSet11, "location")

    End Sub
End Class
0
 
TorrwinCommented:
Ok, which dataset is for all of the client records, and which dataset is for all of the records of a specific client?
0
 
matt_swinburneAuthor Commented:
OOps just realised i havnet created two datasets yet.  At the moment all the tables are bound to dataset11 however i am not sure what I need to bind to the 2nd dataset.  Any help would be appreciated.
0
 
TorrwinCommented:
Your first dataset should be bound with a list of all the patients.

Once a patient is selected, you then fill the second dataset with all the records of that patient.  

For example, say your first dataset contains patients A, B, C, and D.  The user scrolls through the patients, and selects patient C.  Then, the program should fill the 2nd dataset with all the records associated with patient C.  Like, "SELECT * FROM PATIENTS WHERE PATIENT_ID = 'C' "
0
 
matt_swinburneAuthor Commented:
Thats sounds like that is just what i wanted to know!  One thing I am wondering though is being as there are a lot of patients, is there a way of saying "SELECT * FROM PATIENTS WHERE PATIENT_ID = 'CurrentRecord'  (or something along those lines
0
 
TorrwinCommented:
Yes, "SELECT * FROM PATIENTS WHERE PATIENT_ID = '" & Dataset1.Tables(0).Rows(RowIndex).Item("Patient_ID") & "'"

Where, the RowIndex represents the row in Dataset1 of the selected patient, and Patient_ID is the name of a column.
0
 
matt_swinburneAuthor Commented:
Much appreciated Torrwin I will try that tommorow
0
 
matt_swinburneAuthor Commented:
Do you know what I declare rowindex as?
0
 
TorrwinCommented:
RowIndex is an integer, which is the Index of the row in your dataset that you want to access.  Like

DataSet1:

         Column 0
Row0:    A
Row1:    B
Row2:    C
Row3:    D
Row4:    E
0
 
matt_swinburneAuthor Commented:
Got ya.  Got rid of that error.  I am using this line:
        sstring = "SELECT * FROM client WHERE cli_seq = '" & DataSet11.Tables(0).Rows(RowIndex).Item("cli_seq") & "'"

Any idea how would I now load this into the 2nd dataset?
0
 
TorrwinCommented:
Here's how I load data into a dataset:

Dim myCommand as new SqlClient.SqlCommand
Dim myAdapter as new SqlClient.SqlDataAdapter
Dim myConnection as new SqlClient.SqlConnection
Dim myQuery as String
Dim DataSet2 as New Dataset

myConnection.ConnectionString = "Server=SERVER_IP_ADDRESS;initial catalog=DATABASE_NAME;uid=USER_ID;pwd=USER_PASSWORD;"
myCommand.Connection = myConnection

myQuery = SELECT * FROM client WHERE cli_seq = '" & DataSet11.Tables(0).Rows(RowIndex).Item("cli_seq") & "'"

myCommand.CommandText = myQuery
myAdapter.SelectCommand = myCommand
myAdapter.Fill(Dataset2, "Results")
0
 
matt_swinburneAuthor Commented:
Thanks Torrwin
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now