Showing posts with label access. Show all posts
Showing posts with label access. Show all posts

Monday, March 26, 2012

CasdadingDropDown with Db in VB.net

Well I have tried to follow the example in c# and a great post using access. I am trying to populate the first dropdown with Activity categories from a SQL database called PA. I created a dataset with two difference datatables in it: One for populating the first with a distinct clause: Select Distring [Category] FROM [Compendium] SORT asc. The second ta with a Select Command of "Select [CaseCode], [Mets], [Description], [Category] Where [Category = @dotnet.itags.org.Category]

I set the CasdadingDropDown Properties as follows:

 <cc1:CascadingDropDown ID="CascadingDropDown1" runat="server"> <cc1:CascadingDropDownProperties TargetControlID="ddActCat" ServiceMethod="GetActCat" ServicePath="ActType.asmx" PromptText="Choose an Activity Category" Category="Cat" /> <cc1:CascadingDropDownProperties TargetControlID="ddCatType" ServicePath="ActType.asmx" ServiceMethod="GetActType" PromptText="Choose Activity Type" ParentControlID="ddActCat" Category="Type" /> </cc1:CascadingDropDown>
 
The asmx file ActType.asmx:
 
<%@dotnet.itags.org. WebService Language="VB"Class="ActType" %>Imports System.WebImports System.Web.ServicesImports System.Web.Services.ProtocolsImports AtlasControlToolkitImports System.Collections.GenericImports System.DataImports System.Data.SqlClient<WebService(Namespace :="http://tempuri.org/")> _<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _Public Class ActTypeInherits System.Web.Services.WebService <WebMethod()> _Public Function GetActCat(ByVal knownCategoryValuesAs String,ByVal categoryAs String)As AtlasControlToolkit.CascadingDropDownNameValue()Dim actAs New ActivityTableAdapters.ActivityCateogry_TblTableAdapter()Dim ActCatAs Activity.ActivityCateogry_TblDataTable = act.GetActCat()With ActCatDim intRowAs Integer Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)For intRow = 0To .Rows.Count - 1 values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(0).ToString, .Rows(intRow).ItemArray(0)))Next Return values.ToArrayEnd With Return New CascadingDropDownNameValue() {}End Function <WebMethod()> _Public Function GetActType(ByVal knownCategoryValuesAs String,ByVal categoryAs String)As CascadingDropDownNameValue()Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)Dim CompCodeAs Integer If (kv.ContainsKey("Cat")And Int32.TryParse(kv("Cat"), CompCode))Then Dim taAs New ActivityTableAdapters.ActivityType_TblTableAdapter()Dim ActTypeAs Activity.ActivityType_TblDataTable = ta.GetType(CompCode)With ActTypeDim intRowAs Integer Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)For intRow = 0To .Rows.Count - 1 values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(3).ToString, .Rows(intRow).ItemArray(2)))Next Return values.ToArrayEnd With End If Return New CascadingDropDownNameValue() {}End Function End Class

The first dropDown name ddActCat populates fine, but the second does not. Also these dd's are in an updatePanel control and the Cdd Extender is outside the Updatepanel. I have tried it both ways. I need to have it inside the UpdatePanel becuase it is in line with other controls in a form. Should I seperate the others into two UpdatePanels and have the CDD outside?

I am really not sure what is going on in the second WebMethod GetType() please help.

I finally got the code to work after checking the WebService paths and making some minor adjustments to the table Items(Count). Here is my asmx file code that worked with SQL DataSet.

