How can I retrieve a function return value from an executed string?

Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.The function is not completely qualified (DB name, owner)
Try:

declare @.Receptor as sql_variant
set @.Receptor = database_name.dbo.Function(@.in_param_value)

A user defined function can be used like any other pre-defined function

Originally posted by EMoscosoCam
Hello!

Suppose that I have the name of an UDF (@.Function) which returns an scalar value and accepts just one parameter. How can I execute and assign the return value to a variable?

I tried the following, but it did not work:

declare @.Receptor as sql_variant

set @.Receptor=execute('select ' + @.Function + '(10)')

Thanks a lot in advance.sql

How can I protect my parameters?

Hello.
I have a report with parameter called "parm1", that gets a value of
"true" or "false" depanding on another parameter.
When the report is runnig the parm1 value is "false".
How can I protect this parameter from a change by the user?
I mean - the user can run the report and then add to the url "¶m1
= true".
Can I do anything against that?
I tried marking it as "internal" and I thought that now it can get his
value only from inside the report but it didn't worked.
Any ideas?
Thanks.Can you use session variables instead of a querystring?
On Oct 25, 12:54 pm, nicknack <roezo...@.gmail.com> wrote:
> Hello.
> I have a report with parameter called "parm1", that gets a value of
> "true" or "false" depanding on another parameter.
> When the report is runnig the parm1 value is "false".
> How can I protect this parameter from a change by the user?
> I mean - the user can run the report and then add to the url "¶m1
> = true".
> Can I do anything against that?
> I tried marking it as "internal" and I thought that now it can get his
> value only from inside the report but it didn't worked.
> Any ideas?
> Thanks.|||What do you mean by "session variables" ?
I didn't know there are "session variables" in reporting server.
Can you explain please?|||He is integrating his own website with RS. There is no session variables in
RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nicknack" <roezohar@.gmail.com> wrote in message
news:1193337080.315943.85980@.z24g2000prh.googlegroups.com...
> What do you mean by "session variables" ?
> I didn't know there are "session variables" in reporting server.
> Can you explain please?
>|||If the point of this parameter is to use it as a query parameter then you
don't even need it as a report parameter.
Go to your dataset that uses this parameter, click on the ..., parameters
tab. This shows the mapping between query parameters and report parameters.
Change the mapping for this parameter to expression and put in the
expression that evaluates appropriately to true or false depending on the
other parameter. Then in the layout tab menu Reports->Report Parameters
delete the existing report parameter that is now not needed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nicknack" <roezohar@.gmail.com> wrote in message
news:1193331267.699414.296020@.k79g2000hse.googlegroups.com...
> Hello.
> I have a report with parameter called "parm1", that gets a value of
> "true" or "false" depanding on another parameter.
> When the report is runnig the parm1 value is "false".
> How can I protect this parameter from a change by the user?
> I mean - the user can run the report and then add to the url "¶m1
> = true".
> Can I do anything against that?
> I tried marking it as "internal" and I thought that now it can get his
> value only from inside the report but it didn't worked.
> Any ideas?
> Thanks.
>|||Hi Bruce,
Thanks for the idea.
Thats the best solution I have received so far.
To bad microsoft didn't thought about a simple way to protect the
parameter instead of beating around the bushes.
I'll try your solution soon.
Thanks again.|||"nicknack" <roezohar@.gmail.com> wrote in message
news:1193398666.577502.193620@.y42g2000hsy.googlegroups.com...
> Hi Bruce,
> Thanks for the idea.
> Thats the best solution I have received so far.
> To bad microsoft didn't thought about a simple way to protect the
> parameter instead of beating around the bushes.
Well, if you don't want the parameters (or even report URLs) to be visible
at all, why don't you use ReportViewer control INSIDE your page instead of
calling the whole page from SSRS?
Regards,
Dmitry|||Hi Dmitry,
Thanks for your replay but its not a solution.
I still can't understand how can it be that there isn't any way to
protected my parameters.
The report viewer control has his own problems and I really don't see
a reason to use a new ASPX page with another .net control instead of
just opening a new window with a report just because of this this bug
(and in my opinion it is a bug).
Amm, Sorry for taking it all on you but I'm really frustrated from
this issue.
On 27 , 15:45, "Dmitry Duginov" <d...@.nospam.nospam> wrote:
> "nicknack" <roezo...@.gmail.com> wrote in message
> news:1193398666.577502.193620@.y42g2000hsy.googlegroups.com...
> > Hi Bruce,
> > Thanks for the idea.
> > Thats the best solution I have received so far.
> > To bad microsoft didn't thought about a simple way to protect the
> > parameter instead of beating around the bushes.
> Well, if you don't want the parameters (or even report URLs) to be visible
> at all, why don't you use ReportViewer control INSIDE your page instead of
> calling the whole page from SSRS?
> Regards,
> Dmitry|||"nicknack" <roezohar@.gmail.com> wrote in message
news:1193518754.655684.319300@.o3g2000hsb.googlegroups.com...
> Hi Dmitry,
> Thanks for your replay but its not a solution.
> I still can't understand how can it be that there isn't any way to
> protected my parameters.
> The report viewer control has his own problems and I really don't see
> a reason to use a new ASPX page with another .net control instead of
> just opening a new window with a report just because of this this bug
> (and in my opinion it is a bug).
> Amm, Sorry for taking it all on you but I'm really frustrated from
> this issue.
Well, the way you are showing your report right now is called "URL
integration" if memory serves me right. That means that your report is 100%
relying on the URL string it is getting from the application, user or
someone/somebody else.
In the browser (surprise?), any URL is visible. No only your user can change
true to false, they can substitute different report name if you don't take
care about security.
It is generally a bad idea to expose any parameters that can be modified by
user, presenting security breach.
You said that the parameter in question (true/false) somehow depends on
another one, right? But your user can tweak that parameter as well, you
know...
Regards,
Dmitry|||On 29 , 16:00, "Dmitry Duginov" <d...@.nospam.nospam> wrote:
> "nicknack" <roezo...@.gmail.com> wrote in message
> news:1193518754.655684.319300@.o3g2000hsb.googlegroups.com...
> > Hi Dmitry,
> > Thanks for your replay but its not a solution.
> > I still can't understand how can it be that there isn't any way to
> > protected my parameters.
> > The report viewer control has his own problems and I really don't see
> > a reason to use a new ASPX page with another .net control instead of
> > just opening a new window with a report just because of this this bug
> > (and in my opinion it is a bug).
> > Amm, Sorry for taking it all on you but I'm really frustrated from
> > this issue.
> Well, the way you are showing your report right now is called "URL
> integration" if memory serves me right. That means that your report is 100%
> relying on the URL string it is getting from the application, user or
> someone/somebody else.
> In the browser (surprise?), any URL is visible. No only your user can change
> true to false, they can substitute different report name if you don't take
> care about security.
> It is generally a bad idea to expose any parameters that can be modified by
> user, presenting security breach.
> You said that the parameter in question (true/false) somehow depends on
> another one, right? But your user can tweak that parameter as well, you
> know...
> Regards,
> Dmitry
Hi again,
Thanks for your replay.
I see the logic behind your answer.
I will try to find another way to use the parameter.
Maybe by try to use a hidden textbox or something like that.
Thanks again.
Roy.|||Hi Bruce,
Just now I tried your solution again and understand what you meant.
It work fine and it is exactly what I wanted.
Thanks a lot,
Roy.
On 25 , 21:00, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> If the point of this parameter is to use it as a query parameter then you
> don't even need it as a report parameter.
> Go to your dataset that uses this parameter, click on the ..., parameters
> tab. This shows the mapping between query parameters and report parameters.
> Change the mapping for this parameter to expression and put in the
> expression that evaluates appropriately to true or false depending on the
> other parameter. Then in the layout tab menu Reports->Report Parameters
> delete the existing report parameter that is now not needed.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "nicknack" <roezo...@.gmail.com> wrote in message
> news:1193331267.699414.296020@.k79g2000hse.googlegroups.com...
> > Hello.
> > I have a report with parameter called "parm1", that gets a value of
> > "true" or "false" depanding on another parameter.
> > When the report is runnig the parm1 value is "false".
> > How can I protect this parameter from a change by the user?
> > I mean - the user can run the report and then add to the url "¶m1
> > = true".
> > Can I do anything against that?
> > I tried marking it as "internal" and I thought that now it can get his
> > value only from inside the report but it didn't worked.
> > Any ideas?
> > Thanks.|||Glad it worked for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nicknack" <roezohar@.gmail.com> wrote in message
news:1194248091.562106.284270@.k79g2000hse.googlegroups.com...
> Hi Bruce,
> Just now I tried your solution again and understand what you meant.
> It work fine and it is exactly what I wanted.
> Thanks a lot,
> Roy.
> On 25 , 21:00, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> If the point of this parameter is to use it as a query parameter then you
>> don't even need it as a report parameter.
>> Go to your dataset that uses this parameter, click on the ..., parameters
>> tab. This shows the mapping between query parameters and report
>> parameters.
>> Change the mapping for this parameter to expression and put in the
>> expression that evaluates appropriately to true or false depending on the
>> other parameter. Then in the layout tab menu Reports->Report Parameters
>> delete the existing report parameter that is now not needed.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "nicknack" <roezo...@.gmail.com> wrote in message
>> news:1193331267.699414.296020@.k79g2000hse.googlegroups.com...
>> > Hello.
>> > I have a report with parameter called "parm1", that gets a value of
>> > "true" or "false" depanding on another parameter.
>> > When the report is runnig the parm1 value is "false".
>> > How can I protect this parameter from a change by the user?
>> > I mean - the user can run the report and then add to the url "¶m1
>> > = true".
>> > Can I do anything against that?
>> > I tried marking it as "internal" and I thought that now it can get his
>> > value only from inside the report but it didn't worked.
>> > Any ideas?
>> > Thanks.
>

