Iam得到错误未知错误提供给命令的参数不足 [英] Iam getting error unknown error insufficient parameters supplied to the command
问题描述
if （planDS.Tables.Contains（ CloudBackupSchedule））
{
dt = planDS.Tables [ CloudBackupSchedule];
if （dt！= null && dt.Rows.Count > 0 ）
{
// 获取字段名称
string sqlString = 更新CloudBackupSchedule SET;
var sqlParams = new string [dt.Rows [ 0 ]。ItemArray.Count（）+ 1 ];
int count = 0 ;
foreach （DataColumn dc in dt.Columns）
{
if （dc.ColumnName.ToLower（）== baseplan_id）
{
sqlString + = PlanId = @PlanId， ;
sqlParams [count] = @ PlanId;
count ++;
sqlString + = BackupScheduleId = @BackupScheduleId，;
sqlParams [count] = @ BackupScheduleId;
count ++;
}
else
{
sqlString + = string .Format（ {0} = @ {0}，，dc。的ColumnName）;
sqlParams [count] = string .Format（ @ {0}，dc.ColumnName）;
count ++;
}
}
// 创建查询来自字段
sqlString = sqlString.TrimEnd（ new char [] {< span class =code-string>' ，'，（ char ） 32 }）+ WHERE Planid = @planid和BackupScheduleId = @ BackupScheduleId;
cGlobalSettings.oLogger.WriteLog（ string .Format（ Query = {0}，sqlString））;
sqlCmd.CommandText = sqlString;
foreach （DataRow dr in dt.Rows）
{
for （ int i = 0 ; i < sqlParams.Length; i ++）
{
if （sqlParams [i]！= null ）
{
if （sqlParams [ i] .TrimStart（ new char [] {' @'}）== PlanId ）
{
sqlCmd.Parameters.AddWithValue（sqlParams [i]，planid）;
cGlobalSettings.oLogger.WriteLog（ string .Format（ {2}。参数= {0}，值= {1}，sqlParams [i]，planid，i））;
}
else if （sqlParams [i] .TrimStart（ new char [] {' @'}）== BackupScheduleId）
{
sqlCmd.Parameters.AddWithValue（sqlParams [i]，planid）;
cGlobalSettings.oLogger.WriteLog（ string .Format（ {2}。参数= {0}，值= {1}，sqlParams [i]，planid，i））;
}
else
{
sqlCmd.Parameters.AddWithValue（sqlParams [i]，dr [sqlParams [i] .TrimStart（ new char [] {' @'}）] ?? DBNull.Value）;
cGlobalSettings.oLogger.WriteLog（ string .Format（ {2}。参数= {0}，值= {1}，sqlParams [i]，dr [sqlParams [i] .TrimStart（ new char [] {' @'}）] ?? NoValue，i））;
}
}
}
尝试
{
rowsAffected = 0 ;
rowsAffected = sqlCmd.ExecuteNonQuery（）;
cGlobalSettings.oLogger.WriteLog（ string .Format（ 受影响的行= {0}，rowsAffected））;
}
catch （Exception ex）
{
cGlobalSettings.oLogger.WriteLogException（ cSqliteFunction.cs :: ExecuteNonQuery（），ex）;
}
sqlCmd.Parameters.Clear（）;
}
}
}
日志信息
$ / $
12-08-2016 17：36：02.128 | Query = UPDATE CloudBackupSchedule SET BackupScheduleType = @ BackupScheduleType，ScheduleSpecificDate = @ ScheduleSpecificDate，ScheduleSpecificTime = @ ScheduleSpecificTime，ScheduleRecurringType = @ ScheduleRecurringType，DailyOccursAtTime = @DailyOccursAtTime ，DailyOccursEvery = @ DailyOccursEvery，DailyOccursEveryType = @ DailyOccursEveryType，DailyOccursEveryFrom = @ DailyOccursEveryFrom，DailyOccursEveryTo = @ DailyOccursEveryTo，WeeklyRecurringDays = @ WeeklyRecurringDays，MonthlyOccuranceType = @ MonthlyOccuranceType，MonthlyDaysOfMonth = @ MonthlyDaysOfMonth，MonthlyOccuranceDays = @ MonthlyOccuranceDays，MonthlyOccuranceRepeatMonth = @ MonthlyOccuranceRepeatMonth，MonthlyOccuranceStartFrom = @MonthlyOccuranceStartFrom ，YearlyOccuranceRepeatYear = @ YearlyOccuranceRepeatYear，YearlyOccuranceStartFrom = @YearlyOccurance StartFrom，IsScheduleStopPlan = @ IsScheduleStopPlan，ScheduleStopPlanHourMinute = @ ScheduleStopPlanHourMinute，ScheduleStopPlanType = @ ScheduleStopPlanType，IsScheduleMissedPlan = @ IsScheduleMissedPlan，CreatedDate = @ CreatedDate，UpdatedDate = @ UpdatedDate，PlanId = @PlanId，BackupScheduleId = @BackupScheduleId WHERE Planid = @planid而BackupScheduleId = @ BackupScheduleId
12-08-2016 17：36：02.130 | 0。参数= @ BackupScheduleType，值= 0
12-08-2016 17：36：02.132 | 1。参数= @ ScheduleSpecificDate，值=
12-08-2016 17：36：02.133 | 2。参数= @ ScheduleSpecificTime，值=
12-08-2016 17：36：02.135 | 3。参数= @ ScheduleRecurringType，值= 0
12-08-2016 17：36：02.136 | 4。参数= @ DailyOccursAtTime，Value =
12-08-2016 17：36：02.138 | 5。参数= @ DailyOccursEvery，Value = 0
12-08-2016 17：36：02.139 | 6。参数= @ DailyOccursEveryType，Value =
12-08-2016 17：36：02.141 | 7。参数= @ DailyOccursEveryFrom，值=
12-08-2016 17：36：02.142 | 8。参数= @ DailyOccursEveryTo，Value =
12-08-2016 17：36：02.144 | 9。参数= @ WeeklyRecurringDays，值=
12-08-2016 17：36：02.145 | 10。参数= @ MonthlyOccuranceType，值= 0
12-08-2016 17：36：02.146 | 11。参数= @ MonthlyDaysOfMonth，值= 0
12-08-2016 17：36：02.148 | 12。参数= @ MonthlyOccuranceDays，Value =
12-08-2016 17：36：02.149 | 13。参数= @ MonthlyOccuranceRepeatMonth，值= 0
12-08-2016 17：36：02.151 | 14。参数= @ MonthlyOccuranceStartFrom，Value =
12-08-2016 17：36：02.152 | 15。参数= @ YearlyOccuranceRepeatYear，Value = 0
12-08-2016 17：36：02.153 | 16。参数= @ YearlyOccuranceStartFrom，Value =
12-08-2016 17：36：02.154 | 17。参数= @ IsScheduleStopPlan，值= 0
12-08-2016 17：36：02.156 | 18。参数= @ ScheduleStopPlanHourMinute，值= 0
12-08-2016 17：36：02.157 | 19。参数= @ ScheduleStopPlanType，值=
12-08-2016 17：36：02.158 | 20。参数= @ IsScheduleMissedPlan，值= 0
12-08-2016 17：36：02.160 | 21。参数= @ CreatedDate，Value = 05-08-2016 17:01:28
12-08-2016 17：36：02.161 | 22。参数= @ UpdatedDate，Value =
12-08-2016 17：36：02.162 | 23。参数= @PlanId，值= 1
12-08-2016 17：36：02.163 | 24。参数= @ BackupScheduleId，值= 1
我尝试过：
iam匹配查询中的所有参数但仍然得到错误
您应该学习尽快使用调试器。而不是猜测你的代码在做什么，现在是时候看到你的代码执行并确保它完成你期望的。
调试器允许你跟踪执行逐行检查变量，你会看到它有一个停止做你期望的点。
调试器 - 维基百科，免费的百科全书 [ ^ ]
掌握Visual Studio 2010中的调试 - A初学者指南 [ ^ ]
调试器在这里向您展示您的代码正在做什么，您的任务是与它应该做什么进行比较。 />
当代码不做ex的时候你接近一个bug。
在调试器上运行此代码，当出现错误时，检查你有哪个参数以及你的查询是什么。
使用AddWithValue时，参数的顺序并不重要。 AddWithValue的第一个参数是参数的名称，因此顺序不再重要。
错误只是意味着您的SQL需要的参数多于您提供的参数。您所要做的就是遍历代码并确保它正在按照您的预期进行操作。您可以很容易地自行修复。
if (planDS.Tables.Contains("CloudBackupSchedule"))
{
dt = planDS.Tables["CloudBackupSchedule"];
if (dt != null && dt.Rows.Count > 0)
{
//Get field names
string sqlString = "UPDATE CloudBackupSchedule SET ";
var sqlParams = new string[dt.Rows[0].ItemArray.Count() + 1];
int count = 0;
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.ToLower() == "baseplan_id")
{
sqlString += "PlanId = @PlanId, ";
sqlParams[count] = "@PlanId";
count++;
sqlString += "BackupScheduleId = @BackupScheduleId,";
sqlParams[count] = "@BackupScheduleId";
count++;
}
else
{
sqlString += string.Format("{0} = @{0},", dc.ColumnName);
sqlParams[count] = string.Format("@{0}", dc.ColumnName);
count++;
}
}
//create query from fields
sqlString = sqlString.TrimEnd(new char[] { ',', (char)32 }) + " WHERE Planid = @planid And BackupScheduleId = @BackupScheduleId";
cGlobalSettings.oLogger.WriteLog(string.Format("Query={0}", sqlString));
sqlCmd.CommandText = sqlString;
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < sqlParams.Length; i++)
{
if (sqlParams[i] != null)
{
if (sqlParams[i].TrimStart(new char[] { '@' }) == "PlanId")
{
sqlCmd.Parameters.AddWithValue(sqlParams[i], planid);
cGlobalSettings.oLogger.WriteLog(string.Format("{2}. Parameter={0},Value={1}", sqlParams[i], planid, i));
}
else if (sqlParams[i].TrimStart(new char[] { '@' }) == "BackupScheduleId")
{
sqlCmd.Parameters.AddWithValue(sqlParams[i], planid);
cGlobalSettings.oLogger.WriteLog(string.Format("{2}. Parameter={0},Value={1}", sqlParams[i], planid, i));
}
else
{
sqlCmd.Parameters.AddWithValue(sqlParams[i], dr[sqlParams[i].TrimStart(new char[] { '@' })] ?? DBNull.Value);
cGlobalSettings.oLogger.WriteLog(string.Format("{2}. Parameter={0},Value={1}", sqlParams[i], dr[sqlParams[i].TrimStart(new char[] { '@' })] ?? "NoValue", i));
}
}
}
try
{
rowsAffected = 0;
rowsAffected = sqlCmd.ExecuteNonQuery();
cGlobalSettings.oLogger.WriteLog(string.Format("Rows affected= {0}", rowsAffected));
}
catch (Exception ex)
{
cGlobalSettings.oLogger.WriteLogException("cSqliteFunction.cs::ExecuteNonQuery() ", ex);
}
sqlCmd.Parameters.Clear();
}
}
}
log information
12-08-2016 17:36:02.128|Query=UPDATE CloudBackupSchedule SET BackupScheduleType = @BackupScheduleType,ScheduleSpecificDate = @ScheduleSpecificDate,ScheduleSpecificTime = @ScheduleSpecificTime,ScheduleRecurringType = @ScheduleRecurringType,DailyOccursAtTime = @DailyOccursAtTime,DailyOccursEvery = @DailyOccursEvery,DailyOccursEveryType = @DailyOccursEveryType,DailyOccursEveryFrom = @DailyOccursEveryFrom,DailyOccursEveryTo = @DailyOccursEveryTo,WeeklyRecurringDays = @WeeklyRecurringDays,MonthlyOccuranceType = @MonthlyOccuranceType,MonthlyDaysOfMonth = @MonthlyDaysOfMonth,MonthlyOccuranceDays = @MonthlyOccuranceDays,MonthlyOccuranceRepeatMonth = @MonthlyOccuranceRepeatMonth,MonthlyOccuranceStartFrom = @MonthlyOccuranceStartFrom,YearlyOccuranceRepeatYear = @YearlyOccuranceRepeatYear,YearlyOccuranceStartFrom = @YearlyOccuranceStartFrom,IsScheduleStopPlan = @IsScheduleStopPlan,ScheduleStopPlanHourMinute = @ScheduleStopPlanHourMinute,ScheduleStopPlanType = @ScheduleStopPlanType,IsScheduleMissedPlan = @IsScheduleMissedPlan,CreatedDate = @CreatedDate,UpdatedDate = @UpdatedDate,PlanId = @PlanId, BackupScheduleId = @BackupScheduleId WHERE Planid = @planid And BackupScheduleId = @BackupScheduleId
12-08-2016 17:36:02.130|0. Parameter=@BackupScheduleType,Value=0
12-08-2016 17:36:02.132|1. Parameter=@ScheduleSpecificDate,Value=
12-08-2016 17:36:02.133|2. Parameter=@ScheduleSpecificTime,Value=
12-08-2016 17:36:02.135|3. Parameter=@ScheduleRecurringType,Value=0
12-08-2016 17:36:02.136|4. Parameter=@DailyOccursAtTime,Value=
12-08-2016 17:36:02.138|5. Parameter=@DailyOccursEvery,Value=0
12-08-2016 17:36:02.139|6. Parameter=@DailyOccursEveryType,Value=
12-08-2016 17:36:02.141|7. Parameter=@DailyOccursEveryFrom,Value=
12-08-2016 17:36:02.142|8. Parameter=@DailyOccursEveryTo,Value=
12-08-2016 17:36:02.144|9. Parameter=@WeeklyRecurringDays,Value=
12-08-2016 17:36:02.145|10. Parameter=@MonthlyOccuranceType,Value=0
12-08-2016 17:36:02.146|11. Parameter=@MonthlyDaysOfMonth,Value=0
12-08-2016 17:36:02.148|12. Parameter=@MonthlyOccuranceDays,Value=
12-08-2016 17:36:02.149|13. Parameter=@MonthlyOccuranceRepeatMonth,Value=0
12-08-2016 17:36:02.151|14. Parameter=@MonthlyOccuranceStartFrom,Value=
12-08-2016 17:36:02.152|15. Parameter=@YearlyOccuranceRepeatYear,Value=0
12-08-2016 17:36:02.153|16. Parameter=@YearlyOccuranceStartFrom,Value=
12-08-2016 17:36:02.154|17. Parameter=@IsScheduleStopPlan,Value=0
12-08-2016 17:36:02.156|18. Parameter=@ScheduleStopPlanHourMinute,Value=0
12-08-2016 17:36:02.157|19. Parameter=@ScheduleStopPlanType,Value=
12-08-2016 17:36:02.158|20. Parameter=@IsScheduleMissedPlan,Value=0
12-08-2016 17:36:02.160|21. Parameter=@CreatedDate,Value=05-08-2016 17:01:28
12-08-2016 17:36:02.161|22. Parameter=@UpdatedDate,Value=
12-08-2016 17:36:02.162|23. Parameter=@PlanId,Value=1
12-08-2016 17:36:02.163|24. Parameter=@BackupScheduleId,Value=1
What I have tried:
iam have matched all the parameters in query but still getting the error
You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.
The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
When the code don't do what is expected, you are close to a bug.
Run this code on debugger, when the error arise, check which parameter you have and what is your query.
The order of parameters is not important when you use AddWithValue. The first parameter to AddWithValue is the name of the parameter so therefore the order is no longer important.
The error simply means that your SQL is expecting more parameters than you gave it. All you have to do is walk through the code and make sure it is doing what you expect it to. It is pretty easy to fix on your own.
这篇关于Iam得到错误未知错误提供给命令的参数不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
