11 Replies - 396 Views - Last Post: 13 February 2013 - 04:38 PM Rate Topic: -----

#1 sundog1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 11-September 12

UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 08:38 AM

Hi guys,

I've been cracking on with this project for a few days now and had lots of useful pointers to help along the way.

I've now hit a little snag where I think I have written the correct update Statement but It seems to error at the last point.
            if (radioButton1.Checked == true)
            {
                //Creates new versions of the Connection string and Data Set//
                con = new System.Data.OleDb.OleDbConnection();
                ds = new DataSet();

                //The actual connection to the database//
                con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:/Orders1.mdb";

                //Open the Connection to the Database//
                con.Open();

                //The SQL UPDATE String you need to pass into the Data Adapter to collect the information//
                string UpdateCashOrderSQL = "UPDATE cashOrders SET cashQTY = '" + txtBoxQTY.Text + "', cashDescription = '" + txtBoxDesc.Text + "', cashSupplier = '" + txtBoxSupplier.Text + "' , cashDate = '" + dateTimePicker1.Text + "', cashCost = '" + txtBoxCost.Text + "', cashSell = '" + txtBoxSell.Text + "' WHERE CashAccRef_FKID = '" + txtAccRef.Text + "' OR cashID = '" + dataGridView2.SelectedCells[0].Value.ToString() +"' ";

                //Creates a new version of the Data Adapater which passed the SQL and Connection information//
                da = new System.Data.OleDb.OleDbDataAdapter(UpdateCashOrderSQL, con);

                //Data Adapater (da) uses the Insert Command along with the SQL Querey & Connection Details//
                da.UpdateCommand = con.CreateCommand();
                da.UpdateCommand.CommandText = UpdateCashOrderSQL;
                da.UpdateCommand.ExecuteNonQuery();

                //Closes the connection//
                con.Dispose();

                //Shows End User the Account has been added//
                MessageBox.Show("Customer Account updated");
            }


The part I think is causing the trouble is...
 AND cashID = '" + dataGridView2.SelectedCells[0].Value.ToString() +"' ";


even though the form loads and when debugging seems to select the correct information it still errors at the last hurdle stating...

Data type mismatch in criteria expression.

Look forward to some responses and thanks in advance.

Regards
Mark.

Is This A Good Question/Topic? 0
  • +

Replies To: UPDATE SQL statement pulling in GDV value?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9270
  • View blog
  • Posts: 34,773
  • Joined: 12-June 08

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 08:46 AM

Data type mismatch? Use parameters for that query! Not only is it safer when trying to have the backend marry some data to a column datatype, but it's also a best practice.
Was This Post Helpful? 0
  • +
  • -

#3 sundog1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 11-September 12

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 09:20 AM

Hi modi123_1 - many thanks for your comments and I will take them on board for my next project.

However - this just need to be "quick and dirty" as i'm putting it together for a local client machine with a MDB back-end so security is not really an issue at the moment.

any alternative methods you could provide would be great.

I have already been asked if cashID was and integer (which is is, a 'Long Integer') so I amended the code to this...

 + (int)dataGridView2.SelectedCells[0].Value +"' ";


But unfortunately that did not work.

Any other ideas?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9270
  • View blog
  • Posts: 34,773
  • Joined: 12-June 08

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 09:37 AM

