asp.net 必须声明标量变量@Name

Question

我正在尝试将数据提交到 SQL Server 数据库，但收到此错误：

System.Data中发生了类型为"System.Data.SqlClient.SqlException"的异常.dll但未在用户代码中处理

其他信息：必须声明标量变量"@Name"。

但是我的变量与参数处的变量匹配...

请帮忙：

protected void onclickregiPatient(object sender, EventArgs e)
{
    con = new SqlConnection(conString);
    con.Open();
    if (FileUploadControl.HasFile)
    {
        string Name = txtName.Text;
        string surname = txtSurname.Text;
        string gender = genderRadio.Text;
        string dob = txtDob.Text;
        string dop = txtPob.Text;
        string nationality = txtNationality.Text;
        string birthcert = txtBirthCert.Text;
        string nationalid = txtNationalID.Text;
        string occupation = txtOccupation.Text;
        byte[] photo = FileUploadControl.FileBytes;
        string blood = DropDownList1.Text;
        string eye = eyeColor.Text;
        string skin = txtSkinColr.Text;
        string height = txtHeight.Text;
        string status = txtHivstatus.Text;
        string phone = txtPhone.Text;
        string posaddress = txtPosAddress.Text;
        string physicaladdress = txtPhyAddress.Text;
        string fullname = txtFullName.Text;
        string relationship = txtRelationship.Text;
        string phoneno = txtphoneNo.Text;
        string postal = postalAddres.Text;
        string physical = physicalAddress.Text;
        string radio = RadioButtonList1.Text;
        string type = txtType.Text;
        string insertSQL = ("insert into patientProfile values(@Name, @surname, @gender, @dob, @dop, @nationality, @birthcert, @nationalid, @occupation, @photo, @blood, @eye, @skin, @height, @status, @phone, @posaddress, @physicaladdress, @fullname, @relationship, @phoneno, @postal, @physical, @radio, @type)");
        cmd = new SqlCommand(insertSQL, con);
        cmd.ExecuteNonQuery();
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
        cmd.Parameters.Add("@surname", SqlDbType.VarChar).Value = surname;
        cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = gender;
        cmd.Parameters.Add("@dob", SqlDbType.VarChar).Value = dob;
        cmd.Parameters.Add("@dop", SqlDbType.VarChar).Value = dop;
        cmd.Parameters.Add("@nationality", SqlDbType.VarChar).Value = nationality;
        cmd.Parameters.Add("@birthcert", SqlDbType.VarChar).Value = birthcert;
        cmd.Parameters.Add("@nationalid", SqlDbType.VarChar).Value = nationalid;
        cmd.Parameters.Add("@occupation", SqlDbType.VarChar).Value = occupation;
        cmd.Parameters.Add("@photo", SqlDbType.VarBinary).Value = photo;
        cmd.Parameters.Add("@blood", SqlDbType.VarChar).Value = blood;
        cmd.Parameters.Add("@eye", SqlDbType.VarChar).Value = eye;
        cmd.Parameters.Add("@skin", SqlDbType.VarChar).Value = skin;
        cmd.Parameters.Add("@height", SqlDbType.Int).Value = height;
        cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
        cmd.Parameters.Add("@phone", SqlDbType.Int).Value = phone;
        cmd.Parameters.Add("@posaddress", SqlDbType.VarChar).Value = posaddress;
        cmd.Parameters.Add("@physicaladdress", SqlDbType.VarChar).Value = physicaladdress;
        cmd.Parameters.Add("@relationship", SqlDbType.VarChar).Value = relationship;
        cmd.Parameters.Add("@phoneno", SqlDbType.Int).Value = phoneno;
        cmd.Parameters.Add("@postal", SqlDbType.VarChar).Value = postal;
        cmd.Parameters.Add("@physical", SqlDbType.VarChar).Value = physical;
        cmd.Parameters.Add("@radio", SqlDbType.VarChar).Value = radio;
        cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = type;
        con.Open();
        int result = cmd.ExecuteNonQuery();
        con.Close();
        if (result > 0)
        {
            Response.Write("<script type="text/javascript">alert('1 Row added Successfully');</script");
        }
    }
    else
    {
        Response.Write("<script type="text/javascript">alert('Please Select Product Image File');</script");
    }
}

堆栈跟踪：

