How to embed control parameter?

I am trying to embed a control parameter in a sql statement for a datagrid.

The following section generates a runtime error:

... WHERE (WEEK = (SELECT DATEPART(wk, CONVERT(varchar, @Label, 101) AS Expr1)) ...

The control parameter is:
<SelectParameters>
<asp:ControlParameter Name="Label"  ControlID="Label2" PropertyName="Text" />
</SelectParameters>

The following statement DOES work correctly:
SELECT CONVERT (varchar, @Label, 101) as date  ...

I am using VS2008/SQL Server
How do I fix this?



pillmillAsked:
Who is Participating?
 
AsishRajCommented:
Sorry i missed this part
The value of Label is set in Javascript:

OK, after you select Date then you use the submit button and do the insert right. if you doing this then you dont have to worry where it is set. Answer other post questions as well so that you can get the solution asap.
0
 
tigin44Commented:
you should change it to

WHERE WEEK = DATEPART(wk, CONVERT(datetime, CONVERT(varchar, @Label, 101)))

0
 
pillmillAuthor Commented:
Thanks.

WHERE WEEK = DATEPART(wk, CONVERT(datetime, CONVERT(varchar, @Label, 101)))

generates the runtime error:
[SqlException (0x80131904): Conversion failed when converting datetime from character string.]

How do I fix this?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tigin44Commented:
what is the format of the string that you provided in the @Label variable.  it should be like 'mm.dd.yyyy' or 'yyyy.mm.dd' .  Check and correct that string...
0
 
AsishRajCommented:
Try This
DATEPART(wk, CONVERT(VARCHAR(11),@Label,106) AS Date)

CONVERT(VARCHAR(11),@Label,106) - will give date as - 24 Dec 2009
0
 
pillmillAuthor Commented:
This generates the same runtime error:

[SqlException (0x80131904): Conversion failed when converting datetime from character string.]

The value of Label is set in Javascript:

Label.Text = Calendar2.SelectedDates[0].ToLongDateString();

How do I fix this?
0
 
tigin44Commented:
Label.Text = Calendar2.SelectedDates[0].ToLongDateString();

what is the format of the string or siply the value of Label.Text after the assignment.

can you provide a sample
0
 
AsishRajCommented:
AS tigin44 has said please give us what is displayed by:
  1. Label.Text  
  2. Calendar2.SelectedDates[0].ToLongDateString();
we need the exact output so that we can give you the correct solution.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.