passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.