Good day!

I am having problem inserting records to sql server. I have ten records to be inserted in database that is stored in an arrays(). When it reach to a value that has a quote or double quotes in returns error. Please help. What will be the best solution for this.

God bless us All.

Single ticks (Mary's little lamb)...

Dim S As String, strSQL As String
S = "Mary's little lamb"
strSQL = "INSERT INTO Table1 (Field1) VALUES (" & Replace(S, "'", "''") & ")"
'...

Quotes should be the same but it is hard to tell without actual values.

Good Luck

Single ticks (Mary's little lamb)...

Dim S As String, strSQL As String
S = "Mary's little lamb"
strSQL = "INSERT INTO Table1 (Field1) VALUES (" & Replace(S, "'", "''") & ")"
'...

Quotes should be the same but it is hard to tell without actual values.

Good Luck

Thank you. ive heard about parameterized query, more says its much safer to avoid error. Any idea how to use it clearly.?At the same time i also got problem deleting all contents of a table which has a value that has a qoute. Pls correct my query below:

objCommand.ActiveConnection = strConnection

objRS.command="DELETE * FROM tbl_accounts"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText

Set objRS = objCommand.Execute

This works fine. But when its encountered a value that has a qoute it will return error.

Thanks everyone for helping.!

No idea why? Ask over at http://www.dbforums.com/ in their sql forum or here http://www.tek-tips.com/threadminder.cfm?pid=183 (as I know there are more than several members that are on that board and that sites VB board.)


Good Luck

May thanks for helping.Ive got to solve the problem for a long night without sleep.Codes below.:I dont know why it runs with the this code.But thaks God.

objCommand.ActiveConnection = strConnection 
objRS.command="DELETE  FROM tbl_accounts"
objCommand.CommandTimeout = 600
objCommand.CommandType = adCmdText 
Set objRS = objCommand.Execute
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.