Jump to content
Sign in to follow this  
foxhound187

ASP Issues

Recommended Posts

Hi there I'm a junior developer. Got this job a few months back coming from a php background. Here they only run classic ASP. No php at all. Made my life ###### trying to learn the syntax, I'm piecing it together bit by bit, slowly but surley. Well it has come time when I've hit a wall and are out of ideas. I've hit an error ADODB.Recordset error '800a0e7d' The connection cannot be used to perform this operation. It is either closed or invalid in this context./readlog.asp, line 37 I assume this has to do with syntax. What I'm trying to do is take a .txt log file that is auto generated and updated, create a script that will open, read the file, dump all the data within into a mysql db, then once done run another script that will query the db to search for all duplicate values and display the top 5, and rank them from most to least. I'm a bit of a noob so there will be some redundancy I'm sure, also I'm unsure what the queries should be and obviously syntax is a problem for me. Please help. If I'm not making sense sorry, I have aspergers syndrome. readlog.asp<% Option Explicit <!--#include file="searchcon.asp"--> Dim FSODim FilepathDim ContentsDim SQLDim TextStreamDim rs Const Filename = "/searchlog.txt" ' file to readConst ForReading = 1, ForWriting = 2, ForAppending = 3Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 'Create a filesystem object Set FSO = server.createObject("Scripting.FileSystemObject") 'Map the logical path to the physical system path Filepath = Server.MapPath(Filename) if FSO.FileExists(Filepath) Then Set TextStream = FSO.OpenTextFile(Filepath, ForReading, False, TristateUseDefault) Contents = TextStream.ReadAll Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Converting log data<br />" Response.Write "<strong><font size='16'><font face='verdana'><center>into database<br />" SQL = "INSERT INTO logdb(data) VALUES Contents" Set rs = Server.CreateObject("ADODB.Recordset") rs.open SQL Response.Write "<br><strong><font size='16'><font face='verdana'><center>Complete!<br />" Response.AddHeader "Refresh", "5;URL=http://www.lgsuper.com.au/displaylog.asp" TextStream.Close Set TextStream = nothing Response.Redirect("displaylog.asp") Else Response.Write "<strong><font size='16'><font face='verdana'><center>Failed!, Could not did not convert into database<br />" End If Set FSO = nothing%> displaylog.asp<% Option Explicit <!--#include file="readlog.asp"--> Dim SQLDim SQL2Dim SQL3Dim rsDim TextStreamDim objDBConnect SQL = "SELECT * FROM lgsdatalog GROUP BY logdata HAVING count(*) >1;" Set rs = Server.CreateObject("ADODB.Recordset") rs.open SQL TextStream.Close Set TextStream = nothing Response.Write "<strong><font size='16'><font face='verdana'><center>Failed!, did not convert into database<br />" %> <div class="f530Box" style="float:right; margin-right:40px;"> <table width="400" border="0" cellspacing="1" cellpadding="0"><tr><td> // blah blah this is example //<tr><td align="left"><strong>Data searched</strong></td><td align="left"><strong>Times searched</strong></td></tr><tr><td><%=SQL%></td><td><%=SQL2%></td></tr></table> </div>

  • Like 2

Share this post


Link to post
Share on other sites

You're not connecting to the database at all, you need to use an ADODB.Connection object to connect. You send the connection object as one of the parameters to the recordset object when you run the query, so that it knows which connection to use. http://www.w3schools.com/ado/met_rs_open.asp You'll need to get the connection string to use for MySQL, there are examples here: http://www.connectionstrings.com/ Your insert query doesn't look right either, you're not inserting the data that you read from the file. If you want to insert all of the data from the entire file into one field in the database then you need to use the variable that you saved the data into. If you do that you'll need to make sure that the data is properly escaped for the query. You can also use the AddNew method instead, if the recordset is updatable: http://msdn.microsoft.com/en-us/library/windows/desktop/ms677536(v=vs.85).aspx

Share this post


Link to post
Share on other sites

I don't follow ? Set TextStream = FSO.OpenTextFile(Filepath, ForReading, False, TristateUseDefault) *this is what is reading the file*Contents = TextStream.ReadAllSQL = "INSERT INTO logdb(data) VALUES Contents" Set rs = Server.CreateObject("ADODB.Recordset")rs.open SQL I'm trying to insert the contents variable, which contains textstreamso how is it not inserting ?

Share this post


Link to post
Share on other sites

You're using the word "Contents" in the query, not the variable Contents. You concatenate strings in VBScript using "&": SQL = "INSERT INTO logdb(data) VALUES ('" & Contents & "')" The error message says that the connection is invalid, so it still sounds like you need to connect and use the connection when you open the recordset. I can't guess what is in the include file, so I don't know what you're doing there.

Share this post


Link to post
Share on other sites

Thanks, yeah there are issues with the db outside my hands, so until its fixed I wont be able to test furthermy biggest concern was the SQL query, I knew there was something wrong with it and you showed me what.Thanks, I'm sure to be back once I can test more :)

Share this post


Link to post
Share on other sites

