Having trouble with proper syntax for sql update statement.

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 773 Views - Last Post: 14 January 2013 - 04:05 PM Rate Topic: -----

#1 billy_welsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 04-December 12

Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:09 PM

Hi,
I'm having a problem with updating multiple fields in the same row without updating one field at a time in the sql statement. Basically what would be the proper syntax for combining both statements? Any help would be appreciated.

sql1 = "update [NPI Tracker] set [Sales Price] = '" & TextBox13.Text & "' WHERE [RFQ #] = '" & TextBox4.Text & "'"

sql2 = "update [NPI Tracker] set [Gate 0 Approve Name] = '" & TextBox14.Text & "' WHERE [RFQ #] = '" & TextBox4.Text & "'"

 


Is This A Good Question/Topic? 0
  • +

Replies To: Having trouble with proper syntax for sql update statement.

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8365
  • View blog
  • Posts: 31,081
  • Joined: 12-June 08

Re: Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:13 PM

Use a comma between the "columnA = <value>" parts..
Was This Post Helpful? 1
  • +
  • -

#3 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,355
  • Joined: 02-June 10

Re: Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:14 PM

Oh dear {insert your deity here}
You aren't really doing it like this with TextBox43.Text etc. are you?
This is dummy code and not what is really in your program, isn't it? Please say yes.

Stop. Just stop. Work the tutorials below. Then go back to the project with your newfound knowledge.

Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes
Was This Post Helpful? 1
  • +
  • -

#4 AnalyticLunatic  Icon User is online

  • D.I.C Lover

Reputation: 218
  • View blog
  • Posts: 1,003
  • Joined: 25-June 12

Re: Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:15 PM

Not saying this WILL work, but in SQL Server Management Studio you simply separate queries by adding a semicolon ; when wishing to run more than one query in the same script.

Possibly try something like:

sql = "update [NPI Tracker] set [Sales Price] = '" & TextBox13.Text & "' WHERE [RFQ #] = '" & TextBox4.Text & "'; update [NPI Tracker] set [Gate 0 Approve Name] = '" & TextBox14.Text & "' WHERE [RFQ #] = '" & TextBox4.Text & "'"


EDIT: Should probably just listen to tlhIn`toq and modi123_1. They know far more than I.

This post has been edited by AnalyticLunatic: 10 January 2013 - 01:18 PM

Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,355
  • Joined: 02-June 10

Re: Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:16 PM

Then stop using the default names for GUI elements (see tip below).
Then stop using the textbox.text property as a variable and start using real properties (tutorial link in my signature block)


Some of my common tips (some may apply more than others to your specific style):
  • You have to program as if everything breaks, nothing works, the cyberworld is not perfect, the attached hardware is flakey, the network is slow and unreliable, the harddrive is about to fail, every method will return an error and every user will do their best to break your software. Confirm everything. Range check every value. Make no assumptions or presumptions.

  • Take the extra 3 seconds to rename your controls each time you drag them onto a form. The default names of button1, button2... button54 aren't very helpful. If you rename them right away to something like btnOk, btnCancel, btnSend etc. it helps tremendously when you make the methods for them because they are named after the button by the designer.btnSend_Click(object sender, eventargs e) is a lot easier to maintain than button1_click(object sender, eventargs e)

  • You aren't paying for variable names by the byte. So instead of variables names of a, b, c go ahead and use meaningful names like index, timeOut, row, column and so on. You should avoid 'T' for the timer. Amongst other things 'T' is commonly used throughout C# for Type and this will lead to problems. There are naming guidelines you should follow so your code confirms to industry standards. It makes life much easier on everyone around you, including those of us here to help. If you start using the standards from the beginning you don't have to retrain yourself later.
    You might want to look at some of the naming guidelines. Its a lot easier to start with good habits than to break bad habits later and re-learn.



  • Try to avoid having work actually take place in GUI control event handlers. It is better to have the GUI handler call other methods so those methods can be reused and make the code more readable. This is also how you can send parameters rather than use excessive global variables. Get in this habit even if you are using WinForms because WPF works a lot under the idea of "commands" and this will get you working towards that. Think of each gester, control click, menu option etc. as a command to do something such as a command to SAVE. It doesn't matter where the command comes from, all sources should point at the same target to do the actual saving.
    Spoiler


  • Don't replace lines of code that don't work. Instead comment them out and put your new attempts below that. This will keep you from re-trying the same ideas over and over. Also, when you come back to us saying "I've tried this 100 different ways and still can't get it", we can actually see what you tried. So often a failed attempt is very very close and just needs a little nudge in the right direction. So if we can say "See what you did in attempt 3... blah blah" it helps a lot

    Spoiler

    If you are using Visual Studio you can select a block of lines and hit control+k control+c (Kode Comment) to comment it out. control+k control+u (Kode Uncomment) to uncomment a selected block.

Was This Post Helpful? 0
  • +
  • -

#6 billy_welsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 04-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 10 January 2013 - 01:32 PM

Thanks for quick help. tlhIn`toq Thanks for the tutorials I saw examples that resembled that method but didn't figure them out. I did get it working but will rewrite after some reading.
Was This Post Helpful? 0
  • +
  • -

#7 billy_welsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 04-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 01:08 PM

I'm still having a bit of trouble getting the parameters to populate correctly.


        Dim cnUpdate As New OleDb.OleDbConnection
        Dim cmdUpdate As New OleDb.OleDbCommand
        cnUpdate.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE =NPI_Tracker.mdb"
        cnUpdate.Open()
        ' Dim query As String = "UPDATE [NPI Tracker] SET [Sales Price] = @value1,[Gate 0 Approve Name] = @value2 WHERE [RFQ #] = @RFQ"
        
		Dim query As String = "UPDATE [NPI Tracker] SET [Sales Price] = @value1 WHERE [RFQ #] = @RFQ"
               'Clear any parameters
        
		cmdUpdate.Parameters.Clear()
        Try
            'Set the OleDbCommand Object Properties
            With cmdUpdate
                'Tell it what to execute
                .CommandText = query
                'Tell it its a text query
                .CommandType = CommandType.Text
                'Now add the parameters to our query
                .Parameters.AddWithValue("@value1", "33334")
                .Parameters.AddWithValue("@value2", "bob2")
                .Parameters.AddWithValue("@RFQ", TextBox4.Text)
                'Set the connection of the object
                .Connection = cnUpdate
            End With

            cmdUpdate = New OleDb.OleDbCommand(query, cnUpdate)
            cmdUpdate.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try






This is the error I get.

No value given for one or more parameters.

This post has been edited by billy_welsh: 11 January 2013 - 01:09 PM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,542
  • Joined: 12-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 01:34 PM

I would guess that this (2nd occurrence)

cmdUpdate = New OleDb.OleDbCommand(query, cnUpdate)

is wiping out all you details you have just set-up!

You have also commented-out the statement that has 3 parameters, but then supply these 3 parameters - when only 2 are now required.

BTW If cmdUpdate is new (the 2nd line) then is it necessary to clear the parameters..? I wouldn't have thought so.

This post has been edited by andrewsw: 11 January 2013 - 01:35 PM

Was This Post Helpful? 2
  • +
  • -

#9 billy_welsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 04-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:09 PM

That was it the second entry was wiping it.

I forgot that I uncommented the the second parameter before I copied and pasted the code.

As for clearing the parameters is it better to declare the variable once and clear it every time you use it or just declare a new variable every time?

I was thinking I would move the declaration statement out and clear it.

Thank you all for your input and help.
Was This Post Helpful? 0
  • +
  • -

#10 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,355
  • Joined: 02-June 10

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:18 PM

I'm throwing this out as a question to the more MySql experienced.

Quote

Dim query As String = "UPDATE [NPI Tracker] SET [Sales Price] = @value1 WHERE [RFQ #] = @RFQ"


The brackets are needed because the column names have spaces, right?

In the little bit of playing around I've done, I've made a point of avoiding spaces in column names just like I do in all paths because they tend to cause complication. Therefore my version of this would be:

Dim query As String = "UPDATE npitracker SET salesprice = @value1 WHERE rfq1 = @RFQ"


How to most of you feel about the ommission of spaces and symbol characters?