[SqlException (0x80131904): Must declare the scalar variable "@Name".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2440274
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5765240
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +948
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +286
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +286
   Botswna_Centralized_Health_Card_System.registerPatient.registerPatient.onclickregiPatient(Object sender, EventArgs e) in C:UsersBow-tDocumentsVisual Studio 2015ProjectsBotswna Centralized Health Card SystemBotswna Centralized Health Card SystemregisterPatientregisterPatient.aspx.cs:140
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9767618
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +204
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1738

Answer 1

为什么要在添加参数之前执行查询。

cmd = new SqlCommand(insertSQL, con);
cmd.ExecuteNonQuery();
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;       

删除第一个cmd.ExecuteNonQuery();

Answer 2

主要问题是cmd.ExecuteNonQuery();执行了两次，因此请删除此答案中注释的第一个并仅声明一次

 protected void onclickregiPatient(object sender, EventArgs e)
    {
        con = new SqlConnection(conString);
        con.Open();

        if (FileUploadControl.HasFile)
        {
            string Name = txtName.Text;
            string surname = txtSurname.Text;
            string gender = genderRadio.Text;
            string dob = txtDob.Text;
            string dop = txtPob.Text;
            string nationality = txtNationality.Text;
            string birthcert = txtBirthCert.Text;
            string nationalid = txtNationalID.Text;
            string occupation = txtOccupation.Text;
            byte[] photo = FileUploadControl.FileBytes;
            string blood = DropDownList1.Text;
            string eye = eyeColor.Text;
            string skin = txtSkinColr.Text;
            string height = txtHeight.Text;
            string status = txtHivstatus.Text;
            string phone = txtPhone.Text;
            string posaddress = txtPosAddress.Text;
            string physicaladdress = txtPhyAddress.Text;
            string fullname = txtFullName.Text;
            string relationship = txtRelationship.Text;
            string phoneno = txtphoneNo.Text;
            string postal = postalAddres.Text;
            string physical = physicalAddress.Text;
            string radio = RadioButtonList1.Text;
            string type = txtType.Text;
            string insertSQL = ("insert into patientProfile values(@Name,@surname,@gender,@dob,@dop,@nationality,@birthcert,@nationalid,@occupation,@photo,@blood,@eye,@skin,@height,@status,@phone,@posaddress,@physicaladdress,@fullname,@relationship,@phoneno,@postal,@physical,@radio,@type)");
         cmd = new SqlCommand(insertSQL, con); 
           // cmd.ExecuteNonQuery(); comment this line 

            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
            cmd.Parameters.Add("@surname", SqlDbType.VarChar).Value = surname;
            cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = gender;
            cmd.Parameters.Add("@dob", SqlDbType.VarChar).Value = dob;
            cmd.Parameters.Add("@dop", SqlDbType.VarChar).Value = dop;
            cmd.Parameters.Add("@nationality", SqlDbType.VarChar).Value = nationality;
            cmd.Parameters.Add("@birthcert", SqlDbType.VarChar).Value = birthcert;
            cmd.Parameters.Add("@nationalid", SqlDbType.VarChar).Value = nationalid;
            cmd.Parameters.Add("@occupation", SqlDbType.VarChar).Value = occupation;
            cmd.Parameters.Add("@photo", SqlDbType.VarBinary).Value = photo;
            cmd.Parameters.Add("@blood", SqlDbType.VarChar).Value = blood;
            cmd.Parameters.Add("@eye", SqlDbType.VarChar).Value = eye;
            cmd.Parameters.Add("@skin", SqlDbType.VarChar).Value = skin;
            cmd.Parameters.Add("@height", SqlDbType.Int).Value = height;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@phone", SqlDbType.Int).Value = phone;
            cmd.Parameters.Add("@posaddress", SqlDbType.VarChar).Value = posaddress;
            cmd.Parameters.Add("@physicaladdress", SqlDbType.VarChar).Value = physicaladdress;
            cmd.Parameters.Add("@relationship", SqlDbType.VarChar).Value = relationship;
            cmd.Parameters.Add("@phoneno", SqlDbType.Int).Value = phoneno;
            cmd.Parameters.Add("@postal", SqlDbType.VarChar).Value = postal;
            cmd.Parameters.Add("@physical", SqlDbType.VarChar).Value = physical;
            cmd.Parameters.Add("@radio", SqlDbType.VarChar).Value = radio;
            cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = type;

            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();
            if (result > 0)
            {
                Response.Write("<script type="text/javascript">alert('1 Row added Successfully');</script");
            }
        }
        else
        {
            Response.Write("<script type="text/javascript">alert('Please Select Product Image File');</script");
        }
    }