Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Passing Parameters To Stored Proc From Crystal Reports


Hi,

I've written reports based on stored procedures that receive parameters
and it usually seems straight forward.

Today I would like to use Crystal Reports parameter interface t prompt
for a "Date Range" and then pass the lower and upper date values to the
@StartDate and @EndDate of my SPROC's range.

Unfortunately when I try to build a report off of a SPROC havng two
dates it insists on prompting for date AND time indivually.

Is there a way to disable the "automatic prompting", use my own Date
Range parameter and then pass the results of the date range to the
stored procedure?

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Passing Parameters To Stored Proc


I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :

Where actv.ProjID + '-' + actv.Activity = @project

This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!

View Replies !   View Related
Passing A Comma Delimited String Of Parameters To A Stored Proc
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statementwhich in turn is usedto filter on or out particular rowsets.

I considered using a hidden string parameter set= " ' " + join(parameter.value, ',') + " ' "so that the hidden parameter would thencontain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

View Replies !   View Related
Crystal Reports Parameters From One Form To Other


hi friends,
i am having one crystal report from and one web application from which contains one crystal reportviewer i need to give the parameter in the web form and get the data for that parameter untill this its working fine for me.

my problem i need to display the value in the crystal reportwhich i gave in the webform.

View Replies !   View Related
Passing Multi-Valued Parameters Between Reports.


Hello,

I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.

I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.

Can any one help me with this. Its very urgent.

Thanks,
SqlNew

View Replies !   View Related
SQL Stored Procedure And Crystal Reports 8.5
Hi guys!

I am new in Crystal reports so please help me on this one.

I have to create a report that will bring back the TOP 20 customers per Sales Rep. My SQL stored procedure needs a parameter @SalesRep to execute. My stored procedure links between 2 tables. How do I get this stored procedure executed in Crysal Reports to give me results. The SP works fine.

THANKS!!

View Replies !   View Related
SQL Server Stored Procedure With Crystal Reports
Hello,

I have a stored procedure in SQL Server that I am trying to attach to a Crystal Report. When I try to attach the procedure, I get the attached error message. I have few more procedure that are working correctly. The only difference between those and this one is a procedure call... in this procedure, I call another procedure.

Any help you could provide is appreciated. E-mail me if you need further information.

View Replies !   View Related
Adding Field To Stored Procedure For Crystal Reports
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott

View Replies !   View Related
1 To 1, 1 To Many. Create One Stored Procedure For Crystal Reports(CR). For Any CR Users Also.
Situation: If possible, create one stored procedure for Crystal Reports(CR).For any CR users ou
there, looking for coding suggestion also. Thank you for your assistance.

Currently, CRhas a header(Report Header)coming from 1 to 1 tables, there is a parameter
whichispassed in, allowing it to retrieve one record for the header(report header or RH).

The CR then has 4 subreports in which each has its own stored procedure. This I believe happens
because the Report Header recordsrelationship to the subreport is 1 to many. The 3 remaining
subreports relationship to the Report Header is also a 1 to many. The main problem is the
subreports is that there may or may not have any records based on this, the subreport is
suppressedwithin CR and thus there can be alot of unused white space on the 1st page and one
of thesubreports prints on a 2nd page when it could have been on the 1st page.

Example:

Main Stored Procedure(sp)
RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.

Results of subreports and their associated procedure are varchar(8000) decriptions. Each line
should be counted in some manner in the stored procedure(sp) and thenshould be counted in CR
toavoid excess white space. Tocomplicate matters subreport 2to hasfont, bold, showboxbut
canhave different fontsizes.These variations could cause different line space requirements.
Anyideas?


Each lineshould be counted in some manner in the stored procedure and thenshould be counted
in CR. Thereis a count of records for each the main stored procedure.

RHhas 1 record tomany records in subreport 1 with same @xyz parameter.
RH has 1 record to many records in subreport 2 with same @xyz parameter.
RH has 1 record to many records in subreport 3 with same @xyz parameter.
RH has 1 record to many reords in subreport 4 with same @xyz parameter.