<%@. WebService Language="VB"Class="ActType" %>Imports System.WebImports System.Web.ServicesImports System.Web.Services.ProtocolsImports AtlasControlToolkitImports System.Collections.GenericImports System.DataImports System.Data.SqlClient<WebService(Namespace :="http://tempuri.org/")> _<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _Public Class ActTypeInherits System.Web.Services.WebService <WebMethod()> _Public Function GetActCat(ByVal knownCategoryValuesAs String,ByVal categoryAs String)As AtlasControlToolkit.CascadingDropDownNameValue()Dim actAs New ActivityTableAdapters.ActivityCategory_TblTableAdapter()Dim ActCatAs Activity.ActivityCategory_TblDataTable = act.GetActCat()With ActCatDim intRowAs Integer Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)For intRow = 0To .Rows.Count - 1 values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))Next Return values.ToArrayEnd With Return New CascadingDropDownNameValue() {}End Function <WebMethod()> _Public Function GetActType(ByVal knownCategoryValuesAs String,ByVal categoryAs String)As CascadingDropDownNameValue()Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)Dim CatIDAs Integer If (kv.ContainsKey("Category")And Int32.TryParse(kv("Category"), CatID))Then Dim taAs New ActivityTableAdapters.ActivityType_TblTableAdapter()Dim ActTypeAs Activity.ActivityType_TblDataTable = ta.GetType(CatID)With ActTypeDim intRowAs Integer Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)For intRow = 0To .Rows.Count - 1 values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(2).ToString, .Rows(intRow).ItemArray(1)))Next Return values.ToArray()End With End If Return New CascadingDropDownNameValue() {}End Function End Class

Most of the code came fromt converting the Example from the Toolkit and from post:

http://forums.asp.net/1362555/ShowThread.aspx#1362555

ThanksAthertonCreative is not online. Last active: 08-06-2006, 10:42 AMAthertonCreative you were very helpful with your post.

T


One problem I am having is that when I click on the second dd the wrong entry fills the dd text value. The Value of the dd is not the text value and is not unique, could it be looking to place the value in the dd instead of the text? By the By, I tried to do this with two dd's and a SQLDataSource with an Update panel. It worked, and had the same error. Help!!!


Maybe double-check that the order of the parameters to the CascadingDropDownNameValue constructor are correct? The correct order is: name, value.
Thanks for the reply dave. The dataset that gets returned has several values in the query. The first column is the ID, second is Mets, third is Description or Type of Activity, and fourth is CategoryID or (0,1,2,3). So I chose 2 for the Name and 1 for the Value. I want the Activity Type displayed as text and the Value to be the METS so I can calculate calories burnt more precisely. I checked and I have the pair set correctly. I also checked by not using a CDD but with two dd's from sqlDataSource and same problem. I have seen this issue before. When a large number of choice populate a DD then sometimes the wrong value appears when selected. I think it looks for the SelectedValue which is the same as the Item selected and choice the first one it comes to.
I think I can see how that might happen if you have multiple values that are the same. But I wouldn't think it would be a problem if you have multiple names that are the same. If you're seeing this without using CascadingDropDown at all, then it's probably pretty safe to say it's not responsible, but it might be interesting to get to the bottom of this anyway. :)

Well at least in this case I have to change my Cascading Drop downs anyway because I cannot get at the values and the needed selectedItem.Text value, which makes no sense to me what so ever. I would think since you have to use name-value pairs to retrieve the cdd's then you would want to be able to retrieve the SelectedItem.Text value somehow to write it back somewhere.

Write now I have to use the SelectedValue to get the ID of the item I want and make another DB call to get that value AGAIN, seems a waste.

Any help on either front would be great!


I was going to point you athttp://forums.asp.net/thread/1317472.aspx for some more information, but it looks like you're already part of that thread. :)

Dave: Thanks for your input, and I always search the posts prior to posting. I have learned many things that way.

One issue was that I had the DD's in post back mode from trying to use the sqldatasource without altas. after removing the property it works better now, but still has some hickups when alot of data populated the dd's. Not as many errors but still a few.

I also HATE the fact that we cannot get at the selectedItem.text value. This is a pain.


FYI, I've opened work item 1895 to track the .Text request.

Saturday, March 24, 2012

CascadingDropDown with database - newbie question

Sorry if this seems a bit obvious but I'm a relative newcomer to ASP.NET, especially Atlas, and am a bit stuck.

I have an Access database with three tables: tblRegions, tblProvinces and tblTowns. I want to use these tables to drive 3 related dropdown lists. It's no problem achieving this with server-side controls, but I want the performance advantages of client-side processing - hence my interest in Atlas.

