Re: why should I care about ExecuteScalar - ASP.NET Discussion Boards

anubhaw.gupta25-Dec-12 22:45

25-Dec-12 22:45

alter procedure mdi_anu  
(  
@entry_date varchar(20),  
@updated_date varchar(20),  
@status varchar(20),  
@cno varchar(20),  
@eno varchar(20)
)  
  
as 
begin
declare @SQL varchar(max)  
set @SQL='SELECT * FROM anu123 where 1=1 '  
  
	--if (@status='ALL')  
	--	begin  
	--	set @status='ALL'  
	--	set @SQL=@SQL + 'and status=''' +@status
	--end  
  
	if (@status='A')  
		begin  
		set @status='A'  
		--set @SQL=@SQL+' and status='''+@status
	end  
  
	if (@status='R')  
		begin  
		set @status='R'  
		set @SQL=@SQL+' and status=''' +@status
	end  
  
	if (@status='D')  
		begin  
		set @status='D'  
		set @SQL=@SQL+' and status=''' +@status
	end  
  
	if (@status='P')  
		begin  
		set @status='P'  
		set @SQL=@SQL+' and status=''' +@status
	end 
	
	
	 
	if (@entry_date<>'' and @updated_date<>'')  
	begin  
   
	set @entry_date =CONVERT (datetime, @entry_date, 103)        
	set @updated_date= CONVERT(datetime, @updated_date, 103)  
	set @SQL=@SQL+'and entry_date>=''' +@entry_date+'''and updated_date<='''+@updated_date+''''  
	end
  
	if (@cno<>'')  
	begin  
	set @cno=@cno  
	set @SQL=@SQL+'and cno=''' +@cno+
	end  

  
	if (@eno<>'')  
	begin  
	set @eno=@eno  
	set @SQL=@SQL+'and eno=''' +@eno+
	end 

exec (@SQL)

end

the error which m getting is that,suppose m choosing 'D' as my input in the status i get
"Unclosed quotation mark after the character string 'D'.
Incorrect syntax near 'D'."
Whats the problem?

Re: Syntax error in store procedure

sathish s26-Dec-12 1:39

sathish s

26-Dec-12 1:39

May be you should append a single quotes after +@status in all your if conditions, if it a varchar field?

Re: Syntax error in store procedure

Zafar Sultan26-Dec-12 3:31

Zafar Sultan

26-Dec-12 3:31

Include a space before and after every string. For example instead of writing 'and eno=''' write ' and eno='' '

Hope it helps. If it does not instead of exec(@SQL) in your SP write print(@SQL) and you will have the complete SQL Query. That will clear the picture to a good deal where the error is.

id or guid.. which one?

Jassim Rahma25-Dec-12 7:22

Jassim Rahma

25-Dec-12 7:22

Hi,

I have a members website and every memeber will have his images saved ina folder on host.

I wanted to ask what's best way to do it:

/member_id/images

/member_guid/images

Technology News @ www.JassimRahma.com

Re: id or guid.. which one?

Sandeep Mewara25-Dec-12 19:14

Sandeep Mewara

25-Dec-12 19:14

Jassim Rahma wrote:
/member_id/images

I would go with Member_Id as that sounds unique and will have a significant value.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

Re: id or guid.. which one?

CommDev25-Dec-12 19:25

CommDev

25-Dec-12 19:25

Hi,

if the id will be changed, I suggest to choose the first one, or they are the same.

Comm100 - Leading Live Chat Software Provider

modified 28-May-14 1:46am.

why should I care about ExecuteScalar

Jassim Rahma25-Dec-12 5:57

Jassim Rahma

25-Dec-12 5:57

Hi,

I have been using ExecuteNonReader for a quite long time now and it's doing great for me. For example

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteNonReader();

then why should I use it like this:

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteScalar();

Technology News @ www.JassimRahma.com

Re: why should I care about ExecuteScalar

Sandeep Mewara25-Dec-12 19:30

Sandeep Mewara

25-Dec-12 19:30

