calling a function in asp script

[nicole t via AccessMonster.com]

I am writing a script that updates data from one database table to another
database table, and in my sql statement, I compare the first names and the
last names. At first I was getting an error because there were apostrophes
in some of the names, now I have a function that substitutes unfriendly
apostrophes(') with friendly apostrophes(`) in sql statements. I have the
function in my code and am calling it in my sql statement, and I am getting
another error message:

"Microsoft JET Database Engine error '80040e14'
Undefined function 'sqlclean' in expression."

Can anyone help me?! I have been struggling with this for a couple of days.

Thanks in advance,
nicole

 

[Baz]

I am writing a script that updates data from one database table to another
database table, and in my sql statement, I compare the first names and the
last names. At first I was getting an error because there were apostrophes
in some of the names, now I have a function that substitutes unfriendly
apostrophes(') with friendly apostrophes(`) in sql statements. I have the
function in my code and am calling it in my sql statement, and I am getting
another error message:

"Microsoft JET Database Engine error '80040e14'
Undefined function 'sqlclean' in expression."

Can anyone help me?! I have been struggling with this for a couple of days.

Thanks in advance,
nicole

Jet does not support the use of user-defined functions in SQL. The fact
that you can do it in Access with a Jet database is misleading, it cannot be
done with any other technology.

The thing to do is to apply your function as you construct your SQL
statement, rather than make it part of the SQL. For example, I have a
similar function that sorts out quotes in data so as to make them
SQL-friendly, called "SQLQuotes", and I use it as in this example:

strSQL = "UPDATE sometable SET somefield = '" & SQLQuotes(strMyData) & "'"

Incidentally, rather than replacing apostrophes with some other character,
the smart thing would be to double them. The database engine will then
treat the doubled apostrophes as a single apostrophe that is part of the
data. This is my SQLQuotes function:

Public Function SQLQuotes(ByRef String1 As String) As String

SQLQuotes = Replace(String1, "'", "''")

End Function

 

[nicole t via AccessMonster.com]

So I should update my table first and then compare the first names and last
names? What goes in the paranthesis 'SQLQuotes(strmydata)'? I am getting a
syntax error in my update statement now.

Thanks for the help,
Nicole

I am writing a script that updates data from one database table to another
database table, and in my sql statement, I compare the first names and the

[quoted text clipped - 11 lines]

Thanks in advance,
nicole

Jet does not support the use of user-defined functions in SQL. The fact
that you can do it in Access with a Jet database is misleading, it cannot be
done with any other technology.

The thing to do is to apply your function as you construct your SQL
statement, rather than make it part of the SQL. For example, I have a
similar function that sorts out quotes in data so as to make them
SQL-friendly, called "SQLQuotes", and I use it as in this example:

strSQL = "UPDATE sometable SET somefield = '" & SQLQuotes(strMyData) & "'"

Incidentally, rather than replacing apostrophes with some other character,
the smart thing would be to double them. The database engine will then
treat the doubled apostrophes as a single apostrophe that is part of the
data. This is my SQLQuotes function:

Public Function SQLQuotes(ByRef String1 As String) As String

SQLQuotes = Replace(String1, "'", "''")

End Function

 

[Baz]

So I should update my table first and then compare the first names and last
names? What goes in the paranthesis 'SQLQuotes(strmydata)'? I am getting a
syntax error in my update statement now.

Thanks for the help,
Nicole

Sorry, I have no idea what you are trying to achieve overall, I am merely
trying to address the specific question of how you deal with quote marks in
your SQL. As always, it is much easier to help people if they post their
code.

What goes in the parentheses of 'SQLQuotes(strmydata)' is a variable or some
other expression containing the string that might contain apostrophes.

 

[nicole t via AccessMonster.com]

Sorry, I have no idea what you are trying to achieve overall, I am merely
trying to address the specific question of how you deal with quote marks in
your SQL. As always, it is much easier to help people if they post their
code.

What goes in the parentheses of 'SQLQuotes(strmydata)' is a variable or some
other expression containing the string that might contain apostrophes.

Here is a snippet of my code before I tried your function:
--------------------------------------
<%
......
strcon2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("TEST.mdb") & ";"
Set rs2 = Server.CreateObject("ADODB.Recordset")
strsql2 = "select * from

"
strsql2 = strsql2 & " WHERE sqlclean(First) = '" & sqlclean(rs("First")) &
"'"
strsql2 = strsql2 & " and sqlclean(Last) = '" & sqlclean(rs("Last")) & "'"
......
%>
<%
Function sqlclean(s)
name = Replace(s, "'", "`")
sqlclean=s
End Function
....
%>

 

[Baz]

Sorry, I have no idea what you are trying to achieve overall, I am merely
trying to address the specific question of how you deal with quote marks in
your SQL. As always, it is much easier to help people if they post their
code.

What goes in the parentheses of 'SQLQuotes(strmydata)' is a variable or some
other expression containing the string that might contain apostrophes.

Here is a snippet of my code before I tried your function:
--------------------------------------
<%
.....
strcon2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("TEST.mdb") & ";"
Set rs2 = Server.CreateObject("ADODB.Recordset")
strsql2 = "select * from

"
strsql2 = strsql2 & " WHERE sqlclean(First) = '" & sqlclean(rs("First")) &
"'"
strsql2 = strsql2 & " and sqlclean(Last) = '" & sqlclean(rs("Last")) & "'"
.....
%>
<%
Function sqlclean(s)
name = Replace(s, "'", "`")
sqlclean=s
End Function
...
%>
--------------------------------------
This what I had when I was getting the undefined function error.

Thanks,
Nicole

OK, I think this might be what you need (note that I have modified the
sqlclean function, as well as the code that builds the SQL):

<%
......
strcon2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath
("TEST.mdb") & ";"
Set rs2 = Server.CreateObject("ADODB.Recordset")
strsql2 = "select * from

"
strsql2 = strsql2 & " WHERE First = '" & sqlclean(rs("First")) & "'"
strsql2 = strsql2 & " and Last = '" & sqlclean(rs("Last")) & "'"
......
%>
<%
Function sqlclean(s)
sqlclean = Replace(s, "'", "''")
End Function
....
%>