Tracking Forums, Newsgroups, Maling Lists
Home Submit Tracker Forums
  Advanced Search
  HOME    TRACKER    ASP.NET


Advertisements:










Passing Null / Empty String To Oracle Stored Procedure?


We have an ASP.NET web service that invokes a stored procedure on our DB (Oracle 11g). The problem is that the call to the procedure blows up every time an empty string is passed as one of the parameters. We're seeing an ORA-01084 error, which indicates to me that call is failing before the procedure is actually run.

Here's the procedure, minus some logic that I believe is not relevant:

[Code]....

As an experiment, I modified the web service to pass null rather than an empty strings. When null is passed in, I see an error indicating "wrong number or types of arguments in call to 'CREATEREPORT."

I also tried passing DBNull.Value whenever the params were null/empty, but that resulted in the error

Parameter 'p_column_id': No size set for variable length data type: String.

(Of course, p_column_id was the empty parameter in this case).

So, how can I successfully pass empty strings as parameters to my stored procedure? We definitely want to allow the p_column_id parameter to be empty.


View 1 Replies (Posted: Mar 1 10 at 21:50)

Sponsored Links:

Related Forum Messages For ASP.NET category:
Oracle 11g Stored Procedure - Expects A Parameter As A Number, But Receives A String?
we recently upgraded an application (.NET 2.0) that we maintain to use Oracle 11g. The application uses MS Enterprise Library 2.0. We've found that when the database.ExecuteNonQuery(Oracle.DataAccess.Client.OracleCommand) method is called, it bombs when the stored procedure expects a parameter as a number, but receives a string. This didn't happen prior to upgrading. If I cast the parameter to an Int, I don't recieve the error. Was something possible missed during the upgrade? The issue occurs regardless if we are hitting an 11g database, or a 10g database.

EDIT: I neglected to mention that this same issue does not occur when ExecuteDataReader is called (handles implicit conversion of the datatype).

Posted: Mar 23 at 14:36

View 1 Replies!   View Related
DataSource Controls :: How To Convert The Oracle Stored Procedure To Sqlserver 2005 Stored Procedure
This issurareddy.i neadsome small clarification in the "Stored Procedure"

how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.

right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.

how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure

Posted: Apr 02, 2010 07:31 AM

View 4 Replies!   View Related
DataSource Controls :: Passing Query String Value To Stored Procedure
I'm trying to pass the text valueof atext box asa query string value to a stored procedure. I was hoping someone could point me in the right direction. Button Code:

[Code]....

Stored Procedure:

[Code]....

The results page datalist:

[Code]....

Code Behind of results page:

[Code]....

I'm sure i've got this entirely backwards. However if I change the NULL value in the stored procedure with some string value (eg."Donkeys") it pulls all the values that contain "Donkeys" into the datalist on my results page.

Posted: Mar 30, 2010 05:54 PM

View 2 Replies!   View Related
Databases :: Call A Stored Procedure From Within A Stored Procedure In Oracle
I am new to oracle and trying to figure out how to call a Stored Procedure from within Another Stored Procedure. In SQL Server Syntax is Exec StoredProcedureName Parameter1, Parameter2 . . . .

Posted: Jun 10, 2009 03:53 PM

View 2 Replies!   View Related
Converting Empty Textbox To Null Instead Of Empty String?
I have an app where I am writingvalues to a database. One field is a textbox thatis theID (set in sql to start at 1 and auto increment) In my stored procedure if it is null or len < 1 then insert. However, this textbox is left blank which sets the value to an empty string and the stored procedure is looking for an int.

This is the line of code I am having problems with:

cmd.Parameters.Add("@QuoteID", SqlDbType.Int, 4).Value = Convert.ToInt32(txtQuoteNumber.Text);

I get input string in incorrect format because the value I am guessing is "" a string when it wants an int.Is there a way to say if the textbox is blank then it is a null value not ""

Posted: Oct 27, 2009 10:19 PM

View 9 Replies!   View Related
Calling Oracle Stored Procedure From Application?
in my asp.net application, i am calling a stored procedure (oracle) to get some values from database.

Following is the sp:

[code]....

how to call this sp from my asp.net code.

Posted: Jan 13 at 6:12

View 2 Replies!   View Related
Databases :: How To Call Oracle Stored Procedure Using C#
im new to using stored procedures and i have the following stored procedure...