This post has been edited by tlhIn`toq: 11 January 2013 - 02:19 PM

Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,542
  • Joined: 12-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:25 PM

I NEVER use spaces in field, table or query (etc.) names, or even the odd character #. Under_scores are okay.

When we are running SQL programmatically then it is inconsistent, and potentially error-prone, to allow spaces in field-names but not in variable-names.

I don't think spaces should even be permitted!
Was This Post Helpful? 0
  • +
  • -

#12 billy_welsh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 21
  • Joined: 04-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:25 PM

tlhIn`toq I agree completely.

Unfortunately I'm forced to work with something that was created by people that didn't know about standard naming conventions.

I have informed them about them, but until i get permission to change it I have to work around it.
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,542
  • Joined: 12-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:34 PM

Quote

As for clearing the parameters is it better to declare the variable once and clear it every time you use it or just declare a new variable every time?


It depends. If you are running this code infrequently, perhaps on clicking a button, then it seems to me that it would be messy to try and persist the command-object. At what stage would you dispose of this object?

If, on the other hand, you had an extensive section of code that performed a number of database operations then it seems more sensible to re-use the same command and connection objects.

But someone with more experience might elaborate.

This post has been edited by andrewsw: 11 January 2013 - 02:34 PM

Was This Post Helpful? 0
  • +
  • -

#14 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,355
  • Joined: 02-June 10

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 02:44 PM

View Postandrewsw, on 11 January 2013 - 03:25 PM, said:

I NEVER use spaces in field, table or query (etc.) names, or even the odd character #. Under_scores are okay.

When we are running SQL programmatically then it is inconsistent, and potentially error-prone, to allow spaces in field-names but not in variable-names.

I don't think spaces should even be permitted!


Ok. Building on that... How screwy or non-standard is this approach, which I built trying to apply OOP to the MySql tables.
The column names are stored in string variables. This way if there is a change to the table its easy enough to make a program-wide change in a single place
public string TableEmail = "emails";
public string EmailColumnFirstToAddress = "toaddy";
//etc.



Later the methods that do things like INSERT use the variable-ized column name as both the table's column name and the parameter name being set later in code.
p in this example is an emailObj passed as a parameter to the method.

                #region Build parameterized query from object being sent to database

                string query = string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}) " +
                                             "VALUES (@{1}, @{2}, @{3}, @{4}); ",
                                             TableEmail,
                                             EmailColumnGUID, EmailColumnFirstToAddress, EmailColumnFilePath,
                                             EmailColumnMedia);

                query += "SELECT LAST_INSERT_ID()"; // to return the id of the new row

                MySqlConnection conn = GetConnection(ErrorMessage);
                var cmd = new MySqlCommand(query, conn);
                Repo.CreateUseDatabase(conn, DatabaseName, out ErrorMessage, false); //CYA
                cmd.Parameters.AddWithValue("@" + EmailColumnFirstToAddress, p.ToAddresses[0]);
                cmd.Parameters.AddWithValue("@" + EmailColumnFilePath, p.OriginalFileName);
                cmd.Parameters.AddWithValue("@" + EmailColumnMedia, p.PictureSerialized);
                cmd.Parameters.AddWithValue("@" + EmailColumnGUID, p.TrackingID);

                #endregion



So far (early stages) this seems to be serving me well and seems to be giving me a low maintenance scenario. But if there is a better or more accepted approach I'd love to hear it.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,542
  • Joined: 12-December 12

Re: Having trouble with proper syntax for sql update statement.

Posted 11 January 2013 - 04:37 PM

Seems reasonable to me, with my limited experience, but I'm sure a guru will shout me down.

But shouldn't TableEmail, etc., be properties of an object, rather than (possibly) global variables? In which case, isn't this what Entity Framework and other ORM are for? That is, to create an abstraction layer between your code and your data.

Edited: You're referring to MySql so EF is not relevant; substitute "frameworks" instead :)/>/>.

Quote

Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.


But you would learn a lot by implementing it yourself. I suppose it's similar to PHP (and other) frameworks. I built my website and got to understand the benefits that a framework might offer - but I'm reluctant to take up one of these frameworks (Cake, CI) as I feel I would be un-learning all that I had learnt about PHP :devil2:/>/>/>/> (and losing some control in the process).

[Sorry, drifted off-topic slightly!]

This post has been edited by andrewsw: 11 January 2013 - 04:42 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2