ok that idea has been abandoned, thanks to the higher ups. Instead now will be connecting via a MS Access db, having similar issues. Here is the code searchcon.asp <-- db connection<%Private Function OpenDatabase() Set objDBConnect = Server.CreateObject("ADODB.Connection") objDBConnect.ConnectionTimeout = 6000 objDBConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("searchlog.mdb")" 'Session("ConnectionString") 'Check to see if the connection is open OK OpenDatabase = False If objDBConnect.State = 1 Then nDBConnect = 1 'Ok connected OpenDatabase = True Else nDBConnect = 0 'error connecting sError = "Unable to connect to database!" OpenDatabase = False End If End Function readlog.asp <-- read the searchlog.txt and dump into access <% Option Explicit<!--#include file="searchcon.asp"-->Dim FSODim FilepathDim ContentsDim MSAQDim TextStreamDim rsConst Filename = "searchlog.txt" ' file to readConst ForReading = 1, ForWriting = 2, ForAppending = 3Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0'Create a filesystem objectSet FSO = server.createObject("Scripting.FileSystemObject")'Map the logical path to the physical system pathFilepath = Server.MapPath(Filename)if FSO.FileExists(Filepath) Then Set TextStream = FSO.OpenTextFile(Filepath, ForReading, False, TristateUseDefault) Contents = TextStream.ReadAll Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Converting log data<br />" Response.Write "<strong><font size='16'><font face='verdana'><center>into database<br />" MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([ & Contents & ])" Set rs = Server.CreateObject("ADODB.Recordset") rs.open MSAQ Response.Write "<br><strong><font size='16'><font face='verdana'><center>Complete!<br />" Response.AddHeader "Refresh", "5;URL=http://" TextStream.Close Set TextStream = nothing Response.Redirect("displaylog.asp") Else _________________________getting the same error, ADODB.Recordset error '800a0e7d' The connection cannot be used to perform this operation. It is either closed or invalid in this context. /eventsCalendar/database/readlog.asp, line 37

Share this post


Link to post
Share on other sites

The line where the connection is opened has an extra quote at the end. Compare your MSAQ query with the one I posted in post 5, you're still sending the word "Contents" instead of the value of the variable.

Share this post


Link to post
Share on other sites

When you're building a string and you want to insert the value of a variable in the string you need to end the string, add the variable, and open the string again. MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([" & Contents & "])" That tells it to take this: "INSERT INTO lgsdatalog ([logdata]) VALUES (["then add this:Contentsthen add this:"])" So you are joining two strings and a variable.

Share this post


Link to post
Share on other sites

So it should be, MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([" & Contents & "]) or MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([" & Contents "]) ?? Sorry I'm not very good without examples, aspergers.

Share this post


Link to post
Share on other sites

The & operator is what joins 2 strings together, so you need to use that operator between any 2 strings you want to join. It's not correct to have 2 separate strings or variables listed without an operator between them.

Share this post


Link to post
Share on other sites

ok now I'm getting aADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /eventscalendar/database/readlog.asp, line 37 MSAQ = MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([" & Contents & "])" Set rs = Server.CreateObject("ADODB.Recordset")line 37 ----> rs.open MSAQ

Share this post


Link to post
Share on other sites

Alright code has been updated. ** searchcon.asp ** <% path="searchlog.mdb" set conn = Server.CreateObject("ADODB.Connection") ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(""&path&"") conn.open ConnStr %> ** readlog.asp ** <!--#include file="searchcon.asp"--><% Dim FSODim FilepathDim ContentsDim MSAQDim TextStreamDim rs Const Filename = "searchlog.txt" ' file to readConst ForReading = 1, ForWriting = 2, ForAppending = 3Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 'Create a filesystem object Set FSO = server.createObject("Scripting.FileSystemObject")Set rs = server.CreateObject("ADODB.RecordSet") 'Map the logical path to the physical system path Filepath = Server.MapPath(Filename) if FSO.FileExists(Filepath) Then Set TextStream = FSO.OpenTextFile(Filepath, ForReading, False, TristateUseDefault) Contents = TextStream.ReadAll Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Converting log data<br />" Response.Write "<strong><font size='16'><font face='verdana'><center>into database<br />" MSAQ = "INSERT INTO lgsdatalog (logdata) VALUES ('"&Contents&"')" Set rs = Server.CreateObject("ADODB.Recordset") response.Write("MSAQ="&MSAQ&";<br />") rs.open MSAQ <---- Line 37 Response.Write "<br><strong><font size='16'><font face='verdana'><center>Complete!<br />" Response.AddHeader "Refresh", "5;URL=http://" TextStream.Close Set TextStream = nothing Response.Redirect("displaylog.asp") Else Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Failed!, did not convert into database<br />" End If Set FSO = nothing%> ************* It prints out the query correctly Converting log data into database MSAQ=INSERT INTO lgsdatalog (logdata) VALUES blah blah blah, however I'm still getting the ADODB.Recordset error '800a0e7d'The connection cannot be used to perform this operation. It is either closed or invalid in this context. readlog.asp, line 37 I tried changing the path to the DB file, and it gave me a Microsoft JET Database Engine error '80004005'Could not find file 'searchlog.mdb'.searchcon.asp, line 5 So it's connecting, it just doesn't like line 37, rs.open MSAQ ???don't understand why