CREATE OR REPLACE PROCEDURE DRL_PROCEDURE2(var_SOURCE_OBJECTID IN varchar2, var_NEW_OBJECTID OUT varchar2)
AS
BEGIN
SELECT MAX(NEW_OBJECTID)
INTO var_NEW_OBJECTID
FROM DRL_CONVERSION
WHERE SOURCE_OBJECTID = var_SOURCE_OBJECTID;

Posted: Dec 01, 2009 09:59 AM

View 1 Replies!   View Related
Databases :: How To Call Oracle Stored Procedure
I would like to know if there is a way to call Oracle stored procedure from inside code?

Posted: Feb 23, 2011 12:34 PM

View 2 Replies!   View Related
Databases :: How To Return Result From Oracle Stored Procedure
I am basically a SQL Server developer but I have been asked to work on Oracle 8i appication. I would want to know

1.) how to write a stored procedure in oracle which gives resultset of the query ( select * from emp)

2.) call it in c# code

3.) use the results of the strored procedure from step1 to bind a datagrid.

Posted: Jan 15, 2007 09:38 AM

View 15 Replies!   View Related
Databases :: How To Use Oracle Stored Procedure To Send Mail With Attachment
i want to send mails internally and externally for eg. i want to send mail to my boss in the company and also to the customer which doesnt belong to our domain.

[URL]

and also i want to attach an excel file which is stored in my computer. so how am i going to access that file through the same procedure and then attach the file and send it to both internal as well as external email ids.

Posted: Apr 10, 2010 07:03 PM

View 1 Replies!   View Related
Databases :: Getting Multiple Rows From Oracle Stored Procedure And Binding It To Gridview?
To get this stuff you need to create a Ref Cursor to return recordset. create or replace PACKAGE Types AS

TYPE cursor_type IS REF CURSOR;

END Types;

Posted: Apr 15, 2008 10:34 AM

View 1 Replies!   View Related
Databases :: How To Create TableAdapter For Oracle Stored Procedure With T_CURSOR Output Parameter
I'm trying to call Oracle stored procedure withT_Cursor output parameter to produce TableAdapter (Dataset Schema xsd)

Posted: Oct 23, 2009 09:53 AM

View 1 Replies!   View Related
Forms Data Controls :: Intercepting Null And Empty String Values Before Using Filter String?
I currently have some trouble dealing with null and empty string values from my datasource, namely that they seem to break my filter strings when they get there from comboboxes, textfields etc... I'd like to know what's a good way to catch them before they enter the filterstring and turn them into something more useful, such as default none/no/empty etc...

Posted: Dec 07, 2009 01:35 PM

View 3 Replies!   View Related
Passing A Parameter To Stored Procedure In C#?
When I try the following:

[Code]....

I get an error saying No overload for Add method takes 2 arguments. HOw would i pass the PaycheckID in then as a paramter?

Posted: Mar 23, 2011 02:19 PM

View 2 Replies!   View Related
ADO.NET :: Passing An Array To Stored Procedure?
I want to send an array of integer to sql server Stroed Procedure as parameter from vb program. How i can do this.

Secondly how I declare an parameter array in stored procedure which accept the array of integer.

Posted: Mar 21, 2011 10:49 AM

View 1 Replies!   View Related
Stored Procedure Parameters - Sometimes NULL?
i have a stored procedure to update a table. not all fields are required and some can be blank.for example, title is not required, but when it's blank i get an error

Dim ptitle As New SqlParameter("@title", SqlDbType.NVarChar, 20)
ptitle.Value = objFormat.CheckSQL(title)
myCommand.Parameters.Add(ptitle)
ex {"Procedure or Function 'sp_UpdateUser' expects parameter '@title', which was not supplied."} System.Exception


how can i fix it so that it allows for nulls if the data field is blank?

Posted: Jan 19 at 16:09

View 6 Replies!   View Related
ADO.NET :: Passing Parameters To A Stored Procedure Using A SqlDataAdapter?
I am trying to create a method which will access the database via a SqlDataAdapter. I have passed two parameters month and year. When I run it, I get the error "Object reference not set to an instance of an object". I have posted the complete code below. Please take a look..

[Code]....

Posted: Dec 10, 2010 02:49 PM

View 1 Replies!   View Related
Passing Blank Field Value To Stored Procedure C#?
I want to return all rows from a SQL Server 2008 database table into a SQL data source if a textBox field is blank. So I made a stored procedure with an if @date IS NULL clause.

Although the stored procedure seems to be working fine in Visual Studio 2008 the actual webpage displays no results.

I am guessing I have to send a DBNull value to the stored proc if textBox.Text == string.Empty. I tried the SqlDataSource1.SelectParameters.Add but it seems that I get a conversion error from DBNull.Value to string.

