Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

sql

Passing Parameter

I've to create an instance reference to my custom assembly by constructor
method.
It works well with a Code into the report .rdl like this:
---
<Code>
Public Obj As MyClass
Protected Overrides Sub OnInit()
dim MyArg as String = 4
Obj = new MyClass(MyArg)
End Sub
</Code>
--
But i need to pass a real parameter so:
Obj = new MyClass(Parameters!MyPar.Value)
it don't work and break with the error
BC30469 "The reference to a member not shared needs a reference to an object"
(I translate this from the italian version.... sorry for my bad english!!!
How can i do'
Thanks a lot.Try using this reference.
Report.Parameters!MyPar.Value
that should work in the Code.section
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> I've to create an instance reference to my custom assembly by constructor
> method.
> It works well with a Code into the report .rdl like this:
> ---
> <Code>
> Public Obj As MyClass
> Protected Overrides Sub OnInit()
> dim MyArg as String = 4
> Obj = new MyClass(MyArg)
> End Sub
> </Code>
> --
> But i need to pass a real parameter so:
> Obj = new MyClass(Parameters!MyPar.Value)
> it don't work and break with the error
> BC30469 "The reference to a member not shared needs a reference to an
> object"
> (I translate this from the italian version.... sorry for my bad
> english!!!
> How can i do'
> Thanks a lot.|||This not works, but the error is changed with a message box:
" Unable to load the assembly expressions. The expression refers to a
nonexistent parameter in the parameters's collection.".
This is not true, becouse the parameter exists.
Can you help me again?
"Wayne Snyder" wrote:
> Try using this reference.
> Report.Parameters!MyPar.Value
> that should work in the Code.section
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
> news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> > I've to create an instance reference to my custom assembly by constructor
> > method.
> > It works well with a Code into the report .rdl like this:
> > ---
> > <Code>
> > Public Obj As MyClass
> > Protected Overrides Sub OnInit()
> > dim MyArg as String = 4
> > Obj = new MyClass(MyArg)
> > End Sub
> > </Code>
> > --
> > But i need to pass a real parameter so:
> > Obj = new MyClass(Parameters!MyPar.Value)
> > it don't work and break with the error
> > BC30469 "The reference to a member not shared needs a reference to an
> > object"
> > (I translate this from the italian version.... sorry for my bad
> > english!!!
> > How can i do'
> > Thanks a lot.
>
>

passing null reportparameter values

Hi,
Iâ'm using the report viewer object to view a report which works fine until I
need to pass null values. When I use the following code I get â'The
'reportId' parameter is missing a valueâ':
ReportViewer1.ShowParameterPrompts = false;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("reportId");
parameters[1] = new ReportParameter("userid", "123");
ReportViewer1.ServerReport.SetParameters(parameters);
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.Refresh();
The reportId is setup for null values, however it still gives me an error:
<ReportParameter Name="reportId">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<Prompt>reportId</Prompt>
</ReportParameter>
I know other people have had this problem but using â'new
ReportParameter("reportId")â' doesnâ't work for me.
Thanks in advance!I'm guessing here, but do you need to explicitly set the value of the
null parameter. DBNull isn't the same as .Net C# null.
Regards, Rhys
On Sep 21, 11:50 pm, Nathan <nathan.et...@.online.nospam> wrote:
> Hi,
> I'm using the report viewer object to view a report which works fine until I
> need to pass null values. When I use the following code I get "The
> 'reportId' parameter is missing a value":
> ReportViewer1.ShowParameterPrompts = false;
> ReportParameter[] parameters = new ReportParameter[2];
> parameters[0] = new ReportParameter("reportId");
> parameters[1] = new ReportParameter("userid", "123");
> ReportViewer1.ServerReport.SetParameters(parameters);
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.Refresh();
> The reportId is setup for null values, however it still gives me an error:
> <ReportParameter Name="reportId">
> <DataType>Integer</DataType>
> <Nullable>true</Nullable>
> <Prompt>reportId</Prompt>
> </ReportParameter>
> I know other people have had this problem but using "new
> ReportParameter("reportId")" doesn't work for me.
> Thanks in advance!

passing multiple values from a listbox into a stored procedure

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?

protectedvoid confButton_Click(object sender,EventArgs e)

{

try

{

foreach (ListItem itemin authorsListBox4.Items)

{

if (item.Selected)

{

AddConfSqlDataSource.Insert();

}

}

saveStatusLabel.Text ="Save Successfull: The above publication has been saved";

}

catch (Exception ex)

{

saveStatusLabel.Text ="Save Failed: The above publication failed to save" + ex.Message;

}

}

The code you posted looks right, as much as you've posted. You'll have to loop, check for selected, and add. You're on the right track.
One thing that jumps out right away is that I can't see where you're passing any argument into your AddConfSqlDataSource.Insert(); function.
Did you mean something like:

AddConfSqlDataSource.Insert(item);

?

|||

Code looks fine. You are already looping thru each selected item of your multi select list box. So just check the code in this method AddConfSqlDataSource.Insert()

There should be some problem in it that is causing you the issue. Or put the code here for us to take a look.

|||

hmmm if i pass in item to the datasource:AddConfSqlDataSource.Insert(item);

i recieve error: No overload for method 'Insert' takes '1' arguments

the parameters i am passing through the datasource to the SP look like this:

<asp:SqlDataSourceID="AddConfSqlDataSource"runat="server" ConnectionString="<%$ ConnectionStrings:SoSymConnectionString%>" InsertCommand="StoredProcedureTest2" InsertCommandType="StoredProcedure"> <InsertParameters> <asp:ControlParameterControlID="PubTypeDropDownList"Name="typeID" PropertyName="SelectedValue"Type="Int16"/> <asp:ControlParameterControlID="titleTextBox4"Name="title" PropertyName="Text"Type="String"/> <asp:ControlParameterControlID="authorsListBox4"Name="authorID" Type="String"/> </InsertParameters> </asp:SqlDataSource>

and my SP as follows: maybe i have a problem within my SP - looping?

ALTER PROCEDUREdbo.StoredProcedureTest2 @.publicationIDInt=null, @.typeIDsmallint=null, @.titlenvarchar(MAX)=null, @.authorIDsmallint=null ASBEGIN TRANSACTIONSET NOCOUNT ON DECLARE@.ERRORInt SET@.ERROR=0IF EXISTS(SELECT*FROMPublicationWHEREtitle = @.title)SELECT@.publicationID = (SELECTpublicationIDFROMPublicationWHEREtitle = @.title)ELSE BEGIN INSERT INTOPublication (typeID, title) VALUES(@.typeID, @.title) SET@.publicationID = @.@.IDENTITY--Obtain the ID of the created publication SET@.ERROR = @.@.ERROREND IF NOT EXISTS(SELECT*FROMPublicationAuthorsWHEREpublicationID = @.publicationIDANDauthorID = @.authorID)BEGIN INSERT INTOPublicationAuthors (publicationID, authorID)VALUES(@.publicationID, @.authorID) SET@.ERROR = @.@.ERROR END IF(@.ERROR<>0)ROLLBACK TRANSACTIONELSECOMMIT TRANSACTION

Sorry to post loads of code! ...

Thanks

Passing multiple rows of data to a code function

Is there a way to pass multiple rows to a function on the report? Here's
what I'm trying to do:
I have field in my detail section, "customer", that is shows each customer:
[Customer1]
[Customer2]
[Customer3]
[etc.]
I'd like to wrap these into a single field at the parent group so I get the
following in a single field:
[Customer1, Customer2, Customer3, etc.]
Does that make sense?
I was thinking I might be able to write a VB function to take in a group of
records, itereate through them, and return the reformated string.
Of course if there's another way to accomplish this, I'm completely open to
other ideas.hi,i think it's better 2 do it on the sql side,so u get it in the ds as one
field.
"Greg S" wrote:
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get the
> following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group of
> records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open to
> other ideas.
>
>|||Take a look at the matrix control and see if that will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:e2lvbHHIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each
> customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get
> the following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group
> of records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open
> to other ideas.
>|||Follow up solution to my own thread:
Well, I did find some way to do this
concatenation/aggregation/rows-to-a-column on the SQL side. Here's a good
example using CROSS APPLY and leveraging FOR XML in sql 2005
http://www.aspfaq.com/show.asp?id=2529
I found a number of other examples as well - some using UDF functions,
orthers using customer CRL assemblies. Most threads had someone commenting
to the effect of "... this is usually needed for some kind of reporting and
should be handled in the presentation layer... doing it via SQL is breaking
the idea of pure relational databases..." Just thought this was funny as
my presentation layer (reporting services) can't do it. :^)
"'" <@.discussions.microsoft.com> wrote in message
news:4D2617C3-804E-4A10-AE87-53C8CB077DD4@.microsoft.com...
> hi,i think it's better 2 do it on the sql side,so u get it in the ds as
> one
> field.
> "Greg S" wrote:
>> Is there a way to pass multiple rows to a function on the report? Here's
>> what I'm trying to do:
>> I have field in my detail section, "customer", that is shows each
>> customer:
>> [Customer1]
>> [Customer2]
>> [Customer3]
>> [etc.]
>> I'd like to wrap these into a single field at the parent group so I get
>> the
>> following in a single field:
>> [Customer1, Customer2, Customer3, etc.]
>> Does that make sense?
>> I was thinking I might be able to write a VB function to take in a group
>> of
>> records, itereate through them, and return the reformated string.
>> Of course if there's another way to accomplish this, I'm completely open
>> to
>> other ideas.
>>

passing multiple parameters to subreport

Hello Group,
Is it possible to pass multiple parameters down to a subreport? If so how?
I am using this code in the report to show the contents of the parameters on
the top of the report.
Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""
For Each sParamItem In Parameter
If sParamItem Is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next
'-- Remove last comma & space:
Return sParamVal.SubString(0, sParamVal.Length - 2)
End Function
Therefore I was expecting to use this to pass it through like this:
Code.Parameterlist(Parameters!Division.Value)
But that doesnt work unfortunately. Does anybody know an approach that
works....
Thanx
PerryDoes anybody have a clue?
thanx again
Perry
"Perry" <sjaak@.sjaak.net> wrote in message
news:OjSF%23CdzGHA.576@.TK2MSFTNGP03.phx.gbl...
> Hello Group,
> Is it possible to pass multiple parameters down to a subreport? If so how?
> I am using this code in the report to show the contents of the parameters
> on the top of the report.
> Function ParameterList(ByVal Parameter As Object) As String
> Dim sParamItem As Object
> Dim sParamVal As String = ""
> For Each sParamItem In Parameter
> If sParamItem Is Nothing Then Exit For
> sParamVal &= sParamItem & ", "
> Next
> '-- Remove last comma & space:
> Return sParamVal.SubString(0, sParamVal.Length - 2)
> End Function
>
> Therefore I was expecting to use this to pass it through like this:
> Code.Parameterlist(Parameters!Division.Value)
> But that doesnt work unfortunately. Does anybody know an approach that
> works....
> Thanx
> Perry
>
>
>

Passing DataSet's into Custom Code

Is it possible to send a DataSet to a Custom code from the report. I am
planning to use .net dll's in my report as a custom code.
ThanksIf you mean an RS dataset you can't because RS datasets are not exposed
programatically. Some workarounds you may consider:
1. Retrieve an ADO.NET dataset in your code using the same query. In other
words, clone the dataset.
2. If the report has a matrix region, you can redirect the matrix region to
read its values from an embedded function. In this way you can populate a
custom data structure, e.g. an Array or an ADO.NET dataset, which you can
pass the external assembly. As a practical example, you may want to look at
my report sample "Sales by Product Category" which collects the matrix
region data in an array and sends it off to an external assembly to get
forecasted sales.
http://www.manning-sandbox.com/thread.jspa?threadID=10383&tstart=0
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> Is it possible to send a DataSet to a Custom code from the report. I am
> planning to use .net dll's in my report as a custom code.
> Thanks|||What are you trying to accomplish? If you want to calculate additional columns based on data in
previous columns within the same row, look into calculated fields. If you want more control over
the dataset manipulation, you can write your own Data Processing Extension which reads in the
dataset and performs whatever logic you want to do on it prior to submitting the data to the report.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> Is it possible to send a DataSet to a Custom code from the report. I am
> planning to use .net dll's in my report as a custom code.
> Thanks|||For creating your own DPE, start by searching Google Groups [1]. I know there are a few samples out
there.
Without writing a DPE, if you have a dataset with two columns returned (Quantity and Price) and you
need a third column computed (Total), you can either add a column to your table with this
expression:
=Fields!Quantity.Value * Fields!Price.Value
or you can right-click in the Fields window in Report Designer and choose Add Field. You can create
a new computed field named Total and tell it to always have a value equal to the above expression.
To sort, you should attempt to sort the data in the database prior to being sent to the report. If
that isn't possible, look in our Books Online for more information on how to Sort rows shown in
tables.
[1]
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.reportingsvcs
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:995C993F-D0CE-4224-8F25-29EFE1E555F3@.microsoft.com...
> Donovan
> I have two ques,
> Ques1:
> Can you explain me how to do this[you can write your own Data Processing
> Extension]. My situation is, my dataset would return 6 rows. Before display
> these rows in my report using table, i have to assign one of the column
> values in 6 text boxes.
> Ques2:
> Is it possible to suffle/sort my dataset by perticluar column value[s] prior
> to submitting the data to the report ?
> Thanks in advance
>
> "Donovan R. Smith [MSFT]" wrote:
> > What are you trying to accomplish? If you want to calculate additional columns based on data in
> > previous columns within the same row, look into calculated fields. If you want more control
over
> > the dataset manipulation, you can write your own Data Processing Extension which reads in the
> > dataset and performs whatever logic you want to do on it prior to submitting the data to the
report.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "aplearner" <aplearner@.discussions.microsoft.com> wrote in message
> > news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> > > Is it possible to send a DataSet to a Custom code from the report. I am
> > > planning to use .net dll's in my report as a custom code.
> > >
> > > Thanks
> >
> >
> >sql

Passing columns to CLR function

Hello,

I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:

Code Snippet

public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)

{

//Do Something...

x = ....

y=...

z=...

}

I want to call this code from SQL code:

Code Snippet

create table #Temp (float a,float b)

declare @.x float

declare @.y float

declare @.z float

exec dbo.DoSomething(a,b,@.x,@.y,@.z) ?

Do someone have an idea?

Hi Shlomi,

There are many tutorials for achieving this on the net and even in BOL. Look for "CLR User-Defined Functions" Books Online for examples.

In short: If you're using Visual Studio, you open the right project and choose Add Function. Sceleton code is presented and you add your code. Now build the Assembly. After building, go to SQL Server and user CREATE ASSEMBY to import the assembly into SQL Server and last use CREATE FUNCTION to import the function from the assembly. Now you can use your function in SQL Server like you want to.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

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.

Passing back more than 1 output parameter to VBA code

I have a stored procedure which has 2 output parameters, namely @.RecCnt and
@.RetCode. In the stored procedure, I am using the SET statements to pass the
data back. I am calling the stored procedure from my VBA code. I use
objCmd.Execute options:=adExecuteNoRecords.
I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
Could any of you tell me what is wrong?
VBA Code:
--
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_addback_selectcount_CorpAcctCtr"
.NAME = "sp_addback_selectcount_CorpAcctCtr"
.CommandType = adCmdStoredProc
'Create parameter list for objCmd
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
3, vstrCorp)
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
5, vstrAcct)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
vstrCtr)
.Parameters.Append .CreateParameter("RecCnt", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
.Execute options:=adExecuteNoRecords
End With
If objCmd.Parameters("RetCode").Value Then
rlngRecCnt = objCmd.Parameters("RecCnt").Value
fnGetAddbackCnt = True
End If
--Store Procedure
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GOSoooorrryyy...Goofed up the code and stored procedure...
Here is the correct one:
VBA Code:
......
.....
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_lotusdata_load_from_recon"
.NAME = "sp_lotusdata_load_from_recon"
.CommandType = adCmdStoredProc
'Create parameter list for oCmd
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
Len(vstrAcct), vstrAcct)
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
Len(vstrCorp), vstrCorp)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
Len(vstrCtr), vstrCtr)
.Parameters.Append .CreateParameter("RecsAffected", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
'-- execute the proc
.Execute options:=adExecuteNoRecords
'-- return success if stored proc is successful
If .Parameters("RetCode").Value = True Then
rintRecsAffected = .Parameters("RecsAffected")
fnLoadLotusData = True
End If
....
...
Stored Procedure Code:
--
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GO
Sorry for the error.
Regards,
Paddy
"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @.RecCnt an
d
> @.RetCode. In the stored procedure, I am using the SET statements to pass t
he
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
> VBA Code:
> --
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> .NAME = "sp_addback_selectcount_CorpAcctCtr"
> .CommandType = adCmdStoredProc
> 'Create parameter list for objCmd
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
5,
> vstrCtr)
> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> .Execute options:=adExecuteNoRecords
> End With
>
> If objCmd.Parameters("RetCode").Value Then
> rlngRecCnt = objCmd.Parameters("RecCnt").Value
> fnGetAddbackCnt = True
> End If
> --Store Procedure
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO|||Paddy (Paddy@.discussions.microsoft.com) writes:
> I have a stored procedure which has 2 output parameters, namely @.RecCnt
> and @.RetCode. In the stored procedure, I am using the SET statements to
> pass the data back. I am calling the stored procedure from my VBA code.
> I use objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
How do you conclude that you can not retriev the RecCnt value?
I don't think it should really matter, but it is a good idea to align
the names in the parameters collection with the actual parameters names.
Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
@.RetCode. Furthermore there is one parameter missing. That is, each
stored procedure has a return value, which in ADO you declare as a the
first parameter and as adParamReturnValue. Then again, I think it's find
to not include that paraemeter.

> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
The sp_ prefix is reserved for system objects, and SQL Server first
looks in the master database for these. Don't use it, in your own code.

> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
I think adParamInputOutput are more appropriate as that is what they
are.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The above code always return RecsAffected value as 0. When I run the sp in
Query Analyser it shows the correct data.
Please help.
Regards,
Paddy
"Paddy" wrote:
> Soooorrryyy...Goofed up the code and stored procedure...
> Here is the correct one:
> VBA Code:
> ......
> .....
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_lotusdata_load_from_recon"
> .NAME = "sp_lotusdata_load_from_recon"
> .CommandType = adCmdStoredProc
> 'Create parameter list for oCmd
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
> .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> '-- execute the proc
> .Execute options:=adExecuteNoRecords
> '-- return success if stored proc is successful
> If .Parameters("RetCode").Value = True Then
> rintRecsAffected = .Parameters("RecsAffected")
> fnLoadLotusData = True
> End If
> ....
> ...
> Stored Procedure Code:
> --
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO
> Sorry for the error.
> Regards,
> Paddy
> "Paddy" wrote:
>|||Hi, Erland,
Please read my 2nd and 3rd message. I copied the wrong code in the message.
I posted the code which has problem in my second message.
I debugged the VBA code and know it is returning 0.
I knew about sp_ prefix, but used it for easily distinguish stored
procedures. I know there is some performance penalties.
Anyway, is there anything wrong in the way I am setting the output parameter
in the stored procedure?
Thanks.
Paddy
"Erland Sommarskog" wrote:

> Paddy (Paddy@.discussions.microsoft.com) writes:
> How do you conclude that you can not retriev the RecCnt value?
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
> @.RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Paddy (Paddy@.discussions.microsoft.com) writes:
> The above code always return RecsAffected value as 0. When I run the sp in
> Query Analyser it shows the correct data.
But RetCode is still True then?
When you run from QA, I suspect that you run as as or dbo, but how
do run the application? Does that account have CREATE TABLE permissions?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing a selected row column value to the stored procedure

I have a simple Gridview control that has a delete command link on it.

If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...

The grid
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"
ReadOnly="True" SortExpression="member_id" />
<asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />
<asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />
<asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />
<asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"
SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"
DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
>

<DeleteParameters>

<asp:Parameter Name="member_id" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource
the SP

CREATE PROCEDURE renMemberDelete
@.member_id as int
As UPDATE [renMembers]
SET member_status=1
WHERE [member_id] = @.member_id
GO

Try:GridView2.DataKeyNames="member_id"

or

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2" DataKeyNames="member_id">

passing a dataset in to custom code

Is it possible to pass a dataset in to custom code? You can pass values in
but I was wondering if the whole dataset could be passed in.
Thanks,
CraigHi,
I think instead you can create dataset in the reporting server itself and
access it.
Regards
Amarnath
"Craig" wrote:
> Is it possible to pass a dataset in to custom code? You can pass values in
> but I was wondering if the whole dataset could be passed in.
> Thanks,
> Craig
>
>