Because of the relationships, its seems impossible to create one stored procedure which give
inone select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought
concatinating i.e. 3 records together and then parsing it out some how in CR, along with
utilizing the i.e. 3 record count tohelp count lines. Thought of some how creating a temporary
table matrix for 8pt - 28pt for line and spacing considerations.









View Replies !   View Related
Stored Procedure Results Differ In ISQL/W And Crystal Reports
We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :

sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.

In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.

We use SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we get now rows in Crystal.

I enclose an example (with 3 sub- SPs). Help. Martin


CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),

--output from qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
@SumOfdEmployeeNIContractedOut FLOAT,

--output from qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT

SET NOCOUNT ON

DECLARE employee_period CURSOR
FOR
SELECT tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,
tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,
tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,
tblEmployees.dTotalGrossPayThis,
tblEmployees.dTaxableGrossPayThis,
tblEmployees.dTotalGrossPayPrevious,
tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,
tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate
FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)


DECLARE employee_ni_categories SCROLL CURSOR
FOR
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,
tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))

OPEN employee_period

FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,
@SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c @lUniqueID,
@dtQryPeriodEndDate,
@PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,
@PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH FIRST FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a @lUniqueID,
@szNICategory,
@dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,
@SumOfdEmployersNI OUTPUT,
@SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT

SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid,
@szNICategory AS szNICategory,
@SumOfdNIablePay AS SumOfdNIablePay,
@SumOfdEmployersNI AS SumOfdEmployersNI,
@SumOfdEmployeeNI AS SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut

FETCH NEXT FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
END
ELSE
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
END

CLOSE employee_period
DEALLOCATE employee_period

DEALLOCATE employee_ni_categories
RETURN

-----------------


CREATE PROCEDURE coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) = 'A',
@dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT OUTPUT,
@SumOfdContractedOutEarnings FLOAT OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),
@SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),
@SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),
@SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s),
@SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory = @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory

RETURN

-----------------------------

REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT = 1,
@dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000',
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT
@SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))


RETURN

--------------------

CREATE PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate DATETIME = '1900-01-01 00:00:00.000',
@PdTaxableGrossPayThis FLOAT OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET NOCOUNT ON
SELECT @PdTotalGrossPayThis = tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis = tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis = tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid = tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid = tblEmployeePeriodDetails.dSSPPaid

FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))


RETURN

View Replies !   View Related
1 T0 1 , Then 1 To Many Records(4x's). Create One Stored Procedure For Crystal Reports Usage.
Situation: If possible, create one stored procedure for Crystal Reports(CR).For any CR users out there, looking for coding suggestion also. Thank you for your assistance.

Currently, CRhas adetail line,I reference it here asReport Header but it is actually on
the detail line,coming from 1 to 1 tables, there is a parameterwhichispassed in, allowing
it toretrieve one record for the header(report header or RH).

The CR then has 4 subreports in which each has its own stored procedure. This I believe
happensbecause the Report Header recordsrelationship to the subreport is 1 to many. The
3 remainingsubreports relationship to the Report Header is also a 1 to many. The main
problem is thesubreports is that there may or may not have any records based on this, the
subreport issuppressedwithin CR and thus there can be alot of unused white space on
the1st page and oneof thesubreports prints on a 2nd page when it could have been on the
1st page.

Example:

Main Stored Procedure(sp)
RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.

Results of subreports and their associated procedure are varchar(8000) decriptions. Each
lineshould be counted in some manner in the stored procedure(sp) and thenshould be
counted in CRtoavoid excess white space. Tocomplicate matters subreport 2to hasfont,
bold, showboxbutcanhave different fontsizes.These variations could cause different
line space requirements.Anyideas?


Each lineshould be counted in some manner in the stored procedure and thenshould be
countedin CR. Thereis a count of records for each the main stored procedure.

RHhas 1 record tomany records in subreport 1 with same @xyz parameter.
RH has 1 record to many records in subreport 2 with same @xyz parameter.
RH has 1 record to many records in subreport 3 with same @xyz parameter.
RH has 1 record to many reords in subreport 4 with same @xyz parameter.

Because of the relationships, its seems impossible to create one stored procedure which give
inone select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought
concatinating i.e. 3 records together and then parsing it out some how in CR, along with
utilizing the i.e. 3 record count tohelp count lines. Thought of some how creating a
temporarytable matrix for 8pt - 28pt for line and spacing considerations.

View Replies !   View Related
Passing Value From Stored Proc To Another
Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)