How can I print a multi-value parameter

I have a multi-value parameter and I need to print its value in report.
But if I put in a text box =Parameters!Mine.Label when rendering I obtain #Error
How can I do it?
Thanks in advanceUse the .NET Framework function Join(). You can specify the delimiter as well.

How can I place a value from my dataset into the page header?

My report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.

Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.

Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?

many thanks

The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.

In future releases we are looking at adding field references to the other page sections.

|||

Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".

I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.

Have I misunderstood your response? thanks

|||

you need to give reference like these for the header textbox

=ReportItems!txtReportHeader.Value

where txtReportHeader is the text box in the body, which contains the value of the dataset field

|||

Thanks rakam, it worked like a charm....

I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.

How can I pass NULL to a parameter

How can I pass NULL to a parameter, if now entry is made in the textbox?

Dim KeywordParamAsNew SqlParameter("@.Keyword",Me.KeyWordText.Text)

MyCommand.Parameters.Add(KeywordParam)

Hi

You could try something like this:

If (Me.KeyWordText.Text="")Then cmd.Parameters("@.Keyword").Value = DBNull.ValueElse cmd.Parameters("@.Keyword").Value =Me.KeyWordText.Text

End If

How can i pass GUID as sqlreport input paramater?

Hi,
How can I pass GUID as sqlreport Input parameter? I get the following error -

