Inserting a single quotation ' or double quotation "

[lsy]

How can i insert a single quotation ' or double quotation " to the Ms Access database?

 

[boblarson]

Where? For what?

 

[lsy]

Where? For what?

i would like to insert an SQL statement from my application which writing in visual c++, but due to my insert data contain ' and " so is prompt error during inserting. So would like to know how can i insert an ' and " in MS Access database using the sql statement.

 

[JANR]

If you are trying to instert something like O'Brian you have to double opp the quotes.

INSERT INTO tbl (name) Value ("""O'Brian""")

JR

 

[lsy]

If you are trying to instert something like O'Brian you have to double opp the quotes.

INSERT INTO tbl (name) Value ("""O'Brian""")

JR

how if i want to enter a string with the combination of ' and "??
example: ab'c"def

 

[JANR]

This will work: aj'hhh'ss

But not: as"kjfkfk Or dff'ghg"jk

So to handle " in strings I don't have a good answer, perhaps somebody else can give you an answer

JR

 

[gemma-the-husky]

ab'c"def

since ' is chr(39) and " is chr(34)

you could use a variable to hold the assembled string
mystring = "ab" & chr(39) & "c" & chr(34) & "def"

and then manage the variable mystring in code

---------
however if you then need to wrap mystring in quotes (eg in sql)

you end up with

insert blah blah ... chr(34) & mystring & chr(34)

and it is possible that the embedded chr(34) within the string will still ause this to fail.
if so I am not sure offhand how to fix this.

 

[jal]

I recommend using parameterized queries - thereby avoiding all these thorny syntax issues.

 

[lsy]

I recommend using parameterized queries - thereby avoiding all these thorny syntax issues.

sorry, i don't understand what is parameterized queries??

 

[gemma-the-husky]

to go back to the beginning - as Bob asked, what unusual circumstance do you have, that you find yourself needing to insert both a ' and " character

 

[jal]

I don't know Visual C++ but I can give you a .Net example. I assume you are using an OleDBCommand object?

cmd.CommandText = "INSERT INTO tbl (Name) Values (@Name)"
cmd.Parameters.Add("@Name", OleDbType.VarChar, 1000, "Name")
cmd.Parameters("@Name").Value = "O'Brian"
cmd.ExecuteNonQuery

 

[lsy]

I don't know Visual C++ but I can give you a .Net example. I assume you are using an OleDBCommand object?

cmd.CommandText = "INSERT INTO tbl (Name) Values (@Name)"
cmd.Parameters.Add("@Name", OleDbType.VarChar, 1000, "Name")
cmd.Parameters("@Name").Value = "O'Brian"
cmd.ExecuteNonQuery

is there any one know how to transform this to VC++?? if i use replace, that would be bit messy as i need to replace ' to '' and " to \"...
or anyone got any good suggestion?
help...

 

[jal]

Doesn't VC++ have OleDB? if so, wouldn't it be something like this:

OleDBconnection Cn = new OledBconnection(strConnection)
cn.OPen
OleDbcommand cmd = new OledDbCommand
cmd.Connection = cn
cmd.CommandText = SQL
cmd.Parameters.Add("@Name", OleDbType.VarChar, 1000, "Name")
cmd.Parameters("@Name").Value = "O'Brian"
cmd.ExecuteNonQuery

 

[lsy]

Doesn't VC++ have OleDB? if so, wouldn't it be something like this:

OleDBconnection Cn = new OledBconnection(strConnection)
cn.OPen
OleDbcommand cmd = new OledDbCommand
cmd.Connection = cn
cmd.CommandText = SQL
cmd.Parameters.Add("@Name", OleDbType.VarChar, 1000, "Name")
cmd.Parameters("@Name").Value = "O'Brian"
cmd.ExecuteNonQuery

i'm using ADO...
how can i apply the parameterized query in a string of my sql statement??
i have string variable for my sql, during inserting, how can i apply parameterized query to those variable that in used?

if valA is abc'de"f
valB is aaa
CString sql = "INSERT INTO TABLE (COLA, COLB) VALUES ('"+ valA +"', '"+ valB +'")";

this is what i'm doing now... i can't even compile it due to valA.
pls help...

 

[jal]

ADO syntax is very similar to the OLEDB syntax. You can do this:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Update Customers SET Name = @Name"
cmd.Parameters.Refresh
cmd.Parameters("@Name").Value = "John Smith"
cmd.Execute

The "refresh" method above makes it unecessary to append parameters. Without it, you would need to append them like this:

cmd.Parameters.Append (cmd.CreateParameter("@Name", adVarChar, adParamInput, 100))

Some databases and/or providers may require you to use question marks when using ADO, in other words this:

cmd.CommandText = "Update Customers SET Name = ?"

instead of this

cmd.CommandText = "Update Customers SET Name = @Name"

I think I've given you enough examples to get started. You should be able to translate this into VC++.