• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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?



0
pillmill
Asked:
pillmill
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now