Solved

ASP and SQL question

Posted on 2004-08-18
14
621 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
Comment Utility
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
Comment Utility
Post the code where the error is?
0
 

Author Comment

by:trf000
Comment Utility
<%@ 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
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
And idea where the error line is?
0
 

Author Comment

by:trf000
Comment Utility
/business_office/serial_input.asp, line 36?
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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
Comment Utility
>>/business_office/serial_input.asp, line 36?

Comon dude....We don't know what line 36 is!  What's the code?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 2

Expert Comment

by:Darksbane
Comment Utility
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
Comment Utility

doh above line 32 is actually line 36
0
 
LVL 31

Accepted Solution

by:
alorentz earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What is your code now?  That should have worked...
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
OK, thanks.  Good luck!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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 …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now