checkboxlist and SQL search using AND/OR on selected checkboxlist items.

I have a checkbox list like the one above. For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else. Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?Thank you for any help

You can create a procedure that accepts a parameter ('AND' or 'OR), which perform a query based on the parameter:

create proc sp_testQuery @.logicOP varchar(3)='AND',@.id int,@.name varchar(20)
as
if @.logicOP='AND'
select * from t1 whereid=@.id and name=isnull(@.name,name)
else if @.logicOP='OR'
select * from t1 whereid=@.id orname=@.name
else raiserror('You must choose a logic operator ''AND'' or ''OR''',16,1)
go
sp_testQuery 'AND',1,null

Then what your code need to do is just call the stored procedure with providing all required parameters that come from your website.

Checkboxes in Reporting Services?

Hi all

Is it possible to have a checkbox as a parameter.. So a user can check a box to include a column or uncheck to hide a column ?

Thanks
Neil

Hello Neil,

I don't believe there is a way to get a checkbox in the parameters section. Would True/False radio buttons work for you? If so, you can use the Boolean type for your parameter.

Hope this helps.

Jarret

|||Hi Jarret,

Yes radio buttons could work... Although I have just made a parameter boolean but it still rendered as a drop down..

Is there anything specifically I need to do to make the parameter a radio button ?

Thanks.
Neil
|||

What version of Reporting Services are you using?

You should be able to set your parameter to Boolean without any available values and it should show radio buttons. Make sure you don't have Multi-value selected either.

Jarret

|||There we go!

Thanks man!!! I was setting an available value.
Thanks so much!!

Neil.
|||

No problem, glad I could help.

Jarret

Checkboxes in Rdl Table

I am looking fo any suggestion how to put a checkbox in a cell in a table on a rdl report. I used the wizard to make a report that has drilldown in it. I want to be able to have a check box in the cell on every row. When I check this box I want a paramater to run a sub report that builds pdfs of information that corresponsed to the data in that rows thats are checked. Any suggestions. I didnt find any tools that add checkboxes to my toolbox on the report builder. I am using vs2005.net C# asp.net

Right Click on the

|||

I am looking to do the exact same thing. Have you had any progress finding the answer?

|||

No I figured out how to do this in a regular ASP page but not a rdl report I have seen it done thought in an example but can find the example on the web. I will let you know if I finf the example.

CheckBoxes and SQL Server

Hello All,

I am tring to find a better why to do this. I store data in the SQL Server about whether a checkbox was checked or not and the users can come back to the page and alter it as needed. This is the code for Inserting the data. It uses Stored Procedures and I will cut out all unessary code.

myCommand.Parameter.Add("@.CheckBox", myConnection);

That Records it and I use this to retrieve it.

while (myDataReader.Read())
 {
 string strChecked = myDataReader["Test"];
 if ( strChecked == "1" )
 {
cbxPhysicalExam.Checked = true;
 }
 else
 {
cbxPhysicalExam.Checked = false;
 }
 }

This work but I would much rather do this...

while (myDataReader.Read())
 {
 cbxPhysicalExam.Checked = myDataReader["Test"].ToString();
 }

Does anyone know how to do this?

Much Thanks,
RogerDepends on the type you're using in the database. Typical, for a boolean value (that's the case for checkboxes) you use the SQL Server type 'bit'. This returns a 0 or 1 value which can be casted to a boolean value.

Hope this helps?|||OK thats what I use... but how do I code it on the return... It will not compile like this...

cbxExample.Checked = myDataReader["Example"];

I get this on attempt to compile...

Cannot implicitly convert type 'object' to 'bool'

OK... maybe I need to Explicitly convert. I do not know how convert 1 - true and 0 - false|||Yes I see, maybe I was not clear enough in my answer. The bit type in the SQL Server is far more efficient to work with when doing queries etc. How to convert 1 to true and 0 to false? You may use something like this:

cbxExample.Checked = myDataReader["Example"].ToString() == "1";

