Update Database in Windows Form C#

Question

1.00/5 (5 votes)

See more:

Can this command update the database and work?

SQL

SqlCommand cmdupdate = new SqlCommand();
            cmdupdate.Connection = cn;
            cmdupdate.CommandType = CommandType.Text;
            cmdupdate.CommandText = "UPDATE Orders2 " +
                " SET Monthname= DATENAME (MONTH, OrderDate)," +
                " Weekname = DATENAME (WEEKDAY, OrderDate) +' '+ 'of' +' '+ DATENAME (MONTH, OrderDate)," +
                " Yearname = DATENAME (YEAR, OrderDate)," +
                " MonthYear = DATENAME (MONTH, OrderDate) + ' '+ DATENAME(YEAR, OrderDate)," +
                " QuarterNum = 'Quarter' + ' ' + DATENAME (QUARTER, OrderDate) +' ' + 'of' + ' ' + DATENAME (YEAR, OrderDate)," +
                " Weeknum = 'Week' + ' ' + DATENAME (WK, OrderDate) +' '+ 'of' +' '+ DATENAME (YEAR, OrderDate)," + ";";

Posted 11-Dec-10 22:41pm

wolfsor

Updated 12-Dec-10 23:39pm

Toniyo Jackson

v5

Add a Solution

Comments

Henry Minute 12-Dec-10 8:29am

Correct it so that it does what? Do you want it corrected so that it will launch a nuclear missile, or what? You haven't told us how it is currently incorrect.

Dave Kreskowiak 12-Dec-10 13:48pm

Correct what? How do you expect anyone to be able to fix this to expectations that you haven't mentioned anything about??

Toli Cuturicu 12-Dec-10 14:34pm

"How do you correct this?" - I don't.

Sandeep Mewara 13-Dec-10 4:03am

What's wrong?

wolfsor 13-Dec-10 5:36am

I have just updated the question.

1 solution

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)

Eddy Vluggen · Answer 1 · 2010-12-13T00:12:00

Solution 1

wolfsor wrote:
Can this command update the database and work?

Nope, it'll fail. You're concatenating a comma just before terminating the Sql statement. Take a look at the last piece of the code;

SQL

+ DATENAME (YEAR, OrderDate)," + ";";

That would result in this;

SQL

+ DATENAME (YEAR, OrderDate),;

Remove that last comma, and it would work.

Some additional remarks, as you asked how to 'correct' the statement;

A SqlCommand implements the IDisposable interface. You'd best encapsulate it in a using directive.
The SqlConnection class has a factory-method called "CreateCommand" that you can use to create a new command for that particular connection
You're concatenating too much, at the expense of readability. Example;
```
+' ' + 'of' + ' ' 
```
would be more readable in this form;
```
+' of '
```

Posted 13-Dec-10 0:12am

Eddy Vluggen

Updated 13-Dec-10 0:13am

v2

Comments

saeedazam786 7-Jan-13 4:05am

i want to ask a question that in update your are changing the values but how can we Subtract the values in update.for example a stock column contain 20,we give input as 5,then how can we subtract 5 from stock column and then stock column updated to 15.
Thanks in advance

Eddy Vluggen 7-Jan-13 5:30am

UPDATE [MyTableName]
SET CurrentStock = CurrentStock - 5
WHERE Id = @yourKeyValue

saeedazam786 7-Jan-13 23:34pm

Thanks for your answer.Here is my code

DataTable dt = new DataTable();
con = new SqlConnection();
ds1 = new DataSet();
cb = new SqlCommandBuilder(da);

con.ConnectionString = "Data Source=SAEED\\SQLEXPRESS;Initial Catalog=FYPDB;Integrated Security=True";
con.Open();
dt = ds1.Tables["Bus"];
string sql = "Update Bus SET MaleSeat=MaleSeat-"+textBox2.Text + ", FemaleSeat=FemaleSeat-"+textBox3.Text+"Where BusNo='"+textBox1.Text+"'";
da = new SqlDataAdapter(sql, con);
da.Fill(ds1, "Bus");
da.Update(ds1,"Bus");
con.Close();
MessageBox.Show("Data updated");

i am getting this error:

Update unable to find TableMapping['Bus'] or DataTable 'Bus

Plz tell me where i am going wrong.

Eddy Vluggen 8-Jan-13 9:14am

I'd use an IDbCommand, not an adapter.

Google for SqlCommand.ExecuteNonQuery :)