"Failed to convert parameter value from a String to a Guid. (System.Data)"

I also tried passing guid within the curly braces but of now use. Please help!
Ashu

Try creating a ReportParameter object and then storing the GUID, as a string, inside this parameter.

Example:

Microsoft.Reporting.Winforms.ReportParameter tempParameter = new Microsoft.Reporting.Winforms.ReportParameter();

tempParameter = new Microsoft.Reporting.Winforms.ReportParameter("ID", GUID.ToString());

Finally, pass this ReportParameter object to the report.

Example:

reportViewer.LocalReport.SetParameters(tempParameter);

How can I pass a parameter value from VB.Net to Crystal Report?

Hi! I need your help...asap...

How can I pass a parameter value from VB.Net to Crystal Report 11?

I created a report from Crystal Report 11 which has a parameter field for querying the records and I created a form from VB.Net which allows the user to enter a value which will be used for the report but I don't how to pass a parameter value from VB.Net to Crystal Report.Try This...

dim Report as New ReportDocument

Report.Load("YourReport.rpt") 'This is your Crystal Report File

Report.SetParameterValues("PARAM1","Christine") 'PARAM1 should have exactly the same spelling as what can be found in your parameter list in your "*.rpt" file.

CRViewer.Reportsource = Report

How can I pass a parameter to use in an IN

I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.

Dim MySqlParamSelected As New SqlParameter("@.Selected", SqlDbType.Int)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("intSelected")

WHERE tblSelected.Selected_ID IN (@.Selected)

It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.

Here is the error:

Msg 119, Level 15, State 1, Line 4
Must pass parameter number 7 and subsequent parameters as'@.name = value'. After the form'@.name = value' has been used, all subsequent parameters must be passed in the form'@.name = value'.

Does anyone know how I can correctly pass multiple numbers to use in my IN?

Have a look atthis article which discusses this very issue.

|||

Oh, that is a very clever little trick!

|||

I have tried using fn_split function from the artical.

I now get the following error:

Failed to convert parameter value from a String to a Int32.

Does anyone have any Ideas about this?

|||

Did you run the debugger to find out which parameter it was complaining about? What value you were passing in? Whether it should be an Int32 parameter instead of a string parameter?

|||

Do you mean placing break points? I'm not sure how to check for all of the things you listed.

My code_ID data type is int, my parameter is and int, but I'm quessing my session variable is a string. If I'm passing to the parameter '78,79' I don't see how it will ever be and int. This thing has me very confused.

I appreciate any help with this I can get!!!

|||

Your new procedure should take a varchar parameter (string) of id values i.e. '1,2,3,4,5' and that function will split it up. You should'nt be sending an int parameter.

|||

I must be missing something, if I change my parameter to a varchar I get this: Error converting data type varchar to int.

Dim MySqlParamSelectedAs New SqlParameter("@.Selected", SqlDbType.VarChar)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("Selected")

I checked the session varaiable and it is '78,79'

Here is what is in my where

tblTemplates.Template_IDIN(SELECTValueFROM fn_Split(@.Selected,','))

Template_ID is an Int datatype.

What could I be doing wrong?

|||

Ok, I got it. It was my tunnel vision, I was so hung up on looking at the WHERE I didn't see that my @.Selected parameter we declared as an Int.

|||

Just FYI: if you have a few values it might work out okay but if you have hundreds of values it could be a little slow. the IN is internally converted to OR and SQL Server will look for each value in the IN. If you do a JOIN it might be a little faster.

|||

ndinakar,

I must ask, what do you mean by doing a Join?

|||

More like this:

SELECT *FROM Table1 TJOIN (SELECT *FROM dbo.fnGetSomething (@.String,',')) FON F.somecol = T.someothercolWHERE T.something = @.x

