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


ADVERTISEMENT

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

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

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

Sproc Idea: How Sensitive Is A Neural Net To Each Input?

Jun 15, 2007

Wanted some candid feedback on this idea. Everyone knows that neural nets are a black box in terms of the weights and such it uses. The best you can do is to get an idea how "sensitive" the NN is to each input. I don't know of any example code that's out there to help you do this in an automated manner (correct me if I'm wrong) so I'm thinking about writing a sproc that would help with this task.



Basically, the sproc would take in the mining model's name and the key of one case. Let's say the case and its attributes are:



ZipCode=93901 (the case key)

MedianIncome=99,098

PopulationDensity=1234

AvgTemperature=74.3

Predict likelihood to respond to an offer



For that case, it would go through an iteration per input. First it would test the first input, MedianIncome. It would run 100 predictions using the input values listed above. All the inputs would remain the same throughout except for MedianIncome where it would try out the complete range of that input. Based upon how much the prediction changes, you would have an idea how sensitive the model is to MedianIncome. Then it would move on to the next input and do the same.



When it's done testing each input, it could spit out a dataset listing all the inputs in order of how sensitive the model is to them and a few other stats like min and max prediction.



Thoughts? Improvements? Alternatives?

View 6 Replies View Related

Issues With An Output Param From A Sproc Using SQLDataSource

Nov 28, 2007

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
 
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
 
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
 
 
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

View 3 Replies View Related

Sproc Is Returning Output Parameter Inconsistently

Mar 6, 2015

We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.

In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.

The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.

The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.

USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).

change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS

The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?

View 1 Replies View Related

How To Get Employees Attendance As Desired Output From The Input

Jun 27, 2013

I am uploading input sample data and desired output data, can get the desire output.

View 4 Replies View Related

SSIS : Flat File Input And XML Output

Feb 21, 2007



Hi All,

I want to know is it possible to have source as Flat File and destination as XML

Thanks in advance,

Shagun













View 1 Replies View Related

How To Determine If Stor Proc Parameter Is Output Or Input

Jul 23, 2005

I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks

View 3 Replies View Related

Merge Input Output Selection Dialog Is Hanging

Oct 26, 2006

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

View 4 Replies View Related

SqlCommand Return And Output Parameters Not Working, But Input Does?

Feb 18, 2006

The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?

System::Boolean rs::sp(System::String ^ ssp){

System::String ^ k0s0; bool bOK;

System::Data::SqlClient::SqlParameter ^ parami0;

System::Data::SqlClient::SqlParameter ^ parami1;

System::Data::SqlClient::SqlParameter ^ parami2;

System::Data::SqlClient::SqlParameter ^ paramz0;

System::Data::SqlClient::SqlParameter ^ paramz1;

System::Int32 pz0=0;System::Int32 pz1=0;

k0s = ssp;

k0->CommandType=System::Data::CommandType::StoredProcedure;

k0->CommandText=k0s;

paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

paramz0->Direction=System::Data::ParameterDirection::ReturnValue;

paramz0->DbType=System::Data::DbType::Int32;

parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");

parami0->Direction=System::Data::ParameterDirection::Input;

parami1=k0->Parameters->AddWithValue("@SRCXV","UU");

parami1->Direction=System::Data::ParameterDirection::Input;

//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);

paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);

paramz1->Direction=System::Data::ParameterDirection::InputOutput;

paramz0->DbType=System::Data::DbType::Int32;

//k0->Parameters->GetParameter("@RCOUNT");

r0=k0->ExecuteReader();

//pz0=System::Convert::ToInt32(paramz0->SqlValue);

bOK=k0->Parameters->Contains("@RCOUNT");

//k0->Parameters->GetParameter("@RCOUNT");

pz0=System::Convert::ToInt32(paramz0->Value);

pz1=System::Convert::ToInt32(paramz1->Value);

ndx = -1;

while(r0->Read()){

if (ndx == -1){

ndx=0;

pai0ndx=0;

pad0ndx=0;

r0nf=r0->FieldCount::get();

for (iG1_20=0;iG1_20<r0nf;iG1_20++){

this->psf0[iG1_20]=this->r0->GetName(iG1_20);

this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);

this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}



}

else {

pai0ndx=0;

pad0ndx=0;

for (iG1_20=0;iG1_20<r0nf;iG1_20++)

{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}

}

ndx++;

}

r0nr=ndx;

r0->Close();

k0->Parameters->Remove(paramz0);

k0->Parameters->Remove(parami1);

k0->Parameters->Remove(parami0);

k0->Parameters->Remove(paramz1);

return true;

}

View 5 Replies View Related

Execute SQL Tasks With One Input And 2 Output Variables Fails

Mar 21, 2007

I am executing a stored proc with in the Execute SQL Task using OLEDB provider. I am passing the data as

ConnectionType: OLEDB

Connection : to my database

SQLSourceType: Direct

SQL Statment : Exec mysp 'table1',OUTPUT,OUTPUT

In the parmeter mappings:

variable1--direction Output, datatype Long, Parameter name: 0

variable2--direction Output, datatype date, Parameter name: 1

The variable 1 is created as int32 and variable 2 is created as dattime.

When i execute the SQLtask, I get error:

[Execute SQL Task] Error: Executing the query "Exec mysp 'table1',OUTPUT,OUTPUT" failed with the following error: "Error converting data type nvarchar to int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



What am i missing. I tried changing the data types adding the input variable also as a variable in the mapping. Nothing seems to work. Any ideas please?

Anitha

View 2 Replies View Related

Merge Join: Nr Of Output Rows Unchanged When Amount Of Input Changes

May 25, 2007

Dear all,



I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.



Situation as follows.



The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.



If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.



When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.



To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.



Any help will be greatly appreciated.



Kind regards,



Albert.

View 4 Replies View Related

Create Output Columns Based On Input In Custom Component

Aug 28, 2007



I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.

I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?

View 5 Replies View Related

Execute SQL Task : Input And Output Parameters In Tsql Stataments With ADO.NET Connection Type

Jan 2, 2007

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks



 

 

View 11 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'



This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang



View 7 Replies View Related

Output Data Is 2 TIMES The Input Data.SSIS.Newbie

Apr 10, 2008



Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.

I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.

What could be the reasons. Please help me out.

Thank You

View 5 Replies View Related

My Output Parameter Is Being Treated As An Input Parameter...why

Sep 25, 2006

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal.  I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening?  The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){  if (e.Exception == null)    {   string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString();    ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int

Set @stoptrans = 'NO'

/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )

IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN

IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END

IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END

IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END

ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum

SET @NewRetVal = 0
---Successful deletion
END
GO
 --------------------------------------------------------  When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

View 2 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

Input String -&> Table -&> Output String?

Jul 13, 2006

I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam

View 2 Replies View Related

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.

Feb 13, 2007

I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
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.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View 3 Replies View Related

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 --&gt;"Does anyone have any ideas of how I might go about donig this?Thank You..

View 4 Replies View Related

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 ReportsWhere 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 ReportsWhere ReportID = ''
Whis not what I want. What I would want is simply
Select * from Reports
or
Select * from ReportsWhere 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 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

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







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