Is this the source of my problems or am I missing something else? How would I pass DBNull.Value to a stored proc if a textfield is blank?

Posted: Feb 21 at 15:51

View 2 Replies!   View Related
Passing List Of Name / Value Pairs To Stored Procedure?
I have a name/value pair in a List<T> and needing to find the best way to pass these to a stored procedure.

Id Name
1 abc
2 bbc
3 cnn

What is the best way to accomplish this?

Posted: Aug 10 10 at 20:17

View 3 Replies!   View Related
DataSource Controls :: Eliminate Returning Of Empty Table From Stored Procedure
I have a Procedure which should return only 1 Record on selecting record according to Priority.

BEGIN
DECLARE @Accounts nvarchar(MAX)
SELECT @Accounts = ISNULL([map_user_modules].[Accounts], '') FROM [map_user_modules]
WHERE [map_user_modules].[id_user] = 231 AND [map_user_modules].[id_module] = 5
IF ((@Accounts = '') OR (@Accounts = 'All'))
SELECT @Accounts = null

BEGIN
Select @@ROWCOUNT as Priority2
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority3
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

BEGIN
Select @@ROWCOUNT as Priority4
END
IF @@ROWCOUNT = 1
BEGIN
Return
END

In the above statement Suppose we take a scenario where Select @@ROWCOUNT as Priority3 is executed, I get two tables returned, One is the Table returned by Select @@ROWCOUNT as Priority3 and the other is the Table returned by Select @@ROWCOUNT as Priority2 which has no Records. How can I eliminate the Empty table that returns no records.

Posted: Jul 12, 2010 10:27 AM

View 1 Replies!   View Related
SQL Server :: Insert NULL Int Into Stored Procedure?
I'm strugling with this problem for quite some time and I've tried many different solution but nothing works. In my application there are 3 textboxes. First is mandatory but user can leave second and third empty. I that case I want that application insert NULL.

Here is .aspx code:

[Code]....

c# code:

[Code]....

Stored procedure:

[Code]....

I've tested code when user fills all 3 textboxes and it's working fine. If one second or third (or both) textbox(es) is(are) empty I wish to insert NULL into table. I've tried with DbNull.Value, null, 0, "3232131" and nothing works. I always get error Input string was not in a correct format. which is strange since code works when 2. and 3. tbs are filled and I'm not inserting string. And I've specified that table allows inserting of null values.

Posted: Jan 17, 2011 01:10 PM

View 13 Replies!   View Related
Passing A Fields Value Based On A Parameter Stored Procedure?
I have the following to create a stored procedure and it works fine, but I need to get the list of JobId's in the output without supplying parameters. Here is what I am trying to do:

The parameter is automatically entered through code.

CREATEPROCEDURE spSelectedJobs
@id intOUTPUT
AS[code].....

This would provide a list of jobs that the user has not selected.


Posted: May 21, 2009, 11:48

View 1 Replies!   View Related
SQL Server :: Passing Multivalue Parameter To Stored Procedure
I want to know, how do i send a multivalue parameter from C# to Stored Procedure which is used in WHERE CLAUSE in IN statement?

for eg:

CREATEPROCEDURE [dbo].[sp_GetPolicyDivOff1]
@Insid varchar(5000)=null
AS
SET NOCOUNT ON

SELECT MST_GroupCompany.GC_Name,MST_DivisionalOffice.DIV_Name FROM MST_DivisionalOffice INNER JOIN MST_GroupCompany ON

MST_DivisionalOffice.DIV_FK_GC_ID=MST_GroupCompany.GC_ID where GC_Name IN(@Insid)

Posted: Jul 28, 2010 09:23 AM

View 10 Replies!   View Related
DataSource Controls :: Passing Parameters To Stored Procedure?
I've implement paging in my datalist. I've followed the steps given in this link. [URL] Everything work fine. But i don't know how to pass search parameter to this below stored procedure.

CREATE PROCEDURE [dbo].[LoadDataTest]
@startRowIndex int,
@pageSize int,
@totalCount int output
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select SampleTable.*, ROW_NUMBER() OVER (ORDER BY SampleTable.AutoID DESC) as RowNum
FROM SampleTable WHERE
SampleTable.Active = 1
) as ArticleList
WHERE
RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID DESC
SELECT @totalCount = Count(AutoID) FROM SampleTable WHERE Active = 1
END
END
CREATE PROCEDURE [dbo].[LoadDataTest]
@startRowIndex int,
@pageSize int,
@totalCount int output
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select SampleTable.*, ROW_NUMBER() OVER (ORDER BY SampleTable.AutoID DESC) as RowNum
FROM SampleTable WHERE
SampleTable.Active = 1
) as ArticleList
WHERE
RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID DESC
SELECT @totalCount = Count(AutoID) FROM SampleTable WHERE Active = 1
END
END

