Solved

Sql statment correct?

Posted on 2009-05-15
12
234 Views
Last Modified: 2012-05-07
I get an error on this:
"UPDATE t_account SET blance = @summa WHERE accountid = @id"

There isnt any value for one or more parameters is the error


string sql2 = "UPDATE t_account SET blance = @summa WHERE accountid = @id";

            

            OleDbConnection cn = new OleDbConnection(myConnectionString);

            using (OleDbCommand cmd = new OleDbCommand(sql2, cn))

            {
 

                cmd.Parameters.Add("@id", OleDbType.Integer).Value = id;

                cmd.Parameters.Add("@summa", OleDbType.Integer).Value = summa;           
 

                cn.Open();

                cmd.ExecuteNonQuery();

                cn.Close();

            }

Open in new window

0
Comment
Question by:Mickeys
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 100 total points
Comment Utility
try this
 string sql2 = "UPDATE t_account SET blance =" + summa + "WHERE accountid =" + id;
 

            OleDbConnection cn = new OleDbConnection(myConnectionString);

            using (OleDbCommand cmd = new OleDbCommand(sql2, cn))

            {

                 cn.Open();

                cmd.ExecuteNonQuery();

                cn.Close();

            }

Open in new window

0
 
LVL 39

Accepted Solution

by:
abel earned 200 total points
Comment Utility
So not go for the SQL string concatenation proposed in the first answer, that will make you liable for SQL injection attacks. You should stay with the Parameter approach at all times (or further abstractions like ORMs offer)

You are using the @-sign in the Parameters.Add. You should remove it:

string sql2 = "UPDATE t_account SET blance = @summa WHERE accountid = @id";

            

OleDbConnection cn = new OleDbConnection(myConnectionString);

using (OleDbCommand cmd = new OleDbCommand(sql2, cn))

{ 

    cmd.Parameters.Add("id", OleDbType.Integer).Value = id;

    cmd.Parameters.Add("summa", OleDbType.Integer).Value = summa;           

    cn.Open();

    cmd.ExecuteNonQuery();

    cn.Close();

}

Open in new window

0
 
LVL 39

Expert Comment

by:abel
Comment Utility
So == Do (first word)
0
 

Author Comment

by:Mickeys
Comment Utility
I havent tried pratima_mcs: suggesstion since that is the incorrect way to go.

Abel: That dindt help. Same error
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
try this

I think you missed
cmd.CommandType = CommandType.Text;
string sql2 = "UPDATE t_account SET blance = @summa WHERE accountid = @id";

            

            OleDbConnection cn = new OleDbConnection(myConnectionString);

            using (OleDbCommand cmd = new OleDbCommand(sql2, cn))

            {

 cmd.CommandType = CommandType.Text; 

                cmd.Parameters.Add("@id", OleDbType.Integer).Value = id;

                cmd.Parameters.Add("@summa", OleDbType.Integer).Value = summa;           

 

                cn.Open();

                cmd.ExecuteNonQuery();

                cn.Close();

            }

Open in new window

0
 

Author Comment

by:Mickeys
Comment Utility
no Same error. :-/

There are more columns in the table but I dont need to have them in the sql statment when I am just gonna update one of the columns, right?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Mickeys
Comment Utility
Here are the database. It is t_account.

database.bmp
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 200 total points
Comment Utility
try this one
http://www.java2s.com/Code/ASP/ADO.net-Database/UsedirectSQLtoinsertC.htm
            string sql2 = "UPDATE t_account SET blance = @summa WHERE accountid = @id";

            

            OleDbConnection cn = new OleDbConnection(myConnectionString);

            cn.Open();

            using (OleDbCommand cmd = new OleDbCommand(sql2, cn))

            {

                cmd.CommandType = CommandType.Text; 

                cmd.Parameters.Add("@id", OleDbType.Integer).Value = id;

                cmd.Parameters.Add("@summa", OleDbType.Integer).Value = summa;

                cmd.ExecuteNonQuery();

                cn.Close();

            }

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
yes ..correct
0
 

Author Comment

by:Mickeys
Comment Utility
sorry same error. I have used exactly that code for SELECT and INSERT and that works.
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
Think that by now it is time to see what is generated, actually. There is a mismatch between a sql parameter and the name you give it. In the code that you show, it isn't there. Is it possible that the actual query is larger?

Can you apply a Trace on the SQL server to see what the actual code is that is sent to the server and paste that here?
0
 

Author Closing Comment

by:Mickeys
Comment Utility
and the error is found. wrong spelling on the column name balance
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now