Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter - C# Discussion Boards

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK29-Nov-06 23:31

29-Nov-06 23:31

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 ANSI_NULLS ON
GO

Now u able to understand what is the actual problem.
Thanks in advance

Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000

Re: How to read value from database using sqlhelper.ExecuteReader &amp; Stored procedure, Output parameter

Guffa30-Nov-06 0:08

Guffa

30-Nov-06 0:08

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 Value property of the parameter object. The value is returned boxed inside an object, so you have to unbox it by casting it to the type int.

---
b { font-weight: normal; }

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK30-Nov-06 0:07

BalasahebK

30-Nov-06 0:07

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

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

Guffa30-Nov-06 0:10

Guffa

30-Nov-06 0:10

Ok, but then you are not reading the value from the output parameter.

---
b { font-weight: normal; }

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK30-Nov-06 0:49

BalasahebK

30-Nov-06 0:49

yes, no need to use output parameter.

if u know how to take value from output parameter then pls let me know.
that solution will also helpful to me

Thanks for ur help

Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK30-Nov-06 1:31

BalasahebK

30-Nov-06 1:31

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

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

Guffa30-Nov-06 2:04

Guffa

30-Nov-06 2:04

The property is named Value, not value. 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; }

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK30-Nov-06 2:24

BalasahebK

30-Nov-06 2:24

now my prob. has been solved using output parameter too.

it can be possible to retreive value after reder.nextresult.

ok thanks for ur help

have nice day
bye now

Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

Not Active29-Nov-06 21:47

Not Active

29-Nov-06 21:47

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

Re: How to read value from database using sqlhelper.ExecuteReader & Stored procedure, Output parameter

BalasahebK29-Nov-06 22:09

BalasahebK

