DropDownList: loading from SQL Server table in ASP.NET

[stevelaforgeParticipant]

I have a working example of how to load a select list using a jQuery AJAX call, although I am a newbie to the jQuery/AJAX world. I purchased the jqWidgets tools a few weeks ago because I liked a few of the input controls, but I cannot recreate the process of loading a dropdownlist from the same SQL table. For purposes of making this simple, I am using only the Movie table in the standard sample SQL Movies database. All I want to display is the

CREATE TABLE [dbo].[Movies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](max) NOT NULL,
[ReleaseDate] [datetime] NOT NULL,
[genre] [nvarchar](max) NOT NULL,
[Price] [decimal](18, 2) NOT NULL,
[Rating] [nvarchar](5) NULL,
CONSTRAINT [PK_dbo.Movies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

insert into Movies(Title,ReleaseDate,genre,Price,Rating) values(‘When Harry Met Sally’,’01/11/1989′,’Romantic Comedy’,7.99,’R’)
insert into Movies(Title,ReleaseDate,genre,Price,Rating) values(‘Ghostbusters’,’03/13/1984′,’Comedy’,8.99,’R’)
insert into Movies(Title,ReleaseDate,genre,Price,Rating) values(‘Ghostbusters 2′,’02/23/1986′,’Comedy’,9.99,’R’)
insert into Movies(Title,ReleaseDate,genre,Price,Rating) values(‘Rio Bravo’,’04/15/1959′,’Western’,3.99,’R’)

Does anyone have a working example or other resource that they can share? I have searched over and over without luck. The one example I found that stated it used a SQL table in fact used hard-coded values and I never found any reference to a SQL Server database.

Many thanks!

[HristoParticipant]

Hello stevelaforge,

Please, check our “ASP .NET MVC” demos section, there you will find “Bind DropDownList to SQL Database” demo.
Also, I would like to suggest you this article, could be helpful.

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[stevelaforgeParticipant]

I am sorry, but I am using ASP.NET Webforms, not ASP.NET MVC so the example you linked to did not provide must assistance. I have a project using standard jQuery and a couple of select boxes. I would like to use the jqxDropDownList in place of the select box.

My WebService.asmx looks like:

<%@ WebService Language="C#" Class="WebService" %>

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService  : System.Web.Services.WebService {

	//[WebMethod]
	//public string HelloWorld() {
	//    return "Hello World";
	//}

	public WebService()
	{
		//Uncomment the following line if using designed components 
		//InitializeComponent(); 
	}

	[WebMethod]
	public List<DeviceGroups> getDeviceGroups(List<string> myData)
	{
		string connString = ConfigurationManager.ConnectionStrings["mmdb"].ConnectionString.ToString();

		List<DeviceGroups> groupList = new List<DeviceGroups>();

		using (SqlConnection sqlCon = new SqlConnection(connString))
		{
			using (SqlCommand sqlCmd = new SqlCommand())
			{
				sqlCmd.CommandText = "select DeviceClassGroup, GroupDescription from tblCBO_DeviceGroup where DeviceClass = @DC order by DeviceClassGroup";
				sqlCmd.Connection = sqlCon;
				sqlCmd.Parameters.AddWithValue("@DC", myData[0].ToString());

				sqlCon.Open();

				SqlDataReader sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

				if (sqlRdr.HasRows)
				{
					while (sqlRdr.Read())
					{
						string deviceClassGroup = sqlRdr["DeviceClassGroup"].ToString();
						string groupDescription = sqlRdr["GroupDescription"].ToString();

						groupList.Add(new DeviceGroups
						{
							DeviceClassGroup = deviceClassGroup,
							GroupDescription = groupDescription
						});
					}   // of while (sqlRdr.Read())
				}   // end of if(sqlRdr.HasRows
			}   // end of sqlCmd using
		}   // end of sqlCon using
		return groupList;
	}   // end of getDeviceGroups

	[WebMethod]
	public List<Devices> getListOfDevices(List<string> myData)
	{
		string connString = ConfigurationManager.ConnectionStrings["mmdb"].ConnectionString.ToString();

		List<Devices> deviceList = new List<Devices>();

		using (SqlConnection sqlCon = new SqlConnection(connString))
		{
			using (SqlCommand sqlCmd = new SqlCommand())
			{
				sqlCmd.CommandText = "select DeviceNum, DeviceDescription, DeviceClass, DeviceGroup, DeviceType from tblDevices where DeviceNum like '" +
					myData[0].ToString() + "%' and Active = 1";
				sqlCmd.Connection = sqlCon;
				// sqlCmd.Parameters.AddWithValue("@Dev", myData[0]);

				sqlCon.Open();

				SqlDataReader sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

				if (sqlRdr.HasRows)
				{
					while (sqlRdr.Read())
					{
						string deviceNum = sqlRdr["DeviceNum"].ToString();
						string deviceDescription = sqlRdr["DeviceDescription"].ToString();
						string deviceClass = sqlRdr["DeviceClass"].ToString();
						string deviceGroup = sqlRdr["DeviceGroup"].ToString();
						string deviceType = sqlRdr["DeviceType"].ToString();

						deviceList.Add(new Devices
						{
							DeviceNum = deviceNum,
							DeviceDescription = deviceDescription,
							DeviceClass = deviceClass,
							DeviceGroup = deviceGroup,
							DeviceType = deviceType
						});
					}   // end of while(sqlRdr.Read())
				}   // end of if (sqlRdr.HasRows)
			}   // end of sqlCmd using
		}   // endof sqlCon using

		return deviceList;
	}   // end of getListOfDevices

	[WebMethod]
	public string getDeviceInfo(List<string> myData)
	{
		string results = "Device " + myData[0].ToString() + " not found";
		using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["mmdb"].ConnectionString.ToString()))
		using (SqlCommand sqlcmd = new SqlCommand())
		{
			sqlcmd.Connection = sqlCon;
			sqlcmd.CommandText = "select top 1 DeviceNum, DeviceDescription, case when Active = 1 then 'true' else 'false' end as DeviceActive, CreatedBy from tblDevices where DeviceNum like '" +
				myData[0].ToString() + "%'";

			sqlCon.Open();

			SqlDataReader sqlRdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);

			if (sqlRdr.HasRows)
			{
				while (sqlRdr.Read())
				{
					results = string.Format("DeviceNum = {0}<br />", sqlRdr["DeviceNum"].ToString());
					results += string.Format("Description = {0}<br />", sqlRdr["DeviceDescription"].ToString());
					results += string.Format("Active = {0}<br />", sqlRdr["DeviceActive"].ToString());
					results += string.Format("Created By = {0}", sqlRdr["CreatedBy"].ToString());
				}
			}
		}
		return results;
	}   // end of getDeviceInfo
}

