11 Replies - 30271 Views - Last Post: 20 July 2010 - 07:17 AM Rate Topic: -----

#1 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

how to update the gridview by using the button click

Posted 16 July 2010 - 03:55 PM

Hi,

I am new on asp.net. I am working on a website which connect to sql db and get the data from the db to the gridview.

The website now can show the data when the page is loaded. But what I also want is having an option to me to choose the start day and the the end day, so I put the ajax's CalendarExtender and insert two textbox for start day, end day and a button. When the button is clicked then show the data from start day and end day on the gridview.

I have done alot of resarch on the web but haven't got any luck.

Any pointer here would be great.

Thanks all!

Is This A Good Question/Topic? 0
  • +

Replies To: how to update the gridview by using the button click

#2 ramesh866   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 91
  • Joined: 03-October 09

Re: how to update the gridview by using the button click

Posted 18 July 2010 - 04:02 AM

assuming that you have date associated with each data entry in the sql db, you can use the following code for retrieving datas within that particular dates
 string s = ConfigurationManager.ConnectionStrings["masterConnectionString"].ConnectionString;
        SqlConnection cn = new SqlConnection(s);
        cn.Open();
        SqlCommand cm1 = new SqlCommand("select * from table where date between '"+TextBox1.Text+"' and TextBox2.Text",cn);
        DataTable dt1 = new DataTable();
        SqlDataAdapter adp = new SqlDataAdapter(cm1);
        adp.Fill(dt1);
        GridView1.DataSource = dt1;
        GridView1.DataBind();

Was This Post Helpful? 0
  • +
  • -

#3 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: how to update the gridview by using the button click

Posted 18 July 2010 - 11:32 AM

Hi,

Thank you, but I am using vb.net
Was This Post Helpful? 0
  • +
  • -

#4 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: how to update the gridview by using the button click

Posted 18 July 2010 - 02:12 PM

Hi, can someone help me with the following code. Tks!

 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim sql As String = "SELECT * FROM [Custom] WHERE [T_DATE]='" & txtStartDate.Text & "' AND [T_DATE]='" & txtEndDate.Text & "'"
        Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)
        Dim myCommand As New SqlCommand(sql, myConnection)
        myConnection.Open()

        Dim ds As Data.DataSet = New Data.DataSet

        GridView1.DataSource = ds
        GridView1.DataBind() 'Got stucked here says Both DataSource and DataSourceID are defined on 'GridView1'.  Remove one definition.
        myCommand.Dispose()

        myConnection.Close()


Was This Post Helpful? 0
  • +
  • -

#5 Jayman   User is offline

  • Student of Life
  • member icon

Reputation: 423
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: how to update the gridview by using the button click

Posted 18 July 2010 - 07:37 PM

You need to fill the datset with data. Use a SqlDataAdapter instead of a SqlCommand object to fill the DataSet.

See below:
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim sql As String = "SELECT * FROM [Custom] WHERE [T_DATE]='" & txtStartDate.Text & "' AND [T_DATE]='" & txtEndDate.Text & "'"
        Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)
        Dim myAdapter As New SqlDataAdapter(sql, myConnection)
        myConnection.Open()

        Dim ds As Data.DataSet = New Data.DataSet
        myAdapter.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind() 'Got stucked here says Both DataSource and DataSourceID are defined on 'GridView1'.  Remove one definition.
        myCommand.Dispose()

        myConnection.Close()


Was This Post Helpful? 1
  • +
  • -

#6 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 06:32 AM

Thanks Jayman! Still having the same error msg of "Both DataSource and DataSoureID are defined on 'GridView1'. Remove one definition."

Can someone help me with the following code? Thank you!

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:masterConnectionString %>" 
                    
 SelectCommand="SELECT * FROM [Custom]" 
                    
 ProviderName="<%$ ConnectionStrings:masterConnectionString.ProviderName %>">
 </asp:SqlDataSource>

