access query to allow apostrophes

[VGR]

not SQL

[Johnny26652]

<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
Set cmd = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open "database connection string"
Set cmd.ActiveConnection = conn


Set param = cmd.CreateParameter("BookName",adVarChar,adParamInput,CLng(40),"bookname")
cmd.Parameters.Append param
cmd.CommandText="insert into tblBook(CatID,BookName,BookTitle) values('"&CatID&"','@Bookname','"&BookTitle&"')"

cmd.Execute, ,adExecuteNoRecords

book name is of type Memo in my data field..

Everything stored perfectly in my database other than the parameter bookname is stored as @Bookname and not the book name i am adding.. seems like i am doing something wrong.. help please..

thanks guys..

[nico5038]

Try:

values('"&CatID&"','" & @Bookname & "','"&BookTitle&"

When the @Bookname can't hold quotes, else:

values('"&CatID&"'," & chr(34) & @Bookname & chr(34) & "','" & BookTitle & "

Nic;o)

[rdivilbiss]

No, there should be no quoting of the parameter.

[Johnny26652]

it gives me invalid character when i follow what nico has suggested

[nico5038]

Sorry Johnny, forgot the trailing ")":

values('"&CatID&"'," & chr(34) & @Bookname & chr(34) & "','" & BookTitle & ")

Nic;o)

[Johnny26652]

Nico thanks for your help. It is pointing syntax error at @Bookname. i took care of the trailing.. i am thinking may be i should say param.value = request.form("bookname"). i never used command object so i am not sure..

[rdivilbiss]

nico and johnny do not put single or double quotes around a parameter.

e.g.

cmd.CommandText="insert into tblBook(CatID,BookName,BookTitle) values('"&CatID&"',@Bookname,'"&BookTitle&"')"

The value of @Bookname is set with the
 
Set param = cmd.CreateParameter("BookName",adVarChar,adParamInput,CLng(40),"bookname")
cmd.Parameters.Append param

In your Set.param, the first value is the parameter name; Bookname, not BookName.

The last value of the Set.Param is the value to be assigned to @Bookname, which if posted would be Request.Form("bookname")

Set param = cmd.CreateParameter("@Bookname",adVarChar,adParamInput,CLng(40),Request.Form("bookname"))
cmd.Parameters.Append param

Here is an example from a working query of mine...

"UPDATE regtemp SET posted = 1 WHERE (sessionid=@sessionid) AND (remote_addr=@remote_addr);"

Note both @sessionid and @remote_addr are text fields and there are no quotes at all in the command text.

Now it is odd that you are creating command text with only one parameter rather than three.

You should first have:

cmd.CommandText="insert into tblBook(CatID,BookName,BookTitle) values(@catid,@bookname,@booktitle)"

followed by three

Set param = db_cmd.CreateParameter("@catid",ptype,adParamInput,CLng(Len(CatID)),CatID)
cmd.Parameters.Append param
Set param = db_cmd.CreateParameter("@bookname",ptype,adParamInput,CLng(Len(BookName)),BookName)
cmd.Parameters.Append param
Set param = db_cmd.CreateParameter("@booktitle",ptype,adParamInput,CLng(Len(BookTitle)),BookTitle)
cmd.Parameters.Append param

And in the above you must replace ptype, with the correct type for the corresponding Access column.

For a text column you would have adVarChar, for an integer field adInteger and for an Access 2000/2003 memo field adLongVarWChar.

You do not need to worry if BookName contains quotes or not.  It is not relevant using parameters as the parameter is not executed as SQL.

[rdivilbiss]

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

[Johnny26652]

Rod,
thanks for your time. i followed exactly what you said.. now i get this error..


Set param = cmd.CreateParameter("@v_bookname",adLongVarWChar,adParamInput,titlename)
cmd.Parameters.Append param


Provider error '80020005'

Type mismatch.

i think it has to do with memo field.. i am using access 2003


my datafields

id - autonumber
catid - number
bookno - text
bookname - memo
booktitle - memo
addedby - text

[rdivilbiss]

Rod,
thanks for your time. i followed exactly what you said.. now i get this error..


Set param = cmd.CreateParameter("@v_bookname",adLongVarWChar,adParamInput,titlename)
cmd.Parameters.Append param


Provider error '80020005'

Type mismatch.

i think it has to do with memo field.. i am using access 2003

Probably not.  You are missing the size parameter, so the type mismatch is probably where ADO is getting the value of the field when it is expecting the size.

Should be Set param = cmd.CreateParameter("@v_bookname",adLongVarWChar,adParamInput,CLng(Len(titlename)),titlename)

The size has to be a long integer, ergo the CLng, and as long as the length of your memo field is shorter than the maximum length for a memo field in Access, it's safe to just use the exact size of the field value itself.

[Johnny26652]

i just created a table called tblCompany

companyID - Autonumber
companyType - Memo



<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Dim conn, cmd, rs, command, param, numAffected

Dim v_companytype

v_companytype = "testcompany"

Response.write(v_companytype)

' Note the use of @id, that's the parameter
command = "INSERT INTO tblCompany(companyType) VALUES (@v_companytype)"

Set cmd = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("testcompany.mdb")
Set cmd.ActiveConnection = conn

' This is where we tell ADODB what our parameter is, it's maximum allowed length
' and it's value.
'
' Doing this means our value is only used as variable and can not
' be executed as in-line SQL
Set param = cmd.CreateParameter("@v_companytype",adLongVarWChar,adParamInput,CLng(Len(v_companytype)),v_companytype)

' We must add our parameter to the command objects Parameters collection
cmd.Parameters.Append param

' We also must assign our SQL command to the ADODB command object.
cmd.CommandText = command

' Now we open a record set using the command object we prepared.
cmd.Execute numAffected,, adExecuteNoRecords

Set param = nothing
Set conn = nothing
Set cmd = nothing
%>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>

</body>

</html>


Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

company.asp, line 34


this is line 34

' Now we open a record set using the command object we prepared.
cmd.Execute numAffected,, adExecuteNoRecords

hopefully i did all the steps right..

[rdivilbiss]

INSERT INTO tblCompany(companyType) VALUES (@v_companytype) needs a space after the table name.

[Johnny26652]

i had a space.. its still giving me the same error.

[nico5038]

Why not drop the parameter approach and add the value directly by stringing like:

command = "INSERT INTO tblCompany(companyType) VALUES ('" & v_companytype & "')"

or when you want to allow a companytype with a single quote:

command = "INSERT INTO tblCompany(companyType) VALUES (" & chr(34) & v_companytype & chr(34) & ")"

when the type is numeric just use:

command = "INSERT INTO tblCompany(companyType) VALUES (" &  v_companytype  & ")"

Nic;o)