Share this post


Link to post
Share on other sites

It sounds like either the connection is closed or it's not using the right connection. You may need to specify the connection when you create the recordset or open it. The second error means the path does not point to the correct database file. The query you pasted isn't in a correct format, you might have changed it but I obviously can't verify if the original was correct or not by looking at a modified version.

Share this post


Link to post
Share on other sites

I'm aware that's what the second error means, I changed the path to test if it was at least connecting and it was, because soon as I changed it I got that error. I will try specifying the connection when I create or open the recordset and let you know how I go. How is the query not in a correct format if I can print the query fine, wouldn't it of crashed ?

Share this post


Link to post
Share on other sites

The values list of the query is not correct. If you run the query that you pasted here directly on your database it will be an error on the word "blah".

Share this post


Link to post
Share on other sites

Ok changed the code once again, got past that error now have a new one. Microsoft JET Database Engine error '80040e14'Syntax error in INSERT INTO statement. **********************************************<%Dim connDim path path="searchlog.mdb" set conn = Server.CreateObject("ADODB.Connection") ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(""&path&"") conn.open ConnStr Function ReadTextFile(strFilePath) Dim objFSO, objFile, strAllFile Set objFSO = Server.CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(strFilePath) strAllFile = "" If Not(objFile.AtEndOfStream) Then strAllFile = objFile.ReadAll End If objFile.Close Set objFile = Nothing Set objFSO = Nothing strAllFile = Replace(strAllFile, Chr(13)&Chr(10), Chr(13)) strAllFile = Replace(strAllFile, Chr(10)&Chr(13), Chr(13)) ReadTextFile = Split(strAllFile, Chr(13))End Function'usageConst FILE_NAME="searchlog.txt"Dim arrLines, x, curLineResponse.Write("reading file: " & FILE_NAME & "...<br />")arrLines = ReadTextFile(Server.MapPath(FILE_NAME))Response.Write("amount of lines: " & (UBound(arrLines)+1) & "<br />")Response.Write("file contents:<hr />")For x=0 To UBound(arrLines) curLine = arrLines(x) sql = "INSERT INTO lgsdatalog 'data,date,time,ampm' VALUES ("&Server.HTMLEncode(curLine)&")" conn.execute sql 'Response.Write("Line #" & (x+1) & " " & Server.HTMLEncode(curLine) & "<br />")'NextResponse.Write("<hr />") Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Converting log data<br />" Response.Write "<strong><font size='16'><font face='verdana'><center>into database<br />" Response.Write "<br><strong><font size='16'><font face='verdana'><center>Complete!<br />" Response.AddHeader "Refresh", "5;URL=http://" Response.Redirect("displaylog.asp") If conn.execute = False Then _ Response.Write "<br><br><br><br><br><br><br><br><br><br><br><br>" Response.Write "<strong><font size='16'><font face='verdana'><center>Failed!, did not convert into database<br />"%>

Share this post


Link to post
Share on other sites

Your query still isn't formatted correctly, the error message says so. Check the tutorials for how to write an insert query.

Share this post


Link to post
Share on other sites

yeah I fixed it about 5 minutes after posting, then home time. the problem I'm having now is "Number of query values and destination fields are not the same." Obviously because my Select statement has 4 fields and only 1 Value. I need to split the data that's being read by each line into 4 columns so eachcan be put in each column of the database, I assume an array would be needed for this but don't know where to start ? and I've searched far andwide for this on the net, but no information on how to do it in classic ASP.

Share this post


Link to post
Share on other sites

You can use split to split a string into an array, or the various string processing functions to split it up other ways. ASP can be written in more than one language, you're using VBScript so you'll need to specifically check VBScript references. http://www.w3schools.com/vbscript/vbscript_ref_functions.asp#string

Share this post


Link to post
Share on other sites

I'm still getting that error, I have checked the formatting of the query many times and read the tutorial. sql = "INSERT INTO events (INDATE,FNAME,LNAME,EMP,TITLE,ADD,CITY,STATE,PCODE,EMAIL,PHN,OPTN,DIET) VALUES('"&date&"','"&Request.QueryString("givenName")&"','"&Request.QueryString("surname")&"','"&Request.QueryString("Company")&"','"&Request.QueryString("JobTitle")&"','"&Request.QueryString("Address")&"','"&Request.QueryString("City")&"','"&Request.QueryString("TState")&"','"&Request.QueryString("PostCode")&"','"&Request.QueryString("Email")&"','"&Request.QueryString("PhoneNum")&"','"&Request.QueryString("confop")&"','"&Request.QueryString("Diet")&"')" response.write SQL is displaying the insert correctly, so I don't get it ?

Edited by foxhound187

Share this post


Link to post
Share on other sites

If the error says that the number of columns doesn't match the number of values then you need to trust it. If they matched, it wouldn't tell you that they don't. I don't see anything wrong with that code, so it might be a problem with the data. What does the query look like when you print it with all the data? One issue is that you're not sanitizing the data, so if there is a single quote in the data it will break the query.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...