谁能看到这段代码有什么问题吗? [英] Can anyone see what is wrong with this code?

问题描述

您好，我正在尝试将列表视图中的数据获取到数据库中:

Hi, I am trying to get the data from a listview into the database:

try
{
SqlConnection conn = new SqlConnection(connstring)
conn.Open()
   
foreach(var item in listview1.Items)
{
using(SqlCommand command = new SqlCommand("INSERT INTO [User_Food] (FoodName,Calories,Quantity,Fat,Protein,Cards,Date)VALUES (@FoodName,@Calories,@Quantity,@Fat,@Protein,@Carbs,@Date)",conn)
{
command.Parameters.Add(new SqlParameter("@FoodName", FoodName));
command.Parameters.Add(new SqlParameter("@Calories", Calories));
command.Parameters.Add(new SqlParameter("@Quantity", Quantity));
command.Parameters.Add(new SqlParameter("@Fat", FatContent));
command.Parameters.Add(new SqlParameter("@Protein", ProteinContent));
command.Parameters.Add(new SqlParameter("@Carbs", CarbsContent));
command.Parameters.Add(new SqlParameter("@Date", datepicker1.SelectedDate));
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch(Exception ex)
{
string message = ex.Message;
MessageBox.Show(message);
}

此代码引发错误:

参数化查询'(@FoodName nvarchar，@ Calories int，@ Quantity int，@Fat float''需要未提供的参数@'FoodName'."

现在对我来说很清楚，它应该从正在运行的listview项目中提取食物名称-我可能弄错了一些简单的东西，但我看不到我的错误！如果有人可以看看，将不胜感激！

丹

This code throws up the error:

"The parameterized query ''(@FoodName nvarchar,@Calories int,@Quantity int, @Fat float'' expects the parameter @''FoodName'', which was not supplied."

Now to me it quite clearly is supplied it should be pulling the foodname from the listview items it is running through - I may have messed up something simple but I can''t see my mistake! If someone coudl take a look would be much appreciated!

Dan

ObservableCollection FoodCollection = new ObservableCollection();
 
public ObservableCollection _FoodCollection {get { return FoodCollection;}} 

FoodCollection.Add(new FoodData { FoodName = foodNameTxt.Text, Calories = Convert.ToInt16(caloriesNum.Value), Quantity = Convert.ToInt16(quantityNum.Value), FatContent = fatNum.Value, ProteinContent = proteinNum.Value, CarbsContent = carbsNum.Value}); 

public class FoodData { 
public string FoodName {get; set} 
public int Calories {get; set;}
 public int Quantity {get;set;} 
public double FatContent {get;set;} 
public double ProteinContent {get;set;} 
public double CarbsContent {get;set;} }

XAML-

XAML -

<gridviewcolumn displaymemberbinding="{Binding FoodName}" />
<gridviewcolumn displaymemberbinding="{Binding Calories}" />
....etc
 top of XAML :

DataContext="{Binding RelativeSource={RelativeSource Self}}"

<listview itemssource="{Binding _FoodCollection}">

</listview>

每个列都绑定到上述方法.

很抱歉对此进行编辑，因为今天我愚蠢地把记忆棒留在家中，所以必须将其键入！

Each Column is binded to the above method.

Sorry about the editing on this I am having to type it out as I stupidly left my memory stick at home today!

推荐答案

在c#中FoodName的值为NULL.在商品中找不到时如何设置FoodName?
阅读有关sqlparameters缺点的文章.
SqlParameters的劣势变成了优势 [

the value of FoodName in c# is NULL. How is FoodName set when not found in item?
read my article about disadvantages of sqlparameters.
Disadvantages of SqlParameters Turned into Advantages[^]

you need somethind like this:

private static void CheckParameters(List<SqlParameter> sqlParameters)
    {
        foreach (SqlParameter parm in sqlParameters)
        {
            // when a parm.Value is null, the parm is not send to
            // the database so the stored procedure returns with the error
            // that it misses a parameter
            // it is very possible that the parameter should be null,
            // so when set it DBNull.Value the parameter
            // is send to the database

            if (parm.Value == null)
                parm.Value = DBNull.Value;
        }
    }

这会将NULL设置为DBNull.Value.您的参数现在将立即在数据库中接收

this sets NULL to DBNull.Value. Your parameter will be received in the Database now

对我来说似乎应该可以使用.
但是，如果FoodName为null可能导致您出现问题，请尝试使用FoodName ?? DBNull.Value.请注意，这只是一个猜测.

一个小评论.您应该将conn.Close();放在finally块中.这样，即使遇到异常，连接也将关闭.

Seems like it should work, to me.
However if FoodName is null that may be causing your problem, try using FoodName ?? DBNull.Value. Note this is just a guess.

A small comment. You should put conn.Close(); in a finally-block. That way your connection is closed even if you get an exception.

SqlConnection conn = null;
try
{
    conn = new SqlConnection(connstring);
    ...
}
finally
{
    if(conn != null)
        conn.Close();
}

尝试使用Parameters.AddWithValue代替:

Try using Parameters.AddWithValue instead:

using(SqlCommand command = new SqlCommand("INSERT INTO [User_Food] (FoodName,Calories,Quantity,Fat,Protein,Cards,Date)VALUES (@FoodName,@Calories,@Quantity,@Fat,@Protein,@Carbs,@Date)",conn)
   {
   command.Parameters.AddWithValue("@FoodName", FoodName);
   command.Parameters.AddWithValue("@Calories", Calories);
   ...

这篇关于谁能看到这段代码有什么问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！