Hi folks,
The problem I have is that a query string works if hard-coded but
if I pass a variable to it, it does not work as shown here.
This works:
querystring="SELECT * FROM USERS WHERE CNAME = 'MICROSOFT'"
This does not work:
Dim var as string
var = "Microsoft"
querystring="SELECT * FROM USERS WHERE CNAME = " & 'var'"
I have 2 DropDownList controls.
The first control is populated with a dataset of company names.
The second control is populated with a dataset of contact names for
the company that was selected in the first control.
The first control has an OnSelectedIndexChanged event handler.
The first control has a datasource set equal to a function named
GetCompanyNames.
The second control has a datasource set equal to a function named
GetContactNames.
In the OnSelectedIndexChanged event handler, I call the GetContactNames
function to
populate the second control with Contact Names that are associated with the
Company
Name selected in the first control.
All is working well, but the Contact Names in the second control do not
change as
different companies are selected in the first control.
What follows is a pseudo code description of my code:
' THESE ARE CALLED AS GLOBAL VARIABLES AT TOP OF THE PAGE
Dim coName as string
Dim dsCompanyNames as DataSet = New DataSet ( )
Dim dsContacts as DataSet = New DataSet ()
' THIS IS MY OnSelectedIndexChanged EVENT HANDLER
Sub ddlTo_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim list As DropDownList = CType(sender, DropDownList)
coName=list.SelectedItem.Text
GetContactsFromCompanyForThisProject ()
End Sub
' THIS IS MY GETCOMPANYNAMES FUNCTION
Function GetCompanyNamesForThisProject() as DataSet
dsCompanyNames.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND MDB FILE"
Dim queryString As String = "SELECT COMPANYNAMES FROM USERS WHERE
PROJECT = @PROJECT"
Dim dataAdapter As New OleDbDataAdapter (querystring, strConnString)
dataAdapter.Fill(dsCompanyNames, "users")
Return dsCompanyNames
End Function
' THIS IS MY GETCONTACTNAMES FUNCTION
Function GetContactsFromCompanyForThisProject () As DataSet
dsContacts.Clear()
Dim strConnString As String = "JET OLEDB PROVIDER AND PATH TO MDB FILE"
Response.Write("coName = " & coName)
Dim queryString As String
queryString = "SELECT [users].[name] FROM [users] WHERE
(([users].[cname] = '" & coName & "')" & " AND ([users].[project] = '" &
Session("project") & "'))"
' queryString = "SELECT [users].[name] FROM [users] WHERE
(([users]. [cname] = 'FDM') AND ([users].[project] = '" &
Session("project") & "'))"
Dim dataAdapter As New OleDbDataAdapter (querystring,
strConnString)
dataAdapter.Fill(dsContacts, "users")
Return dsContacts
End Function
In the GetContactsFromCompanyForThisProject function, I am able to hard code
a company name and successfully populate the second control but if I attempt
to pass the variable coName to the query string, it will not switch the
contact names for each company that is selected in the first control.
The controls are called out as follows:
<asp:DropDownList
id="ddlTo"
runat="server"
DataValueField="cname"
AutoPostBack="True"
DataSource='<%# GetCompanyNamesForThisProject() %>'
OnSelectedIndexChanged="ddlTo_SelectedIndexChanged "
/>
<asp:DropDownList
id="ddlContact"
runat="server"
DataValueField="name"
AutoPostBack="True"
DataSource='<%# GetContactsFromCompanyForThisProject() %>'
/>
Any replies would be extremely appreciated.
