• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

ASP and SQL question

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
trf000
Asked:
trf000
  • 7
  • 4
  • 3
1 Solution
 
DarksbaneCommented:
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
 
alorentzCommented:
Post the code where the error is?
0
 
trf000Author Commented:
<%@ 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
Industry Leaders: 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!

 
alorentzCommented:
And idea where the error line is?
0
 
trf000Author Commented:
/business_office/serial_input.asp, line 36?
0
 
alorentzCommented:
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
 
alorentzCommented:
>>/business_office/serial_input.asp, line 36?

Comon dude....We don't know what line 36 is!  What's the code?
0
 
DarksbaneCommented:
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
 
DarksbaneCommented:

doh above line 32 is actually line 36
0
 
alorentzCommented:
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
 
trf000Author Commented:
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
 
trf000Author Commented:
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
 
alorentzCommented:
What is your code now?  That should have worked...
0
 
alorentzCommented:
OK, thanks.  Good luck!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now