create proc upGetDates
as
declare @start datetime ,
@end datetime

select@start = '2001-01-01' ,
@end = '2007-12-31'
go

I want to reuse this proc many times for different reports and using the date values in the calling sp

an example in pseudo code would be

create proc usedates
as
declare@rstart datetime ,
@rend datetime

exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go

Not sure how to 'trap' the dates from the called proc

Can it be done ?

Thanks in anticipation

View Replies !   View Related
Passing A Column Into A Stored Proc?
I'm writing a simple voting script and have columns for each options.  I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement?  There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance 

View Replies !   View Related
Passing Xml To Stored Proc Issues.
Hi,

I am passing a xml data to a stored proc on SQL2005 using ADODB.

The XML is pretty short.

'<AMS><DeviceList><Device AmsTag="PDT1" /><Device AmsTag="PDT2" /></DeviceList></AMS>'

Ado Command.Execute generates a Com Exception.

System.Runtime.InteropServices.COMException (0x80040E14): UTF-8 at ADODB.CommandClass.Execute(Object& RecordsAffected, Object& Parameters, Int32 Options) at ALMFile.Form3.Form3_Load(Object sender, EventArgs e) in C:AMSCodesrcprasraoALMUtilsALMFileALMFileForm3.cpp:line 55

The parameter in the stored proc is set to NText while on the ADO side it iset to ADODB.DataTypeEnum.adVarChar

I have tried searching for the meaning of the msg but could not find it.

Interstingly if I execute the SP on the server and pass the data thru Exec , it works fine.

Also the VS2005 if I step into proc with same data it works fine.

This has me totally confused.

Prasanna













View Replies !   View Related
SQL2005 Passing GETDATE() As A Stored Proc Parameter
What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View Replies !   View Related
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.

View Replies !   View Related
Passing Multiple Selections To A Stored Proc Parameter
Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View Replies !   View Related
Passing Table Variable To Stored Proc / Function
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View Replies !   View Related
Passing Datetime Variable To Stored Proc As Parameter
Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar taskpassing those variables as parameters,I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View Replies !   View Related
URGENT: REPORTS Not Working In SQL Report Srvr &&amp; Crystal Reports (moved Server)

Reports not working at all:
Need to move full ASp.Net application(VS.Net 2003) /SQL Server 2000 DBto another machine. The Web application & its SQL Database is successfully moved and functioning but am unable to access any of the reports from within it. The

reports are in: A) SQL server report server & B) Crystal reports

For A)-SQL SRVR-RPT SRVR: Installed SQL Server2005 Express version- with reporting srvr(advance mgt). The report server is working well- from IIS i can open both http://..reports and http://...reportserver links without error.
But when i click on a link in the ASP.Net application it gives error: /HRNE_REPORTS/rpt_employees does not exist( rsitemnotfound) .
1.In http://..reports configration I have made a folder HRNET_REPORTS. But how do i put the individual reports? There is no .RDL file in the application folder only .aspx files for the reports exist.

2.Do i need to restore the tworeporting server databases from the old machine to the new one and have this new 2005 reporting server use these 2 databases instead? If YES - then i hope sql express' reporting server will be able to point to the OLD (sql 2000) database (restored with replace option)??

