Solved

ASP and SQL question

Posted on 2004-08-18
14
635 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

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!

Question has a verified solution.

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

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…
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/…

732 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