Sujet Précédent Sujet Suivant

Requete dans base SQL avec des DropDownList pour faire une recherche

cs_billmax Messages postés 16 Date d'inscription mercredi 15 janvier 2003 Statut Membre Dernière intervention 14 mai 2007 - 3 déc. 2005 à 16:03
cs_Yopyop Messages postés 586 Date d'inscription lundi 7 janvier 2002 Statut Membre Dernière intervention 10 février 2010 - 5 déc. 2005 à 08:15
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>

Filtered Data Report



<form runat="server">

Select an Author:

&nbsp;




<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>


</form>

</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>

Filtered Data Report



<form runat="server">

Select an Author:


&nbsp;




<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>


</form>

</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
)<!--
[InvalidOperationException]: ExecuteReader requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at ASP.sselect_aspx.Page_Load(Object Sender, EventArgs E)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
[HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown.
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.sselect_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
-->

Server Error in '/' Application.

<HR width ="100%" color= silver SIZE=1>



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



<HR width ="100%" color= silver SIZE=1>


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

<!--
[InvalidOperationException]: ExecuteReader requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at ASP.sselect_aspx.Page_Load(Object Sender, EventArgs E)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
[HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown.
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.sselect_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
-->

2 réponses

Réponse 1 / 2
cs_AC1 Messages postés 116 Date d'inscription samedi 14 août 2004 Statut Membre Dernière intervention 25 mars 2010
4 déc. 2005 à 17:06
Bonjour,

Je ne peux pas tester ton code en entier, ceci dit ton message d'erreur est clair, ta connexion est fermée, tu n'as plus accès aux données, il faut réouvrir une connexion et exécuter derrière.
D'autre part pour mermettre un choix multiples tu devrais avoir

Pour la récupération des strings multiples qui te permettront ensuite de créer un filtre qui en tiendra compte :
dim strMyresult as string
dim liMonchoix as Listitem
for each limonchoix in moncontrol.items
if limonchoix.selected then
strmyresult+=limonchoix.value
end if
next

J'espère que ça peut t'aider.

AC1
Databases on the Web & on the LAN
0
Réponse 2 / 2
cs_Yopyop Messages postés 586 Date d'inscription lundi 7 janvier 2002 Statut Membre Dernière intervention 10 février 2010 1
5 déc. 2005 à 08:15
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


'Si j'ai bien compris, tu à plusieurs filtres, mais tu n'as qu'un seul résultat
'Tu n'as donc pas besoin de la ligne suivante:
'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("'", "''")

Dim sWhere as String = "" 'String pour la clause where
'Par défaut la requêtes retourne toutes les villes
Dim sSQL as String = " SELECT DISTINCT ville FROM affaire "

'check si un cp a été sélectionné
If filterValue1 <> "-- All cp --" Then sWhere " WHERE cp '" & filterValue1 & "' "
End If

'check si une ville a été sélectionnée (apparemment c'est la liste des villes?)
If filterValues2 <> "-- All cities --" then
'si il y a déjà qqch dans le where, il faut mettre AND, sinon mettre WHERE
If sWhere = "" Then sWhere " WHERE ville '" & filterValue2 & "' "
Else sWhere sWhere & " and ville '" & filterValue2 & "' "
End If
End If

CommandText1 = sSQL & sWhere

'Ceci donnera :
'dans le cas ou tu n'as rien choisi:
'SELECT DISCTINCT ville FROM affaire
'dans le cas ou tu as selectionné un cp:
'SELECT DISTINCT ville FROM affaire WHERE cp = 'SELECTION'
'dans le cas ou tu as selectionné une ville :
'SELECT DISTINCT ville FROM affaire WHERE ville = 'SELECTION'
'dans le cas ou tu as selectionné un cp et une ville :'SELECT DISTINCT ville FROM affaire WHERE cp 'SELECTION' AND ville 'SELECTION'


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

Ca devrait jouer... mais c'est pas super propre... et je ne suis pas sûr que c'est bien cela que tu veux faire...

yopyop
0

Discussions similaires

Prob de Restauration base de données
cindyb -
Orange -

3 réponses
decimal (12,2) ca veut dire quoi?
cs_deubal -
Momo13 -

3 réponses
Requête LIKE Access
cs_ansizak -
ThomasFalguieres -

3 réponses
Requête SELECT très complexe
Mayzz -
Mayzz -

12 réponses
sous requête dans CASE
citt -
citt -

5 réponses
Rejoignez-nous