[Solved] Passing QueryString to Handler Error: The parameterized query expects the parameter which was not supplied in ASP.Net

Hi arie.keren,

This error is due to passing null value to parameter. you must check parameter for null value. If it is null you must pass DBNull.Value

Check this example and take its reference and correct your code.

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

HTML

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers();
        $("#btnSearch").click(function () {
            GetCustomers();
        });
    });
    function GetCustomers() {
        var table = $("#tblCustomers");
        var customerId = $.trim($("#txtCustomerId").val());
        $.getJSON('<%=ResolveUrl("~/Handler.ashx")%>' + '?customerId=' + customerId + '&callback=?', function (result) {
            table.find("tr:not(:first)").remove();
            $.each(result, function (i, customer) {
                var row = table[0].insertRow(-1);
                $(row).append("<td />");
                $(row).find("td").eq(0).html(customer.CustomerId);
                $(row).append("<td />");
                $(row).find("td").eq(1).html(customer.Name);
                $(row).append("<td />");
                $(row).find("td").eq(2).html(customer.Country);
            });
        });
    }
</script>
CustomerId:
<input type="text" id="txtCustomerId" />
<input type="button" id="btnSearch" value="Search" />
<hr />
<table id="tblCustomers" border="0" cellpadding="0" cellspacing="0">
    <tr>
        <th style="width: 90px">
            Customer Id
        </th>
        <th style="width: 120px">
            Name
        </th>
        <th style="width: 90px">
            Country
        </th>
    </tr>
</table>

Handler.ashx.cs

<%@ WebHandler Language="C#" Class="HandlerCS" %>

using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Script.Serialization;

public class HandlerCS : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        string callback = context.Request.QueryString["callback"];
        string customerId = context.Request.QueryString["customerId"];
        string json = this.GetCustomersJSON(customerId);
        if (!string.IsNullOrEmpty(callback))
        {
            json = string.Format("{0}({1});", callback, json);
        }

        context.Response.ContentType = "text/json";
        context.Response.Write(json);
    }

    private string GetCustomersJSON(string customerId)
    {
        List<object> customers = new List<object>();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "SELECT * FROM Customers WHERE CustomerId = @CustomerId OR @CustomerId IS NULL";
                if (!string.IsNullOrEmpty(customerId))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", customerId);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CustomerId", DBNull.Value);
                }
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        customers.Add(new
                        {
                            CustomerId = sdr["CustomerId"],
                            Name = sdr["ContactName"],
                            Country = sdr["Country"]
                        });
                    }
                }
                conn.Close();
            }
            return (new JavaScriptSerializer().Serialize(customers));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Handler.ashx.vb

<%@ WebHandler Language="VB" Class="HandlerVB" %>

Imports System
Imports System.Web
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.Script.Serialization

Public Class HandlerVB : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim callback As String = context.Request.QueryString("callback")
        Dim customerId As String = context.Request.QueryString("customerId")
        Dim json As String = Me.GetCustomersJSON(customerId)
        If Not String.IsNullOrEmpty(callback) Then
            json = String.Format("{0}({1});", callback, json)
        End If

        context.Response.ContentType = "text/json"
        context.Response.Write(json)
    End Sub

    Private Function GetCustomersJSON(ByVal customerId As String) As String
        Dim customers As List(Of Object) = New List(Of Object)()
        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "SELECT * FROM Customers WHERE CustomerId = @CustomerId OR @CustomerId IS NULL"
                If Not String.IsNullOrEmpty(customerId) Then
                    cmd.Parameters.AddWithValue("@CustomerId", customerId)
                Else
                    cmd.Parameters.AddWithValue("@CustomerId", DBNull.Value)
                End If

                cmd.Connection = conn
                conn.Open()
                Using sdr As SqlDataReader = cmd.ExecuteReader()
                    While sdr.Read()
                        customers.Add(New With {Key .CustomerId = sdr("CustomerId"), Key .Name = sdr("ContactName"), Key .Country = sdr("Country")})
                    End While
                End Using

                conn.Close()
            End Using

            Return (New JavaScriptSerializer().Serialize(customers))
        End Using
    End Function
    
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

Screenshot