ASP/VBscript SQL statements are not executing

[lbarrack]

I am writing a timepunch addition to an existing timecard web app using ASP and Internet Explorer tied to a SQL 7 backend. I have isolated enough of the problem down to what I think is trying to submit the statements without using the classic form/response schema. Here is a very dumbed down version of what I'm doing since I think the problem may be on a basic structural level (I hope). The SQL statements work and so does the button logic so I figure it must be structural.

vbscript,session variables and SQL connection and recordset stuff here

'sample of sql insert statement (not the real thing)
set sqlTimeIn = Server.CreateObject ("ADODB.Command")
sqlTimeIn.ActiveConnection = MM_timecardasp_STRING
sqlTimeIn.CommandText = "INSERT INTO dbo.tbltimepunchcard (SSN) VALUES ( ) "
sqlTimeIn.CommandType = 1
sqlTimeIn.CommandTimeout = 0
sqlTimeIn.Prepared = true


testpin = request.form("punchcard.onclick") 'button gets pushed

case TimeIn
sqlTimeIn.Execute()
case LunchOut
some sql update statement
case LunchIn
some sql update statement
case TimeOut
some sql update statement
End select

HTML stuff including body
some table data displayed from a recordset
<%
If punchtype <> "TimeIn" Then 'button value logic
If vLunchOut="" then 'default value is "TimeIn"
punchtype = "LunchOut" 'for no record
response.write "punchout"& punchtype
ElseIf vLunchIn="" and punchtype <> "TimeIn" then
punchtype = "LunchIn"
response.write "punchout"& punchtype
ElseIf vTimeOut="" and punchtype <> "TimeIn" then
punchtype = "TimeOut"
response.write "punchout"& punchtype
End If
End If
%>
<p align="center">
<label> 'this is the button that's suppose to trigger the SQL
<input name="timepunch" type="button" class="style29" id="timepunch" value=<%=punchtype%>/>
</label>
</p>
</body>
</html>


Nothing happens. I'm sure it's some kind of client side vs. server side error on my part. Please help.

[jhardman]

There are hundreds of things that can go wrong with sql in vbscript, the one thing they all have in common is they generate an error. Since you ahve a working and established script page, I assume you have some kind of error handler in place. I bet that when you attempt this (and I don't see a problem, I'm just guessing there is one there somewhere) the error handler is clearing the error without showing it to you. Search for the "on error" line and comment it out then run the page. I bet you will get an error.

Let me know what you find.

Jared

[lbarrack]

Actually I purposely left out an onerror statement so I can hunt down all the errors. I am a little new to ASP programming so I'm not sure of everthing I'm doing however I do get an error when I push the button but in the browser page at the botton. It just says Error when I push the button. In all the other errors I've got I get a compilation error page and can see what's going on.

[jhardman]

hmm. I've never used adodb.command. Have you considered swtching to ADODB.recordset?

set sqlTimeIn = Server.CreateObject ("ADODB.Recordset")
query = "SELECT * FROM dbo.tbltimepunchcard WHERE ssn = '" & vSSN & "'"
sqlTimeIn.open objConnection query, adOpenDynamic, adLockOptimistic
sqlTimeIn.addNew
   'add each value on its own line
   sqlTimeIn("ssn") = vSSN
   'etc
sqlTimeIn.update

That's how I always try to do that kind of thing.

Jared