Multi table designations in sql string

I'm putting together a resultset in an sql string
(it will be used in asp.net) where I'm drawing from a few different
tables, and I do have a relation with the tblDeal and tblSalesrep ID :
''
strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.salesrep_id WHERE d.Orderdate = '" &
request.querystring("boarddate") & "';"
'''
I am not getting an error, but no response when running this sql string
(i did response.write the "boarddate" variable successfully, so thats
not the problem)
'
.NetSports> strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
> s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
> d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
> FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.salesrep_id WHERE d.Orderdate = '" &
> request.querystring("boarddate") & "';"
If I read your post correctly, you are asking SQL Server to figure out your
boarddate as a parameter to your query.
If I am correct in your interpretation, perhaps your SQL query [VSS script]
(which should be a stored procedure) should look like this:
<sql>
IF EXISTS (
SELECT *
FROM SysObjects
WHERE Type = 'P'
AND Name = 'WhateverYouCallIt '
)
DROP PROCEDURE dbo.WhateverYouCallIt
GO
CREATE PROCEDURE dbo.WhateverYouCallIt (
@.boardDate INT
) AS
SELECT d.salesrep_id,s.fname,s.lname, s.boardtotal_note,
s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross, d.SplitRep_ID,
d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,
b.Amount_Attempted
FROM tblBankTrans b,
tblDeal d
INNER JOIN tblSalesrep s
ON s.ID = d.salesrep_id
WHERE d.Orderdate = @.boardDate
GO
GRANT EXEC ON dbo.WhateverYouCallIt TO PUBLIC
GO
</sql>
Then you call it like this:
<vb.NET>
' Assumes that your connection string is in your Web.config or
App.config
' and that you have an Imports System.Configuration item in your code
class
Dim cnstr As String = ConnectionSettings.AppSetting("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim da As New SqlDataAdapter("dbo.WhateverYouCallIt", cn)
Dim dt As New DataTable()
With da.SelectCommand
.CommandType = CommandType.StoredProcedure
' Assumes that you have a VB variable called boardDate that is the
' same as the stored procedure's (above) @.boardDate parameter:
.Parameters.Add("@.boardDate", boardDate)
End With
da.Fill(dt)
' etc...
</vb.NET>
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||actually, I would probably like to see if the multi-table inner joins
are the correct way to make this sql string work, seeming that I am
also using the tblBankTrans table, but with no joins or relationships.