sqldatasource control

Home ›

ASP.NET Forums » Data Access » SQL Server, SQL Server Express, and SqlDataSource Control » sqldatasource control

Previous Next

Last post 05-11-2008 10:24 PM by Bo Chen – MSFT. 14 replies.

Sort Posts:

sqldatasource control

05-06-2008, 6:02 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi friends,

I created sqldatasource control. In select command i written the query like this "select * form emp" and bounded in grid.How can I change the query for searching the details according the date wise when i click the search button.

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-06-2008, 6:18 AM

Contact

Loading...
rbansal44
Joined on 12-11-2007, 10:09 AM
Posts 89

hi,

what you can do is you can take do by dataset i.e by ado.net.change the query on the button click event and bind it to the gridview.

thanks,

rajiv

Re: sqldatasource control

05-06-2008, 6:38 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi rajiv,

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DMSConnectionString %>"
                    SelectCommand="Sp_GetPatientInfo" SelectCommandType="StoredProcedure" UpdateCommand= "Sp_UpdatePatientInfo"         UpdateCommandType="StoredProcedure" >
                    <SelectParameters>
                                            <asp:SessionParameter Name="DrID" SessionField="UserID" Type="String" />
                        <asp:ControlParameter ControlID="Calendar1" Name="eDate" PropertyName="SelectedDate"
                            Type="DateTime" />
                    </SelectParameters>

                </asp:SqlDataSource>

<asp:GridView ID="grdDocs" runat="server"
                Height="176px" Width="1135px" Font-Names="Arial" Font-Size="X-Small"
                    EmptyDataText="There are no files for this date." DataSourceID="SqlDataSource1"
                    HorizontalAlign="Center" AutoGenerateColumns="False" DataMember="DefaultView"
                    OnRowCommand="grdDocs_RowCommand" OnRowDataBound="grdDocs_RowDataBound"
                    CellPadding="4" ForeColor="#333333" GridLines="None"
                    onselectedindexchanged="grdDocs_SelectedIndexChanged" style="z-index :10;" >

I binded the values like this. While i click the search button how can i change.

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-06-2008, 6:54 AM

Contact

Answer

Loading...
ShivaKarthik
Joined on 04-15-2008, 1:11 PM
Hyderabad
Posts 161

Hi Vidhya,

From ur snippet i came to a conclusion that in the Sqldatasource you are using Stored Procedures right

then i want to know whether the button is on the gridview or on the webform

if it is on the webform

do this forget about the sqldatasource

define a method called

public void BindData()

{

//perform ur stored procedure here

}

in the Button event do this

In HTML tag OnClick=<%Bind(BindData())%>

OnClick=BindData()

so that when the button clciks this method will be called there.

Let me Know if this helps you or else i will provide you with the code.But do inform me what actually tour task is.

Mark As Answer if this helps you

Further Queries REcommended

Happy Coding

Regards,
B.ShivaKarthik
Software Engineer
Hyderabad,India

Re: sqldatasource control

05-06-2008, 6:56 AM

Contact

Answer

Loading...
rbansal44
Joined on 12-11-2007, 10:09 AM
Posts 89

hi,

well i don't have much knowledge about the inline coding.....so i am doing my code in c#.

well in c# i am pasting the code......

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class Default4 : System.Web.UI.Page

{

SqlConnection con = new SqlConnection("server=.;uid=sa;pwd=;database=master");

SqlDataAdapter da;

DataSet ds;protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

string str = "select * from xyz where date='" + Calendar1.SelectedDate + "'";

da = new SqlDataAdapter(str, con);

ds = new DataSet();

da.Fill(ds, "abc");

GridView1.DataSource = ds.Tables["abc"];

GridView1.DataBind();

}

and the inline code is

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Page</title>

</head>

<body>

<div>

 <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

<asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>

</div>

</form>

</body>

</html>

hope it helps you..

thanks,

rajiv

Re: sqldatasource control

05-07-2008, 12:43 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi shivakarthik,

Without using sqldatasource it works fine. By using sqldatasource i m unable to search the content according to the fromdate and todate.

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-07-2008, 12:44 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi rajiv,

thanks for yr reply, I need to bind the values using sqldatasource control.

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-07-2008, 1:41 AM

Contact

Loading...
rbansal44
Joined on 12-11-2007, 10:09 AM
Posts 89

hi vidyashri,

this link might work for you...

http://msdn.microsoft.com/en-us/library/dz12d98w(vs.80).aspx

