Solved

ASP and SQL question

Posted on 2004-08-18
14
628 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 400 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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SP to delete duplicates 15 70
Query to get the soonest date out of a few records 2 49
Query still returning duplicates 5 30
Passing Parameter to Stored Procedure 4 24
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 information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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