rather than this:

SELECT *FROM Table1 TWHERE T.something = @.xAND T.someothercolIN (Select colfrom dbo.fnGetSomething (@.String,',') )

How can I Pass a DataSet to a DPE

Hi,
I have a DataSet which I want to pass to the DPE,
how i pass the DataSet as a parameter to the DPE
Please Help!
Thanks
Girish KumarGirish,
You can serialize the dataset to XML and pass it a parameter. As a side
note, my custom DPE does exactly this.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> Hi,
> I have a DataSet which I want to pass to the DPE,
> how i pass the DataSet as a parameter to the DPE
> Please Help!
> Thanks
> Girish Kumar|||Hi Teo
I Have a DataSet with me which I want to pass directly to the DPE without
any XML serialization I ve seen ur sample application where u pass the xml
file as report parameter in the browser,
But my requirement is not that I want to pass the Dataset to DPE
programmatically at the run time how can i do that
Please Help!!
Regards,
Girish
"Teo Lachev [MVP]" wrote:
> Girish,
> You can serialize the dataset to XML and pass it a parameter. As a side
> note, my custom DPE does exactly this.
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > Hi,
> >
> > I have a DataSet which I want to pass to the DPE,
> > how i pass the DataSet as a parameter to the DPE
> >
> > Please Help!
> >
> > Thanks
> > Girish Kumar
>
>|||Girish,
My extension supports reporting off datasets which are persisted to files.
In this case, instead of passing the serialized copy, you pass the file path
as a report parameter.
Did this address your question?
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> Hi Teo
> I Have a DataSet with me which I want to pass directly to the DPE
without
> any XML serialization I ve seen ur sample application where u pass the xml
> file as report parameter in the browser,
> But my requirement is not that I want to pass the Dataset to DPE
> programmatically at the run time how can i do that
> Please Help!!
> Regards,
> Girish
> "Teo Lachev [MVP]" wrote:
> > Girish,
> >
> > You can serialize the dataset to XML and pass it a parameter. As a side
> > note, my custom DPE does exactly this.
> >
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > Hi,
> > >
> > > I have a DataSet which I want to pass to the DPE,
> > > how i pass the DataSet as a parameter to the DPE
> > >
> > > Please Help!
> > >
> > > Thanks
> > > Girish Kumar
> >
> >
> >|||Hi Teo,
I need a custom DPE which can take input a Dataset I have tried using your
sample but it needs me to again create an xml and xsd file update the files
in initialize component method.
But i in turn have a Dataset got from an external application using
webservices i want to pass that dataset to this dpe and run my report.
I think you got my scenario.
Please Help!!!
Regards
Girish (INDIA)
"Teo Lachev [MVP]" wrote:
> Girish,
> My extension supports reporting off datasets which are persisted to files.
> In this case, instead of passing the serialized copy, you pass the file path
> as a report parameter.
> Did this address your question?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > Hi Teo
> >
> > I Have a DataSet with me which I want to pass directly to the DPE
> without
> > any XML serialization I ve seen ur sample application where u pass the xml
> > file as report parameter in the browser,
> >
> > But my requirement is not that I want to pass the Dataset to DPE
> > programmatically at the run time how can i do that
> >
> > Please Help!!
> >
> > Regards,
> > Girish
> >
> > "Teo Lachev [MVP]" wrote:
> >
> > > Girish,
> > >
> > > You can serialize the dataset to XML and pass it a parameter. As a side
> > > note, my custom DPE does exactly this.
> > >
> > >
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a DataSet which I want to pass to the DPE,
> > > > how i pass the DataSet as a parameter to the DPE
> > > >
> > > > Please Help!
> > > >
> > > > Thanks
> > > > Girish Kumar
> > >
> > >
> > >
>
>|||Hi Teo,
I need a custom DPE which can take input a Dataset I have tried using your
sample but it needs me to again create an xml and xsd file update the files
in initialize component method.
But i in turn have a Dataset got from an external application using
webservices i want to pass that dataset to this dpe and run my report there
is no point of storing a file on my webserver even temporarly
I think you got my scenario.
Please Help!!!
Regards
Girish (INDIA)
"Teo Lachev [MVP]" wrote:
> Girish,
> My extension supports reporting off datasets which are persisted to files.
> In this case, instead of passing the serialized copy, you pass the file path
> as a report parameter.
> Did this address your question?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > Hi Teo
> >
> > I Have a DataSet with me which I want to pass directly to the DPE
> without
> > any XML serialization I ve seen ur sample application where u pass the xml
> > file as report parameter in the browser,
> >
> > But my requirement is not that I want to pass the Dataset to DPE
> > programmatically at the run time how can i do that
> >
> > Please Help!!
> >
> > Regards,
> > Girish
> >
> > "Teo Lachev [MVP]" wrote:
> >
> > > Girish,
> > >
> > > You can serialize the dataset to XML and pass it a parameter. As a side
> > > note, my custom DPE does exactly this.
> > >
> > >
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a DataSet which I want to pass to the DPE,
> > > > how i pass the DataSet as a parameter to the DPE
> > > >
> > > > Please Help!
> > > >
> > > > Thanks
> > > > Girish Kumar
> > >
> > >
> > >
>
>|||No, I don't. You are saying that you need to pass the dataset as a
parameter, then you are saying that you don't want to pass it as a
parameter... I am confused. Can you explain in steps what are you trying to
do?
My extension supports both serialized datasets and datasets saved as files
and, no, you don't have to create a xsd file. A schema file just makes it
easier for the report author to lay out the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:DE5CE16C-2A19-4C63-B2D3-8823FEF71F1C@.microsoft.com...
> Hi Teo,
> I need a custom DPE which can take input a Dataset I have tried using
your
> sample but it needs me to again create an xml and xsd file update the
files
> in initialize component method.
> But i in turn have a Dataset got from an external application using
> webservices i want to pass that dataset to this dpe and run my report
there
> is no point of storing a file on my webserver even temporarly
> I think you got my scenario.
> Please Help!!!
> Regards
> Girish (INDIA)
>
> "Teo Lachev [MVP]" wrote:
> > Girish,
> >
> > My extension supports reporting off datasets which are persisted to
files.
> > In this case, instead of passing the serialized copy, you pass the file
path
> > as a report parameter.
> >
> > Did this address your question?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > > Hi Teo
> > >
> > > I Have a DataSet with me which I want to pass directly to the DPE
> > without
> > > any XML serialization I ve seen ur sample application where u pass the
xml
> > > file as report parameter in the browser,
> > >
> > > But my requirement is not that I want to pass the Dataset to DPE
> > > programmatically at the run time how can i do that
> > >
> > > Please Help!!
> > >
> > > Regards,
> > > Girish
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > > > Girish,
> > > >
> > > > You can serialize the dataset to XML and pass it a parameter. As a
side
> > > > note, my custom DPE does exactly this.
> > > >
> > > >
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > > Home page and blog: http://www.prologika.com/
> > > > ---
> > > >
> > > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in
message
> > > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a DataSet which I want to pass to the DPE,
> > > > > how i pass the DataSet as a parameter to the DPE
> > > > >
> > > > > Please Help!
> > > > >
> > > > > Thanks
> > > > > Girish Kumar
> > > >
> > > >
> > > >
> >
> >
> >