For B)- CRYSTAL REPORTS: Have not installed anything special on the new machine.
The reports give some key problem (Crystal decisions...key..)- I can tell u later the exact error.
Since i dont have VS.Net and crystal reports 9 softwares but i think i can have 'Busines Objects XI software' - Will installing the latest 'Business Objects- Crystal reports' solve this??NEW MACHINE: windows XP-professional(so could'nt install sql 2000).; SQL express 2005

View Replies !   View Related
Passing Parameters Into Stored Procedure
I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors.  How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server"            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink"            SelectCommandType="StoredProcedure">            <SelectParameters>                        <asp:Parameter Name="Zip" Type="string" />             <asp:Parameter Name="MenuCode" Type="double" />                               </SelectParameters>
 

View Replies !   View Related
Passing Parameters To A SQL Stored Procedure
I need help with a really basic task.
I have a formview that works OK for selecting/updating records in the table.  When the user edits the data and clicks submit, the updates post to the table along with two parameter values I add using this in the code-behind:
Protected Sub DS_EditMasterOrg_Updating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles DS_EditMasterOrg.Updating
e.Command.Parameters("@Last_Update_By").Value = User.Identity.Name
e.Command.Parameters("@Last_Update_Dt").Value = System.DateTime.Now
End Sub
The next step, where I am stuck, is if the data is OK and does not need to be updated I have a button the user can click to indicate that fact.  I am trying to use the button's onclick event handler to trigger a stored procedure to update those two fields in the record.  The stored procedure expects these two parameters and executes correctly when they are supplied.  I can't figure out the correct syntax of the statements to plug into the onclick event handler that will send the two parameters to the SP.  What I have so far is simply this (which obviously doesn't work):
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=GV88Z91;Initial Catalog=contests;Integrated Security=True")
Dim cmd As New Data.SqlClient.SqlCommand
Dim rowsAffected As Integer
Dim MOID As String = Profile.MasterOrgId
Dim user As String = Context.User.Identity.Name
With cmd
.CommandText = "Director_VerifyMasterOrg"
.CommandType = System.Data.CommandType.StoredProcedure
.Connection = sqlConnection1
.Parameters.Add("MOID")
.Parameters.Add("user")
.Parameters("MOID").Value = Profile.MasterOrgId
.Parameters("User").Value = Context.User.Identity.Name
End With
sqlConnection1.Open()
rowsAffected = cmd.ExecuteNonQuery()
sqlConnection1.Close()
 
Thanks in advance for the assistance.

View Replies !   View Related
Passing Parameters To Stored Procedures
Hello,

I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.

I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.

Thanks for your help, Gary

Here is the web code:


Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)

cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))

cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue

conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()


Here is the stored procedure:



CREATE PROCEDURE s_ItemLookUp

@Field AS VARCHAR(50),
@Value AS VARCHAR(50)

AS


SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO

View Replies !   View Related
Passing Parameters To A Stored Procedure
Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)

The value I'm passing (@MyLSN) is 24/12359 (one LS Number)

The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))

The value I'm passing (@MyLSN) is '24/12359','24/12360'

This is where the problem begins because the stored procedure now doesn't return a recordset.

If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.

So if someone could point me in the right direction then that would be much appreiciated.

Thanks in advance.

View Replies !   View Related
Passing Parameters To Stored Procedure Using ADO
I have created a form that contains a textbox (txtState), button (cmdRun), and listbox (lstResults). I want to use the txtState textbox to supply a parameter to a stored procedure named (test), and then execute the stored procedure by clicking the (cmdRun) button which will display the results in the lstResults listbox.

I have looked around everywhere for a nice simple answer, but only seem to find complex answers. Could someone please provide a nice simple solution. Here is the code for my stored procedure. It's the ADO code that I am having a great deal of trouble with.

create procedure "test" @s nvarchar(2)
as
select states from customers
where states = @s

View Replies !   View Related
Passing Parameters To A Stored Procedure
Hey people,

I am trying topass a parameter to a stored procedure based on aselect statement. Basically it's something like this (in my head :P ):

foreach PersonID in (SELECTid FROM person)
{
MyStoredProcedure(PersonID);
}

Multiple rows will be returned from the select statement.

The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?

Thanks to you all!

View Replies !   View Related
Comparison Of SQL Server Reporting Services To Access Reports, Crystal Reports, Cognos Or Other Options
Hello SQL Server Experts, Data Analysts, and Report Writers et al:

re: Reporting Options with SQL Server

I wanted to propose an offshoot to the pryor thread:

Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?

I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?

Thank you to all, and I hope this is a beneficial discussion to others.

Hal1490



Hal9000

View Replies !   View Related
Too Many Parameters In Stored Proc?
I am creating a stored proc in SQL7 with my front end coded in VB6Pro. My
question is this: How many parameters can be passed to a stored proc before
performance really suffers? I know, in theory, it doesn't matter... I have
16 parameters that the stored proc will evaluate and insert into a table.
Would there be a more efficient way for me to do this? I need to enforce
several rules through this SP, and the insertions must be compatible with
both my front end app, as well as my web site...

