"Type Mismatch" Error on AccessDB/ASP Update SQL Statement 1

tgilbreath · Mar 12, 2003

Hi guys-

Not sure what's going on here...I've got a dropdown box populated from a DB...I have all this code that is supposed to update the record. But it keeps giving me :

--------------------------------------------
Microsoft VBScript runtime error '800a000d'

Type mismatch

/admin/resource_update.asp, line 49
--------------------------------------------

The DSN is correct, because the connection is being made...but here is the line it's talking about: (49):

<input type="hidden" name="id" value="<%=RS("ID&quot

%>">

Here is the code for the page "resource_update.asp":

<%@Language=VBScript%>
<%Response.Buffer=True%>

<html>
<body>

<%
Dim MyConn, SQL, RS, id, Items

Set MyConn=Server.CreateObject("ADODB.Connection&quot

MyConn.Open "DSN=ticktock-resources"
SQLStmt = "SELECT * "
SQLStmt = SQLStmt & "FROM Items "
Set RS = MyConn.Execute(SQLStmt )
%>

<form action="resource_update.asp" method="Post">
<select name="ID">

<%While Not RS.EOF%>
<option value="<%=RS("ID&quot

%>">
<%=RS.Fields("Items&quot

%>

<%
RS.MoveNext
Wend

RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>

<input type="submit" value="Select">
</select>
</form>

<%
id = Request("ID&quot

If id <> "" Then

Set MyConn=Server.CreateObject("ADODB.Connection&quot

MyConn.Open "DSN=ticktock-resources"

%>

<form action="resource_update2.asp" method="Post">
<input type="hidden" name="id" value="<%=RS("ID&quot

%>">
Items<input type="text" name="item" size="20" value="<%=RS("Items&quot

%>"><br>
<input type="submit" value="Update">

<%
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing

Else
Response.Write "<p>Please Select a Record"
End If
%>

</body>
</html>

Now here is the code for the page that actually does the work, resource_update2.asp:

<%@Language=VBScript%>
<%Response.Buffer=TRUE%>

<html>
<body>

<%
item = Replace(Request.Form("Items&quot

, "'", "''&quot

id = CStr(Request.Form("ID&quot

)

Set MyConn=Server.CreateObject("ADODB.Connection&quot

MyConn.Open "DSN=ticktock-resources"
SQL="Update tblItems Set Items = '"&items&"'' Where [ID] = "&id&""
Set RS = MyConn.Execute(SQLStmt )

MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing

Response.Redirect "resource_update.asp"
%>

</body>
</html>

Any help would be appreciated!!!

Tim

Targol · Mar 13, 2003

Look well the first page you post : you close everything in the middle of the page :

Code:

RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing

... then try to use RS again after. Your error may comes from here. Water is not bad as long as it stays out human body ;-)

MrGreed · Mar 13, 2003

Ok dude your problem is (many) but I'll answer your VBScript error first:

<input type="hidden" name="id" value="<%=RS("ID&quot

%>">

The reason this above line is not working is becuase you have the recordset closed and set to nothing before you hit this line.

Additional problems: (Update1.asp)

Do not name your Select Box and the Hidden field the same "ID".

You could really structure your code better, as you only need to open your connection to the DB one time not twice and you only need to close it once also.

Additional problems: (Update2.asp)

<%@Language=VBScript%>
<%Response.Buffer=TRUE%>

<html>
<body>

<%
item = Replace(Request.Form("Items&quot

, "'", "''&quot

id = CStr(Request.Form("ID&quot

)

Set MyConn=Server.CreateObject("ADODB.Connection&quot

MyConn.Open "DSN=ticktock-resources"
SQL="Update tblItems Set Items = '"&items&"'' Where [ID] = "&id&""
Set RS = MyConn.Execute(SQLStmt )
*Why do you need this above line when you don't use the recordset, unless you left some code out?

MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing

Response.Redirect "resource_update.asp"
%>

</body>
</html>

"did you just say Minkey?, yes that's what I said."

MrGreed

http://www.pastame.com

tgilbreath · Mar 13, 2003

Thanks for the help guys....that cleared up THAT error..now, upon selecting an entry from the dropdown, I get this:

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/admin/resource_update.asp, line 0

I have changed the hidden field name for ID to "id2" and changed the corresponding reference on update2.asp. I also took the SQL RS statement out of the second page like you mentioned. What else could be causing this new error?

T

Targol · Mar 13, 2003

As MrGreed says, your code is ... haw to say ??? ... a little bit dirty. ;-)

Try to format correctly the genarated HTML :
- closing the options tags
- outing the submit buton from select tag that can only content options tags.

Try that and tell me if it works better :

Code:

<form action=&quot;resource_update.asp&quot; method=&quot;Post&quot;>
<select name=&quot;ID&quot;>

<%While Not RS.EOF%>
<option value=&quot;<%=RS(&quot;ID&quot;)%>&quot;>
<%=RS.Fields(&quot;Items&quot;)%>

</option>

Code:

<%
RS.MoveNext
Wend

RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>

~~<input type="submit" value="Select">~~

Code:

</select>

Code:

</form>

Water is not bad as long as it stays out human body ;-)

MrGreed · Mar 13, 2003

ok,

Because when your code hits:

<input type="hidden" name="id" value="<%=RS("ID&quot

%>">
Items<input type="text" name="item" size="20" value="<%=RS("Items&quot

%>"><br>
<input type="submit" value="Update">

Your recordset is already at the end of file, from your while loop above.

You should store the "ID" and "ITEMS" value in variables of what record you want then use those variables instead of the recordset.

"did you just say Minkey?, yes that's what I said."

MrGreed

http://www.pastame.com

Targol · Mar 13, 2003

Hey, MrGreed, I never sayed "Minkey" !!!
LOL LOL LOL Water is not bad as long as it stays out human body ;-)

dilettante · Mar 13, 2003

Just another hint, though this won't fix your current error...

Things like:

Code:

SQL=&quot;Update tblItems Set Items = '&quot;&items&&quot;'' Where [ID] = &quot;&id&&quot;&quot;

Can be dangerous. In order to ensure that you get the results you want (string concatentation) always uses spaces around the "&" operator:

Code:

SQL=&quot;Update tblItems Set Items = '&quot; & items & &quot;'' Where [ID] = &quot; & id & &quot;&quot;

You never know when some &hxxx sequence of characters is going to be interpreted as a hex literal instead.

I also seem to see a variable SQL and another SQLStmt in places - check on that.

And the big question:

Where is your

Code:

Option Explicit

and your variable declaration (

Code:

Dim

) statement? Leaving them out is just asking for trouble.

Targol · Mar 13, 2003

A Star for you dilettante for the "Option explicit" remark. It's always good to say and say it again. Water is not bad as long as it stays out human body ;-)

dilettante · Mar 13, 2003

Now if I could stop forgetting them myself.

Like most people I mean well... but I'm far from perfection, so far I can't see it from where I'm standing.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

"Type Mismatch" Error on AccessDB/ASP Update SQL Statement 1

tgilbreath

Programmer

Targol

Technical User

MrGreed

Programmer

tgilbreath

Programmer

Targol

Technical User

MrGreed

Programmer

Targol

Technical User

dilettante

MIS

Targol

Technical User

dilettante

MIS

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

&quot;Type Mismatch&quot; Error on AccessDB/ASP Update SQL Statement 1

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

MIS

Technical User

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor

"Type Mismatch" Error on AccessDB/ASP Update SQL Statement 1