How can I have a variable number of parameter values in a dataset?

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:

For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next

Can I do this is a dataset method? How?

If I can't, what are my options?

Diane

make use of temp table or table variable

declare @.word table

(

word varchar(100)

)

select *

from sometable t inner join @.word w

on t.Description like '%' + w.word + '%'

|||

Hi,

From the question you mentioned, do you want to make a query with multiple input variables? If so, I just want to know how to split them in your solution?

Actually, the user input the keywords in the textbox and they use space on the keyboard to split each word. And when we recieve the request from the user,

we may use split method to separate each words into array.

string s = "keyword1 keyword2 keyword3";
string[] myar = s.Split(' ');

for (int i = 0; i < myar.Length; i++) {

strSQL = strSQL & "Description LIKE '%" & myar(i) & "%' OR "
}

After the sql statement is created, you may put it into the SqlDataAdapter and fill into a DataSet. So you can get the query result which matches the multiple keywords.

SqlDataAdapter myadpt = new SqlDataAdapter(strSQL, myconn);
DataSet myds = new DataSet();
myadpt.Fill(myds);

If this does not answer your question, please feel free to reply. Thank you!

|||

Hi,

You can search more efficiently using 'Contains' in SQL server. For this you need to enable Full-Text search in sql server for a field.

Contains(FieldName, '+''''+@.GroupKeyWord+''''+')'

@.GroupKeyWord is the keyword you can directly pass, i.e. the user input string. See SQL Server help for more details. It is really interesting. If you don't want to use this feature then you can go for split. But split needs a delimiter string, i.e. a single space or a particular character. The resultant array can be looped from lower bound to upper bound to create Sql statement.

The advantage of using contains is its flexibility. User can create Boolean search himself and you just have to pass the value user entered into the textbox directly to the sql statement.

Don't forget to mark this as Answer if this post helps you

|||

KH, you have me totally confused <grin>!

Michael, I'm not having a problem spliting the variables. Say I have a table with fields name, company, address, description. I know how to use a parameter in a strongly typed dataset, say to search for Name=@.name in a method that expects a name parameter. What I need is a method that returns all records where the description field contains one or more of the words the user entered. I can build the select string, but I don't know how to implement this in a dataset method.

Hamlin, this would be optimal. Unfortunately, full text search isn't enabled, and I can't get it enabled. I asked my host, and they won't permit it.

I'm wondering if I should maybe use an SqlDataSource instead of the dataset?

Diane

|||

Hi Mainship,

Sorry for my misunderstanding. Now I know that your problem is focused on how to implement the select string in a dataset method cause you don't know the number of parameter values.

