How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter
-
Hello Friends Can someone help me. I know how to read value using SqlHelper.ExecuteReader using storedProcedure. But if stored Procedure has a output parameter, I'm not able to read output parameter value. How to read output parameter value. I could read output parameter value using SqlHelper.ExecuteNonQuery, but then I'm not able to read database query column values. how to read database query column values. pls help soon Regards Balasaheb :((
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
Create a paramter object, set the direction to output and add it to the command object (but keep the reference to the parameter object). After you have closed the data reader, you can access the output value in the parameter object.
--- b { font-weight: normal; }
-
Hello Friends Can someone help me. I know how to read value using SqlHelper.ExecuteReader using storedProcedure. But if stored Procedure has a output parameter, I'm not able to read output parameter value. How to read output parameter value. I could read output parameter value using SqlHelper.ExecuteNonQuery, but then I'm not able to read database query column values. how to read database query column values. pls help soon Regards Balasaheb :((
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
BalasahebK wrote:
I could read output parameter value using SqlHelper.ExecuteNonQuery, but then I'm not able to read database query column values.
There is no resultset returned, hence the name, ExecuteNonQuery. You retrieve the output param value the same way from ExecuteQuery as you do with ExecuteNonQuery.
only two letters away from being an asset
-
BalasahebK wrote:
I could read output parameter value using SqlHelper.ExecuteNonQuery, but then I'm not able to read database query column values.
There is no resultset returned, hence the name, ExecuteNonQuery. You retrieve the output param value the same way from ExecuteQuery as you do with ExecuteNonQuery.
only two letters away from being an asset
Thanks for ur reply In case of ExecuteReader:- My stored procedure contains two result set The actual format of stored procedure is ***************************Stored Procedure start************************************* declare @colid1 int declare @col2 uniqueidentifire declare @Status out set @col1 = "001" set @col2 = "{123456789}" declare @id int select @id = (select id from tableid where colid = @colid1) Select col1,col2,col3,col4 from tabel where col1 = @colid1 and col2= @col2 if (exists(select col1,col2 from table2 where col1 = @col1 and col2 = @colid)) { @Status = 1 } else { @Status =0 } select @Status as status *****************************Stored Procedure End**************************************** The above stored procedure is given for reference. Now my problem is I'm able to retreive column values using ExecuteReader by passing stored procedure properly ReaderObj = SqlHelper.ExecuteReader(Pass parameters) ReaderObj retreives only table1 columns values.......now how to read @Status value(look at above stored procedure):(( ***************************************************** arParams[0] = new SqlParameter("@prref",SqlDbType.VarChar,20); arParams[0].Value = strPrjRef; arParams[1] = new SqlParameter("@userid",SqlDbType.UniqueIdentifier); arParams[1].Value = new Guid("{7126886d-2538-429c-ab3c-22b3341dcecf}"); arParams[2] = new SqlParameter("@STATUS", SqlDbType.Int); arParams[2].Direction = ParameterDirection.Output; RdPrjDetails = SqlHelper.ExecuteReaderconnstr,CommandType.StoredProcedure, "GET_PROJECT_DETAILS_SP", arParams); ******************************************************* Pls help me in this regard
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
Create a paramter object, set the direction to output and add it to the command object (but keep the reference to the parameter object). After you have closed the data reader, you can access the output value in the parameter object.
--- b { font-weight: normal; }
Thanks for ur reply In case of ExecuteReader:- My stored procedure contains two result set The actual format of stored procedure is ***************************Stored Procedure start************************************* declare @colid1 int declare @col2 uniqueidentifire declare @Status out set @col1 = "001" set @col2 = "{123456789}" declare @id int select @id = (select id from tableid where colid = @colid1) Select col1,col2,col3,col4 from tabel where col1 = @colid1 and col2= @col2 if (exists(select col1,col2 from table2 where col1 = @col1 and col2 = @colid)) { @Status = 1 } else { @Status =0 } select @Status as status *****************************Stored Procedure End**************************************** The above stored procedure is given for reference. Now my problem is I'm able to retreive column values using ExecuteReader by passing stored procedure properly ReaderObj = SqlHelper.ExecuteReader(Pass parameters) ReaderObj retreives only table1 columns values.......now how to read @Status value(look at above stored procedure) ***************************************************** arParams[0] = new SqlParameter("@prref",SqlDbType.VarChar,20); arParams[0].Value = strPrjRef; arParams[1] = new SqlParameter("@userid",SqlDbType.UniqueIdentifier); arParams[1].Value = new Guid("{7126886d-2538-429c-ab3c-22b3341dcecf}"); arParams[2] = new SqlParameter("@STATUS", SqlDbType.Int); arParams[2].Direction = ParameterDirection.Output; RdPrjDetails = SqlHelper.ExecuteReaderconnstr,CommandType.StoredProcedure, "GET_PROJECT_DETAILS_SP", arParams); ******************************************************* Pls help me in this regard:((
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
Thanks for ur reply In case of ExecuteReader:- My stored procedure contains two result set The actual format of stored procedure is ***************************Stored Procedure start************************************* declare @colid1 int declare @col2 uniqueidentifire declare @Status out set @col1 = "001" set @col2 = "{123456789}" declare @id int select @id = (select id from tableid where colid = @colid1) Select col1,col2,col3,col4 from tabel where col1 = @colid1 and col2= @col2 if (exists(select col1,col2 from table2 where col1 = @col1 and col2 = @colid)) { @Status = 1 } else { @Status =0 } select @Status as status *****************************Stored Procedure End**************************************** The above stored procedure is given for reference. Now my problem is I'm able to retreive column values using ExecuteReader by passing stored procedure properly ReaderObj = SqlHelper.ExecuteReader(Pass parameters) ReaderObj retreives only table1 columns values.......now how to read @Status value(look at above stored procedure) ***************************************************** arParams[0] = new SqlParameter("@prref",SqlDbType.VarChar,20); arParams[0].Value = strPrjRef; arParams[1] = new SqlParameter("@userid",SqlDbType.UniqueIdentifier); arParams[1].Value = new Guid("{7126886d-2538-429c-ab3c-22b3341dcecf}"); arParams[2] = new SqlParameter("@STATUS", SqlDbType.Int); arParams[2].Direction = ParameterDirection.Output; RdPrjDetails = SqlHelper.ExecuteReaderconnstr,CommandType.StoredProcedure, "GET_PROJECT_DETAILS_SP", arParams); ******************************************************* Pls help me in this regard:((
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
Ok, so you are not using an output parameter at all? It's a bit hard to tell, as the stored procedure that you show is lacking the entire declaration part. It's hard to say what parameters you should use in the calling code if you don't show what parameters the stored procedure uses. Also the stored procedure contains some errors that tells me that this is not the code that you are actually using. If you only show me some made up code, I can only give advice on that code, not on the code that you are actually using. It's like if you went to your car mechanic and said: "I've brough my bike. Can you take a look at it and tell me what's wrong with my car?" ;) If you have a query that returns more than one result, use the NextResult method of the data reader to move from the first result to the next.
--- b { font-weight: normal; }
-
Ok, so you are not using an output parameter at all? It's a bit hard to tell, as the stored procedure that you show is lacking the entire declaration part. It's hard to say what parameters you should use in the calling code if you don't show what parameters the stored procedure uses. Also the stored procedure contains some errors that tells me that this is not the code that you are actually using. If you only show me some made up code, I can only give advice on that code, not on the code that you are actually using. It's like if you went to your car mechanic and said: "I've brough my bike. Can you take a look at it and tell me what's wrong with my car?" ;) If you have a query that returns more than one result, use the NextResult method of the data reader to move from the first result to the next.
--- b { font-weight: normal; }
ok I'm giving u actual stored procedure, earlier stored procedure was given only for reference The original stored procedure (working perfectly without error) is SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER PROCEDURE GET_PROJECT_DETAILS_SP ( @prref varchar(20), @userid uniqueidentifier, @STATUS int output ) As begin declare @prjid int select @prjid = (select PRID from EET_PROJECTS where PRREF = @prref) IF ((SELECT PRISPATT FROM EET_PROJECTS WHERE PRID = @prjid) = 0) BEGIN Select P.PRID as PRID, P.PRREF as PRREF, --P.PRNAME as PRNAME, EUP.PROJECTNAME as PRNAME, PRACTIVE = case P.PRACTIVE when 1 then 1 else 0 end, P.PRDESC as PRDESC, EUP.PURCHASERNAME as PRPURCHASERNAME, EUP.ENDUSERNAME as PRENDUSERNAME, EUP.DESTINATION, P.PROWNER as PROWNER, P.PROWNLOC as PROWNLOC, P.PRMKTLOC as PRMKTLOC, '' as PRPATT, EUP.INDUSTRY as PRINDUSTRY, P.PRPROPOSAL as PRPROPOSAL, P.PRSALES as PRSALES, P.PRQUOTCURRENCY as PRQUOTCURRENCY, P.CREATED_DATE as PRDATE, CREATED_BY = (SELECT cast(FIRST_NAME + ' ' + LAST_NAME as varchar(161)) FROM EET_SEC_USERS WHERE USERID = P.CREATED_BY), P.LAST_UPDATED_DATE as PRUP, LAST_UPDATED_BY = (SELECT cast(FIRST_NAME + ' ' + LAST_NAME as varchar(161)) FROM EET_SEC_USERS WHERE USERID = P.LAST_UPDATED_BY), ((SELECT LOCCOUNTRY FROM EET_LOCATIONS WHERE LOCID = P.PRMKTLOC) + ' (' + P.PRMKTLOC + ')') as PRMKTLOCWITHCOUNTRY, EUP.GP3_FLAG, EUP.PROJECTORIGINATOR_NAME, EUP.PROJECTLEAD_NAME, EUP.SYS_FUNNEL_POSITION, EUP.SYS_KOB, EUP.SYS_PROBABILITY, EUP.SIS_CONTENT, EUP.SERV_FUNNEL_POSITION, EUP.SERV_KOB, EUP.SERV_PROBABILITY, P.PRISPATT, P.PRNOPATTREASON, NULL as PRPURCHASERID, case when P.PRUSECUSTPRICE = 1 then 'Yes' else 'No' end as USECUSTPRICE, P.PRCUSTPRICEID, (SELECT CUPRCUSTOMER FROM EET_CUSTOMER_PRICING WHERE CUPRID = P.PRCUSTPRICEID) as CUSTPRICENAME, dbo.FUNC_GET_LOCATION_LIST(P.PRCUSTPRICEID) as CUSTPRICELOCN, isnull(EUP.BOOK_DATE, getutcdate()) as PRBOOKDATE, EUP.transitional_order as PRTRANSPRJSTATUS from EET_PROJECTS P join EET_USER_PATT_DATA EUP on P.PRID = EUP.EUPDPROJECT where PRID = @prjid END if (exists(select opuserid, opprjid from EET_MY_PROJECTS_LISTS where opuserid = @userid and opprjid = @prjid)) begin SET @STATUS = 1 end else SET @STATUS = 0 select @STATUS as status end GO SET QUOTED_IDENTIFIER OFF GO SET A
-
Ok, so you are not using an output parameter at all? It's a bit hard to tell, as the stored procedure that you show is lacking the entire declaration part. It's hard to say what parameters you should use in the calling code if you don't show what parameters the stored procedure uses. Also the stored procedure contains some errors that tells me that this is not the code that you are actually using. If you only show me some made up code, I can only give advice on that code, not on the code that you are actually using. It's like if you went to your car mechanic and said: "I've brough my bike. Can you take a look at it and tell me what's wrong with my car?" ;) If you have a query that returns more than one result, use the NextResult method of the data reader to move from the first result to the next.
--- b { font-weight: normal; }
ok problem has been solved using ExecuteReader. I'm using below code to read next Resultset. Reader = SqlHelper.ExecuteReader(pass parameters) if !(Reader == null) { while(Reader.Read()) { string str = Reader["param1"].ToString(); } Reader.NextResult While(Reader.read()) { string str1 = Reader["Param2"].ToString(); } } Many many thanks for ur help too. Thanks Keep in touch My email id:- kbalu_2001@rediffmail.com
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
ok I'm giving u actual stored procedure, earlier stored procedure was given only for reference The original stored procedure (working perfectly without error) is SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER PROCEDURE GET_PROJECT_DETAILS_SP ( @prref varchar(20), @userid uniqueidentifier, @STATUS int output ) As begin declare @prjid int select @prjid = (select PRID from EET_PROJECTS where PRREF = @prref) IF ((SELECT PRISPATT FROM EET_PROJECTS WHERE PRID = @prjid) = 0) BEGIN Select P.PRID as PRID, P.PRREF as PRREF, --P.PRNAME as PRNAME, EUP.PROJECTNAME as PRNAME, PRACTIVE = case P.PRACTIVE when 1 then 1 else 0 end, P.PRDESC as PRDESC, EUP.PURCHASERNAME as PRPURCHASERNAME, EUP.ENDUSERNAME as PRENDUSERNAME, EUP.DESTINATION, P.PROWNER as PROWNER, P.PROWNLOC as PROWNLOC, P.PRMKTLOC as PRMKTLOC, '' as PRPATT, EUP.INDUSTRY as PRINDUSTRY, P.PRPROPOSAL as PRPROPOSAL, P.PRSALES as PRSALES, P.PRQUOTCURRENCY as PRQUOTCURRENCY, P.CREATED_DATE as PRDATE, CREATED_BY = (SELECT cast(FIRST_NAME + ' ' + LAST_NAME as varchar(161)) FROM EET_SEC_USERS WHERE USERID = P.CREATED_BY), P.LAST_UPDATED_DATE as PRUP, LAST_UPDATED_BY = (SELECT cast(FIRST_NAME + ' ' + LAST_NAME as varchar(161)) FROM EET_SEC_USERS WHERE USERID = P.LAST_UPDATED_BY), ((SELECT LOCCOUNTRY FROM EET_LOCATIONS WHERE LOCID = P.PRMKTLOC) + ' (' + P.PRMKTLOC + ')') as PRMKTLOCWITHCOUNTRY, EUP.GP3_FLAG, EUP.PROJECTORIGINATOR_NAME, EUP.PROJECTLEAD_NAME, EUP.SYS_FUNNEL_POSITION, EUP.SYS_KOB, EUP.SYS_PROBABILITY, EUP.SIS_CONTENT, EUP.SERV_FUNNEL_POSITION, EUP.SERV_KOB, EUP.SERV_PROBABILITY, P.PRISPATT, P.PRNOPATTREASON, NULL as PRPURCHASERID, case when P.PRUSECUSTPRICE = 1 then 'Yes' else 'No' end as USECUSTPRICE, P.PRCUSTPRICEID, (SELECT CUPRCUSTOMER FROM EET_CUSTOMER_PRICING WHERE CUPRID = P.PRCUSTPRICEID) as CUSTPRICENAME, dbo.FUNC_GET_LOCATION_LIST(P.PRCUSTPRICEID) as CUSTPRICELOCN, isnull(EUP.BOOK_DATE, getutcdate()) as PRBOOKDATE, EUP.transitional_order as PRTRANSPRJSTATUS from EET_PROJECTS P join EET_USER_PATT_DATA EUP on P.PRID = EUP.EUPDPROJECT where PRID = @prjid END if (exists(select opuserid, opprjid from EET_MY_PROJECTS_LISTS where opuserid = @userid and opprjid = @prjid)) begin SET @STATUS = 1 end else SET @STATUS = 0 select @STATUS as status end GO SET QUOTED_IDENTIFIER OFF GO SET A
That's better, that stored procedure actually has an output parameter. Why are you returning the value of @STATUS both as an output parameter and a result? Anyway, the code that you showed earlier that created the parameter object should work just fine. After you have closed the reader, you can retrieve the value from the
Valueproperty of the parameter object. The value is returned boxed inside an object, so you have to unbox it by casting it to the typeint.--- b { font-weight: normal; }
-
ok problem has been solved using ExecuteReader. I'm using below code to read next Resultset. Reader = SqlHelper.ExecuteReader(pass parameters) if !(Reader == null) { while(Reader.Read()) { string str = Reader["param1"].ToString(); } Reader.NextResult While(Reader.read()) { string str1 = Reader["Param2"].ToString(); } } Many many thanks for ur help too. Thanks Keep in touch My email id:- kbalu_2001@rediffmail.com
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
Ok, but then you are not reading the value from the output parameter.
--- b { font-weight: normal; }
-
Ok, but then you are not reading the value from the output parameter.
--- b { font-weight: normal; }
Hello Guffa, I'm not reading the value from output parameter, that is the problem because when I write code like string str = arParams[2].value.ToString(); it return null and fires exceprtion. I'm not able to understand what exactly happing behind the screen. ok thanks for ur help
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
Hello Guffa, I'm not reading the value from output parameter, that is the problem because when I write code like string str = arParams[2].value.ToString(); it return null and fires exceprtion. I'm not able to understand what exactly happing behind the screen. ok thanks for ur help
Balasaheb Software Developer Platform: Asp.net,vb.net Database: SQL Server 2000
-
The property is named
Value, notvalue. Other than that, the code is correct.BalasahebK wrote:
it return null and fires exceprtion
What exception does it fire? What is the error message?
--- b { font-weight: normal; }