Passing Paramenter to SP for Column Name

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)

select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

passing more parameters

i am calling a report from a form and want to pass 90 parameters to a report.
but is not supported by reporting services. please help me to solve the
problemI suggest putting those 90 parameters into a table identified by a guid and
then pass the quid instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ter" <ter@.discussions.microsoft.com> wrote in message
news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> i am calling a report from a form and want to pass 90 parameters to a
> report.
> but is not supported by reporting services. please help me to solve the
> problem|||You can set all the parameters in a XML document.
Good Luck.
"ter" <ter@.discussions.microsoft.com> wrote in message
news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> i am calling a report from a form and want to pass 90 parameters to a
report.
> but is not supported by reporting services. please help me to solve the
> problem|||Can you give an example of how to access the XML document parameters in RS
THanks,
Rahul
"Liz Matyas" wrote:
> You can set all the parameters in a XML document.
> Good Luck.
> "ter" <ter@.discussions.microsoft.com> wrote in message
> news:CE88F1A4-4CD0-43BB-8DAA-75B6AC237EC0@.microsoft.com...
> >
> > i am calling a report from a form and want to pass 90 parameters to a
> report.
> > but is not supported by reporting services. please help me to solve the
> > problem
>
>

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

sql

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

passing form variables to populate Report Param..

Is this possible? All of our users are required to login to our website in order to access their applications they have particular access to. is there a way to pass the same from variable to the first Report parameter. Currently i have it set up (development environment) that all report parameters are visible when we go to the web page to access report. I only want certain parameters available based on who is logged on, but i do not want to build 20 different reports to accomplish this.
Any help would be greatly appreciatedInstead of creating many different report you may take a look at creating
linked reports. Linked reports carry their own security settings, parameter
settings but share the same report definition. For example, you can provide
default value for a parameter and declare it read-only on a linked report.
Users who have access to this linked report won't be able to set the value
of that parameter.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Derek M" <DerekM@.discussions.microsoft.com> wrote in message
news:A332BCCD-D844-4B9C-B9B0-33C7698CAA62@.microsoft.com...
> Is this possible? All of our users are required to login to our website
in order to access their applications they have particular access to. is
there a way to pass the same from variable to the first Report parameter.
Currently i have it set up (development environment) that all report
parameters are visible when we go to the web page to access report. I only
want certain parameters available based on who is logged on, but i do not
want to build 20 different reports to accomplish this.
> Any help would be greatly appreciated

Passing Form values from ASP to RS?

Hi everybody,
does anybody knows, if it is possible to pass input from an ASP-page (not
.NET) to RS and populate the dataset with the given values? We are trying to
use the RS so the user is able to print out, export to excel or pdf AFTER
he/she did the collection of the data. For example a search for address data
where the user types in the postal code and/or the cityname, gets the result
inside the browser and is then capable to print out the specific data. I
figured out that to use parameters for this, but as I don't know which
parameters a user typed in, I am not able to build the sql statement in
advance.
Any help would be great.
Regards
MichaelI have not tried this from a form but I have built a URL dynamically to do
the same thing. You could use some client side javascript to build the URL
dynamically or you could just build an interim ASP page with a redirect.
Good Luck
Bill
Michael Bender wrote:
>Hi everybody,
>does anybody knows, if it is possible to pass input from an ASP-page (not
>.NET) to RS and populate the dataset with the given values? We are trying to
>use the RS so the user is able to print out, export to excel or pdf AFTER
>he/she did the collection of the data. For example a search for address data
>where the user types in the postal code and/or the cityname, gets the result
>inside the browser and is then capable to print out the specific data. I
>figured out that to use parameters for this, but as I don't know which
>parameters a user typed in, I am not able to build the sql statement in
>advance.
>Any help would be great.
>Regards
>Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Hi Bill,
thx for your tips. Maybe I'm not getting it, but I don't have the problem to
built the URL dynamically in the ASP page. A more exact example would be:
In a form where a user is able to select from search criteria like: First
Name, Last Name, Postal Code, Town and Country the user only chooses to look
for data based on a certain postal code.
My problem now is to use ONLY the parameter postal code in the SQL statement
to generate the report in RS. (eg: SELECT * from Table_AdressData where
postalcode = @.postalcode) I understand that I can't use all the parameters
in the sql statement (Select * from Table... where postalcode = @.postalcode
and firstname = @.firstname and etc.) because then the statement would fail
if the user does not enter every parameter as a search criteria.
Thx.
Michael
"William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
news:58c684e7207be@.uwe...
>I have not tried this from a form but I have built a URL dynamically to do
> the same thing. You could use some client side javascript to build the
> URL
> dynamically or you could just build an interim ASP page with a redirect.
> Good Luck
> Bill
> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Your question really has nothing to do with integration with ASP. This is
strictly a RS question. For each of your parameters set the default to All
(the string all, if the data type is integer then set the default to an
integer that will not return data).
Then design your query like this:
select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
(field2 = @.Param2 or @.Param2 = 'All')
Then in your URL leave off the other parameters and send only the one you
care about. Or, don't have defaults and pass all the parameter but pass the
ones not filled in with the word All
One reason you might not want to use defaults is if the report is opened on
its own and all the parameters have defaults it will immediate execute.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bender" <technik@.salescom.de> wrote in message
news:dnmn4d$lnb$03$1@.news.t-online.com...
> Hi Bill,
> thx for your tips. Maybe I'm not getting it, but I don't have the problem
> to built the URL dynamically in the ASP page. A more exact example would
> be:
> In a form where a user is able to select from search criteria like: First
> Name, Last Name, Postal Code, Town and Country the user only chooses to
> look for data based on a certain postal code.
> My problem now is to use ONLY the parameter postal code in the SQL
> statement to generate the report in RS. (eg: SELECT * from
> Table_AdressData where postalcode = @.postalcode) I understand that I can't
> use all the parameters in the sql statement (Select * from Table... where
> postalcode = @.postalcode and firstname = @.firstname and etc.) because then
> the statement would fail if the user does not enter every parameter as a
> search criteria.
> Thx.
> Michael
>
>
> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>|||Thanks very much, Bruce.
Regards
Michael
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OACNUv$$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Your question really has nothing to do with integration with ASP. This is
> strictly a RS question. For each of your parameters set the default to All
> (the string all, if the data type is integer then set the default to an
> integer that will not return data).
> Then design your query like this:
> select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
> (field2 = @.Param2 or @.Param2 = 'All')
> Then in your URL leave off the other parameters and send only the one you
> care about. Or, don't have defaults and pass all the parameter but pass
> the ones not filled in with the word All
> One reason you might not want to use defaults is if the report is opened
> on its own and all the parameters have defaults it will immediate execute.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Bender" <technik@.salescom.de> wrote in message
> news:dnmn4d$lnb$03$1@.news.t-online.com...
>> Hi Bill,
>> thx for your tips. Maybe I'm not getting it, but I don't have the problem
>> to built the URL dynamically in the ASP page. A more exact example would
>> be:
>> In a form where a user is able to select from search criteria like: First
>> Name, Last Name, Postal Code, Town and Country the user only chooses to
>> look for data based on a certain postal code.
>> My problem now is to use ONLY the parameter postal code in the SQL
>> statement to generate the report in RS. (eg: SELECT * from
>> Table_AdressData where postalcode = @.postalcode) I understand that I
>> can't use all the parameters in the sql statement (Select * from Table...
>> where postalcode = @.postalcode and firstname = @.firstname and etc.)
>> because then the statement would fail if the user does not enter every
>> parameter as a search criteria.
>> Thx.
>> Michael
>>
>>
>> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
>> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to
>>do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page
>>(not
>>.NET) to RS and populate the dataset with the given values? We are
>>trying to
>>use the RS so the user is able to print out, export to excel or pdf
>>AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>>
>sql

passing empty string to stored procedure -SQL Express 2005

