Question Need Help constructing SQL Query.

[Troy]

I need help contructing an SQL query that allows me to select only Packages from my db that have the same ContactID as whats in Contact.

I've been using QueryBuilder and I can get this,

SELECT Package.PackageID, Package.ContactID, Package.Lender, Package.Manufacturer, Package.FlooringCompany, Package.DateOrder, Package.DateManufacture,
Package.DateEscrow, Package.SerialNumber, Package.ModelNumber, Package.HUDNumber, Package.UnitSizeA, Package.UnitSizeB, Package.New,
Package.Used, Package.SalesPerson, Package.Permits, Package.Comments, Package.FlooringAmount, Package.LoanAmount, Package.InvoiceAmount,
Package.SalesTaxAmount, Package.FreightAmount, Package.HCDAmount, Package.DealerPackAmount, Package.EscrowAmount, Package.AccessoriesAmount,
Package.FoundationAmount, Package.SepticAmount, Package.AirConditioningAmount, Package.SchoolTaxAmount, Package.SalesCommissionAmount,
Package.MiscAmount, Package.ProfitAmount, Contact.ContactID AS Expr1
FROM Package LEFT OUTER JOIN
Contact ON Package.ContactID = Contact.ContactID

but it gives everything not just those with the same ContactID in Contact.

As you can see it gives Package 15 from ContactID 8 as well. I only want those with ContactID the same to be listed.

Any help would be appreciated.

 

[Troy]

Any help here would be appreciated. I'm sure there is a way to create an SQL Query that will display only the Information in Packages with the same ContactID as in Contact.

Anyone?

 

[MattP]

Your query looks like it's only adding ContactID from the Contact table. I would hazard that if you scrolled all the way right on your results that you'd see another ContactID column with values 1, 8, & 1.

This doesn't seem to be your intened result as there's no reason to do a join if you're just bringing back a ContactID you already have. If you want to bring back some contact information scrap the Contact.ContactID column from your select and add something like ...Contact.FirstName1, Contact.LastName1 so your join actually brings back information you can use.

If you just want to see rows with a ContactID of 1 you'd add a WHERE clause to your query and then pass in your value when you execute it.

SELECT <columns>
FROM Package
WHERE ContactID = @ContactID

 

[Troy]

ok now I'm having trouble still trying to implement this.

Here's my code:

Dim Identifier As String
Dim id As String
Identifier = Me.ActiveMdiChild.Text
id = Me.ActiveMdiChild.Tag

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & My.Settings.dbPath & "; Persist Security Info=False"
Dim cn As New OleDbConnection(strConnection)
Dim SC As New OleDb.OleDbCommand("SELECT * FROM Package", cn)
'add condition
SC.CommandText &= " WHERE ContactID = ?ContactID"
'add parameter
SC.Parameters.AddWithValue("?ContactID", SqlDbType.NText)
'set parameter value
SC.Parameters("?ContactID").Value = id
daActiveMDIChildPackages.SelectCommand = SC

cn.Open()

' Fill the DataTable
daActiveMDIChildPackages.Fill(dsActiveMDIChildPackages, "Package")
dtActiveMDIChildPackages = dsActiveMDIChildPackages.Tables.Item("Package")

cn.Close()

What am I doing wrong? I'm trying to set the compare value to id which is the value I have stored in me.ActiveMDIChild.tag.

if the ContactID is 1 for instance I want my SQL to read WHERE ContactID = 1.

 

[Troy]

Anyone have any ideas?