What do you all think?

Thank you very much.

View Replies !   View Related
Passing Classes As Parameters To CLR Stored Procedure?
Is this even possible? I want to pass a class I made as the one parameter to my CLR stored procedure and it gave an error saying it wasn't a dbtype. So I changed the parameter to type Object and the procedure successfully deployed. But now I have no way of passing my class to the procedure since adding a new parameter does not allow type Object. I have tried serializing the object as passing it as type Binary but I am get security permission errors even with the assemblies set to UNSAFE.

View Replies !   View Related
Running A Stored Procedure Without Passing Parameters
HI all, I'd like to run a simple stored procedure on the Event of a button click,  for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks. 

View Replies !   View Related
Passing Parameters To Sqldatasource Stored Procedure
Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a  stored procedure.
Thanks, M.

View Replies !   View Related
Passing Parameters To Extended Stored Procedures
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!

View Replies !   View Related
Passing SQL Clauses As Parameters To A Stored Procedure
Hi,

I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.

I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as

"WHERE lastname LIKE 'A%' "

"ORDER BY lastname "

I need to assign the entire clause because the query may or may not use a particular clause.

I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?

Thanks in advance for your opinion, suggestion, criticism, etc.

--Tom.

View Replies !   View Related
Passing Parameters To Stored Procedures From Access 8.0
We are trying to pass a parameter to a SQL Server stored procedure but are unsure of the syntax. We cannot find a relevant example anywhere but can find articles saying it can be done. Can anyone please help ? The portion of code we have is below:-


Dim wrkODBC As Workspace, cn As Connection
Dim dbs As Database, rstSectors As Recordset, qdfSectors As QueryDef
Dim rstTeamsNeeded As Recordset, qdfTeamsNeeded As QueryDef
Dim criteria As String, SQL_UID As String, strSQL As Variant

DoCmd.Hourglass True

Set dbs = CurrentDb()
SQL_UID = current_UID() 'This is the parameter we want to pass

Set wrkODBC = CreateWorkspace("", "sa", "", dbUseODBC)
Set cn = wrkODBC.OpenConnection("NewConnection", dbDriverNoPrompt, False, "ODBC;DATABASE=sql_tos;UID=sa;PWD=;DSN=sql_tos" )

Set rstTeamsNeeded = cn.OpenRecordset("usp_TeamsNeeded", dbOpenDynaset) 'usp_TeamsNeeded is the SQL Server stored procedure how can we also pass it 'the SQL_UID parameter

Do While Not rstTeamsNeeded.EOF
Debug.Print rstTeamsNeeded!fs_UID
Debug.Print rstTeamsNeeded!Forename
Debug.Print rstTeamsNeeded!Surname
Me.fs_UID = rstTeamsNeeded!fs_UID
Me.fs_forename = rstTeamsNeeded!Forename
Me.fs_surname = rstTeamsNeeded!Surname
rstTeamsNeeded.MoveNext
Loop


It comes up with a DAO connection error message on the line:-

Set rstTeamsNeeded = cn.OpenRecordset("usp_TeamsNeeded", dbOpenDynaset)


The stored procedure is set to receive a @UID char(7) parameter.

Any help would be massively appreciated.

Paul

View Replies !   View Related
Passing Two Or More Parameters To An Update Stored Procedure From VB6 Using ADO
I am trying to pass two parameters to a stored procedure using the code below:

Dim cmd As New Command
Dim rs As New Recordset
Dim prm As New Parameter
Dim ttt As New Parameter

cmd.ActiveConnection = cnn1
cmd.CommandText = "{call usp_PlcwithinUID_upt(?,?)}"
cmd.CommandType = adCmdStoredProc

Set prm = New Parameter
prm.Type = adInteger
prm.Value = gsPlcCode
cmd.Parameters.Append prm

Set ttt = New Parameter
ttt.Type = adVarChar
ttt.Value = "TEST"
cmd.Parameters.Append ttt