29-Nov-06 22:09

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) Cry | :((

*****************************************************

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

potentially dangerous !!!

Tamimi - Code29-Nov-06 20:58

Tamimi - Code

29-Nov-06 20:58

this what i got when i submit a html tag :
Request.QueryString value was detected from the client (keywords="<html").

i know="" that="" we="" can="" solve="" this="" with="" validaterequest="false" ,="" but="" is="" there="" other="" ways="" to="" it.........
thank="" you="" all=""
=""

<div="" class="ForumSig">

When you get mad...THINK twice that the only advice
Tamimi - Code

Re: potentially dangerous !!!

Guffa30-Nov-06 0:17

Guffa

30-Nov-06 0:17

No, there is no other way to solve it. If you want to be able to post any text you have to turn the request validation off.

User input should always be treated as potentially dangerous, regardless if you have request validation on or off. The value should not be used for anything without encoding it properly.

If you display the value on a web page, it should be HTML encoded. If you put the value in an URL, it should be URL encoded. If you put the value in an SQL query, it should be properly encoded to be a SQL string value. If you put the value in Javascript code, it should be properly encoded to be a Javascript string value.

---
b { font-weight: normal; }

XmlDocument - Browser View in Form

h@s@n29-Nov-06 20:35

h@s@n

29-Nov-06 20:35

Hi
please help me in this:

I have loaded xml document using following DOM class:

XmlDocument xDoc = new XmlDocument();<br />
<br />
xDoc.Load("check.xml");

Now how can i view this xml file similar to internet explorer display?

Either i should draw Rich Text Box or drop WebControl on my the form? I have added WebControl in toolbox but it is disable there.

please help me.

Thank you.

Re: XmlDocument - Browser View in Form

Nader Elshehabi29-Nov-06 22:39

Nader Elshehabi

29-Nov-06 22:39

You can parse, and display Xml documents the way you like. This way you'd build your own parse -hard and long, but maybe you need some functionality there?!-, as long as you follow the W3C standards.

If you wish to take the WebControl shortcut -which would seem reasonable-, I guess that'd be fine! You mentioned in your post that the WebControl -assuming you mean WebBrowser- is disabled. Where? In the Toolbox or in your Form?

PS.
You can search CodeProject and other similar sites for free Xml displaying Control. Maybe you'd find one that suits you.

Regards

Re: XmlDocument - Browser View in Form

h@s@n29-Nov-06 23:00

h@s@n

29-Nov-06 23:00

Thanks dear,

WebControl is disabled in mine toolbox. What is right control for the functionality is that WebBrowserControl or WebControl.

Also please tell me steps to perform.

Regards.

Re: XmlDocument - Browser View in Form

Nader Elshehabi30-Nov-06 1:06

Nader Elshehabi

30-Nov-06 1:06

In your Toolbox, look for "WebBrower". You can find it under "All Windows Forms" or "Common Control".

Use the WebBrowser.Navigate() mthod to open your Xml file.

Regards

Number to words utility

Ramesh.V29-Nov-06 20:33

Ramesh.V

29-Nov-06 20:33

Hi friends,

I am new to this forum. now i want a utility to convert numer to words.

for example 234 to two hundred and thirty four.

i searched in the google.com i could not able to trace it out.

please help me

Thank you in advance

V.Ramesh

Re: Number to words utility

Nader Elshehabi29-Nov-06 22:47

Nader Elshehabi

29-Nov-06 22:47

I guess this is your lucky day.;) I made one just few months ago. Here is the code.

It consists of 3 methods. You call the ToLiteral() method and supply it with the string you want to parse. It will subsequently call the other two methods to retreive the literals and the segments names. Forgive me for the poor comment -never been good in commenting-. I included a button click even handler to show you how to test for output and how to use the ToLiteral() method.

private void TestButton_Click(object sender, EventArgs e)
    {
        //Random numbers in a MessageBox to test output
        MessageBox.Show("4\n" + ToLiterals("4"));
        MessageBox.Show("57\n" + ToLiterals("57"));
        MessageBox.Show("209\n" + ToLiterals("209"));
        MessageBox.Show("8734\n" + ToLiterals("8734"));
        MessageBox.Show("24567\n" + ToLiterals("24567"));
        MessageBox.Show("973654\n" + ToLiterals("973654"));
        MessageBox.Show("2315736\n" + ToLiterals("2315736"));
        MessageBox.Show("27065154\n" + ToLiterals("27065154"));
        MessageBox.Show("827464876\n" + ToLiterals("827464876"));
        MessageBox.Show("1675376283\n" + ToLiterals("1675376283"));
    }

    private string ToLiterals(string Input)
    {
        String Output = "";
        while (Input.Length > 0)
        {
            if (Input[0] == '0')//Ignore zeros
            {
                Input = Input.Remove(0, 1);
                Output += "And ";
                continue;
            }
            if (Input.Length % 3 != 2)
            {
                // We are at the Xs if our number is XYX,XYX,XYX
                // Either first, or third position in each segment.
                Output += ConvertChar(Input[0].ToString());
                if (Input.Length % 3 == 0 && Input.Length > 2) //third position in a segment
                    Output += "Hundred ";
                else
                    Output += GetSegment(Input.Length / 3); //first position
                    Input = Input.Remove(0, 1);
                continue;
            }
            else
            {
                //We are talking about first and second positions togerther :YXX,YXX,YXX
                if (Input[0] == '1') //Is it Xteen? ie. 11-19
                {
                    Output += ConvertChar(Input.Substring(0, 2));

                }
                else //Or above 19?
                {
                    Output += ConvertChar(Input[0] + "0");
                    Output += ConvertChar(Input[1].ToString());
                }
                Output += GetSegment(Input.Length / 3);
                Input = Input.Remove(0, 2);
            }
        }
        return Output;
    }

    private string GetSegment(int seg)
    {
        switch (seg)
        {
            case 1: { return "Thousand "; }
            case 2: { return "Million "; }
            case 3: { return "Billion "; }
            case 4: { return "Trillion "; }
            default: { return ""; }
                //The list can grow
        }
    }

    private string ConvertChar(string ToConvert)
    {
        switch (ToConvert)
        {
            case "1": { return "One "; }
            case "2": { return "Two "; }
            case "3": { return "Three "; }
            case "4": { return "Four "; }
            case "5": { return "Five "; }
            case "6": { return "Six "; }
            case "7": { return "Seven "; }
            case "8": { return "Eight "; }
            case "9": { return "Nine "; }
            case "10": { return "Ten "; }
            case "11": { return "Eleven "; }
            case "12": { return "Twelve "; }
            case "13": { return "Thirteen "; }
            case "14": { return "Fourteen "; }
            case "15": { return "Fifteen "; }
            case "16": { return "Sixteen "; }
            case "17": { return "Seventeen "; }
            case "18": { return "Eighteen "; }
            case "19": { return "Nineteen "; }
            case "20": { return "Twenty "; }
            case "30": { return "Thirty "; }
            case "40": { return "Fourty "; }
            case "50": { return "Fifty "; }
            case "60": { return "Sixty "; }
            case "70": { return "Seventy "; }
            case "80": { return "Eighty "; }
            case "90": { return "Ninety "; }
            default: { return ""; }
        }
    }