VB.Net - Tab Pages

[pc20912]

Sir,
My English is not good, because of I attached Picture for you to understand my question.

This is My Access Data Base

There are 2 Table for Database. One for Master Details other for Repeat Details.

According to my example Kevin has come 03 time repeatly. because of that we must create 03 tab pages. Next time he come more than this time.

My Question is

When Click any Tab Page, it must show other details with textbox on the tab page like my attachment.

Please show how to do this, I am beginner because please show simple sample code also.

[G_Waddell]

Hi,

Can you give us what code you have so far and we can take it from there?

As I understand it, for a given person, you want to retrieve their "visits" from the database and display the details of each visit in a separate tab page in the Tab control?

[pc20912]

Thanks for Reply,

This my code
its created no of tab Pages to equal repeat times

I want to put details to tab pages as you write me.

Dim newPage As New TabPage()
Dim RecCount As Integer

While QReaderQ.Read()
RecCount = RecCount + 1 ' Count How many Rows
End While
TabControl1.TabPages.Clear()

For xXx = RecCount To 1 Step -1 ' to Order ---->3,2,1
newPage = New TabPage   'create new
    If xXx = 1 Then
       newPage.Text = "Repeat - 1"

    Else
       newPage.Text = "Repeat - " & xXx.ToString
    End If
  TabControl1.TabPages.Add(newPage)
Next

[G_Waddell]

Hi,
I take it QReaderQ is a datareader? The problem with that is that it is forward only i.e. you have no way of counting the rows returned until you have gone through it and once you have looped through you have to reopen.

If you use a DataAdapter to get a Datatable then you can find out the number of rows in advance and you can loop through as many times as you like.

dim DS as new dataset
dim DA as OLEDBDataadapter
dim cmd as new OLEDBcommand
dim DT as datatable
dim RecCount as integer
Dim conn As New OleDbConnection(connection)
dim DR as datarow
cmd.CommandText ="SELECT * FROM Visits WHERE (UserID =@UserID) ORDER BY VisitDate Desc"
cmd.Parameters.Add("@UserID", MyUserID)

DA.SelectCommand = cmd
DA.Fill(DS)
DT = DS.Tables(0)

RecCount = DT.Rows.Count
TabControl1.TabPages.clear()
IF RecCount > 0 then
    for each DR in DT.rows
        newPage = New TabPage
        newPage.text = "Repeat - " &RecCount
        '.....
        RecCount +=-1
        TabControl1.TabPages.Add(newPage)
    next
end if

[pc20912]

Thank you Sir, I understand it,
with your Datatable code I can create no of Tab Pages to equal of Row count. Then after I need to insert MS-Access Repeat Column's Data to seperate Tab Page with Text Boxes as like my first Attachment Picture. Please Help me to how to do that ?

[G_Waddell]

Hi

You'd need to add the controls to the Tabpage (Labels Textboxes etc) and populate...

dim VisitLabel as Label
dim VisitDateBox as TextBox


for each DR in DT.rows
    newPage = New TabPage        
    newPage.text = "Repeat - " &RecCount
    'I'll add a label and a textbox as an example you can experiment with position etc.
    VisitLabel = new Label 
    VisitLabel.text = "Return Date"
    newPage.Controls.add(VisitLabel)
    VisitDateBox = new TextBox
    VisitDateBox.Text = DR("VisitDate")
    newPage.Controls.add(VisitDateBox)

    RecCount +=-1        
    TabControl1.TabPages.Add(newPage)    
 next

[pc20912]

Sorry Sir, Please explain again with little code about DataTable.
If you can use my own Access database Column names to explain me its more valuable for me. (Attached on first post)
I search a person with "PERID" on my Access DataBase

[G_Waddell]

Hi

