Cant Remember how to pass int value to datasource

I have know this error but have completely forgotten how to take care of it. How do I pass the integer values to the SQL statement?

ERROR: Syntax error converting the nvarchar value 'Label' to a column of data type int.

WHERE (LinkInfo.L_State = @.State) AND (LinkInfo.CG_ID > @.CatIDLow) AND (LinkInfo.CG_ID > @.CatIDHigh)

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text" />
<asp:ControlParameter ControlID="Label2" DefaultValue="0" Name="CatIDLow" PropertyName="Text" />
<asp:ControlParameter ControlID="Label3" DefaultValue="1899" Name="CatIDHigh" PropertyName="Text" />
</SelectParameters>

CODE BEHIND

public partial class TopandBottomSelect : System.Web.UI.Page
{
int CG_IDlower;
int CG_IDupper;

protected void Page_Load(object sender, EventArgs e)
{
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
CG_IDlower = 0;
CG_IDupper = 1900;
Label1.Text = Convert.ToString(CG_IDlower);
Label2.Text = Convert.ToString(CG_IDupper);
}
else
{
CG_IDlower = 1899;
CG_IDupper = 3000;
Label1.Text = Convert.ToString(CG_IDlower);
Label2.Text = Convert.ToString(CG_IDupper);
}
GridView1.DataBind();
}
}

Thank you

Add the datatype to the Parameter declarations:

<asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text"Type="Int32" />

|||

The state value is not the problem, just the CatID High and low. I have tried the below with no success.

NEW EDITED CODE Below - same error

ERROR: Input string was not in a correct format.

WHERE (LinkInfo.L_State = @.State) AND (LinkInfo.CG_ID > @.CatIDLOW) AND (LinkInfo.CG_ID < @.CatIDHIGH)">

<asp:ControlParameter ControlID="Label2" Name="CatIDLow" PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="Label3" Name="CatIDHigh" PropertyName="Text" Type="Int32" />

with this Code Behind:

int CG_IDlower;
int CG_IDupper;
int CatIDLOW;
int CatIDHIGH;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (RadioButtonList1.SelectedIndex == 0)
{
CG_IDlower = 0;
CG_IDupper = 1900;
Label2.Text = Convert.ToString(CG_IDlower);
Label3.Text = Convert.ToString(CG_IDupper);

CatIDLOW = Convert.ToInt32("Label2.Text");
CatIDHIGH = Convert.ToInt32("Label3.Text");
}
else
{
CG_IDlower = 1899;
CG_IDupper = 3000;
Label2.Text = Convert.ToString(CG_IDlower);
Label3.Text = Convert.ToString(CG_IDupper);
CatIDLOW = Convert.ToInt32("Label2.Text");
CatIDHIGH = Convert.ToInt32("Label3.Text");
}
GridView1.DataBind();
}

|||

I am not sure but I think this may have something to do with the object type coming out of the radiobuttonlist control. don't know where to go from where I am.

thank you,

Cant order by text values?

Hi,

I've got the following code that pulls out forum topics that have been added to on a particular day:

SELECT
forum_posts.post_date
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

I tried including the body of the post in this:

SELECT
forum_posts.post_date
, forum_posts.body
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
,forum_posts.body
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

But was told that "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Well, the body field is text datatype alright, but how do I therefore include it in the results?The GROUP BY is what it is complaining about. Why is that there?

Originally posted by Spudhead
Hi,

I've got the following code that pulls out forum topics that have been added to on a particular day:

SELECT
forum_posts.post_date
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

I tried including the body of the post in this:

SELECT
forum_posts.post_date
, forum_posts.body
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
,forum_posts.body
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

But was told that "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Well, the body field is text datatype alright, but how do I therefore include it in the results?|||Because I'm a muppet.

Thanks :)|||Make this...

SELECT
forum_threads.id
,forum_threads.subject
,forum_posts.post_date
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
HAVING day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id, forum_posts.post_date

...the option ASC is default;
...use the command Having.