'cmd(1) = gsPlcCode
'cmd(2) = "Test"

Set rs = cmd.Execute

It works when passing one but NOT two ?? Can anyone shed any light ??
I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?

The stored procedure just performs an update on a table.

Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.

Any clues as to how to get an update stored procedure to except multiple variables.

I have trawled through loads of documentation to no avail.

Please help.

Paul

View Replies !   View Related
Passing Empty Parameters To Stored Procedures
Hi

I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.

IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..


CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I


Thanks
VENU

View Replies !   View Related
Problems Passing Parameters To A Stored Procedure
hi there!
i have problems trying to pass parameters from a VB application. every time i run it this error occurs "error 3708; the application has defined wrong a parameter object"

the stored procedure is this:

CREATE PROCEDURE [ingresar sociedad]
@rut char(9),@nombre char(50),@giro char(50), @dir char(50), @fono char(15), @cont char(35), @url char(50), @mail char(50) AS
insert into sociedad (rut,nombre,giro,direccion,fono,contacto,url,email _contacto,empresa) values (@rut, @nombre,@giro,@dir,@fono,@cont,@url,@mail,7)


and the code in the app is:

Private Sub Command2_Click() 'nuevo

Set cn = New ADODB.Connection
Set adors = New ADODB.Recordset

cn.ConnectionString = "Provider=SQLOLEDB.1;Password=keno;Persist Security Info=True;User ID=prueba;Initial Catalog=SE;Data Source=keno"
cn.Open

Set cm = New ADODB.Command
Set cm.ActiveConnection = cn
cm.CommandText = "[ingresar sociedad]"
cm.CommandType = adCmdStoredProc

Set p = cm.CreateParameter("prut", adChar, adParamInput, , Text1.Text)
cm.Parameters.Append p
Set p1 = cm.CreateParameter("pnombre", adChar, adParamInput, , Text2.Text)
cm.Parameters.Append p1
Set p2 = cm.CreateParameter("pgiro", adChar, adParamInput, , Text3.Text)
cm.Parameters.Append p2
Set p3 = cm.CreateParameter("pdir", adChar, adParamInput, , Text4.Text)
cm.Parameters.Append p3
Set p4 = cm.CreateParameter("pfono", adChar, adParamInput, , Text5.Text)
cm.Parameters.Append p4
Set p5 = cm.CreateParameter("pcont", adChar, adParamInput, , Text6.Text)
cm.Parameters.Append p5
Set p6 = cm.CreateParameter("purl", adChar, adParamInput, , Text7.Text)
cm.Parameters.Append p6
Set p7 = cm.CreateParameter("pmail", adChar, adParamInput, , Text8.Text)
cm.Parameters.Append p7

Set adors = cm.Execute

cn.Close
Set adors = Nothing
Set cn = Nothing

End Sub


by the way i'm connecting to a database called SE in a SQLServer named KENO; inicio de sesion = prueba; password = keno

View Replies !   View Related
Passing Parameters To MySQL From A Stored Procedure


I am trying to pass in parameters from MSSQL Store procedure to a MySql database but it doesn't take the parameters? Any idea how to achieve this? The query works if I pass in the actual dates.

Create procedure [dbo].[MY_STORED_PROC]

@STime datetime,
@ETime datetime

AS