My default.aspx page (there is nothing added to the code-behind page) looks like:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
  <head runat="server">
    <title>jqxWidgets Testing Area</title>
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <!-- ================================================================  -->
    <script src="jQuery/Version2.2.3/jquery-2.2.3.js"></script>
    <script src="bootstrap-3.3.6/js/bootstrap.js"></script>
    <script src="jqWidgets_4.4.0/jqwidgets/jqxcore.js"></script>
    <script src="jqWidgets_4.4.0/jqwidgets/jqxnumberinput.js"></script>
    <script src="jqWidgets_4.4.0/jqwidgets/jqxtooltip.js"></script>
    <script src="jqWidgets_4.4.0/jqwidgets/jqxbuttons.js"></script>
		<script src="jqWidgets_4.4.0/jqwidgets/jqxlistbox.js"></script>
		<script src="jqWidgets_4.4.0/jqwidgets/jqxdropdownlist.js"></script>
    <script src="Moment/moment.js"></script>
    <script src="scripts/ctm.js"></script>
    <!-- ================================================================  -->
    <link href="bootstrap-3.3.6/css/bootstrap.css" rel="stylesheet" />
    <link href="font-awesome-4.6.3/font-awesome-4.6.3/css/font-awesome.css" rel="stylesheet" />
    <link href="jqWidgets_4.4.0/styles/jqx.base.css" rel="stylesheet" />
    <link href="stylesheets/ctmstyles.css" rel="stylesheet" />
  </head>
  <body>
    <form id="form1" runat="server">
      <div>
        <label for="selDeviceGroups">Device Groups:</label>
        <select id="selDeviceGroups"></select>
      </div>
			<div id="ddlDeviceGroups"></div>
      <div>
        <label for="selDevices">Devices:</label>
        <select id="selDevices"></select>
      </div>
			<div id="divInfo"></div>
    </form>
    <script type="text/javascript">
      $(document).ready(function () {
      	var myData = [];		// create an array
      	myData[0] = "GB";		// set the value of the first element in the array to a constant

      	var jsonData = JSON.stringify({ myData: myData });

      	console.log("document.ready: jsonData = " + jsonData);

      	$("#ddlDeviceGroups").jqxDropDownList({
      		width: '200',
					height: '25'
      	});

      	$.ajax({
      		type: "POST",
      		url: "WebService.asmx/getDeviceGroups",
      		data: jsonData,
      		contentType: "application/json; charset=utf-8",
      		dataType: "json",
      		success: OnSuccess,
					error: OnErrorCall
      	});		// end of $.ajax

      	function OnSuccess(response) {
      		console.log("document.ready.OnSucess:response.d = " + response.d);
      		var items = response.d;
      		$("#selDeviceGroups").get(0).options.length = 0;  // clear any existing items
      		$("#selDeviceGroups").get(0).options[0] = new Option("Select Device Group...", "-1");
      		$("#ddlDeviceGroups").jqxDropDownList('addItem', { label: "Select Device Group...", value: "-1"});
      		$.each(items, function (index, val) {
      			var deviceClassGroup = val.DeviceClassGroup;
      			var groupDescription = val.GroupDescription;

      			$("#selDeviceGroups").get(0).options[$("#selDeviceGroups").get(0).options.length] = new Option(groupDescription, deviceClassGroup);
      			$("#ddlDeviceGroups").jqxDropDownList('addItem', { label: groupDescription, value: deviceClassGroup });
      		});
      	};		// end of OnSuccess

      	function OnErrorCall(response) {
      		console.log(response);
      		alert("OnErrorCall: loading of Device Groups failed!");
      	};	// end of OnErrorCall

      });		// end of $(document).ready

      $("#selDeviceGroups").change(function () {
      	var selectedValue = $(this).find('option:selected').val();
      	console.log("#selDeviceGroups.change > selectedValue = " + selectedValue);

      	$("#selDevices").get(0).options.length = 0;		// clears out any existing items

      	if (selectedValue != "-1")
      	{
      		var myData = [];
      		myData[0] = selectedValue;

      		var jsonData = JSON.stringify({ myData: myData });

      		console.log("jsonData: " + jsonData);

      		$.ajax({
      			type: "POST",
      			url: "WebService.asmx/getListOfDevices",
      			data: jsonData,
      			contentType: "application/json; charset-utf-8",
      			dataType: "json",
      			success: OnSuccess,
						error: OnErrorCall
      		})

      		function OnSuccess(response) {
      			var items = response.d;

      			$("#selDevices").get(0).options.length = 0;	// clear out existing items
      			$("#selDevices").get(0).options[0] = new Option("Select device...", "-1");
      			$.each(items, function (index,val) {
      				var deviceNum = val.DeviceNum;
      				var deviceDescription = val.DeviceDescription;
      				$("#selDevices").get(0).options[$("#selDevices").get(0).options.length] = new Option(deviceDescription, deviceNum);
      			});	// end of $each
      		};	// end of OnSuccess

      		function OnErrorCall(response) {
      			console.log("OnErrorCall.response = " + response);
      			alert("OnErrorCall.response = " + response);
      		};	// end of OnErrorCall

      	}		// end of if (selectedValue != "-1"
      });		// end of $("selDeviceGroups").change

      $("#selDevices").change(function () {
      	var selectedValue = $(this).find('option:selected').val();
      	console.log("selDevices.selectedValue = " + selectedValue);

      	if (selectedValue != "-1") {
      		var myData = [];
      		myData[0] = selectedValue;

      		var jsonData = JSON.stringify({ myData: myData });
      		console.log(jsonData);

      		$.ajax({
      			type: "POST",
      			url: "WebService.asmx/getDeviceInfo",
      			data: jsonData,
      			contentType: "application/json; charset=utf-8",
      			dataType: "json",
      			success: OnSuccess,
      			error: OnErrorCall
      		});

      		function OnSuccess(response) {
      			divInfo.innerHTML = response.d;
      		};

      		function OnErrorCall(response) {
      			divInfo.innerHTML = "Failed to retrieve data via AJAX: " + response.d;
      		};
      	}
      });	// end of selDevices.change
    </script>
  </body>
</html>

I have tried to use the same code that loads my select list to also add items to the dropdown. When I run the code, I get: “0x800a138f – JavaScript runtime error: Unable to get property ‘addItem’ of undefined or null reference”.

As I stated earlier, I am brand new to the jqWidgets toolbox and would appreciate any direction you can provide for the optimum way to get what I need. I want to use the jqWidget input controls as much as possible so that I have a consistent look and feel across my apps.

Thank you!

[stevelaforgeParticipant]

After trying the solution I posted above, I kept looking for other solutions. Still using the WebService.asmx file from above, I have looked thru numerous examples on your site and tried numerous combinations, the last being what I have below. The page loads and I see a dropdown listbox, but it does not appear to have any elements. When I click on it, I get the following error:

0x800a138f – JavaScript runtime error: Unable to get property ‘_renderOnDemand’ of undefined or null reference

Here is my current code:

  <body>
    <form id="form1" runat="server">
      <div>
        <label for="ddlDeviceGroups">Device Groups:</label>
        <div id="ddlDeviceGroups"></div>
      </div>
			<div id="divInfo"></div>
    </form>
    <script type="text/javascript">
      $(document).ready(function () {
      	var myData = [];		// create an array
      	myData[0] = "GB";		// set the value of the first element in the array to a constant

      	var jsonData = JSON.stringify({ myData: myData });

      	console.log("document.ready: jsonData = " + jsonData);

      	var url = "WebService.asmx/getDeviceGroups";

      	var source =
					{
						datatype: "json",
						datfields: [
							{ name: 'DeviceClassGroup', type: 'string' },
							{ name: 'GroupDescription', type: 'string' }
						],
						url: url,
						data: jsonData,
						type: "POST",
						async: false
					};

      	// var myDataAdapter = new $.jqx.dataAdapter(source, { contentType: 'application/json; charset=utf-8' });

      	var myDataAdapter = new $.jqx.dataAdapter(source, {
      		contentType: 'application/json; charset=utf-8',
      		downloadComplete: function (data, textStatus, jqXHR)
      		{
      			return data.d;
      		}
      	});

      	$("#ddlDeviceGroups").jqxDropDownList({
					selectedIndex: 0,
					source: myDataAdapter,
					displayMember: "GroupDescription",
					valueMember: "DeviceClassGroup",
      		width: 200,
					height: 25
      	});

      });		// end of $(document).ready
    </script>
  </body>

I did try using ‘myDataAdapter.dataBind();’ at the end of the $(document).ready but that didn’t change the results.

Thank you!

[HristoParticipant]

Hello stevelaforge,

It looks like you do not have an instance of the DropDownList.
I would like to suggest you look at this forum topic.

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[stevelaforgeParticipant]

A) I don’t understand what you are saying. In the first attempted solution I have:
$(“#ddlDeviceGroups”).jqxDropDownList({
width: ‘200’,
height: ’25’
});

In the second attempt, I have:
$(“#ddlDeviceGroups”).jqxDropDownList({
selectedIndex: 0,
source: myDataAdapter,
displayMember: “GroupDescription”,
valueMember: “DeviceClassGroup”,
width: 200,
height: 25
});

B) The forum topic you directed me to doesn’t have anything to do with what I am attempting.

c) If you go to your Documentation site for the DropDownList (http://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxdropdownlist/jquery-dropdownlist-api.htm?search=) and go down to the ‘addItem’ method and click on it, it comes up with 2 methods. Copy-and-paste the second method ( $(“#jqxDropDownList”).jqxDropDownList(‘addItem’, { label: ‘Text’ value: ‘Id’} ); ) and then click on the “Try It” link, which takes you to http://jsfiddle.net/jqwidgets/UxPdt/. Go down to the jqxButton.click event and replace $(“#jqxDropDownList”).jqxDropDownList(‘addItem’, ‘jQuery’); with the code used in the second method – $(“#jqxDropDownList”).jqxDropDownList(‘addItem’, { label: ‘Text’ value: ‘Id’} ); GUESS WHAT? It bombs too!

Now I’m not saying that I’m doing everything right, but some useful guidance would be greatly appreciated. Nothing you’ve provided thus far has given me any insight into the issue.

[stevelaforgeParticipant]

While working with this on jsFiddle, I discovered that my code worked if I included a script reference to:

<script src=”jqWidgets_4.4.0/jqwidgets/jqx-all.js”></script>

Thanks to Dimitar Dakovski, he pointed out the list of all files needed to use a jqxDropDownList. I was missing a reference to:

<script src=”jqWidgets_4.4.0/jqwidgets/jqxdropdownlist.js”></script>

All of the necessary references can be identified by going to the Documentation site, selecting the appropriate control and then going to ‘Getting Started’. The very first section will show you all of the references that are required to use that widget.

One other note that I shared with Dimitar. As of Jan 26, 2017: if you go to the API Documentation for the jqxDropDownList widget (http://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxdropdownlist/jquery-dropdownlist-api.htm?search=) and click on the addItem method, there is an error in Code Example 2.

Currently has: $(“#jqxDropDownList”).jqxDropDownList(‘addItem’, { label: ‘Text’ value: ‘Id’} ); // WRONG: Missing comma between “‘Text'” and ‘value’
but should be: $(“#jqxDropDownList”).jqxDropDownList(‘addItem’, { label: ‘Text’, value: ‘Id’} ); // CORRECT! Comma added

[Author]

Posts