Well, yes, the alternative is you look at your UPDATE statement (after it's been piece-mealed together) and see what values you are trying to assign and reconcile them against the datatypes of the columns you are looking to update.

Literally, that is all you can do. You need to know data types of the columns and how you are presenting said data to the column. Shoving a string into a number typically doesn't go over well.

So put breakpoint line 17... see what 'UpdateCashOrderSQL' is.. and start logically breaking down data presented to what the columns expect in the DB. Basic debugging 101 time!
Was This Post Helpful? 0
  • +
  • -

#5 sundog1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 11-September 12

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 09:45 AM

Hi modi123_1

Once again thanks for your advice.

Already been debugging this and the SQL seems to look logically correct when looking in the Locals window.

But as you say.. there seems to be something not quite right happening and I'm sure its down to that last few bits of code.

The reason is If I take this out.. then the UpdateSQL runs fine!
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9270
  • View blog
  • Posts: 34,773
  • Joined: 12-June 08

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 09:48 AM

Quote

But as you say.. there seems to be something not quite right happening and I'm sure its down to that last few bits of code.

Well you got me.. I have zero idea what your column data types are, what data is in that update statement, or what it looks like after it is formed up.

As I said - typically removing this sort of datatype issue is a perk of parametrized queries.
Was This Post Helpful? 0
  • +
  • -

#7 sundog1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 11-September 12

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 10:02 AM

Hi modi123_1,

Well i've tried to upload a Screenshot on here but its failing on upload (for some reason)

The 'UpdateCashOrderSQL' is holding the following UPDATE Statement.

UPDATE cashOrders SET cashQTY = '12', cashDescription = 'Bottle of Wine', cashSupplier = 'Wine Shop' , cashDate = '04 February 2013', cashCost = '159.99', cashSell = '249.99' WHERE CashAccRef_FKID = 'BBAR003' AND cashID = '3'

The Following Columns DT in the MDB are the following:

cashID = AutoNumber (Long Integer)
cashQTY = Number (Long Integer)
cashDescription = Text
casSupplier = Text
cashDate = Date/Time
castCost = Currency
cashSell = Currency
CashAccRef_FKID = Text
PrintedAccRef_FKID2 = Text
accAccRef_FKID = Text
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5846
  • View blog
  • Posts: 12,705
  • Joined: 16-October 07

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 10:34 AM

That is simply NOT how you write an update statement. And, helpfully, doing it properly can eliminate your problem.

First, write a connection method. Don't copy paste the recreation of it all over creation.
private OleDbConnection GetConnection() {
	var con = new OleDbConnection();
	con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:/Orders1.mdb";
	return con;
}




Now, make yourself a method that takes exactly the values, in the correct type, that you want to pass to the database. User paramters, not just a concatenated string.

e.g.
public void UpdateCashOrders(int cashId, int qty, string des, string supplier, DateTime dt, double cost, double sell, int fkid) {
	var cmd = GetConnection().CreateCommand();
	cmd.CommandText = @"UPDATE cashOrders
		SET cashQTY = ?, cashDescription = ?, cashSupplier = ?, cashDate = ?, cashCost = ?, cashSell = ?
		WHERE CashAccRef_FKID = ?
			OR cashID = ?";
	cmd.Parameters.AddWithValue("cashQTY", qty);
	cmd.Parameters.AddWithValue("cashDescription", des);
	cmd.Parameters.AddWithValue("cashSupplier", supplier);
	cmd.Parameters.AddWithValue("cashDate", dt);
	cmd.Parameters.AddWithValue("cashCost", cost);
	cmd.Parameters.AddWithValue("cashSell", sell);
	cmd.Parameters.AddWithValue("CashAccRef_FKID", fkid);
	cmd.Parameters.AddWithValue("cashID", cashId);

	try {
		cmd.Connection.Open();
		cmd.ExecuteNonQuery();
	} finally {
		cmd.Connection.Close();
	}
}



Call that method from your form, converting the values from .Text as required. If you cannot convert them, don't both with the database call, yell at the user for giving you crap data.

Note, your string concat is failing because value data, like int and float, do not have quotes in SQL. Also, dates can be extra tricky. Learn the parameterized form and save yourself your pending SQL injection.

Hope this helps.
Was This Post Helpful? 2
  • +
  • -

#9 sundog1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 11-September 12

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 12:46 PM

Hi Baavgai,

many thanks for that. I will take this on board.
Was This Post Helpful? 0
  • +
  • -

#10 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 01:43 PM

Just to expand on what baavgai and modi123_1 said, don't think that just because your app is only *supposed* to run internally that you shouldn't always use parameterization in your queries. It is much cleaner, easier to read code. Tell me that

SELECT * FROM Employee WHERE EmpID = @EmpID AND DateHired > @DateHired

isn't cleaner and MUCH easier to read than

SELECT * FROM Employee WHERE EmpID = '" + int32.Parse(txtEmpID.Text) + "' AND DateHired = '" + Convert.ToDateTime(date) + "'"

and I'll call you crazy. Yes, you'll have to type a little more code to register the parameters, but it's much easier to understand. And that's just a simple example. The added security of parameters is honestly a bonus as far as I'm concerned, because the clarity and ease of modification of parameterized queries (vs ugly concatenated ones) is enough to get me to always do it. Here's a good tutorial on it: Parameterize Your SQL Queries

EDIT: fixed modi123_1's member tag

This post has been edited by h4nnib4l: 13 February 2013 - 01:44 PM

Was This Post Helpful? 1
  • +
  • -

#11 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3589
  • View blog
  • Posts: 11,159
  • Joined: 05-May 12

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 01:49 PM

Yes. Do parameterized SQL queries.

Although this is a bit more readable, it still isn't secure:
string query = String.Format("SELECT * FROM Employee WHERE EmpID = '{0}' AND DateHired > '{1}'",
                             int.Parse(txtEmpID.Text),
                             date.ToString("yyyy-MM-dd"));


Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4496
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: UPDATE SQL statement pulling in GDV value?

Posted 13 February 2013 - 04:38 PM

Just throwing my voice in. As h4nnib4l says, readability is key. Security and good practices are important too, but if you can't read your own code you're in a world of hurt.

The best part of adding parameters is that you don't have to decide if it's text, or ints, or whatever. I can already see the problem in your rendered SQL statement, and it's due to this problem. All your numbers are quoted, meaning the DB engine is treating them like a string when it's expecting an integer/decimal/long/whatever.

You can manually fix that by removing the single quotes from the numbers, but the best fix is to use parameters so you don't have to do that.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1