Trying to update to database

Calibus · July 21st, 2004, 10:56 AM

I am getting the following error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/forms/Results.asp, line 124

124 is : Conn.Execute strSQL

bmains · July 21st, 2004, 11:20 AM

Hey,

Could you post the code around connecting to the database? Could the strSQL be being overwritten by something, where that could be the ''?

Brian

Calibus · July 21st, 2004, 11:39 AM

Here it is :

        <option <%If ObjRS("Status")="" Then%>selected<%End If%>>Choose One</option>
        <option value="Open" <%If ObjRS("Status")="Open" Then%>selected<%End If%>>Open</option>
        <option value="Pending Research" <%If ObjRS("Status")="Pending Research" Then%>selected<%End If%>>Pending Research</option>
        <option value="Close" <%If ObjRS("Status")="Close" Then%>selected<%End If%>> Close</option>
    </select><b>      &nbs p;                 &n bsp;       &nbs p;       &nbsp ; </b>
    </p>
    <p><b>       &n bsp;       &nbs p;                 &n bsp;   Feed Back:     </b>

    <textarea name="Wip_Com" rows="5" cols="20"><%= ObjRS("Wip_Com") %></textarea><b>      &n bsp;       &nbs p;             </b><b></td><td></b></p>
    <input type="hidden" name="Wip_Com" value="<%=ObjRS("Wip_Com")%>">
    <input type="hidden" name="Status" value="<%=ObjRS("Status")%>">
    <p> <input type="submit" name="Update Database" value="Update Database">

</form>
<%

strSQL = "UPDATE Escalation_Forms SET Status='"& request.form("Status") &"', Wip_Com='"& request.form("Wip_Com") &"' WHERE ID='"& request.form("itemID")&"'"

Conn.Execute strSQL

ObjConn.Close
Set ObjConn = Nothing
ObjRS.Close
ObjRS= Nothing

%>

</body>

bmains · July 21st, 2004, 12:25 PM

Hey,

I don't see anywhere where the connection object is created. I imagine this is above the code. Can you verify that the Connection is established an open at the time of the errored line?

Thanks,

Brian

Calibus · July 21st, 2004, 02:08 PM

Yes the Connection was created above.

Can you please look over my code and tell me what I can do to improve
or resolve this errro I am having. Question! do I have to open
another again? Code:
<%@ Language=VBScript %>
<%Option Explicit

Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

%>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 2</title>
</head>

<body>

<div align="center">
  <center>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#000000" width="985" height="50" id="table1" bgcolor="#000000">
    <tr>
      <td width="983" height="50" align="center">
    <img border="0" src="LogoMasthead.gif" align="left" width="150" height="45"><img border="0" src="header_level3.jpg" width="828" height="48"></td>
    </tr>
  </table>
  </center>
</div>
        <p align="center">
        Updating
        Records
<p align="left">
         

<%
Dim TicketNum, ObjConn, Conn, ObjRS

TicketNum = Request.QueryString("TicketNum")

if TicketNum = " " then

response.redirect "Results.asp?TicketNum='" & TicketNum & "'"

else

end if

set objConn=Server.CreateObject("ADODB.CONNECTION")

ObjConn.Open "Provider=sqloledb;Data Source=Flmirsql02;Initial Catalog=Source_Forms;User Id=Source_Forms_User;Password=password;"

Set ObjRS = Server.CreateObject("ADODB.Recordset")

Dim strSQL, ID

strSQL = "SELECT * FROM Escalation_Forms WHERE Ticket_Number =' " & TicketNum & "'"

ObjRS.open strSql, ObjConn, 3, 3

if ObjRS.eof then

   Response.write("No data was returned for ticket number: " & TicketNum)

else

Response.Write"<b>Ticket Number:</b>" & objRS("Ticket_Number") & "<br>" &_

    "<b>Status:</b>" & objRS("Status") & "<br>" &_
    "<b>First Name:</b> " & objRS("First_Name") & "<br>" &_
    "<b>Last Name:</b> " & objRS("Last_Name") & "<br>" &_
    "<b>Customer Account Number:</b>" & objRS("Account_Number") & "<br>" &_
    "<b>Customer Phone Number:</b> " & objRS("Phone_Number") & "<br>" &_
    "<b>Tech Number:</b> " & objRS("Tech_Number") & "<br>" &_
    "<b>Job Number:</b> " & objRS("Job_Number") & "<br>" &_
    "<b>Custmer Comment:</b> " & objRS("Cust_Com") & "<br><br><br>"