which will do the job.|||I tried that and it didn't work... Here is all the code

SQL SERVER Table - TEST

Columns - ID int - Test bit

Store Proc For Select
CREATE PROCEDURE dbo.sp_Test
AS
Select * From Test
GO

Store Proc For Insert
CREATE PROCEDURE dbo.sp_TestIns (
@.Testbit )
AS
Insert Into Test (
Test
)
Values (
@.Test
)
GO

Code Behind Page

void SaveClicked()
{
SqlCommand myCommand = new SqlCommand("sp_TestIns", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
 myCommand.Parameters.Add("@.Test", cbxText.Checked);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
 myCommand.Connection.Close();{
}
void Fill()
{
SqlCommand myCommand = new SqlCommand("sp_Test", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlDataReader myDataReader;
myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read())
{
cbxPhysicalExam.Checked = myDataReader["Test"].ToString() == "1";
}
myDataReader.Close();
myConnection.Close();
}

THis doesn't work... what am I doing wrong?|||I figured it out... Thank you for your help...

cbxTest.Checked = (bool)myDataReader["Test"];

Thanks Again...

Roger

Checkbox with the same name truncates at a space

When I use SQL to populate a checkbox and give the checkboxes all the same name, when I retrieve it a name containing a space is truncated at the space. How can I get the whole name?

<%while NOT rs.EOF%>
<INPUT TYPE=CHECKBOX NAME="code" value=<%=rs("code")%> <%If request.Form("code") = rs("code") then%> CHECKED <%End If%>><%=rs("code")%><br>
<%rs.MoveNext
wend
rs.Close%>

strStatus_code = Request.Form("code")

gives me Active, Inactive, Phase

what I want is gives me Active, Inactive, Phase Out

:confused:This is not a problem with SQL, but with the way you are writing out the values.

Place quotes around the value when you write them out for display, so that when you do a view source from the browser you should see.

"Active"
"Inactive"
"Phase Out"

<%while NOT rs.EOF%>
<INPUT TYPE=CHECKBOX NAME="code" value=" <%=rs("code")%>"
<%If request.Form("code") = rs("code") then%> CHECKED <%End If%>>" <%=rs("code")%>" <br>
<%rs.MoveNext
wend
rs.Close%>|||Worked perfectly!! Thank you.:) :) Suzy

Checkbox in reports

I have a report on which i want to add a checkbox to the extreme left of
every row and allow the user the ability to select it, hit a button named
"Export" and move the contents of only that row to MS Excel.
Does Reporting services 2000 support this? Any suggestions on how this can
be done?
Thanks...uh; i'd start with 'not trying to use excel for reporting'
-Aaron
Gaurav wrote:
> I have a report on which i want to add a checkbox to the extreme left of
> every row and allow the user the ability to select it, hit a button named
> "Export" and move the contents of only that row to MS Excel.
> Does Reporting services 2000 support this? Any suggestions on how this can
> be done?
> Thanks...

CheckBox in Crystal Reports 8.0

Hi,
Can we add checkboxes in the crystal reports?
Thanks In advanceHi,
I m showing some boolean values on my report but for this i m writing the formulas that if field = 1 then "Yes" but i want to display checkbox if instead of the values yes / no.
Can we add checkboxes in the crystal reports?

Thanks In advance|||Why do you want to use?

Checkbox data-bound to a bit SQL field cant handle NULL values

I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.

Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?

Thanks!Hi,

You may try to use the ISNULL() function in your SELECT statement,
or you can use aHelper Function to do so.

Colt

checkbox and listbox for report parameters