Pass RS SessionID via URL

Can anybody tell me if the following should work:
I am trying to start a report services session by calling the Render
method in code. This works fine - a session ID is generarted in the
SessionHeaderValue. Now, I would like to able to that same session
via URL. Here's the VB code snippit:
--
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.SessionHeaderValue = sh
result = rs.Render(sender_button.CommandArgument, format, history_id,
_
dev_info, parameter_values, credentials, show_hide_toggle, encoding,
_
mime_type, report_history_parameters, warnings, stream_ids)
Dim temp As String
temp = "http://localhost/ReportServer?%2fDemo+Project%2fMyReport&rs:Command=Render&rs:SessionID="
& rs.SessionHeaderValue.SessionID
Response.Redirect(temp)
--
The redirect works fine, but the Report Server pops up a login screen.
If I have already started the session, and pass the session ID, should
the login still be required?
Any insight on this would be appreciated.
Thanks,
TerryIs rs.Url the same as the root of your temp string? I.e. it's localhost, not
the machine name?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0407230821.3a7bb882@.posting.google.com...
> Can anybody tell me if the following should work:
> I am trying to start a report services session by calling the Render
> method in code. This works fine - a session ID is generarted in the
> SessionHeaderValue. Now, I would like to able to that same session
> via URL. Here's the VB code snippit:
> --
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.SessionHeaderValue = sh
> result = rs.Render(sender_button.CommandArgument, format, history_id,
> _
> dev_info, parameter_values, credentials, show_hide_toggle, encoding,
> _
> mime_type, report_history_parameters, warnings, stream_ids)
> Dim temp As String
> temp ="http://localhost/ReportServer?%2fDemo+Project%2fMyReport&rs:Command=Render&
rs:SessionID="
> & rs.SessionHeaderValue.SessionID
> Response.Redirect(temp)
> --
> The redirect works fine, but the Report Server pops up a login screen.
> If I have already started the session, and pass the session ID, should
> the login still be required?
> Any insight on this would be appreciated.
> Thanks,
> Terry|||Thanks for the reply, Tudor.
The Url of the reporting service is:
"http://localhost/ReportServer/ReportService.asmx" which matches the
root of the 'temp' string in the code.
Is there anything else I should check?
Thank you,
Terry
"Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message news:<OyNbI52cEHA.2236@.TK2MSFTNGP10.phx.gbl>...
> Is rs.Url the same as the root of your temp string? I.e. it's localhost, not
> the machine name?
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Tudor,
Ignore my last reply. The URL's were the same on my development box, but
different on our live server. After I made sure they were the same on the live
server, it worked.
Thanks for your help!
Regards,
Terry|||Tudor:
I spoke too soon. It works only if I run it from the local machine. If
I try to access it from a remote machine via IE, it asks for authorization.
Thanks,
Terry|||If your app and RS are on different machines, you may be hitting the 2
machine hop limitation that exists in NTLM. NTLM credentials do not flow on
more than one machine.
client => your app => RS server
If you are on Windows 2003, you can use Kerberos, and enable delegation
between your app machine and the RS server machine. You could also use basic
auth over SSL (do not use it over plain HTTP as credentials are passed in
un-encrypted on the wire) or forms/custom authentication - but that requires
you write more code and has numerous other security implications.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0407270626.253a9ee4@.posting.google.com...
> Tudor:
> I spoke too soon. It works only if I run it from the local machine.
If
> I try to access it from a remote machine via IE, it asks for
authorization.
> Thanks,
> Terry

pass Date varible to XPath query

Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"
i figure out myself. Thanks
"martin1" wrote:

> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:

> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>

pass Date varible to XPath query

Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"i figure out myself. Thanks
"martin1" wrote:

> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is n
ot
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql"
"
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='"
&
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:

> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>

Pass a array how parameters to stored procedure.

Boy's, I write why I need a example about pass an array how parameters to
stored procedure.
I need an example from a stored procedure and a c# code call.
I'm from Argentina, I wait you understand me and your answer.
Javier
There is no array type in SQL. Generally people fake arrays using a comma
separated value or delimited string as a single parameter to get around
this. For a detailed list of options you can refer to Erland's article at :
http://www.sommarskog.se/arrays-in-sql.html
Anith