I've followed the examples and, after a bit of a struggle, managed to get a "hard-coded" version working (with the names of Regions, Provnces, etc. embedded in the code), but now I'm ready to data-enable it.

I've looked for examples and can't find anything that covers everything I need to do. All the examples seem to assume a fairly high level of coding experience - especially in C# - whereas I'm more used to dragging and dropping datasources and coding, when strictly necessary, in VB. Can anyone explain the exact steps I need to follow to achieve what I want using VB and, preferably, using an Access data source?

At the moment, my code is as follows:
---------- Default.aspx --------------
<%@dotnet.itags.org. Page Language="VB" EnableEventValidation="false" %>
<%@dotnet.itags.org. Register
Assembly="AtlasControlToolkit"
Namespace="AtlasControlToolkit"
TagPrefix="atlasToolkit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server"><title>Test Page</title></head>
<body><form id="Form1" runat="server"><div>
<atlas:ScriptManager ID="ScriptManager1" runat="server" />
<table>
<tr>
<td align="right">Region: </td>
<td><asp:DropDownList ID="ddlRegion" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td align="right">Province: </td>
<td><asp:DropDownList ID="ddlProvince" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td align="right">Town: </td>
<td><asp:DropDownList ID="ddlTown" runat="server"></asp:DropDownList></td>
</tr>
</table>
<atlasToolkit:CascadingDropDown ID="cdd" runat="server">
<atlasToolkit:CascadingDropDownProperties
TargetControlID="DropDownList1"
Category="Region"
ServicePath="ListcountService.asmx"
ServiceMethod="GetRegions"
PromptText="-- Any Region --" />
<atlasToolkit:CascadingDropDownProperties
TargetControlID="ddlRegion"
Category="Region"
ServicePath="ListcountService.asmx"
ServiceMethod="GetRegions"
PromptText="-- Any Region --" />
<atlasToolkit:CascadingDropDownProperties
TargetControlID="ddlProvince"
ParentControlID="ddlRegion"
Category="Province"
ServicePath="ListcountService.asmx"
ServiceMethod="GetProvincesForRegion"
PromptText="-- Any Province --" />
<atlasToolkit:CascadingDropDownProperties
TargetControlID="ddlTown"
ParentControlID="ddlProvince"
Category="Town"
ServicePath="ListcountService.asmx"
ServiceMethod="GetTownsForProvince"
PromptText="-- Any Town --" />
</atlasToolkit:CascadingDropDown>
</div></form></body>
</html
----------- ListcountService.asmx ----------
<%@dotnet.itags.org. WebService Language="VB" Class="ListCountService1" %
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports AtlasControlToolkit

<WebService(Namespace := "http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class ListCountService1
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function GetRegions(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue("Lazio", 1), _
New CascadingDropDownNameValue("Tuscanny", 2), _
New CascadingDropDownNameValue("Umbria", 3)}
End Function

<WebMethod()> _
Public Function GetProvincesForRegion(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim RegionId As Integer
If (kv.ContainsKey("Region") And Int32.TryParse(kv("Region"), RegionId)) Then
If (RegionId = 1) Then
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue("Upper Lazio", 11), _
New CascadingDropDownNameValue("Middle Lazio", 12), _
New CascadingDropDownNameValue("Lower Lazio", 13)}
ElseIf (RegionId = 2) Then
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue("Upper Tuscanny", 21), _
New CascadingDropDownNameValue("Middle Tuscanny", 22), _
New CascadingDropDownNameValue("Lower Tuscanny", 23)}
End If
End If
Return New CascadingDropDownNameValue() {}
End Function

<WebMethod()> _
Public Function GetTownsForProvince(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim ProvinceId As Integer
If (kv.ContainsKey("Province") And Int32.TryParse(kv("Province"), ProvinceId)) Then
If (ProvinceId = 11) Then
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue("Upper Lazio Town 1", 111), _
New CascadingDropDownNameValue("Upper Lazio Town 2", 112), _
New CascadingDropDownNameValue("Upper Lazio Town 3", 113)}
ElseIf (ProvinceId = 12) Then
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue("Middle Lazio Town 1", 121), _
New CascadingDropDownNameValue("Middle Lazio Town 2", 122), _
New CascadingDropDownNameValue("Middle Lazio Town 3", 123)}
End If
End If
Return New CascadingDropDownNameValue() {}
End Function
End Class
---------------------------

