How to use Joins in SQL statements in .aspx page?

Hi can someone help me?

I'm using Access database and Microsoft VS2005 (VB)

I need to take data from 2 different tables in 1 database. But i can't seems to get it right!

I have:
- dbWarehouse.mdb
- tblReadDetails and tblStock

I need to use "Barcode" from tblReadDetails, compare it with "Barcode" in tblStock then retrieve all the column in tblStock and put it in a webpage.aspx list box.

I tried the following code...

cn.ConnectionString = strConn
cn.Open()

cmd =New OleDbCommand("SELECT tblStock.Barcode, tblStock.Item, tblStock.Dept, tblReadDetails.Barcode FROM tblStock JOIN tblReadDetils WHERE tblReadDetails.Barcode = tblStock.Barcode", cn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

While dr.Read()
lstBarcode.Items.Add(dr2.Item("tblStock.Barcode"))
lstItem.Items.Add(dr2.Item("tblStock.Item"))
lstDept.Items.Add(dr2.Item("tblStock.Dept"))
EndWhile

but it prompt me the following error...

OleDbException was unhandled by user code
Syntax error in FROM clause

What's wrong with this code?

Regards and thanks!

Hi rinaxanir,

Try this:

SELECT tblStock.Barcode, tblStock.Item, tblStock.DeptFROM tblStockINNERJOIN tblReadDetailsON tblStock.Barcode = tblReadDetails.Barcode

Hope this helps,

Adam

|||

Thanks Adam!

It's perfect!

Rina

How to use DisplayName in report

I use the new feature of VS2005 to develp LocalReport.
I have set the DisplayName of a LocalReport like this:
ReportViewer1.LocalReport.DisplayName = sDisplayName;
It will change the top node of the Document Map.
How can I bring the DisplayName into the report, for example I want to show
the DisplayName in the footer of the report too.Create a report parameter that is hidden and assign your variable to this
parameter as the default value. Now you can use this information anywhere.

How to use CTE

I use CTE of VS2005:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
'?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
>> After defining the CTE, you should use it in another SQL statement.
>> Try something like this:
>> WITH AA (County,Town ) AS
>> (
>> SELECT County,Town
>> FROM Zip where zip='413'
>> )
>> SELECT * FROM aa
>> Razvan
>