calling a Sub Procedure

[MONFU]

Hi All,

I am trying to do a sub procedure to update the table, so that I can use it in many instances without replicating the code. However I have never used Sub Procedures before and so I am not very familiar with them. Here is my code, which is not working. Can anybody help me out?

<!---------------------MODIFY CLUB INFO ---------------------------------->
<%
if var1 = &quot;ModifyTable&quot; then

Dim Address1,Address2,Address3,Tel,Fax,Email,rsModifyClub,sqlModifyClub,sql1,sql2,sql3,intHowLong,WherePos,ClubID

'********** Replace Single Quotes with Double Single Quotes to avoid sql1 errors
function KillQuotes(theString)
KillQuotes = Replace(theString,&quot;'&quot;,&quot;''&quot 'SYNTAX -- Replace(expression, find, replacewith)
end function

Address1 = Request.Form(&quot;address1&quot
Address2 = Request.Form(&quot;address2&quot
Address3 = Request.Form(&quot;address3&quot
Tel = Request.Form(&quot;tel&quot
Fax = Request.Form(&quot;fax&quot
Email = Request.Form(&quot;email&quot
ClubID = Request.Form(&quot;ClubID&quot

'*** Create a recordset to modify the database
Set rsModifyClub=Server.CreateObject(&quot;ADODB.Recordset&quot
rsModifyClub.ActiveConnection = cnn

'****** The update clause

if Address1 <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubAddress1 = '&quot; & KillQuotes(address1) & &quot;'&quot; & &quot;, &quot;
end if

if Address2 <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubAddress2 = '&quot; & KillQuotes(address2) & &quot;'&quot; & &quot;, &quot;
end if

if Address3 <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubAddress3 = '&quot; & KillQuotes(address3) & &quot;'&quot; & &quot;, &quot;
end if

if Tel <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubTelNo = &quot; & KillQuotes(Tel) & &quot;, &quot;
end if

if Fax <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubFaxNo = &quot; & KillQuotes(Fax) & &quot;, &quot;
end if

if Email <> &quot;''&quot; then
sqlModifyClub = sqlModifyClub & &quot;clubEmail = '&quot; & KillQuotes(Email) & &quot;'&quot; & &quot;, &quot;
end if

sql = sqlModifyClub
table = clubInfo
tableID = clubID
formID = ClubID
call UpdateRecord()
%>

<--------- and then I have the Sub Procedure -------->

<%
sub UpdateRecord()

Dim sql,sql1,sql2,sql3,intHowLong,WherePos,table,tableID,formID

'****** The update clause

sql = &quot;UPDATE &quot; & table & &quot; SET &quot;
sql = sql & sql

'*** Replace the last comma
sql= sql & &quot;WHERE &quot; & tableID = formID & &quot;; &quot;

'*** Procedure to remove the last comma before the where for the sql to work
'--- determine the length of the string
intHowLong = Len(sql)
'--- determine in which position is the WHERE clause
WherePos = InStr(sql, &quot;WHERE&quot
'--- extract the first part of the sql
sql1 = Mid(sql, 1, WherePos-3)
'--- extract the second part of the sql. Number of characters in this case will always be 20 since the
'--- WHERE clause is always the same
sql2 = Right(sql, intHowLong - WherePos+1)
'--- Now concatenate the 2 statements to form the proper sql
sql3 = sql1 & &quot; &quot; & sql2

response.Write(sql3)
'cnn.execute (sql3)

end sub
%>

Can you tell me what I have wrong please?

Thanks for your help

 

[onpnt]

one thing that sticks out is your declarations
you are declaring variables twice.
hence

Dim sql,sql1,sql2,sql3,intHowLong,WherePos,table,tableID

Dim Address1,Address2,Address3,Tel,Fax,Email,rsModifyClub,sqlModifyClub,sql1,sql2,sql3,intHowLong,WherePos,ClubID,formID

that alone will cause problems

I would use functions instead for this. it looks like all you're doing is buiding the SQL statement but I also see even knowing commented out the execute within the sub. I wouldn't do this either. Try just calling the function on the condition and return the value sql3 and then execute it. don't forget to close the connection as well. Just a suggestion: faq183-874

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[onpnt]

just a example of what I'm trying to say. this is client side to make it clearer for you.
remember, decalre those variables once and try it in the option explicit area also.

<script language=&quot;vbscript&quot;>
Function GetSQL
sqlreplace = &quot;UPDATE &quot; & table & &quot; SET &quot;

sqlreplace = sqlreplace & sqlreplace

sqlreplace= sqlreplace & &quot;WHERE tableID = formID &quot; & &quot;;&quot;

intHowLong = Len(sqlreplace)

WherePos = InStr(sqlreplace, &quot;WHERE&quot

sql1 = Mid(sqlreplace, 1, WherePos-3)

sql2 = Right(sqlreplace, intHowLong - WherePos+1)

sql3 = sql1 & &quot; &quot; & sql2

GetSQL = sql3
End Function

sql3 = GetSQL
alert sql3
</script>

Just a suggestion: faq183-874

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com