hi, what i need to do is to get information from 2 tables and show it to the listview. for example, i want to get the surname, first name and middle name from the student_info table and the Payment_Status from the accounting_system. i don't want to show the information that is already on the accounting system that has 'Paid' on the Payment_Status. Hope you can help me

my friend gave me this code

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

but the listview only showed the first data on the database

Recommended Answers

All 10 Replies

Why you not merge it as one sql query?

strsql = "select * from student_info where Surname, First_Name, Middle_Name not in " _
		& "(select Surname, First_Name, Middle_Name from accounting_system)"
MsgBox("student")

Why you not merge it as one sql query?

strsql = "select * from student_info where Surname, First_Name, Middle_Name not in " _
		& "(select Surname, First_Name, Middle_Name from accounting_system)"
MsgBox("student")

hi i've tried doing this but the the information on the database did not appear on the listview at all

How about post your 'showing data' codes?
Maybe it's not about the query.

How about post your 'showing data' codes?
Maybe it's not about the query.

i am currently using this

Sub fillstudinfo()
                strsql = "select * from student_info"
        sqlcmd.CommandText = strsql
        sqlcmd.Connection = sqlconn
        sqlda.SelectCommand = sqlcmd
        sqldr = sqlcmd.ExecuteReader()
        ListView1.Items.Clear()

        While (sqldr.Read())
            With ListView1.Items.Add(sqldr("Surname"))
                .subitems.add(sqldr("First_Name"))
                .subitems.add(sqldr("Middle_Name"))
                '.subitems.add(sqldr("Payment_Type"))
                '.subitems.add(sqldr("Payment_Amount"))
                '.subitems.add(sqldr("Date_Paid"))
                .subitems.add(sqldr("Student_No"))
                .subitems.add(sqldr("Grade_yearlevel"))
            End With

        End While
        sqldr.Close()

    End Sub

and everything works just fine except for what i really want to happen, but once i insert the codes above or the one that was given by Jx_Man, nothing will happen or nothing will appear on the listview. hope you can help me

i want to get the surname, first name and middle name from the student_info table and the Payment_Status from the accounting_system.

The above can be achieved with this query:

select Surname, First_Name, Middle_Name, Payment_Status from t_student_info a
inner join t_accounting_system b 
on a.Surname = b.Surname and a.First_Name = b.First_Name and a.Middle_Name = b.Middle_Name

I don't get what you mean by this:

i don't want to show the information that is already on the accounting system that has 'Paid' on the Payment_Status.

The query bellow will show you Surname, First_Name, Middle_Name for students that don't exist in accounting system.

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

Finally if you want to only show the students with Payment_Status = 'Paid' then add in my first query where Payment_Status = 'Paid'

The above can be achieved with this query:

select Surname, First_Name, Middle_Name, Payment_Status from t_student_info a
inner join t_accounting_system b 
on a.Surname = b.Surname and a.First_Name = b.First_Name and a.Middle_Name = b.Middle_Name

I don't get what you mean by this:


The query bellow will show you Surname, First_Name, Middle_Name for students that don't exist in accounting system.

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

Finally if you want to only show the students with Payment_Status = 'Paid' then add in my first query where Payment_Status = 'Paid'

hi, i tried doing this but the result is still the same, the database is only showing the first data that is already on the accounting system

try posting your data model we are guess at the SQL without knowing how your data is structured

or you could add a where to get yourself the record you are looking for.

PS: What do you mean only the first data?

this is the accounting_system table and what i need from here is the Payment_Type only
USE [WAIS.MDF]
GO

/****** Object: Table [dbo].[accounting_system] Script Date: 01/19/2012 09:30:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[accounting_system](
[Studentpayment_id] [int] IDENTITY(1,1) NOT NULL,
[Total_Payment_Amount] [int] NULL,
[Payment_Type] [varchar](35) NULL,
[Discount] [varchar](35) NULL,
[Payment_Period] [varchar](35) NULL,
[Payment_Amount] [int] NULL,
[Payment_Status] [varchar](35) NULL,
[Date_Paid] [varchar](50) NULL,
[Outstanding_Balance] [int] NULL,
[Surname] [varchar](35) NULL,
[First_Name] [varchar](35) NULL,
[Middle_Name] [varchar](35) NULL,
[Grade_YearLevel] [varchar](20) NULL,
[OptionType] [varchar](50) NULL,
CONSTRAINT [PK_accounting_system] PRIMARY KEY CLUSTERED
(
[Studentpayment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and this is the student_info table, and what i need from here Surname, First_Name and Middle_Name
USE [WAIS.MDF]
GO

/****** Object: Table [dbo].[student_info] Script Date: 01/19/2012 09:32:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[student_info](
[Student_No] [int] NOT NULL,
[Surname] [nvarchar](35) NULL,
[First_Name] [nvarchar](35) NULL,
[Middle_Name] [nvarchar](35) NULL,
[Home_Address] [nvarchar](50) NULL,
[Birth_Date] [date] NULL,
[Religion] [nvarchar](35) NULL,
[Tel_No] [nvarchar](35) NULL,
[Gender] [text] NULL,
[Name_incase_Emergency] [nvarchar](35) NULL,
[Address_emergency] [nvarchar](35) NULL,
[Tel_No_Emergency] [nvarchar](35) NULL,
[Grade_yearlevel] [text] NULL,
[Section] [text] NULL,
[Place_of_Birth] [nvarchar](35) NULL,
[General_ave] [int] NULL,
[Father_Name] [nvarchar](35) NULL,
[Occupation] [nvarchar](35) NULL,
[EmployedAt] [nvarchar](35) NULL,
[Address_employment] [nvarchar](35) NULL,
[Mother_Name] [nvarchar](35) NULL,
[Occupation_Mother] [nvarchar](35) NULL,
[EmployedAt_Mother] [nvarchar](35) NULL,
[AddEmp_Mother] [nvarchar](35) NULL,
[SchoolLastAttended] [nvarchar](35) NULL,
[Address_School] [nvarchar](35) NULL,
[School_Year] [int] NULL,
[Citizenship] [nvarchar](35) NULL,
[Father_EducAttain] [nvarchar](35) NULL,
[Mother_EducAttain] [nvarchar](35) NULL,
[Father_TelNum_Work] [nvarchar](35) NULL,
[Mother_TelNum_Work] [nvarchar](35) NULL,
[Requirements_NSO] [nvarchar](50) NULL,
[Requirements_GoodMoral] [nvarchar](50) NULL,
[Requirements_ReportCard] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

@adam_k, what i mean is the first data on the student_info table

for a start you should be storing the student_no in the accounting_system table not the the student name.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.