Actually you can use DataSet.TableName.Select(SelectString) Method instead of inputing the select string in the TableAdapter configuration wizard. For example:

northwindDataSet.Products.Select(string);

For more details about DataTable.Select Method ,pls check:http://msdn2.microsoft.com/en-us/library/system.data.datatable.select(VS.80).aspx

If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thank you!

Diane

How can I get the Texbox Action's to diplay a report in a new browser.

Hello,
I would like to click on a report's textbox and it opens up another
report in a new browser? I have the URL and it parameter. I placed them in
the textbox's ACTION attribute but the new report displays in the same
browser.
Any suggestions?
Thanks,
SteveHere is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steve kwon" <steven,kwon@.lmco.com> wrote in message
news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to click on a report's textbox and it opens up another
> report in a new browser? I have the URL and it parameter. I placed them
> in the textbox's ACTION attribute but the new report displays in the same
> browser.
> Any suggestions?
> Thanks,
> Steve
>|||Thanks, I went that route too. But I will try it again since you were
successful.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed them
>> in the textbox's ACTION attribute but the new report displays in the same
>> browser.
>> Any suggestions?
>> Thanks,
>> Steve
>|||You have to have either SP1 or SP2 installed (it was introduced with SP1).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steve kwon" <steven,kwon@.lmco.com> wrote in message
news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Thanks, I went that route too. But I will try it again since you were
> successful.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "steve kwon" <steven,kwon@.lmco.com> wrote in message
>> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed
>> them in the textbox's ACTION attribute but the new report displays in
>> the same browser.
>> Any suggestions?
>> Thanks,
>> Steve
>>
>|||Thanks Bruce. It worked.
<Action>
<Hyperlink>="javascript:void(window.open('http://www.google.com',
'_blank'))"</Hyperlink>
</Action>
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23te2YkukFHA.3144@.TK2MSFTNGP12.phx.gbl...
> You have to have either SP1 or SP2 installed (it was introduced with SP1).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Thanks, I went that route too. But I will try it again since you were
>> successful.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "steve kwon" <steven,kwon@.lmco.com> wrote in message
>> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I would like to click on a report's textbox and it opens up another
>> report in a new browser? I have the URL and it parameter. I placed
>> them in the textbox's ACTION attribute but the new report displays in
>> the same browser.
>> Any suggestions?
>> Thanks,
>> Steve
>>
>>
>|||I have been trying this and it is not working for me. I actually have it
embedded in an Iif statement, but even plain navigation JUMP TO URL
expression it is not working. Do I need to add a reference to something else
to get the javascript working?
Here's my code from the expression:
=Iif (Fields!STATUS.Value <>
"6","javascript:void(window.open('"http://<servername/<subdir>/<filename>?FN"
& code.displaylink(Fields!SigFullPath.Value) &
"&PD=','_blank'))","mailto:scconops@.mydomain.com?Subject=Restore%20Request%20For%20Archived%20Document&Body=Please%20restore%20the%20following%20document.%0A%0ACustomer%20Name:%20"&
Fields!DOCNAME.Value & "%0A%0AAcct#:%20"& Fields!ACCOUNTNO.value &
"%0A%0AObligor#:%20" & Fields!OBLIGORNO.value & "%0A%0ASystem:%20"&
Fields!LIBRARY.Value & "%0A%0ASSN:%20"& fields!SSNO1.Value &"&CC="&
(Right(User!UserID,5)) & "@.mydomain.com")
"steve kwon" wrote:
> Thanks Bruce. It worked.
> <Action>
> <Hyperlink>="javascript:void(window.open('http://www.google.com',
> '_blank'))"</Hyperlink>
> </Action>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23te2YkukFHA.3144@.TK2MSFTNGP12.phx.gbl...
> > You have to have either SP1 or SP2 installed (it was introduced with SP1).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "steve kwon" <steven,kwon@.lmco.com> wrote in message
> > news:%23jh%23BSukFHA.1444@.TK2MSFTNGP10.phx.gbl...
> >> Thanks, I went that route too. But I will try it again since you were
> >> successful.
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:e3rnQrtkFHA.3656@.TK2MSFTNGP09.phx.gbl...
> >> Here is an example of a Jump to URL link I use. This causes Excel to
> >> come up with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "steve kwon" <steven,kwon@.lmco.com> wrote in message
> >> news:OsOz9ltkFHA.3580@.TK2MSFTNGP09.phx.gbl...
> >> Hello,
> >>
> >> I would like to click on a report's textbox and it opens up another
> >> report in a new browser? I have the URL and it parameter. I placed
> >> them in the textbox's ACTION attribute but the new report displays in
> >> the same browser.
> >>
> >> Any suggestions?
> >>
> >> Thanks,
> >> Steve
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

How can I get the lable of a Parameter ?

