I'm trying to run a webmethod via a jQuery which will query an SQL database based on the contents of a pair of cascading dropboxes. I've attempted to call the method and grab the results using various jQueries and at one point using UpdatePanel to attempt to get it working but at the end of each attempt I seem too be left at the same point, all the code runs without errors however the result string passed to the final label text is blank.
I'm guessing I may have missed something very rudimentary in the WebMethod and wondered if anyone could point me in the right direction:
The target table, called drivers, in the database has 4 columns:
model_id, an integer, which should be ddlModel.SelectedItem.Value
model, a varchar (255), which should be ddlModel.SelectedItem.Text
driver, an integer (bit has been tried but has no impact on the issue, inter is used in order to allow the record creation page to run correctly), which contains either a 1 or 0 to define if the subs field should be used in the result string
subs, a varchar (255), which contains data to be added to the result string if driver is 0
I have not detailed the tables used to populate the cascading dropdown boxes as they appear to be functioning correctly, however if this or any other information is needed please do not hesitate to ask.
My code is as follows:
printers.asmx.vb
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
<WebService(Namespace:="http://printers.mydomainname.com/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class printers
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetMake(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("PrinterConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strMakeQuery As String = "SELECT * FROM manufacturers ORDER BY make ASC"
Dim cmdFetchMake As SqlCommand = New SqlCommand(strMakeQuery, sqlConn)
Dim dtrMake As SqlDataReader
Dim myMake As New List(Of CascadingDropDownNameValue)
sqlConn.Open()
dtrMake = cmdFetchMake.ExecuteReader
While dtrMake.Read()
Dim strMakeName As String = dtrMake("make").ToString
Dim strMakeId As String = dtrMake("make_id").ToString
myMake.Add(New CascadingDropDownNameValue(strMakeName, strMakeId))
End While
Return myMake.ToArray
End Function
<WebMethod()> _
Public Function GetModel(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim strConnection As String = ConfigurationManager.ConnectionStrings("PrinterConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strModelQuery As String = "SELECT * FROM printers WHERE make_id = @makeid"
Dim cmdFetchModel As SqlCommand = New SqlCommand(strModelQuery, sqlConn)
Dim dtrModel As SqlDataReader
Dim kvModel As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim intMakeId As Integer
If Not kvModel.ContainsKey("make") Or Not Int32.TryParse(kvModel("make"), intMakeId) Then
Return Nothing
End If
cmdFetchModel.Parameters.AddWithValue("@makeid", intMakeId)
Dim myModel As New List(Of CascadingDropDownNameValue)
sqlConn.Open()
dtrModel = cmdFetchModel.ExecuteReader
While dtrModel.Read()
Dim strModelName As String = dtrModel("model").ToString
Dim strModelId As String = dtrModel("model_id").ToString
myModel.Add(New CascadingDropDownNameValue(strModelName, strModelId))
End While
Return myModel.ToArray
End Function
<WebMethod()> _
Public Function GetDriver(ByVal model As String) As String
Dim strConnection As String = ConfigurationManager.ConnectionStrings("PrinterConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strDriverQuery As String = "SELECT * FROM drivers WHERE model = @model"
Dim cmdFetchDriver As SqlCommand = New SqlCommand(strDriverQuery, sqlConn)
Dim dtrDriver As SqlDataReader
Dim intModel As Integer
cmdFetchDriver.Parameters.AddWithValue("@model", intModel)
Dim strResult As String = "The selected printer is"
sqlConn.Open()
dtrDriver = cmdFetchDriver.ExecuteReader
dtrDriver.Read()
Dim intDriver As Integer = dtrDriver("driver")
Dim strSubs As String = dtrDriver("subs").ToString
If intDriver = 1 Then
strResult = strResult + "fully compatible with the Windows 2003 Hosted platform."
Else
strResult = strResult + "supported on the Windows 2003 Hosted platform via a subsituted driver:" + strSubs
End If
Return strResult
End Function
End Class
default.aspx
<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation="false" Inherits="Printer_Compatibility_Matrix_VB._Default" Codebehind="Default.aspx.vb" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!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>Printer Compatibility Matrix</title>
<script src="jquery-1.4.4.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
function CallService() {
$.ajax({
type: "POST",
url: "printers.asmx/GetDriver",
data: $("#ddlModel option:selected").text(),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
error: OnError
});
}
function OnSuccess(data, status) {
$("#lblResult").html(data.d);
}
function OnError(request, status, error) {
$("#lblResult").html(request.statusText);
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods = "true">
<Services>
<asp:ServiceReference Path="printers.asmx" />
</Services>
</asp:ScriptManager>
<div>
Manufacturer: <asp:DropDownList ID="ddlMake" runat="server" Width="170" /><br />
Printer: <asp:DropDownList ID="ddlModel" runat="server" Width="170" /><br />
<asp:Button ID="btnDriver" Text="Submit" OnClientClick="CallService(); return false;" runat="server" />
<asp:Label ID="lblResult" Text=" " Width="100%" runat="server" />
<cc1:CascadingDropDown
id="CascadingDropDown1"
runat="server"
category="Make"
prompttext="Select a Manufacturer..."
ServiceMethod="GetMake"
ServicePath="printers.asmx"
TargetControlId="ddlMake"
/>
<cc1:CascadingDropDown
id="CascadingDropDown2"
runat="server"
category="Model"
prompttext="Select a Printer..."
ServiceMethod="GetModel"
ServicePath="printers.asmx"
TargetControlId="ddlModel"
ParentControlId="ddlMake"
/>
</div>
</form>
</body>
</html>
Many thanks for your time,
Jim
EDIT
Appears that in addition to the error spotted by Frédéric, I had also forgotten to reference jQuery, I have amended the above code to match my current iteration based on these errors.
In its current state it now returns 'internal server error' into the label text. I suspect it to be something to do with my java as even attempts to call a simple 'Hello World' with this code returns a blank result to the label.
I've fixed this now, I did a bit more fiddling with passing $("#ddlModel").val() instead of text and managed to get it to respond correctly.
For anyone else with similar issues my amended working functions are as follows:
printers.asmx.vb
<WebMethod()> _
Public Function GetDriver(ByVal model_id As Integer)
Dim strConnection As String = ConfigurationManager.ConnectionStrings("PrinterConnection").ConnectionString
Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
Dim strDriverQuery As String = "SELECT * FROM drivers WHERE model_id = @model_id"
Dim cmdFetchDriver As SqlCommand = New SqlCommand(strDriverQuery, sqlConn)
Dim dtrDriver As SqlDataReader
Dim intModel As Integer
cmdFetchDriver.Parameters.AddWithValue("@model_id", intModel)
cmdFetchDriver.Parameters("@model_id").Value = model_id
Dim strResult As String = ""
sqlConn.Open()
dtrDriver = cmdFetchDriver.ExecuteReader
dtrDriver.Read()
Dim intDriver As Integer = dtrDriver("driver")
Dim strSubs As String = dtrDriver("subs").ToString
If intDriver = 1 Then
strResult = "<font color=11CC11>The selected printer is fully compatible.</font>"
ElseIf intDriver = 0 Then
strResult = "<font color=FF9933>The selected printer may be compatible via the substitute print driver: " + strSubs + "</font>"
End If
Return strResult
End Function
Default.aspx
<script src="jquery-1.4.4.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
function CallService() {
$.ajax({
type: "POST",
url: "printers.asmx/GetDriver",
data: "{ 'model_id': " + $("#ddlModel :selected").val() + "}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
error: OnError
});
}
function OnSuccess(data, status) {
$("#lblResult").html(data);
}
function OnError(request, status, error) {
$("#lblResult").html("<font color=#FF0000>Please make a valid selection.<font>");
}
</script>