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.

Multi Server/Database Nightmare

We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.

First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.

Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.

Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.

I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.

Any other reasonable options out there. I just can't imagine my setup is all that unique.

Have you explored the possibility of using Synonyms?

(Even dyanmically creating them when necessary.)

|||Whats a Synonym?