Jassim Rahma wrote:
ExecuteNonReader

I don't find a knowledge base article for the same in MSDN. Based on what I know and can infer, using it to get just a 'single' value will be an overhead. It should be used when database query is going to provide a set of records.

My guess, term in discussion is ExecuteNonQuery and should not be ExecuteNonReader.

Jassim Rahma wrote:
ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query.
Refer: Refer: SqlCommand.ExecuteScalar Method [^]

Now, based on the details and documentation, for getting a count, ExecuteScalar fits in the best that will get me the result directly as needed.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

Re: why should I care about ExecuteScalar

CommDev25-Dec-12 19:33

CommDev

25-Dec-12 19:33

Hi,

ExecuteNonReader: used actions like insert, update, delete

ExecuteScalar: will return an object

for details you can learn from some books about these two.

Thanks,
CommDev

modified 6-Dec-16 1:27am.

Re: why should I care about ExecuteScalar

CommDev25-Dec-12 19:40

CommDev

25-Dec-12 19:40

Hi,

ExecuteReader

Do not use: when database query is going to provide for sure exactly 1 record. It may be getting record by its id (which is PK in the database) - GetOrderById and such. In this case use ExecuteNonQuery with output parameters.

Use: when database query is going to provide a set of records. It may be search or report.

ExecuteNonQuery

Use: when we are talking about a single database record - in Update, Insert, Delete and Get by Id. In all these cases we can use input/output/input-output parameters. Please note that from the application architecture point of view it is also good practices when your Insert and Update stored procedure returns changed record exactly like Get By Id method does.

ExecuteScalar

Do not use: when database query returns a single value and this value can be defined as parameter in T-SQL. ExecuteNonQuery with output parameter(s) is always preferred in this case since it is more flexible, tomorrow there will be 2 values therefore having ExecuteNonQuery we do not need to change method signatures.

Use: when database query returns a single value and this value cannot be defined as output parameter, because of T-SQL type limitation for variables. For example type image cannot be output parameter in MSSQL.

The most common example for ExecuteScalar is fetching a single image stored in the database and converting it to array of bytes. If you google it - most examples will demonstrate using of ExecuteReader to accomplish image handler, however ExecuteScalar will be more scalable and faster.

Conclusion

Always use ExecuteNonQuery except: when you have a set of records - use ExecuteReader and when you have a single output value that cannot be defined as a parameter - use ExecuteScalar. Hope this helped to clarify something. Enjoy Smile | :)

Comm100 - Leading Live Chat Software Provider

Re: why should I care about ExecuteScalar

Jassim Rahma28-Dec-12 9:40

Jassim Rahma

28-Dec-12 9:40

Thanks CommDev.

But if I am going to return GetOrderById.. what's the difference if I am going to use ExecuteNonQuery with Output parameter or ExecuteReader with return SqldataReader?

Does it make a difference specially performance wise?

Technology News @ www.JassimRahma.com

Re: why should I care about ExecuteScalar

Naveen Katta26-Dec-12 0:10

Naveen Katta

26-Dec-12 0:10

hi,

Generally we use execute scalar function for displaying the first column and first row, but if we use ExecuteNonReader it will show error why because in asp.net only 3 commands are there ExecuteReader()
ExecuteNonQuery()
ExecuteScalar() this is the option to get one value from the database.

a control that should contain html elements

Ali Al Omairi(Abu AlHassan)23-Dec-12 23:01

Ali Al Omairi(Abu AlHassan)

23-Dec-12 23:01

Guys;
I am developing a control that should contain html elements
like

ASP.NET

<web:CommandLink ID="cmdDelete" runat="Server" >
    <img alt="Delete" src="images/delete.jpg" />
</web:CommandLink>

I started with implementing IParserAccessor.
Am I in the right direction ?

Thank you.

Help people,so poeple can help you.

modified 24-Dec-12 5:29am.

Re: a control that should contain html elements

jkirkerx24-Dec-12 13:09

jkirkerx