select *
into #MyTempTable
from openQuery(MYSQL, '
SELECT a.* FROMmytable a
where a.createdate between @STime and @ETime
')

View Replies !   View Related
Passing Different Number Of Parameters To A Stored Procedure
Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

View Replies !   View Related
How Do Use Stored Proc Passing Parameter From Table In Selcet Query Statement
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.

for example i want to use

select p1 = sp_diff d1,d2 from table1

sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value

View Replies !   View Related
Optional Parameters In A Stored Proc?
Title speaks for itself really.
Is it possible to write a stored proc with optional parameters?
For example consider the following SELECT

SELECT FLD1, FLD2 FLD3 FROM TBL1

I'd like to add optional parameters to that statement so that if they wanted to narrow down the results by providing criteria for some fields they could - but didn't have to.

Is this possible?

View Replies !   View Related
Stored Proc Default Parameters
My stored procedure has several parameters and most of them are defaulted to NULL. The functionality of the stored procedure is simple. It updates a row in a table with all the supplied values. But I am encountering a peculiar problem.

If someone wants to update col1 with new value, they pass it to the proc. Col2 has an existing value and since it is not passed, it is defaulted to NULL. If I update all the columns with the parameter values, the existing value in col2 is lost. I took care of this problem by using COALESCE () function. If the column has a NON NULL value, it will be retained. But the problem arises when the user wants to update the column with NULL. How would I know if the NULL value to this parameter is passed by the user or it is the defaulted one? I thought of defaulting it to a NON NULL value, 'xyz', and then checking it before updating. But it is too much of work as the table has hundreds of columns and also I didn't like the idea.

I would appreciate if someone could give me an idea to handle this situation.

Thanks in advance,
Varma

View Replies !   View Related
Stored Proc - Unknown Parameters
If a procedure is known but all parameters are not known, can I handle this using CommandBehavior.RetrieveParameters.
If yes, do I need to incur an extra round trip to server.

View Replies !   View Related
Passing Multiple Parameters To Stored Procedure Using SqlDataSource
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View Replies !   View Related
Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter
Hello,
I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related.
The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated)
When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
 
Code:
.
.
<td style="width: 3px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"
Width="533px">
<Columns>
<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"
SortExpression="LogId" />
<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />
<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />
<asp:TemplateField>
<ItemTemplate>
<span>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>
</ItemTemplate>
</asp:TemplateField>
 
</Columns>
<HeaderStyle BackColor="#608FC8" />
<AlternatingRowStyle BackColor="#FFFFC0" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"
SelectCommand="spViewLog" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Stored Procedure:
ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )
 
AS
SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')
RETURN
 
I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.
 Any guidance would be much appreciated.
Thank you
Lee
 
 

View Replies !   View Related
Passing In Variable Number Of Parameters To A Stored Procedure
I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View Replies !   View Related
Passing Parameters To Stored Procedures With EXEC() Statment
So I've got some stored procedures in my database that use the EXEC() statment to execute a dynamic string query. Anyway...for some reason RS2005 does not seem to be able to recognize which parameters need to be passed into these stored procedure, so I have to manually type them into on the dataset properties window->parameters tab. Anyway, it's more a nuisance than anything else because I can still get this to work, but does anyone know a workaround? I'm looking for some way to have RS know what the parameters are...

View Replies !   View Related
Passing Parameters To A Stored Procedure In Visual Basic
Hi peeps,

I need some help with passing parameters to a stored procedure from my visual basic code.

Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.

I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.

The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.

The code for the procedure is listed below:


/*

Name: usp_display_all_users

Description: Displays activeuser, personid, comment from table: pswds

Userid and sort from table: people

Where the username is like the parameter supplied.

Both tables joined on personid

Author: Iain Blackwood

Modification log: Change

Description Date Changed by

Created proc 02/01/07 Iain Blackwood

*/

ALTER PROCEDURE usp_display_all_users

(

@searchStr nvarchar(128) =''

)

AS

SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

FROM dbo.pswds INNER JOIN

dbo.people ON dbo.pswds.personid = dbo.people.personid

WHERE (dbo.people.sort LIKE @searchStr + '%')

ORDER BY dbo.people.sort

 

The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.

I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.

Private Sub fillDataGrid()


' I NEED TO:

' 1: Fill the data set with all Accounts

' 2: Diplay the Data to the data grid

' delcare a new SQL connection

sqlCon = New SqlConnection(conStr)

' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE

Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"

comStrPeople += " FROM pswds INNER JOIN"

comStrPeople += " people ON pswds.personid = people.personid"

comStrPeople += " ORDER BY sort"

' Display the command string: TEMPOARY

testlbl2.Text = comStrPeople

' Declare a new SQL data adapter

sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)

Try


' Declare a new dataset

sqlDataSet = New DataSet

' fill the sql data adapter with data from dataset: called PeoplePswds

sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")

' Fill the forms datagrid view with data from the Dataset table PeoplePswds

DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

Catch ex As Exception


' Display suitable error message

MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)

End Try

End Sub

 

I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.

Thanx Flakkie

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved