473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DDL values with DataReader and stored procedures

For the code below, how could I add an item in the drop
down lists for both company and location to be an "All"
selection that would send to the stored proc.
spRptAttachments a value of "%" so that it would bring
back all attachments at all companies or all locations at
a company? Thank you, Rob.

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim cmd As System.Data.SqlClient.SqlCommand
cmd = New System.Data.SqlClient.SqlCommand
("Web_CompanyList", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
ddlCompany.DataSource = myDataReader
ddlCompany.DataValueField = "CompanyID"
ddlCompany.DataTextField = "CompanyName"
ddlCompany.DataBind()
ddlCompany_SelectedIndexChanged(Nothing,
Nothing)
End If
End Sub

Private Sub ddlCompany_SelectedIndexChanged(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles ddlCompany.SelectedIndexChanged
Dim param As System.Data.SqlClient.SqlParameter
Dim cmd As System.Data.SqlClient.SqlCommand
Dim intCompanyID As Integer
cmd = New System.Data.SqlClient.SqlCommand
("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
param = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyID",
SqlDbType.Int))
param.Direction = ParameterDirection.Input
' ********Selected Value of Option List*******
param.Value = ddlCompany.Items
(ddlCompany.SelectedIndex).Value
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
ddlLocation.DataSource = myDataReader
ddlLocation.DataValueField = "CompanyLocationID"
ddlLocation.DataTextField = "CoLo"
ddlLocation.DataBind()
End Sub

Private Sub btnSearch_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnSearch.Click
Dim prmDateFrom As
System.Data.SqlClient.SqlParameter
Dim prmCoID As System.Data.SqlClient.SqlParameter
Dim prmColo As System.Data.SqlClient.SqlParameter
Dim cmd As System.Data.SqlClient.SqlCommand
Dim intCompanyID As Integer
cmd = New System.Data.SqlClient.SqlCommand
("spRptAttachments", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
prmDateFrom = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateFrom",
SqlDbType.DateTime))
prmCoID = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyID",
SqlDbType.Int))
prmColo = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyLocati onID",
SqlDbType.Int))
prmDateFrom.Direction = ParameterDirection.Input
prmCoID.Direction = ParameterDirection.Input
prmColo.Direction = ParameterDirection.Input
' ********Selected Value of Option List*******
prmDateFrom.Value = tbDateFrom.Text
prmCoID.Value = ddlCompany.Items
(ddlCompany.SelectedIndex).Value
prmColo.Value = ddlLocation.Items
(ddlLocation.SelectedIndex).Value
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
DataGrid1.DataSource = myDataReader
DataGrid1.DataBind()

End Sub

Nov 17 '05 #1
5 2221
After you bind your dropdownlist and close the datareader just do an
items.insert "Select All" at position 0. Next line would be to set
items(0).value to % or whatever character you want.

(Note sometimes I do "Choose One" as my position 0 with a value of empty
string "" so I can use a required field validator. Same concept)

Hope this helps.

"Rob Wire" <ST*@hotmail.com> wrote in message
news:8e****************************@phx.gbl...
For the code below, how could I add an item in the drop
down lists for both company and location to be an "All"
selection that would send to the stored proc.
spRptAttachments a value of "%" so that it would bring
back all attachments at all companies or all locations at
a company? Thank you, Rob.

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim cmd As System.Data.SqlClient.SqlCommand
cmd = New System.Data.SqlClient.SqlCommand
("Web_CompanyList", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
ddlCompany.DataSource = myDataReader
ddlCompany.DataValueField = "CompanyID"
ddlCompany.DataTextField = "CompanyName"
ddlCompany.DataBind()
ddlCompany_SelectedIndexChanged(Nothing,
Nothing)
End If
End Sub

Private Sub ddlCompany_SelectedIndexChanged(ByVal
sender As System.Object, ByVal e As System.EventArgs)
Handles ddlCompany.SelectedIndexChanged
Dim param As System.Data.SqlClient.SqlParameter
Dim cmd As System.Data.SqlClient.SqlCommand
Dim intCompanyID As Integer
cmd = New System.Data.SqlClient.SqlCommand
("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
param = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyID",
SqlDbType.Int))
param.Direction = ParameterDirection.Input
' ********Selected Value of Option List*******
param.Value = ddlCompany.Items
(ddlCompany.SelectedIndex).Value
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
ddlLocation.DataSource = myDataReader
ddlLocation.DataValueField = "CompanyLocationID"
ddlLocation.DataTextField = "CoLo"
ddlLocation.DataBind()
End Sub

Private Sub btnSearch_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnSearch.Click
Dim prmDateFrom As
System.Data.SqlClient.SqlParameter
Dim prmCoID As System.Data.SqlClient.SqlParameter
Dim prmColo As System.Data.SqlClient.SqlParameter
Dim cmd As System.Data.SqlClient.SqlCommand
Dim intCompanyID As Integer
cmd = New System.Data.SqlClient.SqlCommand
("spRptAttachments", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
prmDateFrom = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateFrom",
SqlDbType.DateTime))
prmCoID = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyID",
SqlDbType.Int))
prmColo = cmd.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CompanyLocati onID",
SqlDbType.Int))
prmDateFrom.Direction = ParameterDirection.Input
prmCoID.Direction = ParameterDirection.Input
prmColo.Direction = ParameterDirection.Input
' ********Selected Value of Option List*******
prmDateFrom.Value = tbDateFrom.Text
prmCoID.Value = ddlCompany.Items
(ddlCompany.SelectedIndex).Value
prmColo.Value = ddlLocation.Items
(ddlLocation.SelectedIndex).Value
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
DataGrid1.DataSource = myDataReader
DataGrid1.DataBind()

End Sub

Nov 17 '05 #2
Hello Rob,

I have read the code that you paste. It seems that you populate companyID and name in ddlCompany drop down list. Then
populate ddlLocation drop down list according to the selected value in ddlCompany.

In the search button onclick handler, you ran a SP according to values from drop down list.

However, I am quite clear on your questions? Could you please explain more on what you want to implement, what the
problem is so that we could provide more information to you?

Thanks very much.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Rob Wire" <st*@hotmail.com>
!Sender: "Rob Wire" <st*@hotmail.com>
!References: <8e****************************@phx.gbl> <#e**************@tk2msftngp13.phx.gbl>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Wed, 6 Aug 2003 12:29:31 -0700
!Lines: 122
!Message-ID: <07****************************@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Newsgroups: microsoft.public.dotnet.framework.aspnet
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:165489
!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!
!How could I apply the below to a wildcard int primary key
!id field? i.e. to avoid getting:
!
!Syntax error converting the varchar value '%' to a column
!of data type int
!
!>-----Original Message-----
!>After you bind your dropdownlist and close the datareader
!just do an
!>items.insert "Select All" at position 0. Next line would
!be to set
!>items(0).value to % or whatever character you want.
!>
!>(Note sometimes I do "Choose One" as my position 0 with a
!value of empty
!>string "" so I can use a required field validator. Same
!concept)
!>
!>Hope this helps.
!>
!>"Rob Wire" <ST*@hotmail.com> wrote in message
!>news:8e****************************@phx.gbl...
!>> For the code below, how could I add an item in the drop
!>> down lists for both company and location to be an "All"
!>> selection that would send to the stored proc.
!>> spRptAttachments a value of "%" so that it would bring
!>> back all attachments at all companies or all locations
!at
!>> a company? Thank you, Rob.
!>>
!>> Private Sub Page_Load(ByVal sender As System.Object,
!>> ByVal e As System.EventArgs) Handles MyBase.Load
!>> If Not IsPostBack Then
!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("Web_CompanyList", Me.SqlConnection1)
!>> cmd.CommandType =
!CommandType.StoredProcedure
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
!>> (CommandBehavior.CloseConnection)
!>> ddlCompany.DataSource = myDataReader
!>> ddlCompany.DataValueField = "CompanyID"
!>> ddlCompany.DataTextField = "CompanyName"
!>> ddlCompany.DataBind()
!>> ddlCompany_SelectedIndexChanged(Nothing,
!>> Nothing)
!>> End If
!>> End Sub
!>>
!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
!>> sender As System.Object, ByVal e As System.EventArgs)
!>> Handles ddlCompany.SelectedIndexChanged
!>> Dim param As System.Data.SqlClient.SqlParameter
!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>> Dim intCompanyID As Integer
!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
!>> cmd.CommandType = CommandType.StoredProcedure
!>> param = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>> SqlDbType.Int))
!>> param.Direction = ParameterDirection.Input
!>> ' ********Selected Value of Option List*******
!>> param.Value = ddlCompany.Items
!>> (ddlCompany.SelectedIndex).Value
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
!>> (CommandBehavior.CloseConnection)
!>> ddlLocation.DataSource = myDataReader
!>> ddlLocation.DataValueField = "CompanyLocationID"
!>> ddlLocation.DataTextField = "CoLo"
!>> ddlLocation.DataBind()
!>> End Sub
!>>
!>> Private Sub btnSearch_Click(ByVal sender As
!>> System.Object, ByVal e As System.EventArgs) Handles
!>> btnSearch.Click
!>> Dim prmDateFrom As
!>> System.Data.SqlClient.SqlParameter
!>> Dim prmCoID As
!System.Data.SqlClient.SqlParameter
!>> Dim prmColo As
!System.Data.SqlClient.SqlParameter
!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>> Dim intCompanyID As Integer
!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("spRptAttachments", Me.SqlConnection1)
!>> cmd.CommandType = CommandType.StoredProcedure
!>> prmDateFrom = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@DateFrom",
!>> SqlDbType.DateTime))
!>> prmCoID = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>> SqlDbType.Int))
!>> prmColo = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@CompanyLocati onID",
!>> SqlDbType.Int))
!>> prmDateFrom.Direction = ParameterDirection.Input
!>> prmCoID.Direction = ParameterDirection.Input
!>> prmColo.Direction = ParameterDirection.Input
!>> ' ********Selected Value of Option List*******
!>> prmDateFrom.Value = tbDateFrom.Text
!>> prmCoID.Value = ddlCompany.Items
!>> (ddlCompany.SelectedIndex).Value
!>> prmColo.Value = ddlLocation.Items
!>> (ddlLocation.SelectedIndex).Value
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
!>> (CommandBehavior.CloseConnection)
!>> DataGrid1.DataSource = myDataReader
!>> DataGrid1.DataBind()
!>>
!>> End Sub
!>>
!>
!>
!>.
!>
!
Nov 17 '05 #3
What I would like to do is have the ddl have a fake record
in the list that would send to the stored procedure a wild
card like '%' that would work with the int value of the
primary key field in the table that the drop down is
displaying, hence to return all rows no matter what the id
is if that is selected.
-----Original Message-----
Hello Rob,

I have read the code that you paste. It seems that you populate companyID and name in ddlCompany drop down list.
Thenpopulate ddlLocation drop down list according to the selected value in ddlCompany.
In the search button onclick handler, you ran a SP according to values from drop down list.
However, I am quite clear on your questions? Could you please explain more on what you want to implement, what
theproblem is so that we could provide more information to you?
Thanks very much.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
!Content-Class: urn:content-classes:message
!From: "Rob Wire" <st*@hotmail.com>
!Sender: "Rob Wire" <st*@hotmail.com>
!References: <8e****************************@phx.gbl> <#e**************@tk2msftngp13.phx.gbl>!Subject: Re: DDL values with DataReader and stored procedures!Date: Wed, 6 Aug 2003 12:29:31 -0700
!Lines: 122
!Message-ID: <07****************************@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Newsgroups: microsoft.public.dotnet.framework.aspnet
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:165489!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!
!How could I apply the below to a wildcard int primary key!id field? i.e. to avoid getting:
!
!Syntax error converting the varchar value '%' to a column!of data type int
!
!>-----Original Message-----
!>After you bind your dropdownlist and close the datareader!just do an
!>items.insert "Select All" at position 0. Next line would!be to set
!>items(0).value to % or whatever character you want.
!>
!>(Note sometimes I do "Choose One" as my position 0 with a!value of empty
!>string "" so I can use a required field validator. Same!concept)
!>
!>Hope this helps.
!>
!>"Rob Wire" <ST*@hotmail.com> wrote in message
!>news:8e****************************@phx.gbl.. .
!>> For the code below, how could I add an item in the drop!>> down lists for both company and location to be an "All"!>> selection that would send to the stored proc.
!>> spRptAttachments a value of "%" so that it would bring
!>> back all attachments at all companies or all locations!at
!>> a company? Thank you, Rob.
!>>
!>> Private Sub Page_Load(ByVal sender As System.Object,!>> ByVal e As System.EventArgs) Handles MyBase.Load
!>> If Not IsPostBack Then
!>> Dim cmd As System.Data.SqlClient.SqlCommand!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("Web_CompanyList", Me.SqlConnection1)
!>> cmd.CommandType =
!CommandType.StoredProcedure
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader!>> (CommandBehavior.CloseConnection)
!>> ddlCompany.DataSource = myDataReader
!>> ddlCompany.DataValueField = "CompanyID"
!>> ddlCompany.DataTextField = "CompanyName"
!>> ddlCompany.DataBind()
!>> ddlCompany_SelectedIndexChanged(Nothing,
!>> Nothing)
!>> End If
!>> End Sub
!>>
!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
!>> sender As System.Object, ByVal e As System.EventArgs)
!>> Handles ddlCompany.SelectedIndexChanged
!>> Dim param As System.Data.SqlClient.SqlParameter!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>> Dim intCompanyID As Integer
!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
!>> cmd.CommandType = CommandType.StoredProcedure
!>> param = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>> SqlDbType.Int))
!>> param.Direction = ParameterDirection.Input
!>> ' ********Selected Value of Option List*******
!>> param.Value = ddlCompany.Items
!>> (ddlCompany.SelectedIndex).Value
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader!>> (CommandBehavior.CloseConnection)
!>> ddlLocation.DataSource = myDataReader
!>> ddlLocation.DataValueField = "CompanyLocationID"!>> ddlLocation.DataTextField = "CoLo"
!>> ddlLocation.DataBind()
!>> End Sub
!>>
!>> Private Sub btnSearch_Click(ByVal sender As
!>> System.Object, ByVal e As System.EventArgs) Handles
!>> btnSearch.Click
!>> Dim prmDateFrom As
!>> System.Data.SqlClient.SqlParameter
!>> Dim prmCoID As
!System.Data.SqlClient.SqlParameter
!>> Dim prmColo As
!System.Data.SqlClient.SqlParameter
!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>> Dim intCompanyID As Integer
!>> cmd = New System.Data.SqlClient.SqlCommand
!>> ("spRptAttachments", Me.SqlConnection1)
!>> cmd.CommandType = CommandType.StoredProcedure
!>> prmDateFrom = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@DateFrom",
!>> SqlDbType.DateTime))
!>> prmCoID = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>> SqlDbType.Int))
!>> prmColo = cmd.Parameters.Add(New
!>> System.Data.SqlClient.SqlParameter ("@CompanyLocationID",!>> SqlDbType.Int))
!>> prmDateFrom.Direction = ParameterDirection.Input!>> prmCoID.Direction = ParameterDirection.Input
!>> prmColo.Direction = ParameterDirection.Input
!>> ' ********Selected Value of Option List*******
!>> prmDateFrom.Value = tbDateFrom.Text
!>> prmCoID.Value = ddlCompany.Items
!>> (ddlCompany.SelectedIndex).Value
!>> prmColo.Value = ddlLocation.Items
!>> (ddlLocation.SelectedIndex).Value
!>> Me.SqlConnection1.Open()
!>> Dim myDataReader As
!>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader!>> (CommandBehavior.CloseConnection)
!>> DataGrid1.DataSource = myDataReader
!>> DataGrid1.DataBind()
!>>
!>> End Sub
!>>
!>
!>
!>.
!>
!
.

Nov 17 '05 #4
Hello Rob,

Sorry for the late response. I am not quite familar with SQL statements. So I am not sure of how to transfer a value like % for a
int value type. What I knew is that we could only use %, * in string type values.

My suggestion is to implement it in stored procedure level. For an example, when stored procedure received one specific
value, it will return all the rows no matter what ID is selected.

Please let me know if you have any concerns on it. Thanks.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Rob Wire" <st*@hotmail.com>
!Sender: "Rob Wire" <st*@hotmail.com>
!References: <8e****************************@phx.gbl> <#e**************@tk2msftngp13.phx.gbl>
<07****************************@phx.gbl> <mq**************@cpmsftngxa06.phx.gbl>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Fri, 8 Aug 2003 12:39:42 -0700
!Lines: 205
!Message-ID: <01****************************@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Thread-Index: AcNd5NAmk8VGjaZQT1K+C0BMkc2Vuw==
!Newsgroups: microsoft.public.dotnet.framework.aspnet
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:166257
!NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!
!What I would like to do is have the ddl have a fake record
!in the list that would send to the stored procedure a wild
!card like '%' that would work with the int value of the
!primary key field in the table that the drop down is
!displaying, hence to return all rows no matter what the id
!is if that is selected.
!
!>-----Original Message-----
!>Hello Rob,
!>
!>I have read the code that you paste. It seems that you
!populate companyID and name in ddlCompany drop down list.
!Then
!>populate ddlLocation drop down list according to the
!selected value in ddlCompany.
!>
!>In the search button onclick handler, you ran a SP
!according to values from drop down list.
!>
!>However, I am quite clear on your questions? Could you
!please explain more on what you want to implement, what
!the
!>problem is so that we could provide more information to
!you?
!>
!>Thanks very much.
!>
!>Best regards,
!>Yanhong Huang
!>Microsoft Online Partner Support
!>
!>Get Secure! - www.microsoft.com/security
!>This posting is provided "AS IS" with no warranties, and
!confers no rights.
!>
!>--------------------
!>!Content-Class: urn:content-classes:message
!>!From: "Rob Wire" <st*@hotmail.com>
!>!Sender: "Rob Wire" <st*@hotmail.com>
!>!References: <8e****************************@phx.gbl>
!<#e**************@tk2msftngp13.phx.gbl>
!>!Subject: Re: DDL values with DataReader and stored
!procedures
!>!Date: Wed, 6 Aug 2003 12:29:31 -0700
!>!Lines: 122
!>!Message-ID: <07****************************@phx.gbl>
!>!MIME-Version: 1.0
!>!Content-Type: text/plain;
!>! charset="iso-8859-1"
!>!Content-Transfer-Encoding: 7bit
!>!X-Newsreader: Microsoft CDO for Windows 2000
!>!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
!>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!>!Newsgroups: microsoft.public.dotnet.framework.aspnet
!>!Path: cpmsftngxa06.phx.gbl
!>!Xref: cpmsftngxa06.phx.gbl
!microsoft.public.dotnet.framework.aspnet:165489
!>!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
!>!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!>!
!>!How could I apply the below to a wildcard int primary
!key
!>!id field? i.e. to avoid getting:
!>!
!>!Syntax error converting the varchar value '%' to a
!column
!>!of data type int
!>!
!>!>-----Original Message-----
!>!>After you bind your dropdownlist and close the
!datareader
!>!just do an
!>!>items.insert "Select All" at position 0. Next line
!would
!>!be to set
!>!>items(0).value to % or whatever character you want.
!>!>
!>!>(Note sometimes I do "Choose One" as my position 0 with
!a
!>!value of empty
!>!>string "" so I can use a required field validator.
!Same
!>!concept)
!>!>
!>!>Hope this helps.
!>!>
!>!>"Rob Wire" <ST*@hotmail.com> wrote in message
!>!>news:8e****************************@phx.gbl.. .
!>!>> For the code below, how could I add an item in the
!drop
!>!>> down lists for both company and location to be
!an "All"
!>!>> selection that would send to the stored proc.
!>!>> spRptAttachments a value of "%" so that it would bring
!>!>> back all attachments at all companies or all
!locations
!>!at
!>!>> a company? Thank you, Rob.
!>!>>
!>!>> Private Sub Page_Load(ByVal sender As
!System.Object,
!>!>> ByVal e As System.EventArgs) Handles MyBase.Load
!>!>> If Not IsPostBack Then
!>!>> Dim cmd As
!System.Data.SqlClient.SqlCommand
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("Web_CompanyList", Me.SqlConnection1)
!>!>> cmd.CommandType =
!>!CommandType.StoredProcedure
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> ddlCompany.DataSource = myDataReader
!>!>> ddlCompany.DataValueField = "CompanyID"
!>!>> ddlCompany.DataTextField = "CompanyName"
!>!>> ddlCompany.DataBind()
!>!>> ddlCompany_SelectedIndexChanged(Nothing,
!>!>> Nothing)
!>!>> End If
!>!>> End Sub
!>!>>
!>!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
!>!>> sender As System.Object, ByVal e As System.EventArgs)
!>!>> Handles ddlCompany.SelectedIndexChanged
!>!>> Dim param As
!System.Data.SqlClient.SqlParameter
!>!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
!>!>> cmd.CommandType = CommandType.StoredProcedure
!>!>> param = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>!>> SqlDbType.Int))
!>!>> param.Direction = ParameterDirection.Input
!>!>> ' ********Selected Value of Option List*******
!>!>> param.Value = ddlCompany.Items
!>!>> (ddlCompany.SelectedIndex).Value
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> ddlLocation.DataSource = myDataReader
!>!>> ddlLocation.DataValueField
!= "CompanyLocationID"
!>!>> ddlLocation.DataTextField = "CoLo"
!>!>> ddlLocation.DataBind()
!>!>> End Sub
!>!>>
!>!>> Private Sub btnSearch_Click(ByVal sender As
!>!>> System.Object, ByVal e As System.EventArgs) Handles
!>!>> btnSearch.Click
!>!>> Dim prmDateFrom As
!>!>> System.Data.SqlClient.SqlParameter
!>!>> Dim prmCoID As
!>!System.Data.SqlClient.SqlParameter
!>!>> Dim prmColo As
!>!System.Data.SqlClient.SqlParameter
!>!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("spRptAttachments", Me.SqlConnection1)
!>!>> cmd.CommandType = CommandType.StoredProcedure
!>!>> prmDateFrom = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@DateFrom",
!>!>> SqlDbType.DateTime))
!>!>> prmCoID = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>!>> SqlDbType.Int))
!>!>> prmColo = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter
!("@CompanyLocationID",
!>!>> SqlDbType.Int))
!>!>> prmDateFrom.Direction =
!ParameterDirection.Input
!>!>> prmCoID.Direction = ParameterDirection.Input
!>!>> prmColo.Direction = ParameterDirection.Input
!>!>> ' ********Selected Value of Option List*******
!>!>> prmDateFrom.Value = tbDateFrom.Text
!>!>> prmCoID.Value = ddlCompany.Items
!>!>> (ddlCompany.SelectedIndex).Value
!>!>> prmColo.Value = ddlLocation.Items
!>!>> (ddlLocation.SelectedIndex).Value
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> DataGrid1.DataSource = myDataReader
!>!>> DataGrid1.DataBind()
!>!>>
!>!>> End Sub
!>!>>
!>!>
!>!>
!>!>.
!>!>
!>!
!>
!>
!>.
!>
!
Nov 17 '05 #5
Hello Rob,