24-Dec-12 13:09

So your trying to make a server or user control?

Re: a control that should contain html elements

Ali Al Omairi(Abu AlHassan)25-Dec-12 19:51

Ali Al Omairi(Abu AlHassan)

25-Dec-12 19:51

A server one.

the new thing is I implemented IParserAccessor.AddParsedSubObject this way

Public Sub AddParsedSubObject(ByVal obj As Object) Implements System.Web.UI.IParserAccessor.AddParsedSubObject
    If (TypeOf (obj) Is LiteralControl) Then
        Me._templateText = CType(obj, LiteralControl).Text
    Else
        Throw New Exception("Not supported")
    End If
End Sub

it works fine but it gives me the validation error 'img cannot be nested with commandlink'.
what should I do to avoid that?

Help people,so poeple can help you.

checkedchange event of gridview in javascript

Ravi_kant23-Dec-12 19:29

Ravi_kant

23-Dec-12 19:29

Hi All
i have a gridview

XML

<asp:GridView ID="GridView2" runat="server" Width="100%"
            AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="description" HeaderText="Description" >
                <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                </asp:BoundField>
                <asp:BoundField DataField="Amount" HeaderText="Amount" >
                <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                </asp:BoundField>
                <asp:TemplateField HeaderText="Month">
                    <ItemTemplate>
                        <asp:CheckBox ID="CheckBoxJan" runat="server" Text="Jan" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxFeb" runat="server" Text="Feb" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxMar" runat="server" Text="Mar" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxApr" runat="server" Text="Apr" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxMay" runat="server" Text="May" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxJun" runat="server" Text="Jun" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxJul" runat="server" Text="Jul" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxAug" runat="server" Text="Aug" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxSep" runat="server" Text="Sep" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxOct" runat="server" Text="Oct" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxNov" runat="server" Text="Nov" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <asp:CheckBox ID="CheckBoxDec" runat="server" Text="Dec" AutoPostBack="True"
                            oncheckedchanged="CheckBoxJan_CheckedChanged" />
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

and i am using checked change event for checkbox as

protected void CheckBoxJan_CheckedChanged(object sender, EventArgs e)
{
    string desc = "";
    Double amount = 0;
    for (Int32 i = 0; i < GridView2.Rows.Count; i++)
    {
        double amt = 0;
        CheckBox checkBox1 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxJan");
        bool isChecked1 = checkBox1.Checked;
        CheckBox checkBox2 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxFeb");
        bool isChecked2 = checkBox2.Checked;
        CheckBox checkBox3 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxMar");
        bool isChecked3 = checkBox3.Checked;
        CheckBox checkBox4 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxApr");
        bool isChecked4 = checkBox4.Checked;
        CheckBox checkBox5 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxMay");
        bool isChecked5 = checkBox5.Checked;
        CheckBox checkBox6 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxJun");
        bool isChecked6 = checkBox6.Checked;
        CheckBox checkBox7 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxJul");
        bool isChecked7 = checkBox7.Checked;
        CheckBox checkBox8 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxAug");
        CheckBox checkBox9 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxSep");
        bool isChecked8 = checkBox8.Checked;
        CheckBox checkBox10 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxOct");
        bool isChecked9 = checkBox9.Checked;
        CheckBox checkBox11 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxNov");
        CheckBox checkBox12 = (CheckBox)GridView2.Rows[i].Cells[2].FindControl("CheckBoxDec");
        bool isChecked10 = checkBox10.Checked;
        bool isChecked11 = checkBox11.Checked;
        bool isChecked12 = checkBox12.Checked;
        if (isChecked1 && checkBox1.Enabled==true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Jan" + ",";
        }
        if (isChecked2 && checkBox2.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Feb" + ",";
        }
        if (isChecked3 && checkBox3.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Mar" + ",";
        }
        if (isChecked4 && checkBox4.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Apr" + ",";
        }
        if (isChecked5 && checkBox5.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "May" + ",";
        }
        if (isChecked6 && checkBox6.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Jun" + ",";
        }
        if (isChecked7 && checkBox7.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Jul" + ",";
        }
        if (isChecked8 && checkBox8.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Aug" + ",";
        }
        if (isChecked9 && checkBox9.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Sep" + ",";
        }
        if (isChecked10 && checkBox10.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Oct" + ",";
        }
        if (isChecked11 && checkBox11.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Nov" + ",";
        }
        if (isChecked12 && checkBox12.Enabled == true)
        {
            amt = Convert.ToDouble(GridView2.Rows[i].Cells[1].Text);
            amount = amount + amt;
            desc = desc + "Dec" + ",";
        }
    }
    Label24.Text = amount.ToString();
    Label17.Text = (Convert.ToDouble(Label23.Text) + Convert.ToDouble(Label24.Text) + Convert.ToDouble(Label27.Text)).ToString();
    Label29.Text = desc;
}