end if
%>

<form action=results.asp method=post style="text-align: center">

    <p><b>       &n bsp;       &nbs p;                 &n bsp;       &nbs p;    
    Status:    </b>
    <select name="Status" Value="<%=ObjRS("Status") %>">

        <option <%If ObjRS("Status")="" Then%>selected<%End If%>>Choose One</option>
        <option value="Open" <%If ObjRS("Status")="Open" Then%>selected<%End If%>>Open</option>
        <option value="Pending Research" <%If ObjRS("Status")="Pending Research" Then%>selected<%End If%>>Pending Research</option>
        <option value="Close" <%If ObjRS("Status")="Close" Then%>selected<%End If%>> Close</option>
    </select><b>      &nbs p;                 &n bsp;       &nbs p;       &nbsp ; </b>
    </p>
    <p><b>       &n bsp;       &nbs p;                 &n bsp;   Feed Back:     </b>

    <textarea name="Wip_Com" rows="5" cols="20"><%= ObjRS("Wip_Com") %></textarea><b>      &n bsp;       &nbs p;             </b><b></td><td></b></p>
        <input type="hidden" name="Ticket_Number" value="<%=ObjRS("Ticket_Number")%>">
    <p> <input type="submit"; name="Update Database" value="Update Database">

</form>
<%

strSQL = "UPDATE Escalation_Forms SET Status='"& request.form("Status") &"', Wip_Com='"& request.form("Wip_Com") &"' WHERE Ticket_Number='"& request.form("TicketNum")&"'"

Conn.Execute strSQL

ObjConn.Close
Set ObjConn = Nothing
ObjRS.Close
ObjRS= Nothing

%>

</body>
</html>

Imar · July 21st, 2004, 02:19 PM

Your connection is called objConn, not Conn:

Code:

Conn.Execute strSQL
 
ObjConn.Close
Set ObjConn = Nothing
ObjRS.Close
ObjRS= Nothing

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Calibus · July 21st, 2004, 02:46 PM

I want to thank you for pointing that to me. This is the only part I am having problems
understanding the concept.

Afer making some corrections. I tried updating or writing to the database it default to this message:

"No data was returned for ticket number: "

Where have I went wrong?

Imar · July 21st, 2004, 03:20 PM

I think there are some issues with your code. What is the datatype of the Ticket Number in the database?

Look at this:

if TicketNum = " " then
response.redirect "Results.asp?TicketNum='" & TicketNum & "'"
else

end if

What this says, is that when the ticket number equals a space, you redirect to another page. Why is that?
Also, you're passing additional single quotes, so your ticketnumber will end up as '123' for example.

To debug this page, just write out the SQL you're sending to the database:

Code:

strSQL = "SELECT * FROM Escalation_Forms WHERE Ticket_Number =' " & TicketNum & "'"
Response.Write("SQL is " & strSQL)
Response.End()
ObjRS.open strSql, ObjConn, 3, 3

Then post the outcome of the SQL statement to this list so we can take a look at it.

Cheers

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Hey Ya by Outkast (Track 1 from the album: Speakerboxx/The Love Below) What's This?

Calibus · July 21st, 2004, 03:38 PM

I remove the if stament and applied the change:

Output is :
SQL is SELECT * FROM Escalation_Forms WHERE Ticket_Number =' 18'

Imar · July 21st, 2004, 03:44 PM

And what is the datatype for the ticketnumber? Is it text or a numeric type?
If it's a number, remove the single quotes, so your SQL statement ends up like this:

SELECT * FROM Escalation_Forms WHERE Ticket_Number = 18

Quotes cannot be used for numeric types, but are required for types like text, memo, dates (depending on your database and version).

Is there an Escalation record with a ticket number of 18 present in the database?

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: On A Plain by Nirvana (Track 11 from the album: NeverMind) What's This?