read from multiple tables with reader in vb.net 2005

[manared]

I have a program that had an Access backend, but I'm in the process of converting everything to SQL. I think I'm having problems with my sql statements and then trying to read from them. I have multiple tables, but when I read it, I can't list the table and field in it. How else can I do this? My code as of now is shown below.

Dim MySQL As String = "SELECT QuoteMaster.*, QuoteCheckList.*, QuoteForecast.*, Salesperson.*, Customer.*, LookupQuoteIncludeType.*, LookupQuoteStatus.*, LookupSalesRep.* FROM (((((QuoteCheckList INNER JOIN (QuoteMaster INNER JOIN QuoteForecast ON QuoteMaster.ID = QuoteForecast.ID) ON (QuoteCheckList.ID = QuoteMaster.ID) AND (QuoteCheckList.ID = QuoteForecast.ID)) INNER JOIN Salesperson ON QuoteMaster.SalesPersonID = Salesperson.ID) INNER JOIN Customer ON QuoteMaster.BuyerID = Customer.ID) INNER JOIN LookupQuoteIncludeType ON QuoteForecast.IncludeType = LookupQuoteIncludeType.IncludeID) INNER JOIN LookupQuoteStatus ON QuoteForecast.Status = LookupQuoteStatus.StatusID) INNER JOIN LookupSalesRep ON QuoteMaster.SalesRepID1 = LookupSalesRep.ID WHERE (((QuoteMaster.ID)= " & passId & "));"
 Dim cmd As New SqlCommand(MySQL, myconn)
            Dim reader As SqlDataReader
 myConn.Open()
reader = cmd.ExecuteReader
            While reader.Read()
                lblQuoteID.Text = reader("QuoteMaster.ID") & String.Empty
                lblCompany.Text = reader("CustomerName") & String.Empty
 End While

lblCompany.text finds an entry and does not error out, but the first statement does: QuoteMaster.ID. Does anyone have any ideas? Do I have to read each table seperately or create a datatable or something?

 

[TechGnome]

USing * to pull all fields from a table, while syntatically correct, isn't always a good idea. This is one of those times.... You can't use QuoteMaster.ID, as you have noticed. But you cannot use just ID either, since ID is presumably in all of the tables.

What you may need to do is, ask if you *reallY *Need* all fields form all tables. If not, then only select the fields which you actually naeed, and when there's a dup field name (like ID).... alias them.

SELECT QuoteMaster.ID AS QMID, etc....

-tg

 

[manared]

Thanks, it seems to be working so far....

I actually have another program that is very similar to this, but I'm getting several errors when trying to do the Select statement. Here it is:

SELECT DISTINCTROW LookupQuoteIncludeType.IncludeTypeDesc As IncludeType, IIf(QuoteMaster.QuoteDate>#1/1/1980#,QuoteMaster.QuoteDate,QuoteMaster.CreateDate) AS CreateDate, QuoteMaster.QuoteNumber AS QuoteNo, Customer.CustomerName AS CompanyName, QuoteForecast.Description, QuoteCheckList.SellingAmount AS Amount, Salesperson.FirstName & " " & Salesperson.LastName AS Salesperson, LookupSalesRep.Name AS SalesRepresent, LookupQuoteStatus.StatusDesc AS Status, QuoteForecast.RelationshipRating AS Rel, QuoteForecast.BiddingRating AS Bid, QuoteForecast.CommitmentRating AS Com, QuoteForecast.TotalRating AS Total, QuoteForecast.Comments AS CommentsSalesForecast, QuoteMaster.OrderDate, QuoteMaster.RevisedOrderDate AS RevisedDate, QuoteCheckList.EquipmentDeliveryWeeks AS Delivery, QuoteCheckList.SellingMargin AS PerMar, ROUND((QuoteCheckList.SellingMargin/100)*QuoteCheckList.SellingAmount,2) AS DolMar, QuoteMaster.JobSite As JobSite, QuoteMaster.EngHours AS EngHr, QuoteMaster.DraftHours AS DraftHr, QuoteMaster.ShopHours AS ShopHr FROM (LookupQuoteIncludeType INNER JOIN (LookupQuoteStatus INNER JOIN QuoteForecast ON LookupQuoteStatus.StatusID = QuoteForecast.Status) ON LookupQuoteIncludeType.IncludeID = QuoteForecast.IncludeType) INNER JOIN (LookupSalesRep INNER JOIN (Salesperson INNER JOIN ((Customer INNER JOIN QuoteMaster ON Customer.ID = QuoteMaster.BuyerID) INNER JOIN QuoteCheckList ON QuoteMaster.ID = QuoteCheckList.ID) ON Salesperson.ID = QuoteMaster.SalesPersonID) ON LookupSalesRep.ID = QuoteMaster.SalesRepID1) ON QuoteForecast.ID = QuoteMaster.ID WHERE (((QuoteForecast.IncludeType)<>5)) ORDER BY QuoteForecast.IncludeType, QuoteForecast.TotalRating DESC

I'm getting these two errors right now:
Incorrect syntax near '.'.
Incorrect syntax near 'QuoteForecast'.

Thanks for the help!

 

[cjard]

Mang, if youre going to create a database where all tables are uniquely related through an ID column, then just make one table.. youre splitting your database up for no good reason

You have:
PersonNameTable
ID,
Name

PersonAgeTable
ID,
Age

PersonAddressTable
ID,
Address


The rest of the world would just do:
PersonTable
ID,
Name
Age,
Address

 

[manared]

yes, that would definitely be easier.. but I did import this from an old Access database that is still being used. It would take me Forever to figure out how all of the tables are related and bring them all together. I wish i only had a few tables, but actually overall there are around 30 tables or so that I need to work with. If I can figure out how to deal with the tables being seperate, I'm going to do that.

 

[TechGnome]

Do you know if there are Foreign Keys setup?
If from SQL Server side, you create a new Database Diagram, add all of the tables, it will create a "map" of sorts of all the tables and how they relate to each other. But it's only as good as the data. In other words if there are no FKey relationships setup already, then it'll just simply show all the tables.

-tg