Thanks in advace for your help,
AndrewHave you already followed theUsing CascadingDropDown with a Database walkthrough that comes with the Toolkit?
Yes, but I don't understand the GetColorsForModel function, especially the code that reads:

CarsTableAdapters.ColorTableAdapter adapter =
newCarsTableAdapters.ColorTableAdapter();

Cars.ColorDataTable colorTable =
adapter.GetColorsForModel(modelId);

List<CascadingDropDownNameValue> values =
new List<CascadingDropDownNameValue>();

foreach (DataRow drin colorTable)
{
values.Add(newCascadingDropDownNameValue(
(string) dr["Color"],
dr["ColorID"].ToString()));

Maybe I'm naive, but when I used plain ASP.NET (with VWD 2005), I found it very easy to set up a dropdown list. I just dragged a datasource object from the toolbox, set it up by defining a few properties and then dragged a dropdown list next to it and linked it to the datasource by defining more properties. No coding necessary. Coming from that experince, I guess I'm a bit daunted by the amount of C# that comes up in the examples. I'm prepared to get my hands dirty if necessary, but I was hoping that the toolkit would hold my hand a bit. Any ideas?

The CascadingDropDown is more complex because it enables a richer scenario. The web service it calls has the ability to return any set of data based on any information. In the sample above, it's querying part of a database to return only the subset of data that's needed by the CDD that's about to populate. I believe that if you walk through the code above with the MSDN documentation, the purpose of each line should not be too difficult to understand. Once you understand how this example is working, if you have any suggestions for simplifying it from the point of view of someone who's just gone through the process, we'd certainly love to hear them!!

Thanks for your help!


Thanks for my help? I thought it was you helping me!

Anyway, it looks like I can't connect to the database table by dragging and dropping a DataSource object from the toolbox like- right? I need to make the connection in code. As a newbie used to ASP.NET and VWD2005, that's a bit daunting - especially since the example is in C# and I'm more used to VB.

Could you please point me to a code example (preferably in VB) that shows how to connect to an Access database table and put the results into an array?

Thanks in advance
OK here's an update. After much scrabbling around the MSDN documentation, and a lot of trial-and-error guesswork, I've finally managed to make some progress. This is the updated version of my GetRegions function:

Public Function GetRegions(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter _
("SELECT regID, regName FROM tlkpRegionList ORDER BY RegID", _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"User ID = Admin;" & _
"Data Source=D:\WebSites\AtlasWebSite\App_Data\TiberPass.mdb")
Dim objDataSet As New System.Data.DataSet()
objAdapter.Fill(objDataSet)
Dim objTable As System.Data.DataTable
objTable = objDataSet.Tables(0)
With objTable
Dim intRow As Integer
For intRow = 0 To .Rows.Count - 1
Return New CascadingDropDownNameValue() _
{New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1), .Rows(intRow).ItemArray(0))}
Next intRow
End With
Return New CascadingDropDownNameValue() {}
End Function

It succesfully gets a record from my database table. However, there are several problems:

1) I used the absolute path to connect to the Access database file. This won't port onto a web-server. Is there a way to use a relative path instead?

2) At this stage, I've only managed to retrieve one record. I want to loop through all the records in the table, but I don't know how to add additional entries to the CDD. Any clues?

3) Assuming I can solve the above problems, there's still a flaw when it comes to the other two CDDs. They are going to need logic (IF statements, etc.) so that they are populated with data depending on the user selection in the "upstream" CDDs. It seems that I'll need to make calls to the database (on the server) to achieve this. I feel it would be better if I could somehow make a single call to the database to get the contents of the three tables (into a DataSet?) for client-side processing. That would avoid repeated calls back to the server. Any idea how to achieve this?

