.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Cannot pass null values to my Oracle procs (ORA-06550)

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net
 

I am unable to pass null values to my oracle procedures thru my ASP.NET pages. If I do I get the following error:

"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments"

But the thing is I need to send null values to the proc and the columns that I am sending null accepts the null values. Does anyone know what might be going on and how to fix it?




View Complete Post


More Related Resource Links

How to Pass Values Between Web Pages ASP.NET

  
You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

How to Pass Values Between ASP.NET Web Pages

  
You can pass information between pages in various ways, some of which depend on how the redirection occurs. The following options are available even if the source page is in a different ASP.NET Web application from the target page, or if the source page is not an ASP.NET Web page:

Use a query string.

Get HTTP POST information from the source page.

The following options are available only when the source and target pages are in the same ASP.NET Web application.

Use session state.

Create public properties in the source page and access the property values in the target page.

Get control information in the target page from controls in the source page.

How to deal with NULL values in a SQL table

  

 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
 {
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;
...

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default


With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

Help: Too many lines of code to achieve a simple goal: keep my NULL SQL values...

  

Hello all

Question:
--------------
What is the best way (best practice) to preserve my NULL SQL values in the database
during an insert/update operation (without receiving cast invalid errors)?
Also, how to display a string 'n/a' when a sql value is NULL?

Description:
----------------
My project is using FormView with Edit/Insert templates and classes to represent my tables

Goals:
------------
Goal1....: display 'n/a' when there are NULL SQL values in the database
Solution1: I am using ISNULL(field,'') in my store procedure for SELECT statements

Goal2....: if the fields are 'n/a', then save them back in the database as NULL
Solution2: on my insert/edit methods I am having to check the values being passed, i.e:

cmd.Parameters.Add(new SqlParameter("@scope", SqlDbType.NVarChar, 50));

if (item.Scope == "n/a")
 cmd.Parameters["@scope"].Value = DBNull.Value;
else
 cmd.Parameters["@scope"].Value = item.Scope;


Not to mention that for SQL DateType fields when you use ISNULL(field,'') the return string is '1/1/1900 12:00:00 AM'
and I have to change the field value in every field that represents a date, i.e:

protected void FormView1_DataBound(object sender, Sy

How to pass values from webgrid1 and webgrid2 to webgrid3

  
I want to pass the values from webgrid1 and webgrid2  to webgrid3 one with checkbox or radio button selection. When i hit the button i want to pass the selected values from each webgrid to the third one.Is that possible?

How to pass values using http header to another domain?

  
Hi,There are two applications "Sender", "Receiver"."Receiver" is a asp.net page, running in a server"Sender" is not an asp.net page and running in some other server."Sender" is passing a string (eg. userName) through http header."Receiver" is reading the string from http header as follows        Request.Header["userName"].How to pass values through http header? Can I have some samples?Thanks,Ashokan

Pass values From Html.BeginForm To Controller

  
I'm using 2 submit buttons. <form id="form1" runat="server"> First name :<asp:TextBox ID="FNanme" runat="server" /><br /> Last name :<asp:TextBox ID="LName" runat="server" /><br /> </form> <% Html.BeginForm("ActionName", "ControllerClass", FormMethod.Post); %> <input type="submit" name="submitButton" onclick=" return popUp1Msg()" value="Reject" /> <input type="submit" name="submitButton" onclick=" return popUp2Msg()" value="Accept" /> <% Html.EndForm ( ); %> On every submit, Popup appears with Ok and Cancel buttons.When user select Ok. How can I pass FName and LName values to controller? Thanks  

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

  
I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Gridview RowCommand Event returning null values

  
HiI'm trying to retreive values entered by users in the textboxes in the footer of the gridview and insert these values into another sql table.My code is as follows :-protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)    {       if(e.CommandName.Equals("addnew"))       {                       TextBox t1 = (TextBox)GridView2.FooterRow.FindControl("tfrm");            string frm = t1.Text;            TextBox t2 = (TextBox)GridView2.FooterRow.FindControl("tsub");            string sub = t2.Text;            string cmd = "insert into suggestions values('" + frm + "','" + sub + "','c','c')";            con.Open();            SqlCommand com = new SqlCommand(cmd,con);            int a = com.ExecuteNonQuery();&n

Comparing Null Values(from variable) in SSIS

  
Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Comparing columns with NULL values--Merge says unmatched when data is matched.

  
I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

Problems querying XML data with null values

  
If the tags have "xsi:nil" in them, then my query failes with a "XML parsing: line [x], character [x], undeclared prefix" error.  So my question is how to get the query to handle or ignore those tags with "xsi:nil" embedded in them.  So specifically, the first SQL query below is successful, and the second one fails with the aforementioned error.  Any comments appreciated, thanks. -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> </customer> <customer> <id>2</id> <name>Trades International</name> </customer> </data>'; -- Using the query() method SELECT T.customer.query('id').value('.', 'INT') AS customer_id, T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name FROM @data.nodes('data/customer') AS T(customer); Returns: 1 Allied Industries 2 Trades International ================== -- Declare XML variable DECLARE @data XML; -- Element-centered XML SET @data = N'<data> <customer> <id>1</id> <name>Allied Industries</name> <fax xsi:nil="true" /> </customer> <customer> <id>2</id> <name>Trades International</name> <fax xsi:nil=&quo

Oracle Pass through queries

  
G'day So, this is a newbie type issue as I'm quite certain there is a simple solution I just haven't found it yet. I'm an old Oracle DBA who is new to SQL Server so I'm currently struggling. Anyway, I have created a linked server from my SQL Server database. I can easily issue distributed queries or I can use openquery without problems so the link is working. The OLEDB I am using is Microsoft's not Oracle's so it is possible that changing the version of the oledb may solve my problem. The SQL statements I am issuing are simple but are not being executed as pass through queries. The documentation seems to indicate that it should work (do the processing on the Oracle side) but it doesn't. This is important as the source table in Oracle I am hitting is about 7 gig in size (fact table) and the performance sucks. I can't use openquery because my SQL statement is dynamic. Openquery doesn't use variables it want's a constant so if I'm doing domething wrong there let me know. I can limit the result set to about 700,000 records with openquery but the other parameters are variables so I can't get to the small result set I want which are only a few dozen records at most. Is there a way to issue dynamic pass through queries? Should I change oledb versions / products? Should I be using Execute? The documentation says that "SQL Server 2005 extends the EXECUTE statement so

Pass values between user controls on 2 different aspx pages

  
I have 2 user controls that is hosted by 2 different aspx pages.example: srcpage.aspx --> hosts the --> sourceusercontrol.ascxanddestpage.aspx --> hosts the --> destinationusercontrol.ascxI would like the values in the text fields of the sourceusercontrol.ascx to be passed on to the labels in the destinationusercontrol.ascx. I have gone through the MSDN ASP.Net examples and i was able to successfully pass values between two aspx page using the postback URL functionality, but cannot do the same thing with the usercontrols on 2 different aspx pages. Any suggestions would be helpfull....! Thanks..! 

How to Pass Values between two Entity Picker?

  
Hi All, I am implementing the SharePoint custom fields to pull the data from the SQl Database. This custom field contains two entity picker (like people picker) where the first one will get a record from the Parenet records and second picker has to get the record based on the parent selection. Everything works fine except passing the first entity picker value to second one. Any one has any ideas how to do this? I am trying to use the custom Property in the follwoing way public     RecordPickerData PickerData {     get {     string s =     if (_pickerData == null && String.IsNullOrEmpty(CustomProperty) == false) {     byte[] buffer = Convert.FromBase64String(CustomProperty);     using (MemoryStream stream = new MemoryStream(buffer)) {     BinaryFormatter formatter = new BinaryFormatter(); _pickerData = (   RecordPickerData)formatter.Deserialize(stream); } }     return _pickerData; }     set {     if (value != null) { _pickerData =   value;     using (MemoryStream stream = new MemoryStream()) {     BinaryFormatter formatter = new BinaryFormatter(); formatter.Serialize(stream, _pickerData); CustomProperty =   Convert.ToBase64String(stream.ToArray()); }

Create unique constraint on a column which has null values

  
Hi All, I have a table suppose 'Temp' having one of the column as 'ColA' which has some null values as well as non null values. Now i have a requirement to create a unique constraint on it. We have tried but couldnt do it.Apart from having a trigger on insert and update statements is there any other alternate. Can any one please help me on this. Thanks & Regards, Srikanth  
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend