How Can I Return Everything While Using A Paramater

Jun 26, 2007

I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything.

Simple example:

Select * from Reports
Where ReportID = @ReportID 

 If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by

Select * from Reports
Where ReportID = ''

Whis not what I want. What I would want is simply

Select * from Reports

or

Select * from Reports
Where ReportID = ReportID

So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause?

Really need hlpe on this and hope I've made my question reasonably clear. Thanks.

 

 

View 3 Replies


ADVERTISEMENT

How Do I Pass A Paramater?

Oct 19, 2006

Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()

Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader

' Define connection object
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

' Define query to retrieve main category values
Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory"
' Define command object
SqlComm = New SqlCommand(Query, Conn)

' Open connection to database
Conn.Open()

' Create DataReader
myDataReader = SqlComm.ExecuteReader()

' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader("productMainCategory"))
End While

' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing

' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If

End Sub
The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing             SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..

View 4 Replies View Related

Using Output Paramater

Nov 5, 2007

Hi all
         In my application i have to upload a excel file.After that it will retrive tha data from database depending upon the data in excel sheet.So now the problem is that i am using a stored procedure for this and in that procedurei have taken output parameter to catch any type of error.For example suppose in my excel sheet i have 3 columns.let it Group Code,Employee No and Employee Code.So incase it will not find the employee number so using the output parameter i am throwing the error message.So here we are getting two return types.one is the selected data and another is the error message.So how to handle these two thing in asp.net.Usong a dataset i can only retrive the selected data.But how to get the errror message as well as the data.So please help me.

View 1 Replies View Related

Trigger And Paramater

May 24, 2004

is possible for a trigger to check a particular record by passing a parameter during an update event?

if yes, what is the syntax?

View 3 Replies View Related

Parce A Db Paramater / Value

Apr 20, 2007

I need to parce a criteria in the data base
I have a field/colum in my tbl1 call invoice_num
I am left joining to to site_id on tbl2.
how can i talk tbl1 invoice_num and parce it to add in numbers.

IE tbl1.invoice _num is AF3456 and in order to match it to tbl2.site_id I need to add a 0 so that it would be AF03456 or AF034560.

Hope i did not loose you on this question.

View 2 Replies View Related

Multiple Paramater Search

Sep 23, 2005

I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...

View 2 Replies View Related

Removing A Paramater Gives An Error

Feb 6, 2006





Removing a parameter on a Reporting services report based on a 2005 AS cube results in an error:

[rsParameterReference] The Value expression for the query parameter €˜KalenderHierarchieJaarMaand€™ refers to a non-existing report parameter €˜KalenderHierarchieJaarMaand€™.

On the dataset, the parameter still exists. The error still occors after removing the parameter overhere too.

Steps to reproduce:

Build a raport based on a cube. Make a parameter in the dataset. check if the parameter works. Save the report and remove the parameter via the menu: Report> Report parameters > select the parameter and choose [Remove]. go to the "Preview" tab.

Has anyone found a work-around for this?

Thanks in advance,

Pieter

View 2 Replies View Related

Ora-12645 Paramater Does Not Exist

Apr 24, 2007

I get the ora-12645 error when doing an import from oracle to mssql. The error comes-up when setting up the datalink properties of the source database which is oracle. This error happens on a 2003 server machine using the import/export wizard. It works fine when using an xp machine.

any ideas?

View 19 Replies View Related

Retrieving Output Paramater After Insert

Sep 14, 2006

Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control.  The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later.  I'm having trouble getting to this return value via the SQLDataSource .  Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?

View 1 Replies View Related

I Am Passing A Stored Procedure A Paramater...how Do I

May 22, 2000

make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.

Any help is GREATLY appreciated. Thanks.

Robert

View 1 Replies View Related

Stored Procedure Sebquery As A Paramater

Jul 23, 2005

Ok,This sounds dangerous (and yes I know it is)But its in a controlled enviroment and I really need to know how to dothis.How can I pass a Subquery for an Exist or In clause as a paramaterSomething like thisCREATE procedure dbo.mytry@funk varchar(1000)asSelect * from Customers where exists(@funk)GOSo I would execute something like soexec mytry @funk='Select ID From Customers where ID < 100'Any Ideas, I have tried LOTS of things but I can actually get it towork.I need to use it conjunction with a 3rd party product that can onlyselect from a Stored Procedure, and I can only pass paramaters to theSP.Any suggestions ?ThanksChris