Thanks again for your help,
Andrew

1: You can probably use relative paths. Someone who knows Access access can probably help here.

2: You want to build an array of CascadingDropDownNameValue objects, then return the array. Your code's set up for looping through the returned rows, but always returns just the first CascadingDropDownNameValue it creates.

3: Each CDD populates independently, so each associated web method should query the database to get the data it needs. That said, each call can use the information available to it about the values of the parent control(s) to filter that data. But there will be one web service call for each CDD when it needs to populate. If you want to cache things in your web methods such that you only ever need to make one database call, that's certainly something you can do.


I've solved point 1 (relative path) by replacing my code:
"Data Source=D:\WebSites\AtlasWebSite\App_Data\TiberPass.mdb")
with:
"Data Source=" & Server.MapPath(".") & "\App_Data\TiberPass.mdb")
I've yet to test this on a web server, but it works on my local machine.

Point 2 (returning an array of CascadingDropDownNameValue objects) is a bit more daunting.

Point 3 (caching table data) is something I can look into later. So...

Any advice on how to set up and return an array of CDD NameValue objects?

Hi Android,

The key bits you need are as follows:

Dim

valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)

For intRow = 0 To .Rows.Count - 1

values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))

Next

Return

values.ToArray

Not sure I've copy and pasted your data in exactly right, but essentially when you add the CascadingDropDownNameValue pair, you need to put in the displayed text as a string then the hidden value (which doesn't have to be a string - and is obviously normally an Integer type ID field from your db).

Any probs, drop a reply.

Simon


Should've checked the preview, damn editor moved things onto next lines....... here's another try.

The key bits you need are as follows:

Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)

For intRow = 0 To .Rows.Count - 1

values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))

Next

Return values.ToArray

Simon


Thanks a lot Simon, that's exactly the help I needed.Big Smile After adding the lineImports System.Collections.GenericI now have the whole thing working. Thanks again.

I'm now interested in making it more efficient (addressing point (3)). At the moment, every time the user selects a dropdown value, the "downstream" dropdown(s) must be re-populated by a call to the appropriate webservice functions. That's fine but these functions make calls to the database. It seems to me that, since this is fairly static data (new towns don't appear every day) it would be better to make one call at the start and cache the results for later use. That would reduce the number of calls to the database and, presumably, improve performance.

I read in the MSDN documentation that the Data Adapter object (OleDbDataAdapter) can hold a "local" copy of a set of tables. Maybe I could populate this once and then fill my data sets from it when I need to. Trouble is, I'm not sure how to do this - including where to put the code so it's kept available to my functions. I guess I'm thinking of it a bit like a good 'ol global variable - but I guess that's not very object-oriented. Perhaps I need to create a class with Set and Get methods?

I've also notice that a lot of my logic is duplicated between the three webservice functions, e.g. setting up the connection to the database. That makes me think that it may be better to create a single parameterized function (or maybe the class mentioned above), that each function calls with a table name and which returns the CDD array to the calling function. I know the concepts, but my coding skills aren't really up to it.

Finally, I'm concerned about housekeeping, i.e. tidying up and closing objects that are no longer needed to free up resources.

It seems to me that, with a lot of help, I've managed to solve the main problems in getting this working. Maybe someone out there would be interested in addressing the issues I've mentioned above and creating an efficient and flexible object that accepts parameters, such as table names, and returns the appropriate arrays to populate the CDDs whilst hiding all the complexity from the developer. Come to think of it, it would be really great if such an object were part of the Atlas Control Toolkit. That way, novice developers could drag and drop it onto the work surface from the toolbox and set a few properties to get it working. That's my feedback to Microsoft. I like using ASP.NET and Visual Web Developer because I can achieve most of the common tasks without needing to write any code. Having created a set of CDDs in standard ASP.NET, I was dissappointed by the performance. So I had a look at Atlas - but I was suddenly thrown from the cosy drag-and-drop world I was used to into a world of C# code. I would have thought that this was a common enough requirement among web developers to be worth addressing.

Anyway, for anyone interested, my complete webservice code now reads:

<%@.WebServiceLanguage="VB"Class="ListCountService" %>

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports AtlasControlToolkit

Imports System.Collections.Generic

<WebService(Namespace:="http://tempuri.org/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

PublicClass ListCountService

Inherits System.Web.Services.WebService

<WebMethod()> _

PublicFunction GetRegions(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()

Dim objAdapterAsNew System.Data.OleDb.OleDbDataAdapter _

("SELECT regID, regName FROM tlkpRegionList ORDER BY regID", _

"Provider = Microsoft.Jet.OLEDB.4.0;" & _

"User ID = Admin;" & _

"Data Source=" & Server.MapPath(".") &"\App_Data\TiberPass.mdb")

Dim objDataSetAsNew System.Data.DataSet()

objAdapter.Fill(objDataSet)

Dim objTableAs System.Data.DataTable

objTable = objDataSet.Tables(0)

With objTable

Dim intRowAsInteger

Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)

For intRow = 0To .Rows.Count - 1

values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))

