Parameter for tablename?

Hi, I have datareader useing this objectcommand and sqlstring. When I hard code the name of the table it works with the param "@dotnet.itags.org.puser" but when I use as below I get an error that says I must declare the variable @dotnet.itags.org.ptable.
Can you not use a parameter for the table name?

Dim strSQL As String = "SELECT email From @dotnet.itags.org.ptable Where fullname = @dotnet.itags.org.pUser "
Dim objCMD As New SqlCommand(strSQL, objConn)
With objCMD.Parameters
.Add(New SqlParameter("@dotnet.itags.org.ptable", strTable))
.Add(New SqlParameter("@dotnet.itags.org.puser", strUser))
End With

Thanks for the help
Raifyou cannot use a parameter for tablename like that. either use inline sql ( which is not recommended) or move your code to a stored proc and use EXEC to build up a query and execute it.

hth
Well, alright. that's unfortunate. will the stored procedure accept a parameter for the table name. The idea is that I want different pages to call this page and the table that is queried be based on the sending page. Possible?
thanks
Raif
yeah you just send in the paremeter. the sp wont know if its a table name or col name. inside the sp, you build your sql stmt with the table name. check out for EXEC command in BOL for more info.

hth
I am wondering about this as well and I am not to sure I understood your explainations. Let's say would it be possible to write a generic SP to do a Select * From @.paramTable ? (where the @.paramTable would be a parameter indicating the table in question)

Also what is BOL by the way?

I tried the following sp but I get an error: "ADO error: Must be declare the variable '@.myTable'."

CREATE PROCEDURE dbo.GetAllGeneric
@.myTable nvarchar(50)
AS
Select * From @.myTable

Thanks
BOL = Books On Line. free download from MS.

you would need to do something like

EXEC ( 'Select * from ' + @.tablename )

hth
I am sorry I never used such SP so I am a bit confuse as how to write the EXEC() in a SP. Would it be something like this?

CREATE PROCEDURE dbo.GetAllGeneric
(
@.strTable varchar(50),
@.intParentID int
)

AS
Declare @.strTables varchar(50),
Declare @.intParentID int

EXEC ('SELECT * FROM' + @.strTable + 'WHERE ParentID=@.intParentID')
That is pretty much it, with the exception of you need to use sp_executesql since you are using an input parameter. You need something like this:

DECLARE @.SQL nvarchar(1000)
SELECT @.SQL = N'SELECT * FROM' + @.strTable + N'WHERE ParentID=@.intParentID'
EXEC sp_executesql @.SQL, N'@.intParentID INPUT', @.intParentID INPUT

Terri
Hi Dear,

I will not work.

Do this:

StrSql = "select * from " & tblname & "where firstname=" & text1.text

Do this it will work defenately
This approach is definitely not recommended! NEVER use a user's input directly in a SQL statement. This sets you up forSQL injection problems. Use parameters instead:

StrSql = "select * from " & tblname & "where firstname= @.FirstName"
StrSql.Parameters.Add(New SqlParameter("@.FirstName", SqlDbType.VarChar, 20)).Value = text1.text

See Chapter 14 of the MSDN Patterns and PractcesImproving Web Application Security: Threats and Countermeasures for more guidelines on building more secure applications.

Terri