Do you still have any question on the issue? Please post here if you have
follow up questions.

For SQL server stored procedures, please refer to SQL books online to get
code samples. I think the logic should be:

USE pubs
GO
CREATE PROCEDURE au_info
@ID int,
AS
IF (ID == ***)
THEN
SELECT * from ...
ELSE
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @ID
GO

Hope that helps.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Rob Wire" <st*@hotmail.com>
!Sender: "Rob Wire" <st*@hotmail.com>
!References: <8e****************************@phx.gbl>
<#e**************@tk2msftngp13.phx.gbl>
<07****************************@phx.gbl>
<mq**************@cpmsftngxa06.phx.gbl>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Fri, 8 Aug 2003 12:39:42 -0700
!Lines: 205
!Message-ID: <01****************************@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Thread-Index: AcNd5NAmk8VGjaZQT1K+C0BMkc2Vuw==
!Newsgroups: microsoft.public.dotnet.framework.aspnet
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:166257
!NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!
!What I would like to do is have the ddl have a fake record
!in the list that would send to the stored procedure a wild
!card like '%' that would work with the int value of the
!primary key field in the table that the drop down is
!displaying, hence to return all rows no matter what the id
!is if that is selected.
!
!>-----Original Message-----
!>Hello Rob,
!>
!>I have read the code that you paste. It seems that you
!populate companyID and name in ddlCompany drop down list.
!Then
!>populate ddlLocation drop down list according to the
!selected value in ddlCompany.
!>
!>In the search button onclick handler, you ran a SP
!according to values from drop down list.
!>
!>However, I am quite clear on your questions? Could you
!please explain more on what you want to implement, what
!the
!>problem is so that we could provide more information to
!you?
!>
!>Thanks very much.
!>
!>Best regards,
!>Yanhong Huang
!>Microsoft Online Partner Support
!>
!>Get Secure! - www.microsoft.com/security
!>This posting is provided "AS IS" with no warranties, and
!confers no rights.
!>
!>--------------------
!>!Content-Class: urn:content-classes:message
!>!From: "Rob Wire" <st*@hotmail.com>
!>!Sender: "Rob Wire" <st*@hotmail.com>
!>!References: <8e****************************@phx.gbl>
!<#e**************@tk2msftngp13.phx.gbl>
!>!Subject: Re: DDL values with DataReader and stored
!procedures
!>!Date: Wed, 6 Aug 2003 12:29:31 -0700
!>!Lines: 122
!>!Message-ID: <07****************************@phx.gbl>
!>!MIME-Version: 1.0
!>!Content-Type: text/plain;
!>! charset="iso-8859-1"
!>!Content-Transfer-Encoding: 7bit
!>!X-Newsreader: Microsoft CDO for Windows 2000
!>!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
!>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!>!Newsgroups: microsoft.public.dotnet.framework.aspnet
!>!Path: cpmsftngxa06.phx.gbl
!>!Xref: cpmsftngxa06.phx.gbl
!microsoft.public.dotnet.framework.aspnet:165489
!>!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
!>!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
!>!
!>!How could I apply the below to a wildcard int primary
!key
!>!id field? i.e. to avoid getting:
!>!
!>!Syntax error converting the varchar value '%' to a
!column
!>!of data type int
!>!
!>!>-----Original Message-----
!>!>After you bind your dropdownlist and close the
!datareader
!>!just do an
!>!>items.insert "Select All" at position 0. Next line
!would
!>!be to set
!>!>items(0).value to % or whatever character you want.
!>!>
!>!>(Note sometimes I do "Choose One" as my position 0 with
!a
!>!value of empty
!>!>string "" so I can use a required field validator.
!Same
!>!concept)
!>!>
!>!>Hope this helps.
!>!>
!>!>"Rob Wire" <ST*@hotmail.com> wrote in message
!>!>news:8e****************************@phx.gbl.. .
!>!>> For the code below, how could I add an item in the
!drop
!>!>> down lists for both company and location to be
!an "All"
!>!>> selection that would send to the stored proc.
!>!>> spRptAttachments a value of "%" so that it would bring
!>!>> back all attachments at all companies or all
!locations
!>!at
!>!>> a company? Thank you, Rob.
!>!>>
!>!>> Private Sub Page_Load(ByVal sender As
!System.Object,
!>!>> ByVal e As System.EventArgs) Handles MyBase.Load
!>!>> If Not IsPostBack Then
!>!>> Dim cmd As
!System.Data.SqlClient.SqlCommand
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("Web_CompanyList", Me.SqlConnection1)
!>!>> cmd.CommandType =
!>!CommandType.StoredProcedure
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> ddlCompany.DataSource = myDataReader
!>!>> ddlCompany.DataValueField = "CompanyID"
!>!>> ddlCompany.DataTextField = "CompanyName"
!>!>> ddlCompany.DataBind()
!>!>> ddlCompany_SelectedIndexChanged(Nothing,
!>!>> Nothing)
!>!>> End If
!>!>> End Sub
!>!>>
!>!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
!>!>> sender As System.Object, ByVal e As System.EventArgs)
!>!>> Handles ddlCompany.SelectedIndexChanged
!>!>> Dim param As
!System.Data.SqlClient.SqlParameter
!>!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
!>!>> cmd.CommandType = CommandType.StoredProcedure
!>!>> param = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>!>> SqlDbType.Int))
!>!>> param.Direction = ParameterDirection.Input
!>!>> ' ********Selected Value of Option List*******
!>!>> param.Value = ddlCompany.Items
!>!>> (ddlCompany.SelectedIndex).Value
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> ddlLocation.DataSource = myDataReader
!>!>> ddlLocation.DataValueField
!= "CompanyLocationID"
!>!>> ddlLocation.DataTextField = "CoLo"
!>!>> ddlLocation.DataBind()
!>!>> End Sub
!>!>>
!>!>> Private Sub btnSearch_Click(ByVal sender As
!>!>> System.Object, ByVal e As System.EventArgs) Handles
!>!>> btnSearch.Click
!>!>> Dim prmDateFrom As
!>!>> System.Data.SqlClient.SqlParameter
!>!>> Dim prmCoID As
!>!System.Data.SqlClient.SqlParameter
!>!>> Dim prmColo As
!>!System.Data.SqlClient.SqlParameter
!>!>> Dim cmd As System.Data.SqlClient.SqlCommand
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.SqlClient.SqlCommand
!>!>> ("spRptAttachments", Me.SqlConnection1)
!>!>> cmd.CommandType = CommandType.StoredProcedure
!>!>> prmDateFrom = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@DateFrom",
!>!>> SqlDbType.DateTime))
!>!>> prmCoID = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter("@CompanyID",
!>!>> SqlDbType.Int))
!>!>> prmColo = cmd.Parameters.Add(New
!>!>> System.Data.SqlClient.SqlParameter
!("@CompanyLocationID",
!>!>> SqlDbType.Int))
!>!>> prmDateFrom.Direction =
!ParameterDirection.Input
!>!>> prmCoID.Direction = ParameterDirection.Input
!>!>> prmColo.Direction = ParameterDirection.Input
!>!>> ' ********Selected Value of Option List*******
!>!>> prmDateFrom.Value = tbDateFrom.Text
!>!>> prmCoID.Value = ddlCompany.Items
!>!>> (ddlCompany.SelectedIndex).Value
!>!>> prmColo.Value = ddlLocation.Items
!>!>> (ddlLocation.SelectedIndex).Value
!>!>> Me.SqlConnection1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sqlclient.SqlDataReader =
!cmd.ExecuteReader
!>!>> (CommandBehavior.CloseConnection)
!>!>> DataGrid1.DataSource = myDataReader
!>!>> DataGrid1.DataBind()
!>!>>
!>!>> End Sub
!>!>>
!>!>
!>!>
!>!>.
!>!>
!>!
!>
!>
!>.
!>
!

