Incorrect sql statement - not reading variable

In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table....

How do I write this statement to pick up the variable above???

Thanks in advance for your help!

Clare

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"Dim connStringAsString ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da =

New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

ForEach tempRowAs Data.DataRowIn ds.Tables(0).Rows

tempRow.Item(

"imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

EndSub

hi,

try this

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.projectID"

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

yourcommand.Pramaters.Add(p)

|||

Thanks for your quick response but this is not working. I do not have a sql command declared, I am using strSQL ( a string), and da (a sqldataadaptor).

Can you help??

Thanks very much

|||

hi cabby

use following

Dim da As Data.SqlClient.SqlDataAdapterDim strSQL As StringDim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.ProjectID" dim sqlcmdas new sqlcommand(strsql, connstring)sqlcmd.parameter.addwithvalue("ProjectID", request.querystring("projectid"));Dim connString As String ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(sqlcmd) da.Fill(ds)in case your project idis char/varchar field use followingas parametervaluesqlcmd.parameter.addwithvalue("ProjectID","'" + request.querystring("projectid") +"'" ) 'see its included in single quotes

HTH

satish.

|||Hiya,|||

Hiya,

Thanks for the response. Sorry if I am asking simple questions but I have tried your code and am getting the following error:

Dim

sqlcmdAsNew sqlcommand(strSQL, connstring) - - - type 'sqlcommand' is not defined... so I change this to

Dim

sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString) -- and then I get connString - value of type string cannot be converted to 'System.Data.SqlClient.SqlConnection' and I also get an error on the next line

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid")) -- parameter is not a member of 'System.Data.SqlClient.SqlConnection'

My code now is:

Dim

dsAsNew Data.DataSetDim daAs Data.SqlClient.SqlDataAdapterDim strSQLAsStringDim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString)

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid"))Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

Thanks for the help!

Clare

|||first of all make sure you've imported system.data.sqlclient.

DimdsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"

Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL,new sqlconnection(connString) )

sqlcmd.parameters.addwithvalue(

"ProjectID", ProjectID) //make sure variable projectid is not null

Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

hope it will help now.

thanks,

satish.

|||

Thanks again, really appreciate the help.

Nearly there now I think...

I am getting to the line:

da.Fill(ds)

and getting the error:

Invalid column name'@.ProjectID'.

Any ideas?

Thanks

Clare

|||

hi,

try this

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

sqlcmd.Pramaters.Add(p)

|||

errr sorry appologies for silly mistake of me change following line of my code

sqlcmd.parameters.addwithvalue("ProjectID", ProjectID) //make sure variable projectid is not null

to

sqlcmd.parameters.addwithvalue("@.ProjectID", ProjectID) //make sure variable projectid is not null

thanks,

satish.

|||

Hi,

I am still getting the same error. Do I need to declare in my sub function header that it is taking in a parameter? And if so how do I write this on page load?

Thanks

Clare

|||

After all this I can just use:

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] =" & Request.QueryString("ProjectID")

Thanks for all the help

|||

hi

try this, it works fine

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =Request.QueryString("ProjectID")

p.SqlDbType =Data .SqlDbType .String;

sqlcmd.Pramaters.Add(p)