Handling Prohibited Characters in an SQL statement (1 Viewer)

[battenberg]

Hi,

I am using an SQL statement to insert a 'User Notes' string into a record in a table. However, if the strings contains a (; semicolon) or (' appostraphe) it messes up the SQL.

I put this into the onKeypress event to try to capture/combat it..

If KeyAscii = 59 Then MsgBox "Prohibited Character" + vbCrLf + "You cannot use (;) in User Notes", vbCritical
If KeyAscii = 39 Then MsgBox "Prohibited Character" + vbCrLf + "You cannot use (') in User Notes", vbCritical

Although basic code, I dont think that this is the best way to handle it, but it prompts me to sort it out. Does any one have any suggestions...???

or is there a way to handle these characters as variable strings in an SQL statement?

Many Thanks...

 

[RoyVidar]

When you concatenate your string, try doublequoting, i e

dim strsql       as string
strsql = "insert into mytable (myfield) values (""" & Me!txtMyControl & """)"

 

[battenberg]

thankyou, I will try that

 

[battenberg]

Thankyou for your suggestion but this captured the string name in the record.
i.e. '& strHistory &'
here is the actual code:

sql = "INSERT INTO tblHistory ([Date],[time], UserAdd,History, ProjectID) VALUES ( " & dDate & ", " & tTime & "," & blnUserNote & "," & strHistory & "," & strProjectID & " );"

it is the strHistory string that is causing this problem...

 

[RoyVidar]

Did you try

strSql = "INSERT INTO tblHistory " & _
        "([Date],[time], UserAdd, History, ProjectID) " & _
        "VALUES ( " & dDate & ", " & tTime & "," & blnUserNote & _
        ",""" & strHistory & """," & strProjectID & " );"

 

[RoyVidar]

But this looks a bit strange, you seem to stuff the table with information which seems to be dates and strings, but you don't use any delimiters? Values inserted into Date/Time fields, would need octothorpe (#), and text fields single quotes/doublequoting, by reading the names, and the variable datatype prefixes, I'd wonder if this is perhaps more appropriate (note, still untested, only typed)

strSql = "INSERT INTO tblHistory " & _
        "([Date],[time], UserAdd, History, ProjectID) " & _
        "VALUES ( #" & Format$(dDate, "yyyy-mm-dd") & "#, #" & _
        Format$(tTime, "hh:nn:ss") & "#," & blnUserNote & _
        ",""" & strHistory & """,""" & strProjectID & """);"

 

[battenberg]

Thankyou again for your input, I must have put the quotes in the wrong place! I can format the date and time strings earlier in the code at initial capture, using the octothorpe?

 

[Dreamweaver]

I think Maybe these two are reserved words in access

[Date],[time]

 

[RoyVidar]

Dreamweaver, the workaround when dealing with reserved words, is to [bracket] them, so they should cause nothing more than the minor inconvenience of having to type the [brackets] here.

battenberg, here we are concatenating a string, which I am assuming is passed to the Jet engine for execution. For instance through the RunSql method of the DoCmd object, or preferrably through the .execute method of either the DAO database object, or an ADO connection. In such case, the values that are concatenated into the string, becomes litterals, and, needs conform to some standards. Data to be inserted into text fields, needs to be either delimited by single or double quotes. Numbers need no delimiters. Data to be inserted into date/time fields, need two things. One, to be delimited by octothorpe (#), Two - to be in an unambiguous format, where the usual candidates are an "escaped" US version (http://allenbrowne.com/ser-36.html) or ISO 8601 (http://en.wikipedia.org/wiki/ISO_8601), the latter is my preference.

At which point the formatting occurs, is of no importance, the important part is that it is done, else one will either experience 3075 syntax error (in my part of the world) or wondering why so many dates, in particular those where the Day part is less than thirteen, seems to be interpreted wrongly (among amusing phenomena in UK)

If you're having problems, it would be easier to assist if you could post the actual code, and also what your sql string looks like

Debug.Print strSql

Pick up the string from the immediate pane (ctrl+g)

 

[battenberg]

Thankyou for the tips and links, a very interesting article on the allen browne site!

 

[Dreamweaver]

Thanks RoyVidar I'll Remember That

Mick