Nov 17 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Mike | last post by:
Hello, I'm currently working on debugging a very large DTS package that was created by someone else for the purpose of importing data into my company's database. The data is mainly...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
1
by: Patrick.O.Ige | last post by:
I have been paging with DataSet using "DataGridPageChangedEventArgs" and i guess it works with only Dataset because default paging requires that the DataGrid be able to determine the number of...
2
by: orencs | last post by:
Hi, I am using Datareader and stored procedure in C# ADO.NET. When I am running the stored procedure in the SQL Query Analyzer and recieve two rows (as I hace expected) col1 col2 0 1 0 ...
7
by: simon | last post by:
What is the best way to read the values from the datatbase. I have sql="SELECT userName, userCountry, userVisit from users where userID=2" Create a command object? Dim oCmd As SqlCommand...
6
by: Kalim Julia | last post by:
Is it possible to open more than 1 datareader / dataadapter on one connection ? Is it possible to duplicate / clone connection ?
4
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier...
3
by: Sirisha | last post by:
Hi, I am inserting values into databse using sqlserver stored procedures. i wrote stored preocedure,but in codebehind file(.vb file) i dont know how to pass the parameters, i got error message...
3
by: =?Utf-8?B?ZGVuIDIwMDU=?= | last post by:
Hi, Trouble in retaining values of dropdownlist, textboxes, and other controls when dropdownlist selectedindexchanged event is triggered, the controls are inside a user control and this user...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.