View 6 Replies View Related

Deselect All On Multivalue Paramater When Other Value Is Chosen

Dec 26, 2007



I use "All" as default value in my parameters, my parameters are multivalue.
Now if a user chooses a value it will mark the value but the All will also remain marked. Isn't there a way that it deselects the all when one or more individual values are chosen ?

I thought that the reporting services just didn't have that functionality but someone told me that its possible but i haven't found it anywhere.

View 3 Replies View Related

To Use Multiple Value Paramater In The IN List Of A Where Clause

Jan 8, 2008

I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)

It gives me an error. any way to make this work.

View 14 Replies View Related

SPROC Output Paramater Asking For Input?

Jan 3, 2008

I am trying to get a stored proceedure to return the autogenerated numerical primary key of the last row created to our appliciation. I have created what I thought was an output parameter to handle this however when the application runs I get a message that seems to indicate that it is ASKING for the parameter instead of returning it. Here is the code of the sproc:




Code Block
USE [chronicle]
GO
/****** Object: StoredProcedure [dbo].[CreateNewLicense] Script Date: 01/03/2008 06:35:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateNewLicense]

@VendorId int,
@PoId int,
@LicenseTypeId int,
@LicenseUserId int,
@LocationId int,
@LicenseStartDate smalldatetime,
@DaysAllowed int,
@SerialNum varchar(50),
@ActivationKey varchar(50),
@MaxUsers int,
@Comments varchar(1000),
@LicenseId int OUTPUT
AS
BEGIN
INSERT INTO license
(vendor_id,
po_id,
license_type_id,
lic_user_id,
location_id,
lic_start_date,
days_allowed,
serial_num,
activation_key,
max_users,
comments
)
VALUES
( @VendorId,
@PoId,
@LicenseTypeId,
@LicenseUserId,
@LocationId,
@LicenseStartDate,
@DaysAllowed,
@SerialNum,
@ActivationKey,
@MaxUsers,
@Comments
)
SELECT @LicenseId = @@IDENTITY
END





View 5 Replies View Related

Paramater Use For Both Relational And Analysis Services?

Nov 9, 2007



Does anyone know if it is possible to have one parameter that is used for both a relational(SQL) and Analysis Services(MDX) query within one report? I need to pull up-to-date inventory counts for a product, which are not in my Analysis Services cube I also have related Product information in Analysis Services that I need in the same report. I can't seem to find any information about how to do this or if it is even possible...

Frank

View 3 Replies View Related

OUTPUT Paramater From Stocked Procedure Sent To SDS Not Working

Mar 16, 2007

Juste have fun with the following, if you can manage this out your my savior, been wasting far too much time on it, tried a couple of method and none ever worked. Conversion from type 'DBNull' to type 'String' is not valid.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code. Exception Details: System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.Source Error:




Line 105:Line 106: Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.InsertedLine 107: Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)Line 108: End SubLine 109:          With sdsProprActionAJO            .ConnectionString = ConfigurationManager.AppSettings("DB_CS_MSSQL")            .InsertCommandType = SqlDataSourceCommandType.StoredProcedure            .InsertCommand = "dbo.sp_adm_cartListe"            .InsertParameters.Clear()            .InsertParameters.Add("Mode", TypeCode.String, "SEL")            .InsertParameters.Add("LPR_Groupe", TypeCode.String, CBool(CType(Me.pnlProprActionAJO.FindControl("rblProprActionAJOGroupe"), RadioButtonList).SelectedValue))            .InsertParameters.Add("LPR_Etiquette", TypeCode.String, CType(Me.pnlProprActionAJO.FindControl("tbProprActionAJOEtiquette"), TextBox).Text)            .Insert()        End With    End Sub    Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.Inserted        Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)    End Sub    Protected Sub sdsProprActionAJO_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sdsProprActionAJO.Inserting        Dim Param As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter()        param.DbType = DbType.String        param.Direction = ParameterDirection.Output        param.ParameterName = "SQL_MSG"        e.Command.Parameters.Add(Param)    End Sub CREATE PROCEDURE [dbo].[sp_Adm_Proprietes_AJO](@MODE        VARCHAR(3),@LPR_Groupe        BIT = NULL,@LPR_Etiquette    NVARCHAR(32) = NULL,@RPP_LPR_IdParent    [INT] = NULL,@RPP_LPR_IdEnfant    [INT] = NULL,@SQL_MSG        NVARCHAR(8)  OUTPUT)AS--DECLARE @SQL_TEMP NVARCHAR(4000)DECLARE @INT_TEMP [INT]--IF @MODE = 'RRP'BEGIN    INSERT INTO t__REL__PropParent_PropEnfant (        RPP_LPR_IdParent,        RPP_LPR_IdEnfant    ) VALUES (        @RPP_LPR_IdParent,        @RPP_LPR_IdEnfant    )END--IF @MODE = 'LPR'BEGIN    IF NOT @LPR_Etiquette = NULL        BEGIN        SELECT * FROM t_Lib_Proprietes  WHERE (LPR_Groupe = @LPR_Groupe ) AND (LPR_Etiquette = @LPR_Etiquette )            IF @@ROWCOUNT <= 0        BEGIN            INSERT INTO t_Lib_Proprietes (                LPR_Groupe,                LPR_Etiquette            ) VALUES (                @LPR_Groupe,                @LPR_Etiquette            )            SET @SQL_MSG = 'CON_000A'        END        ELSE        BEGIN            SET @SQL_MSG = 'ERR_000A'        END    END    ELSE    BEGIN        SET @SQL_MSG = 'ERR_003A'    ENDENDRETURN @SQL_MSGGO 

View 8 Replies View Related

Passing In Query Paramater Values Through WebUserControls

Jun 7, 2007

Hi, I don't want to pass in parameter values in the url to get a certain record from my database (for a better search engine result) so instead i'm using webuser controls (this is a must) with Public Values that should be read by the SqlDataSource. So in the page that contains the usercontrol there is something like:UserControl.Value = "myvalue"Now I want the SqlDataSource in the usercontrol to read this value and pass it into a query (in the parameter of the WHERE statement, eg.: @recordID = myvalue).Thanks in advance! 

View 1 Replies View Related

New To SSRS - Pass Query Paramater At Runtime

Jun 21, 2007

Hi all,

Im new to SSRS, basically I have created and deployed a report to my reporting server.

Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.

Thanks for your time.
Pace

"Impossible is Nothing"

View 1 Replies View Related

How To Pass Table Name As Paramater In Stored Procedure

Feb 5, 2008

I am using SQL2005 and would like to know if it is possible to pass a table name as an input parameter in a stored procedure?? I am struggling to get it to work.

For example:

CREATE PROCEDURE dbo.StartBlock

@TableName varchar(30),

@minX float OUTPUT,



AS



BEGIN

SET NOCOUNT ON;





SET @minX = (SELECT min(X) as mX

FROM @TableName);



END

GO

The above does not work and give the following error

Must declare the table variable "@TableName"

Thanks
Christie

View 5 Replies View Related

New To SSRS - Pass Query Paramater At Runtime

Jun 21, 2007

Hi all,

Im new to SSRS, basically I have created and deployed a report to my reporting server.

Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.

Thanks for your time.
Barry Andrew

View 3 Replies View Related

Easy Question, How Do I Put A Paramater In Stored Proc, With A Memory Table

Apr 11, 2008

All i want to do is add a parameter for Territory_code how do i do that, in a stored procedure that uses a memory table.
the yellow shows the two places i thought i should be able to include it. Any help will be greatly appreciated!! thanks!
I couldnt post the whole code, so i deleted alot , just need to know where to put the param!




Code Snippet

USE [RC_STAT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PROC_RPT_Breeder_Sales]
AS (@Territory varchar(20)
BEGIN
SET NOCOUNT ON;

Declare @ReportingTbl TABLE (
Source_Id int,
Territory_Code varchar(20),
Territory_Description varchar (30),
Sort_Id int,
Column_Text varchar(20),
Subbrand_Key int,
SubBrand_Description varchar (30),
Period_1 Decimal(18,0),
Period_2 Decimal(18,0),
Period_3 Decimal(18,0),
Period_4 Decimal(18,0),
Period_5 Decimal(18,0),
Period_6 Decimal(18,0),
Period_7 Decimal(18,0),
Period_8 Decimal(18,0),
Period_9 Decimal(18,0),
Period_10 Decimal(18,0),
Period_11 Decimal(18,0),
Period_12 Decimal(18,0),
Period_13 Decimal(18,0),
YTD Decimal (18,0),
Total_Amount decimal (18,0))
------------------------------------------------------------------------------------------------------------------------------------------
----Distributor Load Section
------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO @ReportingTbl
(Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount)
SELECT
1 as Source_Id,
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
CASE WHEN cusSales.Customer_Sales_Summary_Year = YEAR(GETDATE()) THEN 2 ELSE 1 END as Sort_Id,
cusSales.Customer_Sales_Summary_Year Column_Text,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13,
0 as YTD,
SUM (cusSales.Customer_Sales_Summary_Amount) As Total
FROM RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension
INNER JOIN Tbv_Customer ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code = Tbv_Customer.Breeder_Territory_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales
ON Tbv_Customer.Customer_Code = cusSales.Customer_Code
INNER JOIN Qry_Report_Level_Brand
ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id
WHERE
cusSales.Bill_Customer_Code NOT IN ('RNPROC','RNPROF')
AND cusSales.Sub_Brand_Id <> 65 AND cusSales.Report_Level_Id = 85
AND cusSales.Report_Level_Id = 85
AND cusSales.Consolidated_Sales_Tables_Id = 6
AND cusSales.Customer_Sales_Summary_Year >= YEAR(GETDATE()) - 1
GROUP BY
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
cusSales.Customer_Sales_Summary_Year,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description


SELECT
Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount
FROM @ReportingTbl
-- ORDER BY Source_Id, Sort_Id
where Territory_Code=@Territory
END





View 6 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Return Value From SP

Dec 6, 2006

I have the following SP
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO
When  calling it from code like this:            MyConnection.Open()            cmd = New SqlCommand("spGetResettedPassword", MyConnection)            cmd.CommandType = Data.CommandType.StoredProcedure            myTrans = MyConnection.BeginTransaction()            cmd.Transaction = myTrans            fBeginTransCalled = True            bSuccess = False            cmd.Parameters.Add(New SqlParameter("@Email", Email))            Dim NewPassword As Object = cmd.ExecuteScalar            If NewPassword <> Nothing And CType(NewPassword, String) <> "" _            And TypeOf (NewPassword) Is String Then                Return CStr(NewPassword)            Else                Return "ERROR, CONTACT US ON THE SITE"            End If            bSuccess = True            fBeginTransCalled = False
 
I get the following error:
Conversion failed when converting the nvarchar value '40DC5F3' to data type int.
What is happening here?!?!

View 1 Replies View Related

Return Value From SQL

Jun 14, 2008

I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE     BEGIN              INSERT INTO table1 (nameA, NameB)              VALUES (@nameA, @nameB)          RETURN 03     END ****************************************************************************                 dim cn as New sqlConnection(connectionstring)                 dim cm as sqlcommand            cn.Open()            With Cm                .CommandType = CommandType.StoredProcedure                .CommandText = "STOREDPROCEDURE"                .Connection = cn            End With            'INSERT Names            Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text            Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text            param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int)            param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.*******************************************************************************************            dim insertStatus as integer            insertStatus = Cm.ExecuteScalar()            If insertStatus = 01 Then                warningLabel.Text = "NameA AlreadyExists."                If insertStatus = 02 Then                    warningLabel.Text = "NameA AlreadyExists."                    If insertStatus = 9999 Then                        Response.Redirect("success.aspx")                    End If                End If            End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.

View 2 Replies View Related

Return

May 18, 2004

whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry
(
@name varchar( 50 ) = NULL,
@alt varchar( 24 ) = NULL,
@code varchar( 3 ) = NULL,
@id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code )
@id = @@identity
RETURN

View 8 Replies View Related

If...Else Return Value

Oct 11, 2006

Hi,

Here is my sp:

Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr
(
@awardbody varchar(100)
)
AS
BEGIN
declare @message int

if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody)
set @message = 1
else
set @message = 2
insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)

END
return @message

The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!

Any help much appreciated.

Dave

View 3 Replies View Related

How Could I Return The Next Row(Or 8th Row For Example)

Sep 25, 2005

Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-Thanks

View 8 Replies View Related

Getting The Return Value

Aug 15, 2007

Hello everyone -

Please advise on why the output value is an empty

declare
@DynamicSQL3 NVARCHAR(4000

SET @DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '

exec sp_executesql @DynamicSQL3, N'@i int output', @AdministratorSecurityID output


I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,

but when i execute a print @AdministratorSecurityID
there is a space character in the results window

please advise on what i am doing wrong

thanks
tony

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved