Syntax for convert in ado.net

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a dataadapter that I pass a sqlcommand to, which has paramters.

I have the following sqlcommand on my page:

SELECT *
FROM tbl_Changes
WHERE (CAST(@column1 AS nvarchar(20)) LIKE @Criteria1)

and have the following code to call this sqlcommand in my webpage:

cmdSelectNone.Parameters("@Column1").Value = "Change_Status"
cmdSelectNone.Parameters("@Criteria1").Value = "%" & txtSearchCriteria1.Text
& "%"
SqlDataAdapter2.SelectCommand = cmdSelectNone
SqlDataAdapter2.Fill(DsChanges2)
dgSearch.DataSource = DsChanges2
dgSearch.DataBind()

Every time I call the above code it returns no records, yet if I comment out
the '@column1' parameter in the code and hard code the column name in the
sqlcommand it works just fine.

Could anyone please tell me what's wrong with my
cmdSelectnone.Parameters("@Column1").value="Change_Status" line please??

Thanks in advance,

Jason
 
Hi Jason,

Shouldn't you compare the actual column1 and not the value you pass by
parameter?
WHERE (CAST(column1 AS nvarchar(20)) LIKE @Criteria1)
 
@ paramater types are treated as literals. If you look at what's being sent
over, after the query, the value of each of the parameters is sent. So
it'll say that @Column1 = 'Change-Status'. So the equivalent becomes "WHERE
(Case('Change_Status' As nvarchar(20)))", which is not what you want.

Given that, I don't believe you can use parameters for column names, table
names, or anything else that isn't a literal (string, numeric, etc).

So, you will have to just concatenate your query manually to have table and
column names be dynamic at run time. You should stil use parameters for the
@Criteria1 parameter, since that is a good way to go.
 
Well, I'm actually passing the column name into the @Column1 parameter via a
dropdownlist, so I think that part is correct..
@Column1 is just a parameter that I'm using to populate the sqlcommand with
the 'column name'.




Miha Markic said:
Hi Jason,

Shouldn't you compare the actual column1 and not the value you pass by
parameter?
WHERE (CAST(column1 AS nvarchar(20)) LIKE @Criteria1)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Jason said:
I have a dataadapter that I pass a sqlcommand to, which has paramters.

I have the following sqlcommand on my page:

SELECT *
FROM tbl_Changes
WHERE (CAST(@column1 AS nvarchar(20)) LIKE @Criteria1)

and have the following code to call this sqlcommand in my webpage:

cmdSelectNone.Parameters("@Column1").Value = "Change_Status"
cmdSelectNone.Parameters("@Criteria1").Value = "%" & txtSearchCriteria1.Text
& "%"
SqlDataAdapter2.SelectCommand = cmdSelectNone
SqlDataAdapter2.Fill(DsChanges2)
dgSearch.DataSource = DsChanges2
dgSearch.DataBind()

Every time I call the above code it returns no records, yet if I comment out
the '@column1' parameter in the code and hard code the column name in the
sqlcommand it works just fine.

Could anyone please tell me what's wrong with my
cmdSelectnone.Parameters("@Column1").value="Change_Status" line please??

Thanks in advance,

Jason
Click to expand...
Click to expand...
 
Thank you very much Marina,

I'm not sure why that didn't come to me, but what a nice
solution...sometimes I guess all of the gui objects are a little too
tempting when a simple non-gui solution is staring you in the face.

Just for documentation sake, all I did was dim a sqlcommand as my select
statement while passing in parameters to it directly from the dropdownlist
and text boxes to make up the query, then filled my dataset with it from
there.

Dim cmdSelectNone As New SqlCommand("SELECT * FROM tbl_Changes where " &
ddlSearchCriteria1.SelectedValue & " like '%" & txtSearchCriteria1.Text &
"%'", SqlConnection2)

Thanks again,

Jason.
 
Back
Top