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


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

Problem with Oracle Stored Procedure/Web Form(C#)

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :ASP.Net
 

Hi!

I receive the following message when trying to insert a record into a table via Web Form(C#):

Error: Invalid operation. Connection closed.

The button event, when clicked, should connect to the database and then call the stored procedure and
insert a new record according to the text input on the Web Form. Hence, returning the new flight id (TID).

Note: Some entries can be null.

Frontend-Code:

{code}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Add_Flight.aspx.cs" Inherits="Add_Flight" %>   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 89%; } .style2 { height: 23px; } .style3 { width: 360px; } .style4 { width: 137px; } .style5 { height: 23px; width: 137px; } .style6 { width: 95px; } .style7 { height: 23px; width: 95px; } .style8 { width: 200px; } .style9 { width: 129px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table cellspacing="3" class="styl


View Complete Post


More Related Resource Links

Trying to run a stored procedure from vb code with oracle data provider.

  

Hello,

Here is my SP:

create or replace
PROCEDURE ZGETUSERSSIGNONS (vUid IN VARCHAR2, p_getuserssignon_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
FROM ZSIGNON
WHERE substr(UserId,1,2) <> vUid
Order By UserId;
END ZGETUSERSSIGNONS;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 

Second:

Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.

 

 


DTA doesn't find referenced table form stored procedure with SP_executesql

  
Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

time out stored procedure windows form

  
I have a form that runs kicks bulk copies data to a data base and then runs a stored procedure.  the problem I have is the stored procedure takes a long time and users have no way to know what is going on.  the app window freezes says not responding and then after a while it completes and moves on. is there a way to prevent the application from freezing and being marked as not responding?

problem for using webservice (sql parameter stored Procedure)

  
i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

Calling Oracle stored procedure

  

I am calling oracle procedure in SSIS script task, This stored procedure retrun value as output, I tried OLEDB it not working, Now i am trying using ADO.net connection, while executing task i am getting following error msg

 

[Execute SQL Task] Error: Executing the query "exec ssisproctes" failed with the following error: "Unable to cast object of type 'System.Data.OracleClient.OracleConnection' to type 'System.Data.SqlClient.SqlConnection'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am able to test connection in connection manager?

any help to resolve this issue, or is there any other method to call oracle procedure and get output value in a SSIS variable

Thanks

V

 


Pass Multi-Value parameters to Stored Procedure problem

  

Hello

I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

AS
BEGIN
SET NOCOUNT ON;
 
WITH cte AS
(
SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
),
cte2 as
(
SELECT [ID],[First Name],[Last Name] FROM Sta

GridView delete using stored procedure problem (strange)

  

Hi!!
I'm getting an error and I can't understand why is this happening...
I have an stored procedure which receives a parameter to delete the database row corresponding to that key-parameter. In the GridView_RowCommand I'm setting up the datasource in the e.CommandName == "Delete" option     

this.SqlDataSourceGridView.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure;
this.SqlDataSourceGridView.DeleteCommand = "spDeleteGridView";

Then in the GridView_RowDeleting event, I'm preparing datasourceDeleteParameters with the Key that I want to delete in my database and call the         

this.SqlDataSourceGridView.Delete ( );

The row is deleted in the database but, and here is the problem, there is something that before it got error and throws me that:

El procedure or funcion 'spDeleteGridView' waits the parameter '@PARAM', that what not expected.

I must add the information that debugging GridView_RowDeleting it ends normally and calls the GridView_RowDeleted...

Any help??

Thank you!!!


Problem with stored procedure.

  

Hi All,

am using sql server 2005,

am writing a stored procedure to which i need to pass some parameters. (like company name,minimum revenue,maximum revenue,min emplotees,max employees etc..)

in the stored procedure i need to check the parameter value

ex: i need to check if the max revenue is containing the ">" or not


But am unable to write the stored procedure with the above requirement.It is giving error


CREATE PROCEDURE CompanyAdvSrch
(
--am passing all these values to the stored procedure
    
    @companyName    VARCHAR(250),
    @CurrentPage    VARCHAR(50),
    @type            VARCHAR(50),
    @country        VARCHAR(50),
    @state            VARCHAR(50),
    @city            VARCHAR(50),
    @industry        VARCHAR(50),
    @minrevenue        VARCHAR(50),
    @minemp            VARCHAR(

My Sql Stored procedure problem

  

Dear all,

          I am in need to import the Data from .DAT file to my database.when I tried with LOAD DATA INFILE statement ,it's working fine.But my ultimate aim is to do the same with stored procedure  ,it fails and gives an error message 


Script line: 4 LOAD DATA is not allowed in stored procedures


Script line: 4 LOAD DATA is not allowed in stored procedures


This is my Stored procedure


DELIMITER $
DROP PROCEDURE IF EXISTS `paylink`.`Test` $
CREATE PROCEDURE `paylink`.`Test` ()
BEGIN
Load Data INfile 'E:\Collect.dat' into table paylink.te

Custom paging sorting stored procedure problem

  

hi.

I want to make my gridview enable for custom paging and sorting. I started to follow a tutorial and write the Stored procedure.

But my stored procedure seems not to be working. here it is:

ALTER PROCEDURE [dbo].[USP_getStudentsEmail]
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int,
    @receiver nvarchar(38)
)
AS


IF LEN(@sortExpression) = 0
    SET @sortExpression = 'SendDate'


DECLARE @sql nvarchar(4000)
SET @sql = 'Select *
From
(
	Select m.Body,m.Subject,m.SendDate,m.SenderProfile,m.Receiver AS Receiver,m.ID,s.FirstName,ROW_NUMBER() OVER (ORDER BY '+ @sortExpression + ') AS RowRank 
	from Message m INNER JOIN StudentProfile s ON m.Sender=s.UserId
) AS OrderedEmails
            WHERE     m.Receiver= '+ CONVERT(nvarchar(38), @receiver) + 'AND RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) +')'


EXEC sp_executesql @sql


I tried to run this by the following line

exec USP_getStudentsEmail 'SendDate',1,1,'5ed19f91-eeb7-4c6a-b564-98c8dd83e18c'

but it says  

Incorrect syntax near 'd19f91'-

can anybody help

Selecting Oracle Stored Procedure in SSRS Crashes Visual Studio 2005 IDE

  

Problem Conditions

I have a very simple Oracle (11g) Stored Procedure that is declared like so:

CREATE OR REPLACE PROCEDURE pr_myproc(L_CURSOR out SYS_REFCURSOR)
is
   
BEGIN
     OPEN L_CURSOR FOR
       SELECT
* FROM MyTable;
   
END;

This compiles correctly. The cursor contains col1, col2 and col3.

In SSRS, i have a Shared Data Source that uses Oracle OLEDB Provider for Oracle 11g:

Provider=OraOLEDB.Oracle.1;

How to call Oracle Stored Procedure which has an output parameter from SSIS?

  

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

 

    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  

 


Problem with decryption data in stored procedure if local date on my machine was changed

  

Hi people!

Can anybody help me with my problem?

I have one machine (Server 2003 x64 SP2, SQL Server Standard Edition (64-bit) version 9.00.4053.00). Also I have a stored procedure in database that doing a validation of users, and contains the next code:

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
  SELECT
  [UserId],
  [AccountNumber],
  [Login],
  CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'Password'
  FROM [User]
  WHERE ([Login] = @Login)
        AND (CONVERT(varchar, DecryptByKey(EncryptedPassword)) = @Password);
CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey;

All works correctly but if i change the "Date and Time Properties" on my machine i receive the next error

(0 row(s) affected)
Msg 15315, Level 16, State 1, Procedure user_login, Line 111
The key 'PasswordFieldSymmetricKey' is not open. Please open the key before using it.

If i set the current date it will work correctly again.
One strange thing:
if execute the query "SELECT * FROM sys.symmetric_keys" i have the next date of creation for my symmetric keys

2010-11-04 11:18:49.300

And if i set a date less then date of

problem inserting using a stored procedure and identity column.

  

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

  
Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:

1)http://www.mcpressonline.com/database/db2/db2-integration-with-sql-server-2005-part-i-linked-server-enhancements.html
2)http://stackoverflow.com/questions/329051/execute-db2-iseries-stored-procedure-from-a-sql-2005-linked-server

I installed linked server using all this 3 providers

sp_addlinkedserver
    @server=N'DB2',
    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
    @catalog='S65CF29B'
go
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.


declare
  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

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