Cant import an Access table due to null values.

Question: Why would I not be able to import an Access 97 table in
which some records have null values in fields that allow null values?

Wouldn't the table's design be imported first, bringing the columns'
"allow nulls" attribute with it?

I'm dealing with both text and numeric columns. Not all columns
containing nulls cause an error.

Thanks,

Bob C."Bob C." <bcanavan@.bmghomes.com> wrote in message
news:2e5623fb.0402201301.4098061b@.posting.google.c om...
> Question: Why would I not be able to import an Access 97 table in
> which some records have null values in fields that allow null values?
> Wouldn't the table's design be imported first, bringing the columns'
> "allow nulls" attribute with it?
> I'm dealing with both text and numeric columns. Not all columns
> containing nulls cause an error.
> Thanks,
> Bob C.

It's hard to say (at least for me) what the issue may be without more
information. In particular, how are you importing the table, and what is the
exact error message you see? Also, which version of SQL Server are you using
as the target for the import? If you're using the upsizing wizard, you may
want to post this in an Access newsgroup as well, as it's an Access tool,
not an MSSQL one.

Simon|||Thanks, Simon.

I'm using DTS to import to SQL2k. Sorry for not posting that
information.

It turns out that only two of the columns in my Access table were
affected, and they both disallowed nulls. This attribute was one I
set after the Access database had been in production awhile, and the
db manager asked that nulls for those columns be blocked. I think
what happened was that nulls already in those columns were
grandfathered(strange that I wasn't warned when I set the "allow
nulls" attribute to false), and the new table structure in SQL2k
correctly carried over the attribute - tripping over the grandfathered
nulls when I tried to import them.

Thanks for your help.

Bob C.

can't get multiple values using reportItems

On a report I have I have a hidden table which I am trying to get values from for a total. On a seperate table, I have an if statement that checks if a product # matches one on this table. This works fine if there is only one record in the hidden table. If there are more than one records, the ReportItems call only see's the last textbox (the last record) of the hidden table. Here's the total expression I use

=IIf( sum(Fields!PickupDeliveryDetails_New.Value)+ sum(Fields!PickupDeliveryDetails_Extra.Value) <= 0, 0 ,((sum(Fields!PickupDeliveryDetails_New.Value) + sum(Fields!PickupDeliveryDetails_Extra.Value)) /

(ReportItems("textbox36").Value

)

)

* (Fields!Actuals_TotalPTypeCharge.Value)

+ (IIf(ReportItems("table6"."TableColumn1").Value = Fields!Product_BPCSNumber.Value, ReportItems("textbox50").Value ,

IIf(First(ReportItems("table6"."TableColumn1").Value) = Fields!Product_BPCSNumber.Value, First(ReportItems("textbox50").Value) , 0 ) ) )

)

Any help on this would be greatly appreciated.

Morgan

That piece of code was not working. Heres the working code.

=IIf( sum(Fields!PickupDeliveryDetails_New.Value)+ sum(Fields!PickupDeliveryDetails_Extra.Value) <= 0, 0 ,((sum(Fields!PickupDeliveryDetails_New.Value) + sum(Fields!PickupDeliveryDetails_Extra.Value)) /

(ReportItems("textbox36").Value

)

)

* (Fields!Actuals_TotalPTypeCharge.Value)

+ (IIf(ReportItems("textbox27").Value = Fields!Product_BPCSNumber.Value, ReportItems("textbox50").Value , 0 ) ))

|||First of all, never use neon green on the post its VERY HARD to read. Is it possible to do a group by Product# in the hidden table and then run an aggregate sum on the values you want added. That way when you reference the textbox in the hidden table from the other table it would be the sum of all values for that same product #|||

That wouldn't work because I need seperate values for each product type total. There is a group by product # but there are multiple product #s.

I have solved my problem by getting the results from the hidden table in the second table (i got the values in the dataset by using left outer joins).

I would still like to know if there is a way to do what I was trying to do if it ever came up again and it couldn't be solved in the query.