Posted: Nov 24, 2009 04:48 AM

View 7 Replies!   View Related
SQL Server :: Passing An Array From C# Code-behind To Stored Procedure?
Via the button1_ click event I retrieve all files in a directory (Example Follows)

[Code]....

I now need to check whether thisfilenameexists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt which does not exist in the(Table =TableFileStore;s Field =FileRetrievals) then I want to insert the filename.

How do I construct a stored procedure to pass the filename(s) into a stored procedureas aparameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Doesthe stored procedure belong within the foreach loop?

Posted: Nov 02, 2010 01:25 PM

View 3 Replies!   View Related
SQL Server :: Passing A Variable To A Remote Stored Procedure?
I have a SQL stored procedure on a SQL server that uses a variable to create a new database. This works in SQL I have a connection string that works I have added a sqldatasource to a webform, and configured the datasource to connect to the remote SQL database and am able to select my stored procedure. I am able to bind the variable to a control in the sqldatasource wizard (often two of the same control appear- if that is significant)

When I test the sqldatasource in the configuration wizard and put in a manual entry, the procedure runs and works

It is on the web page I am having troubles. I want the user to enter a name in a text box, click a button/link, and run the procedure. Ive used buttons and links...but not in the right way I think, becuase when I click them the stored procedure doesnt run.

As mentioned above, I have selected the right control in the sqldatasource wizard. So what do I need to do now?

Is it button click event? If so whats the syntax?

Should I be embedding my textbox in a dataview?

Posted: Feb 27, 2011 11:10 PM

View 4 Replies!   View Related
Passing Whole Dataset To Stored Procedure In MSSQL 2005?
How do I pass a dataset object to a stored procedure? The dataset comprises multiple tables and I'll need to be able to access them from within the SQL.

Posted: Oct 5 10 at 11:23

View 3 Replies!   View Related
DataSource Controls :: Passing Radiobuttonlist Value To Stored Procedure?
I have an .ASPX form. All I want to do is simply "Submit" the form field values into a database.

"I have been tasked with re-writing the entire .ASPX form to use "Stored Procedures" instead of a "SQL-String" to insert form values. I think I have most of the form submitting to the database, except the "Radio Buttons" just say "NULL".

I do not know how to progam the Radio Buttons to insert the value (Yes, No, N/A) the user chose into the database. I have GOOGLED but cannot find an answer, and some answers are too complex for my level of understanding at this point.

I need to take the following code and change it so it just calls a Stored Procedure. If you could show me "Stored Procedure" code and how to change my current code to work with the Sproc it would be much appriciated. Here is the code.

I have "11" groups of Radio Buttons I need to change. Here is an example of one of the rows.

<tr>
<td><input type="radio" name="group1" value="yes"></td>
<td><input type="radio" name="group1" value="no"></td>
<td><input type="radio" name="group1" value="N/A"></td>
<td><asp:Label ID="Label22" runat="server" Text="Surfaces exposed to corrosives pH between 5 and 9."></asp:Label></td>
</tr>

Summary: I just want to call a stored procedure and store the value the user chose into my database.

Posted: Dec 11, 2010 04:07 PM

View 5 Replies!   View Related
SQL Server :: Passing Array Paramaters To Stored Procedure?
I need to send an array of strings to a stored procedure and then use that array of strings to create a filter of the data returned.for example I need need to send the following array to the strored procedure:

[Code]....

and use that array to run the fillowing sql command:

[Code]....

Posted: Nov 01, 2010 07:58 AM

View 17 Replies!   View Related
How To Handle Null Values In Stored Procedure With Pivot
I am using this stored procedure with pivot.If i dont have data i am getting null with this stored procedure.Can u tell me how to handle null.below query is pivot.

[Code]....

Posted: Oct 30 10

View 1 Replies!   View Related
SQL Server :: Send NULL Value To A Stored Procedure Variable?
I want to send an image to a stored procedure variable that isvarbinary(max) field, It works correctly when an image selected by fileupload andmy image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that isvarbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure?

Posted: Sep 26, 2010 07:18 AM

