If I want to do a SELECT on a table where I set the WHERE clause to a variable from my program, I structure the SQL command string like this:
"SELECT * FROM Notes WHERE CaseID='" + cID + "'"
… where the value of cID is supplied by my program and everything works fine.
How would I structure the syntax for an UPDATE command in a similar manner if I have a string (s) that I want to save to the column “NotesData” in the table “Notes”. As close as I can get from trying different variations is what you see here:
"UPDATE Notes SET NotesData= '" + s + "' WHERE CaseID =" + cID
That throws a db error saying I’m missing a quote at the end of the string (and yeah, you guessed it, when I added a quote at the end of the string, it failed for another reason)
This code is very error prone and potentially very dangerous. It should be changed to:
Dim ps As PreparedStatement = db.Prepare("UPDATE Notes SET NotesData=? WHERE CaseID=?")
If db.Error Then
// Handle Error
End If
ps.SQLExecute(s, cID)
If db.Error Then
// Handle Error
End If
Yeah … unfortunately I know that, but the current way I’m doing it with Prepared Statements has another problem (see my other post floating around about saving long strings. I’ve already provent that I can manually save the data I need to correctly, so I wanted to try using UPDATE to see if the prepared statements were the source of my problem.
Might be, Tim … it’s a string of RTF text data that I’m running into a strange 2000 character limit problem and I’m trying different things to see if I can’t narrow down the source of the problem. I did check it once to make sure there were no literals … hard on the old eyes to make sure 2000 characters have no apostrophes ^^ … I will look again though. Thanks!
Yep … I know, but I’ve got to try some different things here to figure out what in hells bells is causing my UPDATE to max out at 2000 characters. Here’s what I had originally (with prepared statements) that’s giving me the weird “cap” problem on byte count:
dim db As SQLDatabaseMBS = App.mDB
dim ss as string
// save the NOTES section
for iloop As Integer = 0 to Ubound(oContainers)
if oContainerIndex(iloop) = "nc" then
ss = cntNotes(oContainers(iloop)).TANotes.WinRTFDataMBS
Exit For
end if
next
//MsgBox (str(ss.Len) + EndOfLine + ss)
// get the caseID of the current record
Dim rs As RecordSet = db.SQLSelect("SELECT CaseID FROM caseModel WHERE (CaseName='" + caseName + "') AND (Revision='" + caseRevision + "')")
Dim cIDnum As Integer = rs.Field("CaseID").IntegerValue
rs.close
//Dim cID As String = Str(cIDnum)
Dim cID As String = Str(cIDnum)
// save the Notes
Dim sqln As String = _
"UPDATE Notes " + _
" SET NotesData = :nts " + _
" WHERE CaseID = :cID; "
dim vn as Variant = db.Prepare(sqln)
If db.Error Then
// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set PREPARE NOTES Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal
End If
dim pn as SQLPreparedStatementMBS = vn
pn.BindType("nts", SQLPreparedStatementMBS.kTypeString)
pn.BindType("cID", SQLPreparedStatementMBS.kTypeLong)
pn.Bind("nts", c.Text)
pn.Bind("cID", cIDnum)
pn.SQLExecute
If db.Error Then
// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set SAVE NOTES Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal
else
db.Commit // Commit the recordset to the database
End If
Rather than trying to update it via SQL can you update it via a Recordset? It’s a little slower but it does some magic behind the scenes.
[code]dim s as string = “SELECT NotesData, CaseID FROM Notes WHERE CaseID=’” + cID + “’”
dim rs as recordset = db.sqlselect(s)
//check for errors
rs.edit
rs.field(“NotesData”).stringvalue = sRTFData
rs.update
//check for errors[/code]
If that works, I think it would indicate that you’re not escaping all of the data you need to in the RTF string.
If it were me, unless you plan on doing some searches in the DB for strings in the RTF I’d EncodeBase64 the string and use DecodeBase64 when you pull it out of the db. That way you know with 100% certainty that it’s okay.
Ah…I see that you are using MBS SQL plugin. I’d see if Christian could help.
Thanks for the reply, Bob. My first approach (way, way back) was to use RecordSets but after I changed to the MBS SQL plugin, Christian and I found out that a little know Microsoft issue was causing a problem with using RecordSet.Edit
[quote]I normally use OLEDB to avoid the need to install native MS SQL Client.
But it seems like you can’t have a RecordSet open with it while another query runs.[/quote]
He has since modified his plugin and it throws an Unsupported Operation Error if you attempt using RecordSet.Edit, so unfortunately, I can’t “go back there”, so to speak.
I’m not sure about the MBS SQL plugins sorry. Just an unrelated note though… you can use string constants to make your SQL a little more maintainable, i.e.
Dim sqln As String = _
"UPDATE Notes " + _
" SET NotesData = :nts " + _
" WHERE CaseID = :cID; "
could easily be a constant, which is editable in a text area.
BTW … for anyone who may have attempted to follow the code I posted, this line (part of what I posted) is actually as you see below in my program … I had been trying various things (including placing a large text string on the clipboard and attempting to save it) and left that incorrect variable reference in there before I copied the code to post.
But as I posted afterwards, that (c.Text) was not really the variable used … it was a mistake on my part to not remove it from what I posted. The actual code line generating the RTF from the TextArea it gets it from is:
ss = cntNotes(oContainers(iloop)).TANotes.WinRTFDataMBS
Thanks, Tim … tried that once already … but went ahead and tried it again … here’s the text it displays with the error message (I did double-check the string to see that literals were escaped … and they were … don’t know why they appear not to be in the error message text):
[quote]DB Error: 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Unclosed quotation mark after the character string '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil\fcharset0 Segoe UI;}{\f2\fnil\fcharset2 Symbol;}}
{\colortbl ;\red34\green30\blue31;\red0\green0\blue0;}
{\\generator Msftedit 5.41.21.2510;}\viewkind4\uc1\pard{\pntext\f2\'B7\tab}{\\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\'B7}}\fi-180\li180\sl241\slmult0\cf1\lang9\fs18 For meat application: extended front sills are required for maintaining proper case temperature levels (5" baseframe = 24" or higher; 11" baseframe = 30" or higher)\par
{\pntext\f2\'B7\tab}BTUH/ft notes: \par
\pard{\pntext\f2\'B7\tab}{\\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\‘B7}}\fi-180\li540\sl241\slmult0 Listed BTUH/ft indicate unlighted shelves. For LED lighting, add 36 BTUH per 4’ lighted shelf and 27 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case. For T8 lighted shelves (see Appendix D) and 3rd row lighting, add 92 BTUH per 4’ lighted shelf and 69 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case. \par
{\pntext\f2\'B7\tab}Rear refrigerated box increases refrigeration load by 110 BTUH/ft. \par
{\pntext\f2\'B7\tab}Add 132 BTUH/ft when aftermarket merchandising accessories are utilized to determine the total BTUH load.\par
\pard{\pntext\f2\'B7\tab}{\\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\'B7}}\fi-180\li180\sl241\slmult0 Listed evaporator temperature indicates unlighted shelves. For lighted shelves and/or aftermarket merchandising accessories, reduce the listed evaporator temperature by \b 2\'b0F\b0 .\cf2\lang1033\f1\par
}
'.
42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil\fcharset0 Segoe UI;}{\f2\fnil\fcharset2 Sy’.
42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.[/quote]
Unfortunately, Jym, I can’t try that since I’m using the MBS SQL plugin and the “Prepared Statement” class in Jeremy’s response comes from the Xojo plugin.