Need Help with Multi-Line SQL Statement Syntax

Databases
1

Getting the correct syntax of SQL statements has been a lifelong coding challenge for me … one that I will surely never come anywhere close to mastering. At the moment, I’m using the MBS SQL plug-in and trying to create a multi-line SQL string to execute a prepared statement. Apparently, although it compiles OK, the syntax is not correct because it throws a db error (see below after code block):

[code] dim sql as string = "Update caseModel set CaseName = :cn " + _
"TmpElectrical = :tmplE " + _
"DescriptorLine1 = :dLn1 " + _
“WHERE (CaseName = :caseN AND Revision= :caseR)”

dim v as Variant = db.Prepare(sql)

dim p as SQLPreparedStatementMBS = v

p.BindType(“cn”, SQLPreparedStatementMBS.kTypeString)
p.BindType(“tmplE”, SQLPreparedStatementMBS.kTypeString)
p.BindType(“dLn1”, SQLPreparedStatementMBS.kTypeString)

p.BindType(“caseN”, SQLPreparedStatementMBS.kTypeString)
p.BindType(“caseR”, SQLPreparedStatementMBS.kTypeString)

p.Bind(“cn”, rsGen.Field(“CaseName”).StringValue)
p.Bind(“tmplE”, rsGen.Field(“TmpElectrical”).StringValue)
p.Bind(“dLn1”, rsGen.Field(“DescriptorLine1”).StringValue)

p.Bind(“caseN”, caseName)
p.Bind(“caseR”, caseRevision)

p.SQLExecute[/code]

When I run it, it throws the following db error:
“42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘TmpElectrical’.
42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.”

What am I missing this time??? Any help would be greatly appreciated.

2

For one thing are you missing the commas in your statement?

dim sql as string = "Update caseModel set CaseName = :cn, " + _ "TmpElectrical = :tmplE, " + _ "DescriptorLine1 = :dLn1, " + _ "WHERE (CaseName = :caseN AND Revision= :caseR)"

3

Thanks, Patrick … although the code change you suggest does run without throwing an error, it doesn’t update the data in the db table. I tried by also eliminating the comma you have after the “:dLn1” ( "DescriptorLine1 = :dLn1, " + _ ) thinking that the “WHERE” clause follows and therefore that comma isn’t needed. That also ran without throwing an error, but also didn’t update the database table.

4

That comma "DescriptorLine1 = :dLn1, " + _ shouldn’t be there!! You should have got an invalid syntax error when you tried to execute it.

I presume you have this code wrapped in a Try Catch and checking for an exception to get the error?

Strictly speaking you don’t need the brackets around your where clause.

Could it be that the query is running but there isn’t a row to update meeting your where clause? Are you sure caseN and caseR have the values your expect?

5

You could format your SQL strings like that:

Dim sql As String = _ "UPDATE caseModel " + _ " SET CaseName = :cn, " + _ " TmpElectrical = :tmplE, " + _ " DescriptorLine1 = :dLn1 " + _ " WHERE CaseName = :caseN " + _ " AND Revision = :caseR; "

6

Put it in a constant. The constant editor is a text area. Easy to edit there.

7

Right now, I’m simply using a if db.Error then after the UPDATE statement … and that DOES throw an error when I use the version with the “extra comma”.

There’s only one row in that table right now. I double-checked the variables caseN and caseR and they do contain the correct data to match what is in that one row of the table.

… Thanks, Eli! I’ll reconfigure my code to match.

… That’s a great suggestion Jeremy … Let me go try all of this now … thanks!

8

Just to close the loop … Many thanks to you guys who responded to my question … A combination of all your inputs resulted in success!
Now, let me go find my next problem ^^

9

Cool, that you guys actually use the named parameters there. That was an addition from this year and it is very useful to avoid errors.

10

I would suggest to switch to exceptions instead of error property. With SQLDatabaseMBS it’s simply a property RaiseException and now any error causes an exception, so you will not miss an error if you catch them right.

11

I’m really glad you pointed that out, Christian … that’s an added plus for sure! You had mentioned to me in our correspondence over the weekend that, “or you set con.RaiseExceptions = true” … It didn’t register with me at the time that it was MORE than just an equivalent alternative to the old db.error routine. Another MBS-value-added ^^

12

Glad you got it sorted.

I really need to make use of named parameters, it’s ingrained to use numbers - must break that habit.