Building SQL for Fields and Memo-type fields

[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

 

[Guest]

Alex,

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

Kerry Moorman

 

[Kevin Yu [MSFT]]

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.)

 

[Paul Clement]

¤ 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)

 

[Guest]

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?

 

[Kevin Yu [MSFT]]

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.)

 

[Paul Clement]

¤ 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)