Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP and SQL question

Posted on 2004-08-18
14
Medium Priority
?
642 Views
Last Modified: 2012-06-27
I need to insert data to an SQL server about company assets. The primary key is the serial number. inserting the data is no problem, but i need to display that data after it is inserted, and i keep getting the same error:

ADODB.Recordset.1 error '80004005'

SQLState: 37000
Native Error Code: 8180
SQLState: 37000
Native Error Code: 170
[MERANT][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'lop'.
[MERANT][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.


/business_office/serial_input.asp, line 36


Thing is, i only get that error if there are letters in the serial number. if it is all numbers, i get no error. Either way it submits fine, but i need to display it so the person entering can then print it for there records.

any help is appreciated.
0
Comment
Question by:trf000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 2

Expert Comment

by:Darksbane
ID: 11832244
Well seeing your SQL statement would be nice. Offhand it looks like you might either be inserting Letters into a field which does not accept them in the database or in your SQL statement you might be missing the single quote around your serial number.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11832380
Post the code where the error is?
0
 

Author Comment

by:trf000
ID: 11832416
<%@ Language = "VBScript"%>
<%
strSQL = ""
set conn = server.createobject("adodb.connection")
conn.Open "DSN=;UID=;pwd=;"
strSQL = "INSERT INTO workorder_serial"
strSQL = strSQL & " SELECT "
strSQL = strSQL & "'" & Replace(Request.Form('serial_number'), "'", "''") & "' as text1,"
strSQL = strSQL & "'" & Replace(Request.Form("description"), "'", "''") & "' as text2,"
strSQL = strSQL & "'" & Replace(Request.Form("current_building"), "'", "''") & "' as text3,"
strSQL = strSQL & "'" & Replace(Request.Form("current_room"), "'", "''") & "' as text4,"
strSQL = strSQL & "'" & Replace(Request.Form("new_building"), "'", "''") & "' as text5,"
strSQL = strSQL & "'" & Replace(Request.Form("new_room"), "'", "''") & "' as text6,"
strSQL = strSQL & "'" & Replace(Request.Form("sending_dept"), "'", "''") & "' as text7,"
strSQL = strSQL & "'" & Replace(Request.Form("sending_account"), "'", "''") & "' as text8,"
strSQL = strSQL & "'" & Replace(Request.Form("receiving_dept"), "'", "''") & "' as text9,"
strSQL = strSQL & "'" & Replace(Request.Form("receiving_account"), "'", "''") & "' as text10,"
strSQL = strSQL & "'" & Replace(Request.Form("moved_by"), "'", "''") & "' as text11,"
strSQL = strSQL & "'" & Replace(Request.Form("request_date"), "'", "''") & "' as text12,"
strSQL = strSQL & "'" & Replace(Request.Form("move_completed"), "'", "''") & "' as text13,"
strSQL = strSQL & "'" & Replace(Request.Form("asset_action"), "'", "''") & "' as text14,"
strSQL = strSQL & "'" & Replace(Request.Form("disposal_reason"), "'", "''") & "' as text15"


conn.execute(strSQL)
conn.close
'set conn = nothing
%>


<%
'serial_number = CLng(Request.QueryString("serial_number"))
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DSN=business;UID=acweb;pwd=cheese;"
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * FROM workorder_serial WHERE workorder_serial.serial_number = " & Request('serial_number'), adoCon
'rsUpdateEntry.Open strSQL, adoCon
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>
<head>
<title>SQL Add record example</title>
<link rel="stylesheet" type="text/css" href="../index_style.css">
</head>
<body>
<h1 align="center">Amarillo College </h1>
<h1 align="center">Equipment Transfer/Disposal Form</h1>

<p align="center">Your Equipment transfer has been submitted. Click
<a href="fixed_assets.htm">here</a> to submit another request.
    <p align="center">If the information below is incorrect,
    <a href="http://acweb.actx.edu/business_office/serialupdate1.asp?serial_number=<% = rsUpdateEntry("serial_number") %>">Click here</a><div align="center">
    <table cellspacing="0" cellpadding="0" class="i8" width="800">
      <tbody>
      <tr>
        <td ALIGN="right" class="bold"><em>Asset Action</em></td>
        <td width="100%"><% = rsUpdateEntry("asset_action") %></td>
      </tr>
      <tr>
        <td ALIGN="right" class="bold"><em>Sending Department Name:</em></td>
        <td width="100%"><% = rsUpdateEntry("sending_dept") %></td>
      </tr>
      <tr>
        <td ALIGN="right" class="bold"><em>Receiving Department Name:</em></td>
        <td width="100%"><% = rsUpdateEntry("receiving_dept") %></td>
      </tr>
      <tr>
        <td ALIGN="right" valign="top" class="bold"><em>Description:</em></td>
        <td width="100%">
        <% = rsUpdateEntry("description") %></td>
      </tr>
      <tr>
        <td ALIGN="right" class="bold"><em>Serial Number</em></td>
        <td width="100%">
        <% = rsUpdateEntry("serial_number") %></td>
      </tr>
      <tr>
        <td ALIGN="left" class="right">
        <p class="bold"><em>Current Location:</em></td>
        <td ALIGN="left" width="100%"><% = rsUpdateEntry("current_building") %>&nbsp;<% = rsUpdateEntry("current_room") %></td>
      </tr>
      <tr>
        <td ALIGN="left" class="right">
        <p class="bold"><em>New Location</em>&nbsp;</td>
        <td ALIGN="left" width="100%"><% = rsUpdateEntry("new_building") %>&nbsp;<% = rsUpdateEntry("new_room") %></td>
      </tr>
      <tr>
        <td nowrap>
        <p class="bold"><em>Department requested to move equipment:</em></td>
        <td width="100%"><% = rsUpdateEntry("moved_by") %></td>
      </tr>
      <tr>
        <td nowrap class="bold" align="right"><em>
        Disposal Reason (if applicable)</em></td>
        <td width="100%"><% = rsUpdateEntry("disposal_reason") %></td>
      </tr>
      <tr>
        <td colspan="2">
        <p class="miniheader">Approvals:</td>
      </tr>
      <tr>
        <td colspan="2">
        <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" class="i8">
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td><hr width="95%"></td>
            <td><hr width="95%"></td>
          </tr>
          <tr>
            <td>Sending Dept. Chair</td>
            <td>Date:</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td><hr width="95%"></td>
            <td><hr width="95%"></td>
          </tr>
          <tr>
            <td>Receiving Department Representative</td>
            <td>Date:</td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td><hr width="95%"></td>
            <td><hr width="95%"></td>
          </tr>
          <tr>
            <td>Delivered By: </td>
            <td>Date:</td>
          </tr>
        </table>
        </td>
      </tr>
    </tbody>
    </table>
<%
rsUpdateEntry.Close
adoCon.Close
%>
</div>

</body>
</html>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:alorentz
ID: 11832452
And idea where the error line is?
0
 

Author Comment

by:trf000
ID: 11832481
/business_office/serial_input.asp, line 36?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11832501
Here it appears that serial number is a number datatype:

rsUpdateEntry.Open "SELECT * FROM workorder_serial WHERE workorder_serial.serial_number = " & Request('serial_number'), adoCon

Because you are querying like number datatype.  So when you included letters in serial number it may cause problem.  What is the datatype of the serial number field?

May need to be like this:

rsUpdateEntry.Open "SELECT * FROM workorder_serial WHERE workorder_serial.serial_number = '" & Request('serial_number') & "'", adoCon
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11832509
>>/business_office/serial_input.asp, line 36?

Comon dude....We don't know what line 36 is!  What's the code?
0
 
LVL 2

Expert Comment

by:Darksbane
ID: 11832529
Try this

On line 8 and 32

 Request('serial_number') should be Request("serial_number") I think

On line 32 this is likely causing your SQL query to bomb out as the rest of the line is commented out in asp because of the single quote
0
 
LVL 2

Expert Comment

by:Darksbane
ID: 11832568

doh above line 32 is actually line 36
0
 
LVL 31

Accepted Solution

by:
alorentz earned 1600 total points
ID: 11832601
True, didn't see that in my answer above....you need to have double quotes as well as text datatype:

rsUpdateEntry.Open "SELECT * FROM workorder_serial WHERE workorder_serial.serial_number = '" & Request("serial_number") & "'"
0
 

Author Comment

by:trf000
ID: 11832646
ok, tried that. BTW my datatype is varchar

I still get this error:

ADODB.Recordset.1 error '80004005'

SQLState: 37000
Native Error Code: 8180
SQLState: S0022
Native Error Code: 207
[MERANT][ODBC SQL Server Driver][SQL Server]Invalid column name 'a4654'.
[MERANT][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.


/business_office/serial_input.asp, line 36

i fixed the single quoutes, and changed the select statement. still nothing. works with numbers only, but not letters.
0
 

Author Comment

by:trf000
ID: 11832686
never mind, i left off the tail end of the select statement you provided. Thanks for the great work, this is the third or fourth time you have helped me.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11832691
What is your code now?  That should have worked...
0
 
LVL 31

Expert Comment

by:alorentz
ID: 11832698
OK, thanks.  Good luck!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question