I am taking data from a form and passing it to a stored procedure to insert
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"
The stored procedure is:
ALTER PROCEDURE [dbo].[AddNewContract]
@.strContractorName nVARCHAR(50),
@.strOrderNumber nVARCHAR(50) = null,
@.strWorkLocation ntext = null,
@.Report datetime = null,
@.NewContractID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
@.dtReport;
SELECT @.NewContractID = SCOPE_IDENTITY();
END
The VB code is:
With MyCmd
.ActiveConnection = conn
.CommandText = "dbo.AddNewContract"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
.Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
.Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
.Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
.Parameters.Append .CreateParameter("@.NewContractID", adInteger,
adParamOutput)
End With
MyCmd.Execute
lContractID = MyCmd.Parameters("@.NewContractID").Value()
I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.
I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?Just some ideas, not sure if they will solve the problem or not...
I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.
Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).
Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@.microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
> The stored procedure is:
> ALTER PROCEDURE [dbo].[AddNewContract]
> @.strContractorName nVARCHAR(50),
> @.strOrderNumber nVARCHAR(50) = null,
> @.strWorkLocation ntext = null,
> @.Report datetime = null,
> @.NewContractID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
> SELECT @.strContractorName, @.strOrderNumber, @.strWorkLocation,
> @.dtReport;
> SELECT @.NewContractID = SCOPE_IDENTITY();
> END
> The VB code is:
> With MyCmd
> .ActiveConnection = conn
> .CommandText = "dbo.AddNewContract"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
> .Parameters.Append .CreateParameter("@.OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
> .Parameters.Append .CreateParameter("@.WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
> .Parameters.Append .CreateParameter("@.dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
> .Parameters.Append .CreateParameter("@.NewContractID", adInteger,
> adParamOutput)
> End With
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@.NewContractID").Value()
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?|||> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.
Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.
Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.
The error message you see is not a SQL Server error.
ML
http://milambda.blogspot.com/|||Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade faile
d
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.
"ML" wrote:

> Not quite true. Defaults get used when parameters are not set at all by th
e
> caller, or if the caller specifies DEFAULT as the parameter value.
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
> The error message you see is not a SQL Server error.
>
> ML
> --
> http://milambda.blogspot.com/|||"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "
I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.

Passing DateTime Parameter to Subreport (Error)

Hi guys,

I have managed to pass a string parameter form the main report to the subreport with no problems, but when i try and use the same method to pass a datetime type, i am issued with the following error messase:

"[rsErrorExecutingSubreport] An error occurred while executing the subreport ‘SubReportName’: The value provided for the report parameter 'MasterDate' is not valid for its type."

I cant see why it work for strings, but not for datetime. Any suggestions will be very much appreciated

Just to clarify, i am using the same dateTime type to define both main and sub report parameters|||Does anybody know why i am getting this error. Your help will be very much appreciated.|||Does anybody know why i am getting this error?

Passing arrays to stored procedures

Dear all,

i want to know how i can pass multiple values in the form of arrays to a stored procedures.

the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:

Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
 Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
 Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
 Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
 '
 'cmdInsSlabHmst
 '
 Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
 Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
 Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
 '
 'OleDbConnection1
 '
 Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
 Dim strCode As String
 Dim i As Integer
 'Dim dblSlabRate As Decimal
 'Dim dblSlabFrom As Decimal
 Dim strSlabRate As String
 Dim strSlabFrom As String
 Dim strSlabRateP As String
 Dim strSlabFromP As String
 Dim intCntr As Integer
 'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
 'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
 'OleDbConnection1.Open()
 'strCode = cmdInsSlabHmst.ExecuteScalar
 'OleDbConnection1.Close()
 For i = 0 To dgSlabDtl.Items.Count - 1
 If i = dgSlabDtl.Items.Count - 1 Then
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
 Else
 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
 strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
 End If
 strSlabRateP += strSlabRate & "~"
 strSlabFromP += strSlabFrom & "~"
 intCntr += 1
 'If dblSlabRate <> "" And dblSlabFrom <> "" Then
 'InsDtl(strCode, dblSlabFrom, dblSlabRate)
 'End If
 Next
 If strSlabRateP <> "" And strSlabFrom <> "" Then
 With cmdInsSlabHmst
 .Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
 .Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
 .Parameters("iREMARKS").Value = txtRemarks.Text
 .Parameters("iSLABFROM").Value = strSlabFromP
 .Parameters("iSLABRATE").Value = strSlabRateP
 .Parameters("iNOOFRECORDS").Value = intCntr
 End With
 OleDbConnection1.Open()
 cmdInsSlabHmst.ExecuteNonQuery()
 OleDbConnection1.Close()
 End If

to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.

i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.

regards
subhajitWell, what you are doing is probably the way to go. I don't understand though, how you can say a 'complicated stored procedure' was written to chop up a ~ delimited list. It should be only a couple of very simple statements to do.

I do the same thing quite often, for example if I have a checkboxlist and I want to pass a list of all items that the user checked to a stored proc, I go through the list in my ASP code and create a string in much the same way you do. Then in my stored proc I do something like:

while strpos(@.List, '~') > 0
begin
 insert into #Table values (substring(@.List, 1, strpos(@.List, '~') - 1))
 set @.List = substring(@.List, strpos(@.List, '~') + 1, length(@.List))
end

which is easy enough for me, and executes in a flash, even for hundreds of items.

If you reach the limit of this code (@.List close to 8000 chars) you need to do something else. In this case it can be useful to consider your specific application. Where are those values coming from? Are you storing it in bits and pieces to the array, and then in one shot you want to write it to your DB? If so you should think about writing the values to your DB as they become available - instead of keeping them in an array in server memory.

But from your code it looks like you're just doing the same sort of thing I do, so I don't see the problem.

BTW, your code is very difficult to look at, since 1/2 of it is commented out. If you can distill it into just what we need to see your problem, it makes it easier for us to help you. It will also increase the likelyhood of an accurate answer.sql

Passing ANY value to the <InsertParameters> section of a SQLDataSource

Ok-

I'm new at this, but just found out that to get data off a form and insert into SQL I can scrape it off the form and insert it in the <insertParameter> section by using

<asp:ControlParameterName="text2"Type="String"ControlID="TextBox2"PropertyName="Text"> (Thanks CSharpSean)

Now I ALSO need to set the user name in the same insert statement. I put in a UserName control that is populated when a signed in user shows up on the page. But in my code I've tried:

<asp:ControlParameter Name="UserName" Type="String" ControlID="LoginName1" DefaultValue="Daniel" PropertyName="Text"/>

and I get teh error

DataBinding: 'System.Web.UI.WebControls.LoginName' does not contain a property with the name 'Text'.

So I take PropertyName="Text" out, and get the error:

PropertyName must be set to a valid property name of the control named 'LoginName1' in ControlParameter 'UserName'.

what is the proper property value?

SO....BIG question...

Is there a clean way to pass UserName to the insert parameters? Or ANY value for that matter? Id like to know how to write somehting like

String s_test = "test string";

then in the updateparameter part of the sqldatasource pass SOMEHITNG like (in bold) <asp:Parameter Name="UserName" Type="String"Value=s_test/>

Thanks in advance...again!

Dan

Create the parameter, with a name attribute and a type. Then in SqlDataSource_Inserting event, just set the value of the parameter to whatever you want it to be. I believe the syntax (VB.NET) is either

e.Command.Parameters("@.UserName").Value=s_test

or

e.InsertCommand.Parameters("@.UserName").Value=s_test

|||

Sounds easy.

Im writing in c#

But Im not sure where you are suggesting dropping in the code?

Is it in the '<InsertParameters> of the sql data source?

What is the 'E' in the e.insert...

Sorry for being so dense, but can you give me a psudo-code example?

Thanks for the help!
Dan

|||

Step by step:

"Create the parameter, with a name attribute and a type." This is what you did before, just add:

<asp:Parameter name="UserName" type="String" /> in the <InsertParameters> section of the sqldatasource control.

"Then in SqlDataSource_Inserting event": double click the sqldatasource control while in design mode.

That should take you to your code behind and create a dummy event called "SqlDatasource1_Selecting".

Now at the top of the window, there are two dropdown list boxes. The right one should say "Selecting", change it to "Inserting". Now you should have a dummy event sub called "SqlDataSource1_Inserting". The code I gave goes in there. "e" is the second parameter of the two that gets passed in whenever the sqldatasource control is about to do an insert.

If you were doing this in VB.Net, your code behind page (Mypage.aspx.vb) would have these two new subs:

ProtectedSub SqlDataSource1_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Inserting

e.Command.Parameters("@.UserName").Value=s_test

EndSub

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

EndSub

You can now delete the "SqlDataSource1_Selecting" sub if you want, since you aren't really using it.

|||

Thanks a BUNCH!
(Will try it after i make MORE coffee...)

|||

C# People can use the above discussion and use

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
String var1 = TextBox1.Text;
e.Command.Parameters["@.text1"].Value = var1;
}

in the inserting function

sql

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing a report parameter from a Visual C# form to a report parameter

Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.

I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?

Thanks!

Terry B

I hope this this article will help.

Passing a parameter from a form to an sql sequel view

Hi,

We have recently upsized an access db to a sequel server db. The queries in access have been made into views in sql. ASP will be the front end. We are having problems passing an input from a form into a view. In access the parameter had to match what was selected on the form but sequel doesn't like it. At the moment, for testing purposes, the value has to be hard coded i.e. case_id = 64. Is there anything is ASP that can help?

Thank you

You stated that you're looking for something in ASP to help you... I assume you are using some version of ASP.NET?

If you are using ASP.NET 1.1, you have a number of options, the easiest of which is creating a SqlCommand or SqlDataReader object and filtering the rows on the parameters you use. You'll need to manually pass the value on your form into the parameter value.

However, if you're on ASP.NET 2.0, the solution is even simpler. Just create an instance of the SqlDataSource class and wire up a SelectParameter to the desired control.

Hope this helps...

Pass Date Range from VB to CR

Hi All,

i m Currently using VB,CR 9 and MS-Access

i have Create Cr report,

in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..

how can i do this..

AnyoneCan help me...

Thanx in Advance

Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.

Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String

Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print

Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)

Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub

Inside frmRPrint code section:

Option Explicit

Public Report As New ProdReport 'ProdReport.dsr is a Designer file

Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection

Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2

Screen.MousePointer = vbHourglass

Set adoc = New ADODB.Command
Set conn = New ADODB.Connection

conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn

strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

conn.Close
Set conn = Nothing

CRViewer1.ReportSource = Report

CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi

Thanx u very much..

If i get any error,than i will again ask u question.....again thnx

God with u always,

Regards,

Sabina|||hi

As u gave me Code of the passing Date range i did it

but when i use

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

this codding lines

it not show me database,AutoSetUnboundFieldSource crBMTName like these properties

my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?

tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine

Thanx in Advance

Regards,

Sabina|||Hi..

Instead of doing that one, try this one

XSTART = txtFrom.text
XEND = txtEnd.txt

Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report

REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"

Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...

Place 2 Formula Fields in yr report and in the editor write "Date".

Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"

Revert, if u still require assistance..

pass a value from form to report

I have a form on which the user enters a date range. I would like the date range to be displayed at the top of the report

like: For Period 08/01/2006 - 08/31/2006

Is it possible to pass a value from a form into a label in a report?

You can pass information to a report using parameters. The form fields must be named exactly like the report parameters. You could then set the value of the label to the parameter value passed in from your form.

I have enountered problems when a form contained server side .NET controls. (if there are 2 report parameters, there can only be 2 fields in your form... and .net adds 2 form fields automatically to handle post-back and state information) To get around this limitation I used querystring parameters instead of posting via a form. (there is also a parameter length limitation using this approach however)

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

Pass a collection to a SPROC

I am collecting companyID's form a data grid, I want to pass the selected values to a sproc via a variable. Any idea on the syntax?

this works using a query string within my code

WHERE (dbo.Promotions.ExpirationDate > GETDATE()) AND (dbo.Promotions.CompanyID IN (" + selectedCompanies + "))

this doesn't within my sproc

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (@.SelectedCompanies))

I also tried

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (SELECT @.SelectedCompanies))

and

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (' + @.SelectedCompanies + '))

ThanksI know it's a little dirty, but I just created an user defined function to parse out a comma delimited string, and return a recordset of one column. Select [code] FROM dbo.MyFunctionName(@.myCommaDelimitedString) can be used in your where clause.|||Thanks,
I will give that a try|||We've had this discussion several times in this forum -- there are various solutions and various advocates of the different solutions (e.g.view post 306752). I happen to use the one KraGiE suggests. Here's an example of the parsing function:Treat Yourself to Fn_Split().

Terri|||Thanks a TON..I search the other post, but I didn't know what to search for.
Thanks again