Using post variable to assign table name

[fluxdemon]

rsUpdate.Open (Request.Form("transaction&quot), conn

The above gives me "The specified table does not exist". Is there a way to force the value of Request.Form("transaction&quot into this open statement?

 

[foxbox]

it maybe helpful to inspect the value of "transaction".
Put these two debugging lines before the Open statement:

response.write "Value of transaction = " & Request.Form("transaction&quot
response.end




br
Gerard

 

[fluxdemon]

I know that transaction is set because I use it in the sql statement on the next line.

 

[foxbox]

mmmmmm, based on what i see "transaction" is set on the previous FORM (you do a request.form). That you use it in the next line is no proof at all.
"Transaction" must be a valid SQL statement. When you respond.write this to screen, you can inspect this (or copy/paste this to eg the SQL Query Manager).



br
Gerard

 

[fluxdemon]

I see the confusion. I am not launching a sql statement from transaction. Transaction contains the table name to open the recordset. The platform I am using doesn't allow me to do use - recordset sql connection.

 

[fluxdemon]

This is the whole thing

<%
dim conn,sql
dim rsChk,rsOhc,rsSav,rsUpdate
dim chkBalance,savBalance,ohcBalance

dim chkCredit,chkDebit

set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.ConnectionString = &quot;data source = \pasppages\money\money.cdb&quot;
conn.open

Set rsChk = Server.CreateObject(&quot;ADODB.Recordset&quot
Set rsUpdate = Server.CreateObject(&quot;ADODB.Recordset&quot

if(Request.Form(&quot;transaction&quot<>&quot;&quot then

''response.write(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')<br>&quot
response.write(&quot;Request.Form('account') = &quot; & Request.Form(&quot;account&quot & &quot;<br>&quot
''response.write(conn.state)

select case (Request.Form(&quot;account&quot)
case &quot;checking&quot;
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
response.write(&quot;Request.Form('account') = &quot; & Request.Form(&quot;account&quot & &quot;<br>&quot
rsUpdate.Open &quot;checking&quot;, conn
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
response.write(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')<br>&quot
''conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')&quot
case &quot;savings&quot;
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
''response.write(&quot;Request.Form('account') = &quot; & Request.Form(&quot;account&quot & &quot;<br>&quot
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
rsUpdate.Open &quot;savings&quot;, conn
response.write(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')<br>&quot
''conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')&quot
case &quot;ohc&quot;
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
''response.write(&quot;Request.Form('account') = &quot; & Request.Form(&quot;account&quot & &quot;<br>&quot
response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
rsUpdate.Open &quot;ohc&quot;, conn
response.write(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')<br>&quot
''conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')&quot
default
response.write(&quot;Not a valid account&quot
response.end
end select

''response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
''rsUpdate.Open Request.Form(&quot;account&quot, conn
''response.write(&quot;conn.state: &quot; & conn.state & &quot; rsUpdate.state: &quot; & rsUpdate.state & &quot;<br>&quot
''conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')&quot

end if

rsChk.Open &quot;checking&quot;, conn
conn.Execute(&quot;select checking.debit, checking.credit from checking&quot
''conn.Execute(&quot;select sum(checking.debit), sum(checking.credit) from checking&quot
''chkBalance = -rsChk(&quot;debit&quot.value + rsChk(&quot;credit&quot.value

do until rsChk.eof

chkDebit=chkDebit+rsChk(&quot;debit&quot.value
chkCredit=chkCredit+rsChk(&quot;credit&quot.value
''response.write(rsChk(&quot;debit&quot.value & &quot; &quot; & rsChk(&quot;credit&quot.value & &quot;<br>&quot
''response.write(chkDebit & &quot; &quot; & chkCredit & &quot;<br>&quot
rsChk.movenext

loop

chkBalance = -chkDebit + chkCredit

''rsOhc.Open &quot;ohc&quot;, conn
''conn.Execute(&quot;select sum(ohc.debit), sum(ohc.credit) from ohc&quot
''chkBalance = -rsOhc(&quot;debit&quot.value + rsOhc(&quot;credit&quot.value

''rsSav.Open &quot;savings&quot;, conn
''conn.Execute(&quot;select sum(savings.debit), sum(savings.credit) from savings&quot
''chkBalance = -rsSav(&quot;debit&quot.value + rsSav(&quot;credit&quot.value

conn.close
set conn=nothing

%>

<html>
<body>
<form method='post' action='default.asp'>
<table>

<tr><td width=&quot;20&quot; align=&quot;right&quot;>On Hand Cash</td><td width=&quot;20&quot; align=&quot;left&quot;><%=formatcurrency(ohcBalance)%></td></tr>
<tr><td width=&quot;20&quot; align=&quot;right&quot;>Checking</td><td width=&quot;20&quot; align=&quot;left&quot;><%=formatcurrency(chkBalance)%></td></tr>
<tr><td width=&quot;20&quot; align=&quot;right&quot;>Savings</td><td width=&quot;20&quot; align=&quot;left&quot;><%=formatcurrency(savBalance)%></td></tr>

</table>

<br>

<select name='account'>
<option value=&quot;ohc&quot;>On Hand Cash</option>
<option value=&quot;checking&quot;>Checking</option>
<option value=&quot;savings&quot;>Savings</option>
</select>
<br>
<select name='transaction'>
<option></option>
<option value='debit'>debit</option>
<option value='credit'>credit</option>
</select>
<br>
<input type='text' name='amount'>
<input type='submit' name='submit'>



</form>
</body>
</html>

 

[foxbox]

1. there is no line with &quot;rsUpdate.Open (Request.Form(&quot;transaction&quot), conn&quot; ?

Transaction (debet or credit) is not a table, but a field (based on your sourcecode)

2. response.write(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')<br>&quot

amount is numeric i presume, so no surrounding single quotes here


3.<select name='transaction'><option></option>

leaves the possibility to post your form without a transaction . . .













br
Gerard

 

[fluxdemon]

rsUpdate.Open (Request.Form(&quot;account&quot), conn seems to work the way I want it to. Thanks for your help.

But, I am getting another error with conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value ('&quot; & Request.Form(&quot;amount&quot & &quot;')&quot which is right after rsUpdate.Open Request.Form(&quot;account&quot, conn. The error message I am getting is: The operation requested by the application is not allowed if the object is closed. If I comment out the conn.execute line and check the state of the connection and recordset are open(1).

 

[foxbox]

first: the current statement is eg
conn.Execute(&quot;insert into OHC (debet) value ('100')&quot
but this is money, so i think:
conn.Execute(&quot;insert into OHC (debet) value (100)&quot
is better.


second:
With INSERT INTO i normally do not use a recordset object, because i normally do not want to receive something back (exception: in cases where i want to know the value of an identity field). I simply execute the SQL statement. This is much simpler:

'- - - - - - - -
if(Request.Form(&quot;transaction&quot<>&quot;&quot then

dim cSQL
cSQL = &quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; &_
Request.Form(&quot;transaction&quot &_
&quot value (&quot; & Request.Form(&quot;amount&quot & &quot&quot;

conn.Execute(cSQL)

end if
'- - - - - - - -



third:
When you want to do someting with the results from your SQL statement, you create a recordset. So change the part after the IF-statement from above:

' - - - - - - - - -
cSQL = &quot;select checking.debit, checking.credit &quot; &_
&quot;from checking&quot;
rsChk.Open cSQL, conn

do until rsChk.eof
chkDebit = chkDebit+rsChk(&quot;debit&quot
chkCredit = chkCredit+rsChk(&quot;credit&quot
rsChk.movenext
loop
rsChk.close
' - - - - - - - - - -

But this summation is something SQL can do for you (much faster), and you know that too (because it's commented now).

' - - - - - - - -
' Construct the SQL
cSQL = &quot;select sum(debit), sum(credit) &quot; &_
&quot;from checking&quot;
' Run the SQL and put it into a record set
rsChk.open cSQL, conn

' credit - debet
chkBalance = rsChk(1) - rsChk(0)
rsChk.close
' - - - - - - - - - -



br
Gerard

 

[fluxdemon]

Thanks for most of that. I would use the sql sum function but it seems not to be supported by pocketASP. I am still having a problem with conn.Execute(&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value (&quot; & Request.Form(&quot;amount&quot & &quot&quot. The apostrophes were only a minor issue with the statement. I can only guess that the connection is lost just before the execute statment or using post data doesn't work with the connection in pocketASP. code below:

<%
dim conn,sql
dim rsChk,rsOhc,rsSav,rsUpdate
dim chkBalance,savBalance,ohcBalance

dim chkCredit,chkDebit

set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.ConnectionString = &quot;data source = \pasppages\money\money.cdb&quot;
conn.open

Set rsUpdate = Server.CreateObject(&quot;ADODB.Recordset&quot

if(Request.Form(&quot;transaction&quot<>&quot;&quot then

sql=&quot;insert into &quot; & Request.Form(&quot;account&quot & &quot; (&quot; & Request.Form(&quot;transaction&quot & &quot value (&quot; & Request.Form(&quot;amount&quot & &quot&quot;
''response.write(sql)
response.write(&quot;Request.Form('account') = &quot; & Request.Form(&quot;account&quot & &quot;<br>&quot
response.write(&quot;conn.state: &quot; & conn.state & &quot;<br>&quot
conn.Execute(sql)

end if

scanAccount &quot;checking&quot;,chkBalance
scanAccount &quot;ohc&quot;,ohcBalance
scanAccount &quot;savings&quot;,savBalance

conn.close
set conn=nothing

function scanAccount(account,balance)

Set rsBalance = Server.CreateObject(&quot;ADODB.Recordset&quot

rsBalance.Open account, conn
''response.write(&quot;select &quot; & account & &quot;.debit, &quot; & account & &quot;.credit from &quot; & account & &quot;<br>&quot; & chr(10))
conn.Execute(&quot;select &quot; & account & &quot;.debit, &quot; & account & &quot;.credit from &quot; & account)

''response.write(account & &quot;<br>&quot; & chr(10))

do until rsBalance.eof

debit=debit+rsBalance(&quot;debit&quot.value
credit=credit+rsBalance(&quot;credit&quot.value
''response.write( debit & &quot; &quot; & credit & &quot;<br>&quot; & chr(10))
rsBalance.movenext

loop

balance = -debit + credit

rsBalance.close

end function
%>

 

[fluxdemon]

or maybe with insert statements in general

 

[foxbox]

it is only now that i see a tiny spelling-error in 'our' SQL statement: VALUES

br
Gerard