I dimmed the DR as a datarow and the datarow is coming from the Datatable i.e. it is a row in the DataTable.
The Datatable has been populated by the result of your SQL SELECT statement, so each Row in the Datatable is a returned row from your statement and each Column in each row equates to the Fields you selected from your database i.e. "PERID" is a field in your Access Database table and if included in your SELECT statement would be a Column in your DataTable, DR("FieldName") is just a short hand way of typing DR.Columns("FieldName").Value

[G_Waddell]

To explain further:

We created a new Dataset, we used an OLEDBDataAdaptor to fill the dataset with the results of executing a OleDBCommand.
The Command Executed the SQL SELECT statement we passed into it as Command text.

When we filled the dataset what we actually did was to add a new Datatable to our dataset with the records we returned from our query. We then selected the datatable (it was the only one in the Dataset therefore had an index of zero,) and we got a count of the rows in the datatable.

As we had one or more rows we set up a loop (For each DR in DT.Rows) to get each DataRow in the DataTable.

For each Datarow we created a new tab page and populated it.

[pc20912]

Sir, Here is the my full code, sorry it more line to read to you,
Please explain me I have some problem,

Dim newPage As New TabPage()
Dim DS As New DataSet
Dim DA As OleDbDataAdapter
Dim cmd As New OleDbCommand
Dim DT As DataTable
Dim DR As DataRow

Dim VisitLabel As Label
Dim VisitDateBox As TextBox

cmd.CommandText = "Select * from Repeat where PERID ='" & ComboBox1.SelectedValue.ToString & "'"
cmd.Parameters.Add("@PERID", MyUserID)
DA.SelectCommand = cmd
DA.Fill(DS)
DT = DS.Tables(0)
RecCount = DT.Rows.Count
TabControl1.TabPages.Clear()

If RecCount > 0 Then
For Each DR In DT.Rows
newPage =  TabPage
newPage.Text = "Repeat - " & RecCount
'I'll add a label and a textbox as an example you can experiment with position etc.
VisitLabel = New Label
VisitLabel.Text = "Repeat Data" ' MS-Access Field Name "Repeat Name"
newPage.Controls.Add(VisitLabel)
VisitDateBox = New TextBox
VisitDateBox.Text = DR("Company") ' MS-Access Field Name "Company"
newPage.Controls.Add(VisitDateBox) 
RecCount += -1
TabControl1.TabPages.Add(newPage)
Next
Endif

Sir, I really not understand about "cmd.Parameters.Add("@PERID", MyUserID)" what is MyUserId here, it call error like not declare.

Thats the my code with my database field names. From Begin I select PERID from ComboBox its come from Master Tables.

Please correct me to do that..

[G_Waddell]

Hi,
Sorry you have misunderstood me. I was using a parameterised Query string. These lines here are wrong:

cmd.CommandText = "Select * from Repeat where PERID ='" & ComboBox1.SelectedValue.ToString & "'"
cmd.Parameters.Add("@PERID", MyUserID)

They should be something like this:

cmd.CommandText = "Select * from Repeat where PERID =@PERID"
MyUserID = trim(ComboBox1.SelectedValue.ToString)
cmd.Parameters.Add("@PERID", MyUserID)

The reason for using parameters is to avoid a nasty technique called SQL injection - basically people place SQL code inside your inputs e.g. someone puts "'; DELETE FROM Repeat;" into your combo box.
If you are passing the whole SQL command in as a line of text you could get Select * from Repeat where PERID =''; DELETE FROM Repeat; The database server would read this as two seperate SQl queries and the second one would wipe your table. By using a parameter the database server knows this is a parameter and will treat it as such.

[G_Waddell]

Also this line is wrong too:

VisitLabel.Text = "Repeat Data" 

Should be:

VisitLabel.Text = DR("Repeat Data")

[pc20912]

Sir, Thank for Your Reply,
There are two error message display, what is says'

1. 'MyUserID' is not declared. for this lines
   MyUserID = Trim(ComboBox1.SelectedValue.ToString)
   cmd.Parameters.Add("@PERID", MyUserID)

