Building SQL for Fields and Memo-type fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm building a SQL query that will perform an INSERT into an Access MDB.
Most are Text fields but one is a Memo-type field.

My question is, is there a function I can call which escapes characters that
won't work inside the SQL string? Examples include the single quote "'" but
also, I don't know what to do with the much longer string that might include
NEWLINES, etc. that will be written to the Memo field in the database.

Is there a standard function that will properly escape all of this stuff so
that I can build a properly formed SQL string for the INSERT?

Alex
 
Alex,

Are you using a parameterized insert statement or are you trying to build
the insert statement by concatenating strings?

Kerry Moorman
 
Hi Alex,

If you're using parameterized insert statement. you needn't worry about the
chars to escape. Please check the following link for more information.

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbparameter.as
px

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
¤ Hi. I'm building a SQL query that will perform an INSERT into an Access MDB.
¤ Most are Text fields but one is a Memo-type field.
¤
¤ My question is, is there a function I can call which escapes characters that
¤ won't work inside the SQL string? Examples include the single quote "'" but
¤ also, I don't know what to do with the much longer string that might include
¤ NEWLINES, etc. that will be written to the Memo field in the database.
¤
¤ Is there a standard function that will properly escape all of this stuff so
¤ that I can build a properly formed SQL string for the INSERT?

Below is an example that uses a parameterized SQL statement (as Kerry and Kevin mentioned). All you
need to do is change the UPDATE query to an INSERT:

Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("UPDATE Table1 SET MemoField=? WHERE [record id] = 1",
AccessConnection)
Dim StringValue As String = "Yadda...Yadda...Yadda"
Dim QueryParameter As New OleDbParameter("@LongText", OleDbType.LongVarWChar,
Len(StringValue), ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current,
StringValue)
AccessCommand.Parameters.Add(QueryParameter)
AccessConnection.Open()
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Wow this is confusing! In your sample code below, your OleDBParameter
constructor has an "@LongText" parameter. I'm confused about what "@LongText"
refers to. It's not the name of the column in the database because you're
already established that in the OleDbCommand as "MemoField". So what is the
"@LongText"?

Also, I think that the question mark is, in essence, replaced when you do
the "Parameters.Add()" below. So what happens if I want to have a bunch of
question marks? Do I just do a successive "Parameters.Add()" for each
question mark?
 
Hi Alex,

The added parameters will follow the sequence of question marks in the
query statement.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
¤ Wow this is confusing! In your sample code below, your OleDBParameter
¤ constructor has an "@LongText" parameter. I'm confused about what "@LongText"
¤ refers to. It's not the name of the column in the database because you're
¤ already established that in the OleDbCommand as "MemoField". So what is the
¤ "@LongText"?
¤

@LongText is just a name for the parameter. I simply used it for documentation purposes.

¤ Also, I think that the question mark is, in essence, replaced when you do
¤ the "Parameters.Add()" below. So what happens if I want to have a bunch of
¤ question marks? Do I just do a successive "Parameters.Add()" for each
¤ question mark?

Yes.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top