View 3 Replies!   View Related
DataSource Controls :: Passing Querystring Parameter To Stored Procedure
I have a listbox on the page and when I select 1, 2, or 3 items from the list box and hit submit I go here:

[Code]....

The querystring take over how many selections (dte) and put's it in this querystring on the next page:

http://Test/test1.aspx?period=m&dte1=11/13/2009&dte2=11/06/2009&dte3=10/30/2009

Now the page will only bring up dte1 because the stored procedure doesn't see the other choices.

How do I update the stored procedure to see the other querystrings?

Here's my stored procedure:

[Code]....

[Code]....

Posted: Dec 04, 2009 02:49 PM

View 3 Replies!   View Related
SQL Server :: Stored Procedure Is Not Passing In A Primary Key Into A Foriegn Key In Another Table?
I am a rookie developer here and I have tried looking around for an answer but I am little confused. I have a stored procedure that calls multiple stored procedures when I go to create a new contact.[Code]....

I though I could use the OUTPUT Command to copy out the NewContactID but I am getting all kinds of syntax errors. I am confused as to what I am missing here.

Posted: Jul 14, 2010 01:34 AM

View 3 Replies!   View Related
DataSource Controls :: Using LINQ Passing The Name Of The Stored Procedure Dynamically?
I have an application where depending on what the user selects run a different stored procedure.

I am using LINQ andam passing the stored procedure name into a sub that will run the stored procedure and return values that I am loading into a DDL.

For example I want to pass the procedure name "GetProducts_Hats" to the sub and not hardcodeit.

Protected Sub LoadParamDropDowns(
ByVal SqlObjToExec
As
String )

I am passing the storedprocedure name in parameter SqlObjToExec

so instead of

values= mydb.GetProducts_Hats

I want to code it to be a variable

values = mydb.SqlObjToExec

But the above codedoes not work.

Posted: Feb 23, 2010 06:28 PM

View 7 Replies!   View Related
DataSource Controls :: Passing Column Name As Parameter To Stored Procedure?
ALTER procedure [dbo].[Sample2]
@Col as nvarchar(1000),
@Value as nvarchar(1000)

[code]...

Posted: Feb 12, 2010 05:51 AM

View 5 Replies!   View Related
DataSource Controls :: Passing Default Parameter To Stored Procedure ?
I am using DAAB with stored procedures. I use stored procedures more then once if possible. If I add a parameter later in stored procedure, I have to supply value for this in functions where it is even not being used as well, other wise it shows an error that number of parameters are not same. if I add parameter in last of parameter list in stored procedure and set a default value for it and I may have not to provide value for this from functions where I am not using it.

Posted: Feb 13, 2010 03:33 PM

View 1 Replies!   View Related
Updating Empty String As NULL?
I have to update a numeric data type field but if there is nothing in thetextbox, I have save it as NULL.

For example, I'minitializing the field as following DimvVal as String =Val(tbVal.text)

But if there no value (i mean empty textbox), it is saving it as "0" which is incorrect. I would like it to save as "NULL" if there no value in the textbox.

I have tried the following but itreturns an error Dim vVal as String = IIF(tbVal.text.length>0,Val(tbVal.text),"")

Posted: Jul 15, 2009 09:16 PM

View 1 Replies!   View Related
Forms Data Controls :: Gridview Passing Values On To Stored Procedure?
In ASP.Net 2.x I have a Gridview where in edit mode I'm passing on to a stored procedure the values of 5 different columns needed to perform an update. The columns are not otherwise informational to the user so I would make them not visible. I've done this numerous times before, and in fact do the exact same thing elsewhere in the same project.

For whatever reason, when I make the column fields not visible, the values therein are empty, but if I make them visible, they have the expected values. I confirmed this using a label control troubleshooting.

I'll include some code as an example of what I'm doing, but any one have a clue as to what piece I'm missing?

[Code]....

Bear in mind for brevity's sake I haven't included all of my code.

Posted: Oct 06, 2009 02:15 PM

View 6 Replies!   View Related
SQL Server :: How To Execute Stored Procedure From Front End Without Passing Values To Optional Parameters
In my stored procedure I have declared some parameters as NULL values(Optional Parameters).

In my C#.NET code I didn't use the parameters to pass values to Stored Procedure's NULL

Parameters. So I am getting Exception.

I don't want to pass parameters from my code.Because I have declared 30 parameters in my

Stored Procedure as NULL values.

Is there any other way to execute Stored Procedure from Front End without passing values to

Optional Parameters in SQL stored procedure?

Posted: Jan 29, 2011 04:49 AM

View 4 Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved