ASP.NET C# SQL Submit Error

Question

I am getting the below error when posting my form.

Parameterized query expects a parameter value which was not supplied. Parameter name: 0 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Parameterized query expects a parameter value which was not supplied. Parameter name: 0

Source Error:

Line 70: var dbSave = Database.Open("QualityMonitoring"); Line 71: var insertCommand = "INSERT INTO Scores (Agent, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Notes) VALUES(@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20, @21)"; Line 72: dbSave.Execute(insertCommand, Agent, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Notes); Line 73: Response.Redirect("~/BureauForm"); Line 74: }

Source File: c:\Users\David\Documents\My Web Sites\EmptySite\BureauForm.cshtml Line: 72

Stack Trace:

[ArgumentNullException: Parameterized query expects a parameter value which was not supplied. Parameter name: 0]
System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings(Boolean verifyValue) +1415
System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) +569
System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() +21
WebMatrix.Data.Database.Execute(String commandText, Object[] args) +116 ASP._Page_BureauForm_cshtml.Execute() in c:\Users\David\Documents\My Web Sites\EmptySite\BureauForm.cshtml:72
System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197
System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69 System.Web.WebPages.WebPage.ExecutePageHierarchy() +151 System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +76
System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContext

context) +249

Please see my code and markup below.

@{

// SELECT AGENT

var db = Database.Open("QualityMonitoring") ;
var listAgent = "SELECT Agent FROM Data";

List<SelectListItem> agentdropdownlistdata = new List<SelectListItem>();
bool isSelected = false;
agentdropdownlistdata.Add(new SelectListItem {Text = "Please Choose...",Value = "0", Selected = true });
foreach(var item in db.Query(listAgent)){   
    agentdropdownlistdata.Add(new SelectListItem
    {
        Text = item.Agent,
        // Value = item.ID.ToString(), 
        Selected = isSelected
    });
}

// SAVE FORM

var Agent = "";
var Q1 = "";
var Q2 = "";
var Q3 = "";
var Q4 = "";
var Q5 = "";
var Q6 = "";
var Q7 = "";
var Q8 = "";
var Q9 = "";
var Q10 = "";
var Q11 = "";
var Q12 = "";
var Q13 = "";
var Q14 = "";
var Q15 = "";
var Q16 = "";
var Q17 = "";
var Q18 = "";
var Q19 = "";
var Q20 = "";
var Notes = "";

    if(IsPost){

    Agent = Request.Form["Agent"];
    Q1 = Request.Form["Q1"];
    Q2 = Request.Form["Q2"];
    Q3 = Request.Form["Q3"];
    Q4 = Request.Form["Q4"];
    Q5 = Request.Form["Q5"];
    Q6 = Request.Form["Q6"];
    Q7 = Request.Form["Q7"];
    Q8 = Request.Form["Q8"];
    Q9 = Request.Form["Q9"];
    Q10 = Request.Form["Q10"];
    Q11 = Request.Form["Q11"];
    Q12 = Request.Form["Q12"];
    Q13 = Request.Form["Q13"];
    Q14 = Request.Form["Q14"];
    Q15 = Request.Form["Q15"];
    Q16 = Request.Form["Q16"];
    Q17 = Request.Form["Q17"];
    Q18 = Request.Form["Q18"];
    Q19 = Request.Form["Q19"];
    Q20 = Request.Form["Q20"];
    Notes = Request.Form["Notes"];

    var dbSave = Database.Open("QualityMonitoring");
    var insertCommand = "INSERT INTO Scores (Agent, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Notes) VALUES(@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20, @21)";
    dbSave.Execute(insertCommand, Agent, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Notes);
    Response.Redirect("~/BureauForm");
}
}

<html>
<body>

<h2>Bureau Quality Monitoring</h2>

<form method="get">
<div>
<fieldset>
   Select Agent: @Html.DropDownList("Agent", agentdropdownlistdata)
</fieldset>
</div>
</form>

<br/>

<form method="post">
<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q1">Question 1</label>
    Yes<input type="radio" name="Q1" value="Yes">
    No<input type="radio" name="Q1" value="No">
    N/A<input type="radio" name="Q1" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q2">Question 2</label>
    Yes<input type="radio" name="Q2" value="Yes">
    No<input type="radio" name="Q2" value="No">
    N/A<input type="radio" name="Q2" value="N/A">
    <br/>
    <label for="Q3">Question 3</label>
    Yes<input type="radio" name="Q3" value="Yes">
    No<input type="radio" name="Q3" value="No">
    N/A<input type="radio" name="Q3" value="N/A">
    <br/>
    <label for="Q4">Question 4</label>
    Yes<input type="radio" name="Q4" value="Yes">
    No<input type="radio" name="Q4" value="No">
    N/A<input type="radio" name="Q4" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q5">Question 5</label>
    Yes<input type="radio" name="Q5" value="Yes">
    No<input type="radio" name="Q5" value="No">
    N/A<input type="radio" name="Q5" value="N/A">
    <br/>
    <label for="Q6">Question 6</label>
    Yes<input type="radio" name="Q6" value="Yes">
    No<input type="radio" name="Q6" value="No">
    N/A<input type="radio" name="Q6" value="N/A">
    <br/>
    <label for="Q7">Question 7</label>
    Yes<input type="radio" name="Q7" value="Yes">
    No<input type="radio" name="Q7" value="No">
    N/A<input type="radio" name="Q7" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q8">Question 8</label>
    Yes<input type="radio" name="Q8" value="Yes">
    No<input type="radio" name="Q8" value="No">
    N/A<input type="radio" name="Q8" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q9">Question 9</label>
    Yes<input type="radio" name="Q9" value="Yes">
    No<input type="radio" name="Q9" value="No">
    N/A<input type="radio" name="Q9" value="N/A">
    <br/>
    <label for="Q10">Question 10</label>
    Yes<input type="radio" name="Q10" value="Yes">
    No<input type="radio" name="Q10" value="No">
    N/A<input type="radio" name="Q10" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q11">Question 11</label>
    Yes<input type="radio" name="Q11" value="Yes">
    No<input type="radio" name="Q11" value="No">
    N/A<input type="radio" name="Q11" value="N/A">
    <br/>
    <label for="Q12">Question 12</label>
    Yes<input type="radio" name="Q12" value="Yes">
    No<input type="radio" name="Q12" value="No">
    N/A<input type="radio" name="Q12" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject/legend>
    <label for="Q13"> Question 13</label>
    Yes<input type="radio" name="Q13" value="Yes">
    No<input type="radio" name="Q13" value="No">
    N/A<input type="radio" name="Q13" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Subject</legend>
    <label for="Q14">Question 14</label>
    Yes<input type="radio" name="Q14" value="Yes">
    No<input type="radio" name="Q14" value="No">
    N/A<input type="radio" name="Q14" value="N/A">
    <br/>
    <label for="Q15">Question 15</label>
    Yes<input type="radio" name="Q15" value="Yes">
    No<input type="radio" name="Q15" value="No">
    N/A<input type="radio" name="Q15" value="N/A">
    <br/>
    <label for="Q16">Question 16</label>
    Yes<input type="radio" name="Q16" value="Yes">
    No<input type="radio" name="Q16" value="No">
    N/A<input type="radio" name="Q16" value="N/A">
    <br/>
    <label for="Q17">Question 17</label>
    Yes<input type="radio" name="Q17" value="Yes">
    No<input type="radio" name="Q17" value="No">
    N/A<input type="radio" name="Q17" value="N/A">
    <br/>
    <label for="Q18">Question 18</label>
    Yes<input type="radio" name="Q18" value="Yes">
    No<input type="radio" name="Q18" value="No">
    N/A<input type="radio" name="Q18" value="N/A">
    <br/>
    <label for="Q19">Question 19</label>
    Yes<input type="radio" name="Q19" value="Yes">
    No<input type="radio" name="Q19" value="No">
    N/A<input type="radio" name="Q19" value="N/A">
    <br/>
    <label for="Q20">Question 20</label>
    Yes<input type="radio" name="Q20" value="Yes">
    No<input type="radio" name="Q20" value="No">
    N/A<input type="radio" name="Q20" value="N/A">
</fieldset>
</div>

<br/>

<div>
<fieldset>
<legend>Notes</legend>
    <textarea name="Notes" rows="10" cols="50"></textarea>
    <br/>
    <input type="submit" name="saveMonitoring" value="Save Monitoring" />
</fieldset>
</div>

<br/>
</form>
</body>
</html>

I have a feeling that the values from the radio buttons are not being assigned to the variables and therefore there is nothing to submit, however if anyone else can help it would be greatly appreciated.

What if any of your value is null?? Then you need to pass DBNull.Value — huMpty duMpty
From the stack trace "Parameter name: 0" is not supplied.So better give break point and check whether var Agent has some values. — Sasidharan
@dreamviewer Please check Agent = Request.Form["Agent"]; value . — Suraj Singh

GarethD GarethD · Accepted Answer · 2013-09-03T12:44:00

The problem is that when you pass a parameter with value that is a null reference to the SqlParameterCollection.Add Method it does not assume you mean DBNull.Value it just doesn't add the parameter. Since there is no default for each of your radio buttons or your drop down list I think that when nothing is selected you will get a null value, so if nothing is selected for the Agent dropdownlist, Request.Form["Agent"] will be null. Therefore at this line:

Agent = Request.Form["Agent"];

you are setting the variable Agent to null. Something like this would resolve the issue:

object Agent = DBNull.Value;
if (Request.Form["Agent"] != null) 
{
    Agent = Request.Form["Agent"];
}

However, this can get quite cumbersome and verbose for a problem that is quite frequent, as such I normally provide an extension for the SqlParameterCollection.AddWithValue Method. Below is an example of such an extension from code project

public static SqlParameter AddWithValue(this SqlParameterCollection target, string parameterName, object value, object nullValue) 
{
    if (value == null) 
    {
        return target.AddWithValue(parameterName, nullValue ?? DBNull.Value);
    }
    return target.AddWithValue(parameterName, value);
}

EDIT

I see the problem now. Your Agent Dropdown list is contained within the GET form, and the rest of your radio boxes are within the post form:

<form method="get">
    <div>
        <fieldset>
           Select Agent: @Html.DropDownList("Agent", agentdropdownlistdata)
        </fieldset>
    </div>
</form>
<br/>
<form method="post">
    ... All other controls
</from>

So at this line:

if(IsPost){
    Agent = Request.Form["Agent"];

Request.Form["Agent"] is null because it not contained within the POST form. Move your dropdown list into the same form as the rest of your controls.

ASP.NET C# SQL Submit Error

context) +249

2 Answers