'***************************  VB.NET  
 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

 Dim sql As String = "SELECT * FROM [Custom]"
 Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)
 'Dim myCommand As New SqlCommand(sql, myConnection)
 Dim myAdapter As New SqlDataAdapter(sql, myConnection)
 myConnection.Open()

 Dim ds As Data.DataSet = New Data.DataSet
 myAdapter.Fill(ds)

 GridView1.DataSource = ds
 GridView1.DataBind() 'InvalidOperationException was unhandled by user code (Both DataSoure and DataSourceID are defined on 'GridView1'. Remove one definition.
 'myCommand.Dispose()

 myConnection.Close()

    End Sub
End Class


Was This Post Helpful? 0
  • +
  • -

#7 Frinavale   User is offline

  • D.I.C Addict
  • member icon

Reputation: 205
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 07:11 AM

Go to your ASPX page and look at the code. You have probably set the DataSoureID property for the GridView.

You cannot set the GridView's DataSoureID property and then (in your VB.NET Code) set the GridView's DataSoureID property...

-Frinny
Was This Post Helpful? 1
  • +
  • -

#8 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 08:09 AM

Tks Frinny! It works fine now.

For somehow the GridView didn't show any data when I added the specific date's range to the sql query.

Do you know what did I do wrong?

Dim sql As String = "SELECT [T_Date], [Curr], [Orders], ([Sold] + [Bought]) * 0.001 as [Comm], [Sold], [Bought], FROM [Custom] WHERE [T_Date]='" & txtStartDate.Text & "' and [T_Date]='" & txtEndDate.Text & "'"


Was This Post Helpful? 0
  • +
  • -

#9 Frinavale   User is offline

  • D.I.C Addict
  • member icon

Reputation: 205
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 08:47 AM

I'm not sure, try executing your SQL command using Query Analyzer to see if it actually returns data there. If it does then, step through your application (debug) and make sure that there is data within the table in the DataSet that you are filling.

-Frinny
Was This Post Helpful? 0
  • +
  • -

#10 Jayman   User is offline

  • Student of Life
  • member icon

Reputation: 423
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 01:17 PM

Sql Server uses a DateTime data type for storing dates, ie. 11/24/2009 12:23:40 PM. So you cannot use the equals sign to check for the date, unless you are entering the date and time into your textboxes.

I am guessing you are only entering the date, therefore you need to check if it is GREATER THAN OR EQUAL TO the start date and LESS THAN the end date + 1. You can use the DATEADD function to add 1 day to the end date.

Example:
Dim sql As String = "SELECT [T_Date], [Curr], [Orders], ([Sold] + [Bought]) * 0.001 as [Comm], [Sold], [Bought], FROM [Custom] WHERE [T_Date] >= '" & txtStartDate.Text & "' and [T_Date] < DATEADD(day, 1, '" & txtEndDate.Text & "')"


Was This Post Helpful? 0
  • +
  • -

#11 Frinavale   User is offline

  • D.I.C Addict
  • member icon

Reputation: 205
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: how to update the gridview by using the button click

Posted 19 July 2010 - 01:38 PM

To be quite honest I would never create a SQL command by concatenating user provided values into the string that contains the command. This leaves you wide open to an SQL Injection Attack.

An SQL Injection Attack occurs when the user provides SQL syntax instead of the intended value.

For example, say the user was supposed to provide a "First Name" in a TextBox...but instead of entering their First Name they entered "; DROP TABLE Employees; --"

Their input ends your SQL statement (the ";" ends your SQL statement and the "--" comments out the rest of your command) and executes something that could potentially be devastating to your database!

Since the Database management system compiles their input as part of your SQL command...your database management system will execute their command.

To avoid this take the necessary steps to limit your database user permissions, consider using Stored Procedures (which are pre-compiled by the database management system... so their code isn't compiled into yours), and use SQL Parameters. When you use SQL Parameters the data provided by the user is treated as "literal" data instead of a command.

-Frinny

This post has been edited by Frinavale: 20 July 2010 - 07:28 AM

Was This Post Helpful? 1
  • +
  • -

#12 vennesschan   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

Re: how to update the gridview by using the button click

Posted 20 July 2010 - 07:17 AM

Got it, tks! :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1