access query to allow apostrophes

[rdivilbiss]

Why not drop the parameter approach and add the value directly by stringing like:
Nic;o)

Because it isn't as safe, it isn't as fast, and because you do have to worry about quoting values, which is just plain stupid.

Why put O''Brian into your database when you can put in O'Brian.

I'm sorry johnny is having trouble, but there is an explanation for each error he has had, no different than if he had an error using an inline SQL statement.

http://www.rodsdot.com/ee/parameterized_sql_multi_parameter.asp

Has integer, decimal, dates, memo and text data types, and successfully inputs into an Access 2003 database.

Code and table structure are on the page.

[rdivilbiss]

http://www.rodsdot.com/images/multi-param-success.jpg

[rdivilbiss]

http://www.rodsdot.com/ee/parameterized_sql_show_employees.asp, Note record 5 has multiple single and double quotes, neither of which I needed to escape, and the database holds exactly what was entered.

[Johnny26652]

Rod,
i am not sure why i have problems.. I am going to copy your code and see if i get the same result.  i can then compare and see what i am doing wrong? looking at your insert statement i see you have brackets around your fields..

insert into ([],[]) does it matter if i don't have them?

thanks for taking time to explain in detail. i do agree with your last comments and hope to learn how to use command object and parameters..

thanks again

[rdivilbiss]

Only if they are key words in Access, which one of my field names is so rather than try to figure out which one I just put the [] around all of them.  Probably description was the field I needed square brackets for.

[Johnny26652]

i tried rod's explanation and it didn't work. so i wanted to try one last time before i give up.. i changed my driver from jet to oledb and followed the code on this page

http://msdn2.microsoft.com/en-us/library/ms524771.aspx

'Open a connection using Connection object. Notice that the Command object
    'does not have an Open method for establishing a connection.
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Inventory.mdb" 
    Set cnn = Server.CreateObject("ADODB.Connection")
    cnn.Open strConnectionString

    'Instantiate Command object; use ActiveConnection property to attach 
    'connection to Command object.
    Set cmn= Server.CreateObject("ADODB.Command")
    Set cmn.ActiveConnection = cnn

    'Define SQL query.
    cmn.CommandText = "INSERT INTO Inventory (Material, Quantity) VALUES (?, ?)" 

    'Save a prepared (or pre-compiled) version of the query specified in CommandText
    'property before a Command object's first execution. 
    cmn.Prepared = True

    'Define query parameter configuration information.
    cmn.Parameters.Append cmn.CreateParameter("material_type",adVarChar, ,255 )
    cmn.Parameters.Append cmn.CreateParameter("quantity",adVarChar, ,255 )

    'Define and execute first insert.
    cmn("material_type") = "light bulbs" 
    cmn("quantity") = "40" 
    cmn.Execute ,,adCmdText + adExecuteNoRecords

    'Define and execute second insert.
    cmn("material_type") = "fuses" 
    cmn("quantity") = "600" 
    cmn.Execute ,,adCmdText + adExecuteNoRecords
    .
    .
    .
  %>


if some one is interested. that took me a while. but was happy to see the end result. thank you so much rod. appreciate your help.

[rdivilbiss]

You know, I never posted my connection string.  I havn't used an OBDC driver with Access in 9 years, and never imagined that someone did.  A failure on my part to think outside the box.

FWIW:  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='E:\path\to\file\examples.mdb"

ODBC drivers greatly limit capabilities and are much slower than OLEDB drivers with all MS databases.

I will update my example pages with a note about OLEDB so other people won't have the struggle you did.

Given your tenacity, I'm sure you are going to be an excellent developer. 

I'm happy you have it working and didn't give up and go back to inline SQL commands.

Kudos to you,
Rod

[Johnny26652]

i learned/learning from you and other mentors. this site is awesome. i don't know about other guys but i am kind of addicted. i have to login few times a week. other wise i feel empty.thanks again guys.. i am sure i will have more questions.

[rdivilbiss]

I think it is also very valuable that the mentors here a free to present contrary ideas in a non competitive manner.  Thus giving the people seeking to learn the opportunity to get more than one way to solve a problem, or a window into the differing viewpoints.

Better a conversation then a competition.

[GrandSchtroumpf]

Well, actually I think we all pretty much agree on most subjects theoretically.
The main differences are where we place our priorities.

[VGR]

c'est beau ce que tu dis (snif)

8-)

[rdivilbiss]

Những gì anh nói thật là tuyệt làm tôi cảm động vô cùng!

[GrandSchtroumpf]

The only real argument i can think of is the episode on FAKE RAID.
It's FAKE FAKE FAKE :)

There was also the argument on OOP vs. procedural, but that was not a real argument since we all pretty much agreed from the start that both have good and bad points.  It's more a matter of how you use it.

Soon we'll need to designate someone as devil's advocate just to spice up the discussions a little.