thanks,

rajiv

Re: sqldatasource control

05-07-2008, 2:10 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi rajiv,

<asp:GridView ID="grdDocs" runat="server"
                Height="176px" Width="1135px" Font-Names="Arial" Font-Size="X-Small"
                    EmptyDataText="There are no files for this date." DataSourceID="SqlDataSource1"
                    HorizontalAlign="Center" AutoGenerateColumns="False" DataMember="DefaultView"
                    OnRowCommand="grdDocs_RowCommand" OnRowDataBound="grdDocs_RowDataBound"
                    CellPadding="4" ForeColor="#333333" GridLines="None"
                    onselectedindexchanged="grdDocs_SelectedIndexChanged" style="z-index :10;" >

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DMSConnectionString %>"
                    SelectCommand="Sp_GetPatientInfo" SelectCommandType="StoredProcedure" UpdateCommand= "Sp_UpdatePatientInfo"         UpdateCommandType="StoredProcedure" >
                    <SelectParameters>
                                            <asp:SessionParameter Name="DrID" SessionField="UserID" Type="String" />
                        <asp:ControlParameter ControlID="Calendar1" Name="eDate" PropertyName="SelectedDate"
                            Type="DateTime" />
                    </SelectParameters>

                </asp:SqlDataSource>

Like this I created my stored procedure,

create procedure Sp_GetPatientInfo (@DrID varchar(25), @eDate smalldatetime)as

select patientid as 'Patient ID', patname as 'Patient Name', edate as 'Dictated Date' from waveinfo where drid = @DrID and edate = @eDate

When i click the button i need to display the records between two dates.I need to use the below stored procedure how i can use it ?

create procedure Sp_GetPatientInfo (@DrID varchar(25), @eDate smalldatetime,@eDate1 smalldatetime)as

select patientid as 'Patient ID', patname as 'Patient Name', edate as 'Dictated Date' from waveinfo where drid = @DrID and edate>= @eDate and edate<=@edate1

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-07-2008, 2:20 AM

Contact

Loading...
rbansal44
Joined on 12-11-2007, 10:09 AM
Posts 89

hi vidyashri,

well as i have said you i am not good at inline coding....what i can help you is i can just provide the link where you can find the tutorial for sqldatasource.

http://www.asp.net/LEARN/data-access/tutorial-47-cs.aspx

Thanks,

rajiv

Re: sqldatasource control

05-07-2008, 3:39 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi rajiv,

Thanks for spending your golden time for me. Partially i got solution.

Thanks & Regards,
B.Vidhyashri.,

Re: sqldatasource control

05-07-2008, 6:26 AM

Contact

Answer

Loading...
ShivaKarthik
Joined on 04-15-2008, 1:11 PM
Hyderabad
Posts 161

Hi Vidya,

Here is the solution please follow the steps

1) Once you configured Sqldatasource right?????

2)so you click on the smart tag of your Data Grid there will be a option by name "Configure Data Source"

3)Click on that you will come across the wizard.

4)From the dropdown list uou pick up the ConnectionString.

5)Then since once you configured it already there will be radiobutton checked of Using stored procedure

6)Click next then under the select statement there will be you query you modify the previous query to the present query

7)Click next then you will come across u supplied values to your parameters on the left side.

8)To the right side there will be three dropdownboxes

9)From the first dropdownbox pick up control to it

10)From the second DropDown Box pick up from where you are suppling the value to the database from webforms for example(Textbox);

11)Click next and complete the wizard

12)SO NOW YOU MODIFIED UR STORED PROCEDURE right???????

Let me know if this helps you

NOTE:These Steps are for Using SqlDatsource Using Wizard.If not let me know vidhya i will help you from scratch

Mark As Answer if this helps you

Further Queries Recommended

Happy Coding

Regards,
B.ShivaKarthik
Software Engineer
Hyderabad,India

Re: sqldatasource control

05-07-2008, 6:32 AM

Contact

Loading...
aloksinha83@gmail.com
Joined on 03-11-2008, 9:43 AM
Delhi
Posts 87

Hi,

SqlDataSource1.SelectCommand = "ur changed query ";

Alok

Re: sqldatasource control

05-07-2008, 9:30 AM

Contact

Loading...
vidhyashri
Joined on 04-07-2008, 7:42 AM
Posts 24

hi alok,

I m getting this error. when i try to click the button to search the result according to the date.

"Both DataSource and DataSourceID are defined on 'grdDocs'. Remove one definition."