so here after every checked change page is getting reloaded but i want to do this in javascript(client side)

so please help me to do this

regards
Ravi kant

Re: checkedchange event of gridview in javascript

Sampath Kumar Sathiya24-Dec-12 0:20

Sampath Kumar Sathiya

24-Dec-12 0:20

http://www.c-sharpcorner.com/uploadfile/MohanKumar.R/selecting-checkboxes-inside-the-gridview-control/[^]

Sam

Re: checkedchange event of gridview in javascript

Ravi_kant24-Dec-12 7:40

Ravi_kant

24-Dec-12 7:40

Thanks for the reply

updated data from gridview

Member 970720723-Dec-12 0:30

Member 9707207

23-Dec-12 0:30

helo frnds,

im trying to capture updated data from gridview upon check box selection..

however im unable to retreive new data..

it alwasy capture old data...pls help me...

i haave used gridediteventargs..but in vain......

i want to capture new data in button click event

best rgrds

nancy

Re: updated data from gridview

Ali Al Omairi(Abu AlHassan)24-Dec-12 4:31

Ali Al Omairi(Abu AlHassan)

24-Dec-12 4:31

Ma'am;
You have a gridview with a check box in each row and you want an array of the selected rows keys. right?

if so, you should have a template column that contain a check box with a specific name say 'Selection'

HTML

<input type="checkbox" name="Selection" value="<%# Eval("Key")%>" />

when you postback the page you can get the array using Request["Selection"]

Help people,so poeple can help you.

Re: updated data from gridview

Sandeep Mewara25-Dec-12 19:35

Sandeep Mewara

25-Dec-12 19:35

Member 9707207 wrote:
im trying to capture updated data from gridview upon check box selection..however im unable to retreive new data..

Just check if you are binding the grid data again on postback. If so, before the update event is raised the data will be back to previous state and changes will be lost.

You need to put the databind code for the grid in Page.IsPostback property to avoid a rebind until unless specified in a postback.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

.net question

Tarani Dutt Joshi21-Dec-12 23:53

Tarani Dutt Joshi

21-Dec-12 23:53

How to convert English words to Hindi words with help of coding it is possible with out any data base?

Re: .net question

Richard MacCutchan22-Dec-12 0:18

Richard MacCutchan

22-Dec-12 0:18

Think about this: how many words do you expect to convert? Can you keep that many words somewhere without a database of some sort?

One of these days I'm going to think of a really clever signature.

Re: .net question

MaulikDusara24-Dec-12 21:38

MaulikDusara

24-Dec-12 21:38

Hi,
You can try Google Translator API. Given link may be useful to you.

Google Translator[^]

Maulik Dusara
Sr. Sofware Engineer

Re: .net question

Sandeep Mewara25-Dec-12 19:33

Sandeep Mewara

25-Dec-12 19:33

Tarani Dutt Joshi wrote:
with help of coding it is possible with out any data base

If you plan to translate by your own, you would need database! If you want the conversion without too much of an effort, you can try a 3rd party translation service where you pass on the English words and get back the translated value. Google translate could be of help to start with.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.