MYSQL Update Command via VBScript

Question

Having problems getting a csv imported into a MYSQL database to update existing data. The importing new data works fine, just getting errors updating.

`CSVSerial = arrServiceList(0)
CSVAssetTag = arrServiceList(1)
CSVLocation = arrServiceList(2)
CSVRoom = arrServiceList(3)
CSVCheckedOutTo = arrServiceList(4)

commandText = "Update Inventory Set Room = CSVRoom, Location = CSVLocation, AssetTag = CSVAssetTag, CheckedOutTo = CSVCheckedOutTo where SerialNumber = CSVSerial"`

I've tried doing $CSVSerial, '$CSVSerial', 'CSVSerial', they don't seem to recognize any of them. If I do a

Msgbox (CSVSerial & "," & CSVRoom & "," & CSVLocation & "," & CSVAssettag & "," & CSVCheckedOutTo)

all the data is correct there. I'm sure it's something simple, just don't have alot of MySQL experience.

Thanks

Ed Gibbs · Accepted Answer · 2013-06-06 17:47:35Z

1

Your query just uses the literal strings CSVRoom, CSVLocation, etc. rather than their values. The $CSVSerial won't work in VB - that's a PHP thing (plus some other languages). In addition, if these are string values they need to be surrounded by single quotes. Try something like this instead:

commandText = "Update Inventory Set Room = '" & CSVRoom & "', " & _
    "Location = '" & CSVLocation & "', " & _
    "AssetTag = '" & CSVAssetTag & "', " & _
    "CheckedOutTo = '" & CSVCheckedOutTo & "' where SerialNumber = '" & CSVSerial & "'"

I've assumed all the columns are strings. If any are numbers you don't need the single quotes around them but then again they shouldn't hurt either.

Addendum: Followup question is how to update the columns only if they're not blank or null.

To test for blank or null, you can use a condition like this:

Room IS NULL OR Room = ''

... or like this:

COALESCE(Room, '') = ''

The second one is shorter so I'll go with that because the command lines are getting pretty long. The basic MySQL command goes like this:

UPDATE Inventory SET
  Room = CASE WHEN COALESCE(Room, '') = '' THEN Room ELSE 'new value' END,
  ...

So if Room is '' or NULL it's set to its existing '' or NULL value. If not, it's set to the new value.

Here's how it looks in VBScript. This is untested because I don't have VBScript available.

commandText = "UPDATE Inventory SET " & _
  "Room = CASE WHEN COALESCE(Room, '') = '' THEN Room ELSE '" & CSVRoom & "' END, " & _
  "Location = CASE WHEN COALESCE(Location, '') = '' THEN Location ELSE '" & CSVLocation & "' END, " & _
  "AssetTag = CASE WHEN COALESCE(AssetTag, '') = '' THEN AssetTag ELSE '" & CSVAssetTag & "' END, " & _
  "CheckedOutTo = CASE WHEN COALESCE(CheckedOutTo, '') = '' THEN CheckedOutTo ELSE '" & CSVCheckedOutTo & "' END " & _
  "WHERE SerialNumber = '" & CSVSerial & "'"

edited Jun 6, 2013 at 17:47

answered Jun 5, 2013 at 19:49

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phillip Allen Over a year ago

Thanks, that worked perfect. Is there a way inside of the commandtext to not update if the field is blank?

Ed Gibbs Over a year ago

Do you mean for each column? For example, update Room unless it's blank, update Location unless it's blank, and so forth? And by "blank" do you mean a zero-length column value, or do you mean NULL? Or either?

Phillip Allen Over a year ago

correct, if it's blank or null, I don't want to overwrite what's currently there.

Ed Gibbs Over a year ago

Got it Phillip. I've updated my answer to show how to do this; the answer assumes that if the Room column is blank or null it won't be updated, but if it already has a value it will be updated. Hope I got that the right way around :) If not just let me know.

Collectives™ on Stack Overflow

MYSQL Update Command via VBScript

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related