How can I use checkbox and listbox (with multiple selection) for report
paramters? Do I need to go into the coding to do this, or I can do this from
the designing tool? Currently I can only use either a textbox (for single
value) or a pulldown box (for list of pre-defined values). I want to have a
listbox where the user can select multiple values and a checkbox to indicate
a Yes/No filter. Thanks.There have been lots of posts about this topic. Check the archives, which
you can find here...
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs?hl=en&lr=
... and search for multi-value parameters.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:9EA971CF-8606-43E2-A09F-223008E2076A@.microsoft.com...
> How can I use checkbox and listbox (with multiple selection) for report
> paramters? Do I need to go into the coding to do this, or I can do this
> from
> the designing tool? Currently I can only use either a textbox (for single
> value) or a pulldown box (for list of pre-defined values). I want to have
> a
> listbox where the user can select multiple values and a checkbox to
> indicate
> a Yes/No filter. Thanks.|||Thanks for the info.
"Jeff A. Stucker" wrote:
> There have been lots of posts about this topic. Check the archives, which
> you can find here...
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs?hl=en&lr=
> ... and search for multi-value parameters.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:9EA971CF-8606-43E2-A09F-223008E2076A@.microsoft.com...
> > How can I use checkbox and listbox (with multiple selection) for report
> > paramters? Do I need to go into the coding to do this, or I can do this
> > from
> > the designing tool? Currently I can only use either a textbox (for single
> > value) or a pulldown box (for list of pre-defined values). I want to have
> > a
> > listbox where the user can select multiple values and a checkbox to
> > indicate
> > a Yes/No filter. Thanks.
>
>

checkbox - using in update statement as bit field

hi
I have a bit field in sql server represented by a checkbox ...
I am updating the database in code ( ie not using formview generated update .. )
the line that is falling over is
.Parameters.Add("@.archive", SqlDbType.Bit).Value = txtarchive.Checked
it falls over saying
failed to convert string parameter to boolean
thevalue of txtarchive.checked is either true or false - how do i convertthis to 1 or 0 or something that sql is happy with ...
thanks

you can use IIF. Give this a try:

IIF(txtarchive.Checked = TRUE, 1,0)

|||

NDinakar's solution should work, but I'm confused as to the problem since I have pretty much the same thing you did, and it works fine.

The only difference is how the parameter was set up, I not only told it that it was a bit, but also a size of 1. (Actually, the sql command/parameters were set up by using a designer, and I copied the code)

Checkbox

Hi,

In the reports tollbox there is no Checkbox control,In my report i want to add the checkboc control,How to add checkboc control to the toolbox.

Please help me.

Thanks in advance

I do not believe that you can do that. Are you wanting to add a check box to actual body of a report or are you wanting to use a check box to select a parameter?|||

Hi,

Thank you very much for your reply.I want to display available columns to the users,so he can select the columns he want,we have to display those selected columns in the report body.thatswhy i want to display avalable columns to user.Without checkbox how to achieve this.

Thanks inadvance

|||

Maybe use multivalue parameter with list of available report columns ? Every value have embedded checkbox and you can add default value "show all" or something like that.

Maciej

|||

Hi,

I tried in that way.I put one multivaled parameter that display all the available columns,Suppose i have the multivaled parameter named "columns" with values:Date,Empid,State.When user select date and Empid,Columns parameter returns the string in this format:'date,empid',then how we set the visibility property for the table columns based on this string.With this iam struggling.

Please help me

Thanks in advance

|||

Try this:

in your column visibility expression enter this code: "=iif(InStr(Parameters!Columns.Value,"show all") > 0 or InStr(Parameters!Columns.Value,"<this column name>") >0, true, false)"

I assumed that InStr() returns "0" or less when there is no match but better check it yourself

Maciej

Checkbox

Hi,

Even this question is not related to this forum but may be some one help me.

I have a master detail form. I want to delete record but only those record that user click with checkbox. There is no field like boolean. How can I make a scenerio about adding an unbound checkbox in detail form and how would i link with rows in detail sub form.

any help will be highly appreciated.

Regards,I'm guessing you are using MS Access? You should probably post your message in the MS access forum.

Use the bit data type in SQL Server to represent boolean values.|||i'd rub it with bacon.
everything works better with bacon.

mmmmmmmm