bind 2 tables in a datalist

life_s Ng · December 18th, 2003, 07:04 AM

hi,
everytime i read about relational database, and i really cant figure out how to display a datalist for the columns in two table... lets say, in a datalist, few columns is displayed from table1, and few columns is displayed from table2...
coz table1 is for storing particular of the custormer, and another table is for storing the time and all the stuffs that the custormer bought, and i wish to display the stuffs the custormer bought, and his/her paticular from another table in the same datalist...

i tried to get some documentation, but have no luck with it, or... i need to merge the 2 tables...?
can any1 provide some documentation or idea?

when i use the sql statement like this:
dim SQL1 as String="Select * From VisVisit, Vispart Where ICNum=@Name"
or change it to this
dim SQL1 as String="SELECT VisVisit.ICNum AS icnum1, Vispart.ICNum AS icnum2, VisVisit.Name AS name1, Vispart.Name AS name2, Address, ContactNum From VisVisit, Vispart Where ICNum=@Name"

it both show me this error:

Ambiguous column name 'ICNum'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Ambiguous column name 'ICNum'.

Source Error:

Line 33: dim DlVisitorDataSet as DataSet = new DataSet()
Line 34: objCmd.Fill(DlVisitorDataSet, "VisitorList")

is my sql statement have problem...? its working well without the where clause, but its not what i need...i need the where clause... can any1 pls help with this? thanks in advance

Best Regards
life's Ng

alyeng2000 · December 18th, 2003, 08:25 AM

first: you are not refearing the column ICNum to any table

Quote:

quote:when i use the sql statement like this:

Quote:

dim SQL1 as String="Select * From VisVisit, Vispart Where ICNum=@Name"
or change it to this
dim SQL1 as String="SELECT VisVisit.ICNum AS icnum1, Vispart.ICNum AS icnum2, VisVisit.Name AS name1, Vispart.Name AS name2, Address, ContactNum From VisVisit, Vispart Where ICNum=@Name"

second:
you are doing cross join (is that what you need)

third:
did you add the parameter @name to a Command and set it's value

Ahmed Ali
Software Developer

planoie · December 18th, 2003, 10:20 AM

How familiar are you with the basics of SQL queries? It's going to be quite difficult to figure out the complexities of displaying relational data in an ASPX without the understanding of how to get the data you want.

Peter
------------------------------------------------------
Work smarter, not harder.

life_s Ng · December 19th, 2003, 01:05 AM

hi, my problem has been solved by using this sql statement
dim SQL1 as String="Select * FROM VisVisit INNER JOIN Vispart ON VisVisit.ICNum = Vispart.ICNum WHERE(VisVisit.ICNum = @Name)"
the @Name is actually add to a tbIC, just becoz last time using name for it, sorry to made it blurr Mr Ahmed Ali.
this is the 1st time i use Inner Join,
usually i onli learned those basic Sql queries like select, update, insert, where clause, and, like, order by...
can Sir pls provide some good ducumentation about others than the sql above to let this dumb learn more bout...? Thanks in advance. i've not enough knowleged bout others than that, and this is the 1st time i saw this kind of statement :( Now then i relize sql statement can be so complex (powerful~)... :)

Best Regards
life's Ng

alyeng2000 · December 19th, 2003, 04:37 AM

as what you say

Quote:

quote:usually i onli learned those basic Sql queries like select, update, insert, where clause, and, like, order by...

i advice you first
to ask about Sql Querying in Database->Sql Language Section

Ahmed Ali
Software Developer