Can anyone see what is wrong with this code?

Question

4.00/5 (1 vote)

See more:

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);
}

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 -

<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!

Posted 24-Aug-11 23:00pm

DanHodgson88

Updated 24-Aug-11 23:43pm

v4

Add a Solution

3 solutions

Solution 3

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.

C#

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

Posted 24-Aug-11 23:09pm

Simon Bang Terkildsen

Solution 1

Try using Parameters.AddWithValue instead:

C#

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);
   ...

Posted 24-Aug-11 23:05pm

OriginalGriff

Comments

Simon Bang Terkildsen 25-Aug-11 5:11am

1 voted, hmm. Seems like a fine suggestion to me, +5 from me.

DanHodgson88 25-Aug-11 5:17am

I just tried this mate and it didn't work but thanks for your help. Wasn't me who 1 voted by the way.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Accepted Answer · 2011-08-24T23:06:00

Solution 2

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:

C#

private static void CheckParameters(List&lt;SqlParameter&gt; 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;
        }
    }

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

Posted 24-Aug-11 23:06pm

Herman<T>.Instance

Updated 24-Aug-11 23:07pm

v2

Comments

DanHodgson88 25-Aug-11 5:17am

I think the problem is that I am not looking through the listview for the right things, I am telling it to look through the list of items and for every item insert certain params into the db. But how do you know which params are actually being taken from the item? My item has 7 params as you can see but I don't really understand how it is taking the value from each column of my listview and placeing it in the db. This is deffinetly from a lack of experience and im a bit stuck! Thanks again for you rhelp

Herman<T>.Instance 25-Aug-11 5:22am

can you show me how your listview definition looks like?

DanHodgson88 25-Aug-11 5:24am

Yup be 2 mins - I am on 2 comps so just copying across now - would you like the xaml also or just the code?

Herman<T>.Instance 25-Aug-11 5:28am

xaml please

DanHodgson88 25-Aug-11 5:33am

ObservableCollection<fooddata> FoodCollection = new ObservableCollection<fooddata>();

public ObservableCollection<fooddata> _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;}
}

The XAML simply binds the columns of the viewlist to each of methods above within the FoodData section so:

<gridviewcolumn displaymemberbinding="{Binding FoodName}">

Hope that's enough info!

Herman<T>.Instance 25-Aug-11 5:41am

try this:
try
{
SqlConnection conn = new SqlConnection(connstring)
conn.Open()

foreach(var item in listview1.Items)
{
var FoodName = item.SubItem[0].ToString();
var Calories = item.SubItem[1];
... and so on

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);
}

DanHodgson88 25-Aug-11 5:44am

Will do mate will try it in a couple of mins just got to grab a coffee, codeing can be a tab stressfull when you get stuck!

DanHodgson88 25-Aug-11 5:46am

It says that item does not contain a definition for subitem pal

Herman<T>.Instance 25-Aug-11 5:56am

it must!see: http://msdn.microsoft.com/en-us/library/system.windows.forms.listviewitem.listviewsubitem(v=VS.90).aspx

Herman<T>.Instance 25-Aug-11 5:57am

it is SubItems[0], sorry for my typo

DanHodgson88 25-Aug-11 6:05am

still getting the same thing i'll check out that site and see what it says, I do appreciate your time mate came to a standstill with it!

DanHodgson88 25-Aug-11 6:14am

Thanks for all your help mate, I will repost a question asking why subitems doesn't seem to want to work and see what I get. After looking at that site like you it looks like it should work I can't see why it won't!

Herman<T>.Instance 25-Aug-11 6:22am

if you hover your mouse over var item. what type of object is shown for var?

DanHodgson88 25-Aug-11 5:33am

oh for god sake dodgy format will put it in code block on question