Two Tables, one form retrieval.

[aishapot]

I was just wondering if I can retrieve two tables from my database to one form?
because i'm trying it but it doesn't seem to be working. just the first table i'm trying to retrieve is working.

[aishapot]

is there any other way to do this?

[adam_k]

There are a number of ways to do this, but we can't tell what "any other" would be before letting us know which is the one you are using.
If the data in the 2 tables share keys and make sense to join them you can join the tables and use 1 connection to retrieve them.
If the 2 tables cannot or should not be joined then you can use 2 separate connections and query the db or you can recycle your connection and datareader or command or whatever to read the data from the second table.

Please share the code you've used and have problems with in order to find the error and help you.

[aishapot]

For you to understand better, I have a listview (listview.jpg) where the table I'm using to retrieve the data is accounting_system. When I doubleclick on a student another form will show showing the retrieved student no from accounting system (newstudform.jpg) I only need to retrieve the student no from the accouting system, because the rest of the student info is stored in the temp_student_info.

The way its like this is bec in the process of enrollment, its the accounting(the last step) who's creating the student id. after that what we did is we will just retrieve the created student no from the accounting and retrieve the other information of the student stored in the temp_student_info and then save it all in the permanent database of the students.

This is my code for retrieving the accouting_system in the newstudform.

Sub retrieve()
        Try
            strsql = "select * from accounting_system where Student_No = @field1"
            sqlcmd = New SqlClient.SqlCommand(strsql)
            sqlcmd.CommandText = strsql
            sqlcmd.Connection = sqlconn
            sqlda.SelectCommand = sqlcmd

            With sqlcmd
                .Parameters.AddWithValue("@Field1", txtstud_no.Text)
            End With

            sqldr = sqlcmd.ExecuteReader
          
            sqlcmd.Dispose()
            sqldr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

and this is the code i used to retrieve the temp_student_info

Sub retrievestud()
        Try
            strsql = "select * from temp_student_info where TempStudNo = @field1"
            sqlcmd = New SqlClient.SqlCommand(strsql)
            sqlcmd.CommandText = strsql
            sqlcmd.Connection = sqlconn
            sqlda.SelectCommand = sqlcmd

            With sqlcmd
                .Parameters.AddWithValue("@Field1", txttempstudno.Text)
            End With

            sqldr = sqlcmd.ExecuteReader
            If (sqldr.Read()) Then
                txtsurname.Text = (sqldr("Surname"))
                txtfirstname.Text = (sqldr("First_Name"))
                txtmiddlename.Text  = (sqldr("Middle_Name"))
            End If
            sqlcmd.Dispose()
            sqldr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

[aishapot]

Oh no no, I just solved it. Haha. Right after I replied XD
Thank you anyway :D

[aishapot]

Nooo! I take it back! *.*
Yes, it's retrieving BUT it only retrieves the first data in my temp_student_info.
the data from accounting system is fine.

[aishapot]

please help >.

[adam_k]

What is the value for txttempstudno.Text ?
How do you get it populated?

In my mind this is done by adding a variable in newstudform and on load query the db with that variable as ID. In your listview double click event you call the newstudform, passing as variable the ID of the current row.

Since you've succeeded in retrieving only the first user from your temp_student_info table, I'm guessing that you've removed the criteria from your retrievestud query, which is wrong.