Next

Return values.ToArray

EndWith

ReturnNew CascadingDropDownNameValue() {}

EndFunction

<WebMethod()> _

PublicFunction GetProvincesForRegion(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()

Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim RegionIdAsInteger

If (kv.ContainsKey("Region")And Int32.TryParse(kv("Region"), RegionId))Then

Dim objAdapterAsNew System.Data.OleDb.OleDbDataAdapter _

("SELECT provID, provName FROM tlkpProvinceList WHERE provRegion=" & RegionId &" ORDER BY provID", _

"Provider = Microsoft.Jet.OLEDB.4.0;" & _

"User ID = Admin;" & _

"Data Source=" & Server.MapPath(".") &"\App_Data\TiberPass.mdb")

Dim objDataSetAsNew System.Data.DataSet()

objAdapter.Fill(objDataSet)

Dim objTableAs System.Data.DataTable

objTable = objDataSet.Tables(0)

With objTable

Dim intRowAsInteger

Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)

For intRow = 0To .Rows.Count - 1

values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))

Next

Return values.ToArray

EndWith

EndIf

ReturnNew CascadingDropDownNameValue() {}

EndFunction

<WebMethod()> _

PublicFunction GetTownsForProvince(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()

Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

Dim ProvinceIdAsInteger

If (kv.ContainsKey("Province")And Int32.TryParse(kv("Province"), ProvinceId))Then

Dim objAdapterAsNew System.Data.OleDb.OleDbDataAdapter _

("SELECT townID, townName FROM tlkpTownList WHERE townProvince=" & ProvinceId &" ORDER BY townID", _

"Provider = Microsoft.Jet.OLEDB.4.0;" & _

"User ID = Admin;" & _

"Data Source=" & Server.MapPath(".") &"\App_Data\TiberPass.mdb")

Dim objDataSetAsNew System.Data.DataSet()

objAdapter.Fill(objDataSet)

Dim objTableAs System.Data.DataTable

objTable = objDataSet.Tables(0)

With objTable

Dim intRowAsInteger

Dim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)

For intRow = 0To .Rows.Count - 1

values.Add(New CascadingDropDownNameValue(.Rows(intRow).ItemArray(1).ToString, .Rows(intRow).ItemArray(0)))

Next

Return values.ToArray

EndWith

EndIf

ReturnNew CascadingDropDownNameValue() {}

EndFunction

EndClass


I have now stumbled my way through creating my own first cascading dropdown (hooked into a database, and all in VB), and it is thanks largely to the posts here. I'm so happy I got it to work! For the sake of others trying to do this same thing, I will also post my code here, so that others may benefit. Also, if any experts out there see something inefficient in the code below, please let me know - I want this project to be as successful as possible! Thanks! - Matthew

In the aspx file that contains the dropdownlist:

<%

@.Register
Assembly="AtlasControlToolkit"Namespace="AtlasControlToolkit"TagPrefix="cc1" %>

That's at the top. Now, down in the area where we are using the drop down lists:

<cc1:CascadingDropDownID="CascadingDropDown1"runat="server">
<cc1:CascadingDropDownProperties
TargetControlID="DropDownList4"PromptText="Please select a product group name."ServicePath="ProductsService.asmx"ServiceMethod="GetProductGroups"Category="Group"/><cc1:CascadingDropDownProperties
TargetControlID="DropDownList5"PromptText="Please select a product code."ParentControlID="DropDownList4"ServicePath="ProductsService.asmx"ServiceMethod="GetCatalogCodesForGroup"Category="CatalogCode"/><cc1:CascadingDropDownProperties
TargetControlID="DropDownList6"PromptText="Please select a product description."ParentControlID="DropDownList5"ServicePath="ProductsService.asmx"ServiceMethod="GetProductsForCatalogCode"Category="Product"/>

</cc1:CascadingDropDown>

Now, I created theProductsService.asmxfile and put the follwing code in the .vb codebehind file:

Imports

System.Web

Imports

System.Web.Services

Imports

System.Web.Services.Protocols

Imports

System.Xml

Imports

System.Collections.Generic

Imports

AtlasControlToolkit

Imports

System.Data.SqlClient

<WebService(Namespace:=

"http://tempuri.org/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<

Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public

Class ProductsServiceInherits System.Web.Services.WebService

<WebMethod()> _

PublicFunction GetProductGroups(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()
'-- Declare the "List" of Name/Value pairs that we will build with our query to the databaseDim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)Dim conAs SqlConnectionDim SQLcmdAs SqlCommandDim DataRAs SqlDataReaderDim strSQLAsString'-- Select all of the GroupNames from the database

strSQL =

"SELECT DISTINCT([GroupName]) FROM dbo_C_CatalogGroup ORDER BY GroupName"

con =

New SqlConnection(ConfigurationManager.ConnectionStrings.Item("CrosbyCertSQLConnectionString").ConnectionString)

con.Open()

SQLcmd =

New SqlCommand(strSQL, con)

DataR = SQLcmd.ExecuteReader()

While DataR.Read()

values.Add(

New CascadingDropDownNameValue(DataR("GroupName"), DataR("GroupName")))EndWhile

DataR.Close()

con.Close()

Return values.ToArray()EndFunction

<WebMethod()> _

Public

Function GetCatalogCodesForGroup(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()
Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)If (kv.ContainsKey("Group"))Then
'-- Declare the "List" of Name/Value pairs that we will build with our query to the databaseDim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)Dim conAs SqlConnectionDim SQLcmdAs SqlCommandDim DataRAs SqlDataReaderDim strSQLAsString'-- Select all of the CatalogCodes from the database

strSQL =

"SELECT [CatalogCode] FROM dbo_C_CatalogGroup WHERE [GroupName] = '" & kv("Group") &"' ORDER BY [CatalogCode]"

con =

New SqlConnection(ConfigurationManager.ConnectionStrings.Item("CrosbyCertSQLConnectionString").ConnectionString)

con.Open()

SQLcmd =

New SqlCommand(strSQL, con)

DataR = SQLcmd.ExecuteReader()

While DataR.Read()

values.Add(

New CascadingDropDownNameValue(DataR("CatalogCode"), DataR("CatalogCode")))EndWhile

DataR.Close()

con.Close()

Return values.ToArray()ExitFunctionEndIf'-- Return an empty list, otherwiseReturnNew CascadingDropDownNameValue() {}EndFunction

<WebMethod()> _

Public

Function GetProductsForCatalogCode(ByVal knownCategoryValuesAsString,ByVal categoryAsString)As CascadingDropDownNameValue()
Dim kvAs StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)If (kv.ContainsKey("CatalogCode"))Then
'-- Declare the "List" of Name/Value pairs that we will build with our query to the databaseDim valuesAs List(Of CascadingDropDownNameValue) =New List(Of CascadingDropDownNameValue)Dim conAs SqlConnectionDim SQLcmdAs SqlCommandDim DataRAs SqlDataReaderDim strSQLAsString'-- Select all of the Product Descriptions from the database

strSQL =

"SELECT [Description], [ProductCode] FROM dbo_C_Product WHERE [CatalogCode] = '" & kv("CatalogCode") &"' ORDER BY [ProductCode]"

con =

New SqlConnection(ConfigurationManager.ConnectionStrings.Item("CrosbyCertSQLConnectionString").ConnectionString)

con.Open()

SQLcmd =

New SqlCommand(strSQL, con)

DataR = SQLcmd.ExecuteReader()

While DataR.Read()

values.Add(

New CascadingDropDownNameValue(DataR("Description"), DataR("ProductCode")))EndWhile

DataR.Close()

con.Close()

Return values.ToArray()ExitFunctionEndIf'-- Return an empty list, otherwiseReturnNew CascadingDropDownNameValue() {}EndFunction

End

Class

Thank you very much for your example! It helped me out tramendously. I did have to modify your example some as I was using a SQL DataSet and TableAdapters. My tread is at:

http://forums.asp.net/thread/1362449.aspx

Wednesday, March 21, 2012

CascadingDropDown Javascript error "Access is Denied."

Hi All,

I am trying to use the CascadingDropDown control from the AJAX Toolkit, but I recieve a Javascript "Access is Denied." error when I run the page. Googling off and on for the past couple of days hasn't yielded many possible solutions. The only one that looked promising washere but it didn't work in my case. The one piece thats different in my code than the examples I've found is that I'm trying to use an external Web Service/Method. The examples I've found all contain the .asmx file in the same project as the CascadingDropDown.

Information Regarding the Error
File: MicroSoftAjax.debug.js
Line: 4079
Code: this._xmlHttpRequest.open(verb, this._webRequest.getResolvedUrl(), true );
The page has a disabled DropDownList with the text "Loading States..." in it.

ASPX Code

 <form id="form1" runat="server"> <div> <asp:ScriptManager ID="ScriptManager1" runat="server"> <Services> <asp:ServiceReference Path="http://l3y9ypb1/IDSWebService/IDSCommon.asmx" /> </Services> </asp:ScriptManager> <ajax:CascadingDropDown ID="CascadingDropDown1" runat="server" Category="Town" LoadingText="Loading states..." PromptText="Select a state..." ServiceMethod="getCDDTowns" ServicePath="http://l3y9ypb1/IDSWebService/IDSCommon.asmx" TargetControlID="DropDownList1"> </ajax:CascadingDropDown> <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList> </div> </form>

Web Service Code - This Web Method works properly. The only reason I'm posting it is to make sure I didn't make any mistakes with the Service/Method attributes.

using AjaxControlToolkit;using IDSUtility;using System;using System.Collections.Generic;using System.Collections.Specialized;using System.Data;using System.Web;using System.Web.Script.Services;using System.Web.Services;using System.Web.Services.Protocols;[System.Web.Script.Services.ScriptService()][WebService(Namespace = 'http://tempuri.org/')][WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]public class IDSCommon : System.Web.Services.WebService{public IDSCommon() { } [ScriptMethod] [WebMethod]public CascadingDropDownNameValue[] getCDDTowns(string knownCategoryValues,string category) {string districtCode ="05";return Utility.getCDDVArrayFromDataTable(Utility.getDataTableFromStoredProcedure("districtCode", districtCode,"cursorTowns","getTowns","TOWNS"),"TOWN_NAME","TOWN_CODE"); }}

I'd appreciate any information regarding this problem.

Thanks,
Andy

I think you may be hitting cross-domain limitations that are outlined here and in the linked article discussing a related issue:http://blogs.msdn.com/delay/archive/2007/02/05/safely-avoiding-the-access-denied-dialog-how-to-work-around-the-access-denied-cross-domain-iframe-issue-in-the-ajax-control-toolkit.aspx


Hi Andy,

XmlHttpRequest doesn't support cross domain posting, so you can't use a web service in a different domain.

You may solve it by adding a proxy web service in the same domain, and this web service simply expose a serials of methods similar to the remote web service's. It internally calls the remote web service to implement.

On your page, register the web service within the same domain instead.

Hope this helps.


Thank you very much, that did the trick!