2. 'TabPage' is a type and cannot be used as an expression. for this line
   newPage = TabPage

[G_Waddell]

Hi,

Have you declared MyUserID? i.e. Dim MyUserID as String

You Tab page declaration is wrong and the line you have highlighted is wrong:
Dim newPage As New TabPage() should be Dim newPage As TabPage
Then instead of newPage = TabPage you should put newPage = New TabPage

You do realise I'm just giving you pointers and examples? I'm not going to sit here and write the whole routine for you.

[pc20912]

Sorry Sir, I asked lot of questions from you because I just start my vb.net learning.

Before I post last one,
I try declare MyUserID as string, when that time, no error also can't debug. it display with green underline, that's why I post you question. Please don't misunderstand me,

[G_Waddell]

Hi,

I always try and do all my declarations at the top of the routine, that way you can find them easily and see if you have already declared it. I suspect you my have already declared the MyUserID if you move your mouse over the green line, is there a message that pops up?

[pc20912]

Sir, I learned lot of thing from you, thank for all.
I try many times to do project, still not success.

This is my full code, in my knowledge seemingly it correct no error showing before debug,
When Debug few error,

Sir, If you have enough time, Please advise me to correct my mistakes.

Dim newPage As New TabPage()

Dim cmd As New OleDbCommand
Dim DR As DataRow
            Dim VisitLabel As Label
            Dim VisitDateBox As TextBox
            Dim MyUserID As String
            Dim DS As New DataSet

            Dim DA As New OleDbDataAdapter   ' Change as New
            Dim DT As New DataTable          ' Change as New

            cmd.CommandText = "Select * from Repeat where PERID =@PERID"
            MyUserID = Trim(ComboBox1.SelectedValue.ToString)
            cmd.Parameters.AddWithValue("@PERID", MyUserID)

            DA.SelectCommand = cmd
            DA.Fill(DS)
            DT = DS.Tables(0)
            RecCount = DT.Rows.Count
            TabControl1.TabPages.Clear()

            If RecCount > 0 Then
                For Each DR In DT.Rows
                    newPage = New TabPage
                    newPage.Text = "Repeat - " & RecCount
                    VisitLabel = New Label
                    VisitLabel.Text = DR("Repeat Data") ' MS-Access Field Name "Repeat Name"
                    newPage.Controls.Add(VisitLabel)
                    VisitDateBox = New TextBox
                    VisitDateBox.Text = DR("Company") ' MS-Access Field Name "Company"
                    newPage.Controls.Add(VisitDateBox)
                    RecCount += -1
                    TabControl1.TabPages.Add(newPage)
                Next
            End If

1. If I declare "Dim newPage As TabPage()"-----> there is error says'value of type 'System.Windows.Forms.TabPage' cannot be converted to '1-dimensional array of System.Windows.Forms.TabPage'

2. When Debug with this code error on "DA.Fill(DS)", it says "Fill: SelectCommand.Connection property has not been initialized."

3. Am I correct use like this for my project
   cmd.Parameters.AddWithValue("@PERID", MyUserID)

[G_Waddell]

1. Dim newPage As New TabPage() should be Dim newPage As TabPage - NOTE: there is no () at the end of the line. (If you put a () you are declaring it to be an array of this type)

2. You did not specify or open the connection to the database for the command to use:

3. Yes, this is fine

To Connect to your database you must use a connection string, you must then let your command know that it has to use this connection and then open the connection to allow the command to use it. Bookmark the following link in your browser connectionstrings.com this site lists just about ever connection string format you'll ever need.

Here is an example for access: dim conn as new oledbconnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pathtomyaccessDB;Persist Security Info=False")

Now you will need to open the connection : conn.open

Next where you set up your command add the following line :cmd.Connection = conn

Now when your command fills the dataset, the command will connect to the Access database

[pc20912]

It works fine.

Thank you Very Much Sir,