I know that I can have access to the value of a paramter by using
Paramaters!Name.Value .I'm using a dropdown in my report which has lable and
values ,I'd like to get the lable not the value.
How can I do that?
Thanks for your time in advance.
AliHi Ali,
I find that =Paramaters!Name.Label will work MOST of the time.
I have several queried parameters and one that is not queried but has
values and lablels like: ="Q1-" & Year(Today)
The queried parameters work perfect with =Paramaters!Name.Label, but
the non queried parameter does not print if I use .Label However, it
will print if I use .Value
Take care,
Michelle|||My Problem is that I am using a non-queried parametre:-(
and I get an error if I use Parameters!Name.Value.What a bizzare,Isn't it?
Maybe Microsoft guys should add it to their wish list;-)
Ali
<Michelle@.bwalk.com> wrote in message
news:1106693367.969333.310150@.c13g2000cwb.googlegroups.com...
> Hi Ali,
> I find that =Paramaters!Name.Label will work MOST of the time.
> I have several queried parameters and one that is not queried but has
> values and lablels like: ="Q1-" & Year(Today)
> The queried parameters work perfect with =Paramaters!Name.Label, but
> the non queried parameter does not print if I use .Label However, it
> will print if I use .Value
>
> Take care,
> Michelle
>|||I'll double check this tomorrow at work but I don't think this is true.
.Label should always work. One thing to keep in mind is that the parameter
name is case sensitive. Parameters!somename.Value is not the same as
Parameters!SomeName.Value. Be sure that you have your parameter with the
appropriate case.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ALI-R" <newbie@.microsoft.com> wrote in message
news:%23wTTcHzAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> My Problem is that I am using a non-queried parametre:-(
> and I get an error if I use Parameters!Name.Value.What a bizzare,Isn't it?
> Maybe Microsoft guys should add it to their wish list;-)
> Ali
> <Michelle@.bwalk.com> wrote in message
> news:1106693367.969333.310150@.c13g2000cwb.googlegroups.com...
>> Hi Ali,
>> I find that =Paramaters!Name.Label will work MOST of the time.
>> I have several queried parameters and one that is not queried but has
>> values and lablels like: ="Q1-" & Year(Today)
>> The queried parameters work perfect with =Paramaters!Name.Label, but
>> the non queried parameter does not print if I use .Label However, it
>> will print if I use .Value
>>
>> Take care,
>> Michelle
>|||Yes,Bruse it is working,I misspelled Label as Lable:-) ,Funny then
Thanks for your help guys
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMb8inzAFHA.724@.TK2MSFTNGP10.phx.gbl...
> I'll double check this tomorrow at work but I don't think this is true.
> .Label should always work. One thing to keep in mind is that the parameter
> name is case sensitive. Parameters!somename.Value is not the same as
> Parameters!SomeName.Value. Be sure that you have your parameter with the
> appropriate case.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:%23wTTcHzAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> > My Problem is that I am using a non-queried parametre:-(
> > and I get an error if I use Parameters!Name.Value.What a bizzare,Isn't
it?
> > Maybe Microsoft guys should add it to their wish list;-)
> >
> > Ali
> >
> > <Michelle@.bwalk.com> wrote in message
> > news:1106693367.969333.310150@.c13g2000cwb.googlegroups.com...
> >> Hi Ali,
> >>
> >> I find that =Paramaters!Name.Label will work MOST of the time.
> >>
> >> I have several queried parameters and one that is not queried but has
> >> values and lablels like: ="Q1-" & Year(Today)
> >>
> >> The queried parameters work perfect with =Paramaters!Name.Label, but
> >> the non queried parameter does not print if I use .Label However, it
> >> will print if I use .Value
> >>
> >>
> >> Take care,
> >> Michelle
> >>
> >
> >
>

How can I get c# text and use as parameter?

I want to grab what is in textbox1, put in string, and send as a query
to bring up report with only textbox1 occurances in table. How do I do
that please?
Thanks,
Trint"trint" <trinity.smith@.gmail.com> wrote in message
news:1104768719.403928.182220@.f14g2000cwb.googlegr oups.com...
>I want to grab what is in textbox1, put in string, and send as a query
> to bring up report with only textbox1 occurances in table. How do I do
> that please?
> Thanks,
> Trint

You will probably get a better answer in a C# or .NET forum - from a pure
MSSQL point of view, you probably want to look at creating a stored
procedure which takes @.mystring as a parameter. But there are different ways
to call a stored proc (check out the Command and Connection objects in ADO),
which is why you might want to try a different group for a more exact
reponse.

Simon

How can I get and use an OUTPUT parameter

Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?

ALTER PROCEDURE [dbo].[NumberCounter]-- Add the parameters for the stored procedure here@.InsertDatedatetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereINSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);SelectIDENT_CURRENT('tblNumberCounter')ENDPublic Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs) Dim ConAs SqlConnection Dim StrInsertAs String Dim cmdInsertAs SqlCommand Dim myNewReceiptNumberAs Integer Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString'Add row to receipt table, then get it for ReceiptNumberText field. cmdInsert = New SqlCommand cmdInsert.CommandText = "NumberCounter" cmdInsert.CommandType = CommandType.StoredProcedure cmdInsert.Connection = New SqlConnection(ConnectStr) cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString()) Try Con.Open() myNewReceiptNumber = cmdInsert.ExecuteScalar() 'Response.Write(myNewReceiptNumber) Catch objExceptionAs SqlException Dim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally Con.Close()End TryEnd Sub

Try this...

ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@.InsertDatedatetime,
@.InsertedInteger int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);Select @.InsertedInteger =IDENT_CURRENT('tblNumberCounter')
END

Public Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs)

Dim ConAs SqlConnection
Dim StrInsertAs String
Dim cmdInsertAs SqlCommand
Dim myNewReceiptNumberAs Integer
Dim ConnectStrAs String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)

cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString())

'Create a new parameter with direction set to output, and add it to the parameters collection
dim InsertedInteger as new SqlParameter("@.InsertedInteger", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
cmdInsert.Parameters.Add(InsertedInteger)
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
MessageBox.Show(InsertedInteger.Value)
End Sub

|||Use SCOPE_IDENTITY() to get the ID of the record just inserted. IDENT_CURRENT will give you the ID for any session and any scope. So if the proc gets executed multiple times in parallel your Id's could get mixed up.|||Both of you have help very much.|||Welcome. Now can you mark the post as answered? Doesnt matter whose post you choose (you can choose both too). This helps in a number of ways especially when people search for posts with similar problem like yous.|||

Well, I could use a little more help with this:

I need to use the ID that is output in a query string, however, when I try to use it I get an error 'Object reference not set to an instance of an object'

Anyone see why this might happen??

Try cmdInsert.Connection.Open() InsertedInteger = cmdInsert.ExecuteScalar()Catch objExceptionAs SqlExceptionDim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally cmdInsert.Connection.Close() Response.Redirect("../editnew.aspx?ID=" & InsertedInteger.Value)End Try

|||

You would use ExecuteScalar to capture the output from your SELECT statement and it only captures one value. If you are using output parameters you would use ExecuteNonQuery and create parameters of type OUTPUT and use them to retrieve the values. You can use the sample code below and modify it accordingly:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="usp_testproc"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))myCommand.Parameters("@.userid").Value = Trim(userid)myCommand.Parameters.Add(New SqlParameter("@.ttype",SqlDbType.VarChar,25))myCommand.Parameters("@.ttype").Value ="charge"'output parametermyParam = mycommand.CreateParameter()myParam.ParameterName ="@.result"myParam.Direction = ParameterDirection.OutputmyParam.SqlDbType = SqlDbType.bigintmycommand.Parameters.Add(myParam)TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Response.Write(convert.todouble(mycommand.Parameters("@.result").Value))Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

How can i find the default values for a Stored Procedure parameter

I have a stored procedure,

CREATE PROCEDURE [dbo].[SReport]

(

@.ListByBatch BIT,

@.UnPrintedOnly BIT = 1

)

AS

............................

........

I can find the information regarding parameters of this Stored Procedure from "syscolumns" or

"Information_Schema.Parameters" system tables. These tables provides information about parametername, datatype ...etc. But dont provide information about default values for these parameters.

Here i have assigned a value, Does SQL Server stores this value?

Does SQL Server maintains information about Default Values for Parameter ? (For tables it maintains the default value for a column).

Is there any way , i can find All parameter information for a stored procedure including Default value..........?

Hi,
you can try sp_procedure_params_rowset

sp_procedure_params_rowset will return the following result set.

PROCEDURE_CATALOG Name of the database containing the procedure.

PROCEDURE_SCHEMA Owner's name of the procedure.

PROCEDURE_NAME Name of the procedure.

PARAMETER_NAMEName of the parameter.

ORDINAL_POSITION Ordinal position of the parameter.

PARAMETER_TYPE If the parameter is an OUTPUT parameter or not.

PARAMETER_HASDEFAULTIf the parameter has a default value.

PARAMETER_DEFAULTThe default value of the parameter.

IS_NULLABLEIf the parameter accepts NULL values.

DATA_TYPEData type of the parameter.

CHARACTER_MAXIMUM_LENGTHThe maximum length of the parameter's data.

CHARACTER_OCTET_LENGTHThe maximum octect length of the parameter's data.

NUMERIC_PRECISIONThe precision of the parameter.

NUMERIC_SCALEThe scale of the parameter.

DESCRIPTIONDescription of the parameter. Always NULL.

TYPE_NAMEActual name for the data type.

LOCAL_TYPE_NAMELocal name for the data type, if the data type is a user-defined data type.

|||

Hi,

Thanks for reply.

I tried this , But it shows NULL for PARAMETER_DEFAULT and 0 for PARAMETER_HASDEFAULT though i have set default value for that parameter.

Is there any other way , we can find this default value?

|||

SQL SERVER does not store the default parameter values for transact sql stored procedure. It does so for clr stored procedures. See here

http://msdn2.microsoft.com/en-us/library/ms176074.aspx

In SQL SERVER 2005, You can use object_definition() function to get the definition of the procedure and then parse it to get the default parameter values.

In SQL 2K, the syscomments system table stores the definitions for the various stored procedures.