How to pass variable in SQL query

having a problem

[hal12b]

Hi -
I am setting up an application where once somebody logs in they are first able to see certain pages, then based on the Viewer Level (1-3), they will see different content.

So this code would display the "About us" page and show content for a user with Level 2.

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID_PAGE = '1' and VIEWER_LEVEL = '2' "

BUT I don't want the 2 hard coded in there. I want it more like

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID_PAGE = '1' and VIEWER_LEVEL = Session("brokerlevel") "

I am getting an error, just not sure of the correct way to have the session in the query statement.

Any ideas?
Thanks

[marcel]

I think this will work:

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID_PAGE = '1' and VIEWER_LEVEL = '" & Session("brokerlevel") & "'"

But you should look into using parameterized queries.

[hal12b]

Thanks. That did work. I am diving into ASP.net, so I may need to think outside of the Classic ASP mentality. If you have any decent web sites I can visit as resources (besides here obviously ahah), please let me know.

[marcel]

Actually, what you where doing in a previous thread is fine, ie.

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID = @ID AND VIEWER_LEVEL = @VIEWER_LEVEL "
cmsAdapt.SelectCommand = New SqlCommand(cmsQuery, cmsCt)
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@ID", strid))
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@VIEWER_LEVEL", strvl))

Just change it to this:

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID_PAGE = @ID_PAGE AND VIEWER_LEVEL = @VIEWER_LEVEL "
cmsAdapt.SelectCommand = New SqlCommand(cmsQuery, cmsCt)
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@ID_PAGE", strid))
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@VIEWER_LEVEL", Session("brokerlevel")))

* Edit - And to actually answer your question, I find www.asp.net and Four guys from Rolla have some great stuff.

[hal12b]

Thanks a lot. I appreciate the help to date.