Bonjour,
==========
MON PROBLEME
je veux faire une selection de tuple de ma base en fonction de critere grace a des DropDownList
==========
je suis etudiant en BTS Informatique de gestion 1ere annee.
Je NE PEUX PAS AVOIR D AIDE EN COURS , CAR ON NE DEVELOPPE PAS EN ASP mais en PHP et avec mySQL.
je developpe depuis peu en ASP (avec ASP.NET Web Matrix v0.6), pour des raisons techique. Je dois realiser pour une agence immobiliere un site Internet avec toutes les affaires de leur vitrine, et le fournisseur du PROGICIEL de cette agence utilise SQL Serveur.
J'ai donc utiliser une base de donnees SQL avec SQL Serveur 2000 édition developper (qui est une version entreprise = merci Microsoft pour le programme étudiant MSDN academic alliance). Tout est correctement configure, j'arrive a effectuer une requete dans ma base appelé staimmo, qui contient 1 table ( type transaction, type de bien, code affaire, no mandat, cp, ville, prix mandat... il y en a 50 propriété en tout!).
J ai utiliser le code de base de ASP.NET WebMatrix (File / NewFile / DataPages / FiltredDataRport). Tout fonctionne, voici le code source :
J ai fais une requete sur le code postal (propriete cp) qui donne la ville dans un datagrid (propriete ville)
BIEN LIRE JUSQU A LA FIN DE CE POST
====================================
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
' Databind the filter dropdown on the first request only
' (viewstate will restore these values on subsequent postbacks).
' TODO: update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "server=(local);database=staimmo;trusted_connection=true"
Dim CommandText As String = "select distinct cp from Affaire"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)
' TODO: Update the DataTextField value
DropDownList1.DataTextField = "cp"
myConnection.Open()
DropDownList1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataBind()
' insert an "All" item at the beginning of the list
DropDownList1.Items.Insert(0, "-- All Authors --")
End If
End Sub
Sub ApplyFilter_Click(Sender As Object, E As EventArgs)
' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server=(local);database=staimmo;trusted_connection=true"
Dim CommandText As String
' get the filter value from the DropDownList
Dim filterValue As String = DropDownList1.SelectedItem.Text.Replace("'", "''")
' TODO: update the CommandText value for your application
If filterValue = "-- All Authors --" Then
CommandText = "select distinct ville as ville from Affaire"
Else
CommandText = "select ville as ville from Affaire where cp = '" & filterValue & "'"
End If
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.Open()
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub
</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Filtered Data Report
</h2>
<hr size="1" />
<form runat="server">
<p>
Select an Author:
<asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList>
<asp:Button id="Button1" onclick="ApplyFilter_Click" runat="server" Text="Show Titles"></asp:Button>
</p>
<asp:datagrid id="DataGrid1" runat="server" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False">
<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>
====================================
Je veux multiplier les selections : voici le code que j ai realise avec 2 DropDownList
1 et 2
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
' Databind the filter dropdown on the first request only
' (viewstate will restore these values on subsequent postbacks).
' TODO: update the ConnectionString and CommandText values for your application
' // CONNECTION A LA BASE DE DONNEES
Dim ConnectionString As String = "server=(local);database=staimmo;trusted_connection=true"
' // SELCTION DES ELEMENTS A AFFICHER DANS LES COMBOBOX
Dim CommandText1 As String = "select distinct cp from Affaire"
Dim CommandText2 As String = "select distinct ville from Affaire"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand1 As New SqlCommand(CommandText1, myConnection)
Dim myCommand2 As New SqlCommand(CommandText2, myConnection)
' TODO: Update the DataTextField value
' // MISE A JOUR DES COMBOBOX
DropDownList1.DataTextField = "cp"
DropDownList2.DataTextField = "ville"
myConnection.Open()
DropDownList1.DataSource = myCommand1.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList1.DataBind()
DropDownList2.DataSource = myCommand2.ExecuteReader(CommandBehavior.CloseConnection)
DropDownList2.DataBind()
' insert an "All" item at the beginning of the list
' // AFFICHAGE DES ELTS DANS LES COMBOBOX
DropDownList1.Items.Insert(0, "-- All Authors --")
DropDownList2.Items.Insert(0, "-- All Ville --")
End If
End Sub
Sub ApplyFilter_Click(Sender As Object, E As EventArgs)
' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server=(local);database=staimmo;trusted_connection=true"
Dim CommandText1 As String
Dim CommandText2 As String
' get the filter value from the DropDownList
Dim filterValue1 As String = DropDownList1.SelectedItem.Text.Replace("'", "''")
Dim filterValue2 As String = DropDownList2.SelectedItem.Text.Replace("'", "''")
JE SUIS BLOQUER ICI
If filterValue1 = "-- All Authors --" Then
CommandText1 = "select distinct ville as ville from Affaire"
Else
CommandText1 = "select ville as ville from Affaire where cp = '" & filterValue1 & "'"
End If
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText1, myConnection)
myConnection.Open()
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub
</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Filtered Data Report
</h2>
<hr size="1" />
<form runat="server">
<p>
Select an Author:
<asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList>
<asp:DropDownList id="DropDownList2" runat="server"></asp:DropDownList>
<asp:Button id="Button1" onclick="ApplyFilter_Click" runat="server" Text="Show Titles"></asp:Button>
</p>
<asp:datagrid id="DataGrid1" runat="server" EnableViewState="False" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1">
<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>
====================================
Comment faire?
J ai ce message ( PS j ai Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42 )
Server Error in '/' Application.
ExecuteReader requires an open and available Connection. The connection's current state is closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:
1. Add a "Debug=true" directive at the top of the file that generated the error. Example:
<%@ Page Language="C#" Debug="true" %>
or:
2) Add the following section to the configuration file of your application:
<configuration>
<system.web>
<compilation debug="true"/>
</system.web>
</configuration>
Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.
Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. |
Stack Trace:
[InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.] System.Data.SqlClient.SqlConnection.GetOpenConnection(String method) +43 System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +4 System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +56 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +72 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +59 ASP.sselect_aspx.Page_Load(Object Sender, EventArgs E) +282 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743 |
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
======================
Merci par avance de votre aide.
Je peux utiliser Microsoft Visual Studio.NET 2003 Pro (ou avec la beta 2005 ou version pro 2002 = 
Merci Merci Microsoft ! , bon je sais, cela leur permet de me former a leurs technologies !)
Si vous voulez plus d'infos, n hesiter pas a me contacter.
Encore un grands merci d'avance, j attend toute les reponses possibles .
Billmax 