Update one of two joned SQL tables in C#

Question

0.00/5 (No votes)

See more:

I am trying to learn C# and SQL Databases. I thought that trying to develop a very simple program would assist in this. However, I am wavering on that point.

The following Select command works fine:

VB

findCommand = new SqlCommand("Select   Bought.Stockid, Bought.BuyDate, Bought.Number_Bought," +
                         " Bought.Price, Bought.Brokerage, Bought.Number_Bought,  Bought.Interest_Rate, Bought.Acc_Interest, " +
                          "Bought.Total_Cost, Bought.BuyNotes, ASXCode.Stock_Code" +
                         " FROM Bought  INNER JOIN ASXCode ON Bought.Stockid = ASXCode.Stockid ORDER BY ASXCode.Stock_Code", JKPLConnection);

I have then used the CurrencyManager to edit, add and delete rows. Then on closing the application I want to update all records with any additions, deletions or changes that have been made to any of the records. I thought that the following code would do it. However, the application closes without any problem but all changes I have made are lost.

VB

findCommand = new SqlCommand("UPDATE Bought SET Bought.Stockid = Bought.Stockid, Bought.BuyDate = Bought.BuyDate , Bought.Number_Bought = Bought.Number_Bought," +
                         " Bought.Price = Bought.Price, Bought.Brokerage = Bought.Brokerag, Bought.Number_Bought = Bought.Number_Bought,  Bought.Interest_Rate = Bought.Interest_Rate, Bought.Acc_Interest = Bought.Acc_Interest, " +
                          "Bought.Total_Cost = Bought.Total_Cost, Bought.BuyNotes =Bought.BuyNotes " +
                         " FROM Bought  INNER JOIN ASXCode ON Bought.Stockid = ASXCode.Stockid", JKPLConnection);

I am apprehensive about posting this question because I don't know whether I have provided sufficient information or whether the whole question might be considered dumb. However, I have run out of other options.

Posted 21-Sep-15 19:22pm

99Johnny

Add a Solution

Comments

Ehsan Sajjad 22-Sep-15 1:33am

Have you tried executing query in Sql Server Management Studio?

Sinisa Hajnal 22-Sep-15 2:18am

Try your query in the database first. Consider using stored procedures which you will call from the code. Or format your SQL in the code by using stringbuilder (that way you can call Append in lines so your SQL looks aligned and readable.

Sinisa Hajnal 22-Sep-15 2:21am

Upon re-reading: update command updates the table Bought with values from that same table. It essentially does nothing. What do you mean when you say you use CurrencyManager to change rows? If you change rows in your local dataset and want to propagate changes to the database then you have to handle writing the data from the dataset, it will not happen automagically by simply sending query to update the data. You have to send actual data. There are ways to do bulk updates from datasets or you can loop through the rows. Google for some examples for sqlbulkinsert.

99Johnny 22-Sep-15 3:02am

I don't know if this helps explain my predicament better. If I use the select statement from only the "Bought" table I can use the following code in the form closing event that updates the table with all additions, edits and deletions made to it.

SqlCommandBuilder findAdapterCommand = new SqlCommandBuilder(findAdapter);
findAdapter.Update(findTable);

I want to emulate what the SQLCommandBuilder function does but which will not work with a select statement to two tables.

99Johnny 22-Sep-15 3:30am

OriginalGriff thank you. As I understood it from my "studies" the currencymanager object functions between my Data Bound controls and the datatable that I create using DataAdapter. It enables me to edit, add and delete from the created datatable. However, in order for these changes to be written to/saved to the database I need to Update the database with these changes. I am using the SQLcommandbuilder to update individual tables but it won't work where I have selected from two tables. So I am trying to emulate its function in that instance. I am sorry about not describing my issue clearly.

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)

OriginalGriff · Answer 1 · 2015-09-21T20:43:00

Well...look at your update command.
I'll pare it down to the basics so you can see more clearly what it is doing:

SQL

UPDATE Bought SET Stockid = Stockid, BuyDate = BuyDate , ...

Which means "Set the value of the field to it's current value".
The chances are that it's actually throwing an exceptiopn rather than executing at all, since you forgot the final "e" in "Brokerage":

SQL

.Price, Bought.Brokerage = Bought.Brokerag, Bought.

So unless there are two columns with very similar names SQL is not going to be happy.

I can't suggest what you need to do - I don't know where you are storing the info you want to save in "CurrencyManager" (I assume this is the name of your app?) - but you do need to think a little more carefully about what you are trying to do: even fixed, that SQL isn't going to do anything useful.