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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data type mismatch in criteria expression 2

Status
Not open for further replies.
Airpan

Airpan

Technical User
Jun 14, 2005
172
US
I built a rather large form to update the database, with a second page to handle inserting the form data. I was getting the above error and decided in order to trouble shoot, that I would take the form down to one item to insert and the page with the SQL down to the same.

It states that the issue is with line 46. Line 46 is:
Code: 
Set Rec = oConn.Execute("UPDATE cust_inventory SET [STK]="& frmstk &" WHERE [ListingID]= '"& listing &"' ")

stk is setup as text in my database, due to the customer being the one to enter their stock number (some of which will have letters incorporated into the numbers). The ListingID is actually a number that is auto assigned.

The form page looks like so:
Code: 
<%=Response.Write("Welcome ")%>
<%=Response.Write(Session("sessUserName"))%>
<%Response.Write("<br>")%>
<%listing=Request.Querystring("listing")%>
</head>
<body>

Edit Listings
<form name="inventory" method="post" action="editlistings2.asp">
STK:<input name="stk" type="text" size="6" maxlength="6"><br>
<input name="Submit" type="submit">&nbsp;<input name="Clear" type="reset" value="Clear">
</form>
</body>

The page handling the insertion looks like so:
Code: 
</head>
<%=Response.Write("Welcome ")%>
<%=Response.Write(Session("sessUserName"))%>
<body>
<%
UserID = Session("sessUserName")
listing=Request.Querystring("listing")
frmstk=Request.Form("stk")
if request.form("submit") <> "" Then

   dim oConn, Rec, sConn, sFilePath

  	Set oConn = Server.CreateObject("ADODB.Connection")
   	sFilePath = Server.MapPath("db/logins.mdb")
   	sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";"
   	oConn.Open sConn
 	Set Rec = oConn.Execute("UPDATE cust_inventory SET [STK]="& frmstk &" WHERE [ListingID]= '"& listing &"' ")
	   'response.write(sql)
		   End if 
Rec.Close
oConn.Close
I am trying to use ListingID to identify the specific listing to be edited.

~E
 
Sort by date Sort by votes
stk is setup as text in my database, due to the customer being the one to enter their stock number (some of which will have letters incorporated into the numbers). The ListingID is actually a number that is auto assigned.
Click to expand...

Since STK is text, you need to have single quotes surrounding it when you build your UPDATE.

Since ListingID is a number, you don't put single quotes around it.

Code: 
Set Rec = oConn.Execute("UPDATE cust_inventory SET [STK]=[!]'[/!]"& frmstk &"[!]'[/!] WHERE [ListingID]= "& listing)


[monkey][snake] <.
 
Upvote 0 Downvote
monksnake,
I corrected the code. It generates the following error:
Syntax error (missing operator) in query expression '[ListingID]=' Line 46.

I have a question... I am passing the ListingID as a querystring. Is that something that should be passed through the form? Just throwing that out there because I have had issues similar to this one.

If you read the this thread thread183-1385640, it looks similar to the above issue. At the bottom of that thread I realized that I was trying to pass a variable but it was not holding any data. So does this mean that I should possibly add a ListingID field in the form? If so, I am leary of doing so because I do not want the user to be able to edit that number... only this listing itself. Is it possible to pass it through a hidden field?

~E
 
Upvote 0 Downvote
on your form page you are collecting the value of listing using

<%listing=Request.Querystring("listing")%>

but you are NOT passing it to the next action page editlistings2.asp

and because of this you are getting an empty value when you do listing=Request.Querystring("listing") on the page where you handle inserting record...

SO you have to make an hidden form field that holds the value of listing and then use request.form("listing")..something like this:

<input name="listing" type="text" hidden value=<%=listing%>>

-DNG

 
Upvote 0 Downvote
oops...small correction

<input name="listing" type="text" type="hidden" value="<%=listing%>" />

-DNG

 
Upvote 0 Downvote
Yeah, you need to do what DotNetGnat is saying Airpan.
The hidden input field would go in this form:
Code: 
<form name="inventory" method="post" action="editlistings2.asp">
STK:<input name="stk" type="text" size="6" maxlength="6"><br>
[!]<input name="listing" [s]type="text"[/s] type="hidden" value="<%=listing%>" />[/!]
<input name="Submit" type="submit">&nbsp;<input name="Clear" type="reset" value="Clear">
</form>

[monkey][snake] <.
 
Upvote 0 Downvote
Thanks guys - will give it a shot and post back. :eek:)


~E
 
Upvote 0 Downvote
It worked! WOOT! Will trudge forward with getting the other form variables to pass. Thanks again!

~E
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
171
DiDYRO_
DiDYRO_
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
193
leifoet
leifoet
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
177
leifoet
leifoet
Swi
  • Locked
  • Question
PDF Interrogation
Replies
0
Views
109
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top