foxhound187 Posted January 19, 2012 Share Posted January 19, 2012 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> 2 Link to comment Share on other sites More sharing options...
justsomeguy Posted January 19, 2012 Share Posted January 19, 2012 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 Link to comment Share on other sites More sharing options...
foxhound187 Posted January 19, 2012 Author Share Posted January 19, 2012 sorry the connection string is in the include at the top of the page <!--#include file="searchcon.asp"--> Link to comment Share on other sites More sharing options...
foxhound187 Posted January 19, 2012 Author Share Posted January 19, 2012 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 ? Link to comment Share on other sites More sharing options...
justsomeguy Posted January 19, 2012 Share Posted January 19, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted January 20, 2012 Author Share Posted January 20, 2012 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 Link to comment Share on other sites More sharing options...
foxhound187 Posted January 27, 2012 Author Share Posted January 27, 2012 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted January 27, 2012 Share Posted January 27, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted February 2, 2012 Author Share Posted February 2, 2012 Yeah the MSAQ is changed because I was told the way you execute an access query is differen't to a mySQL queryso how should it be ? Link to comment Share on other sites More sharing options...
justsomeguy Posted February 2, 2012 Share Posted February 2, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted February 2, 2012 Author Share Posted February 2, 2012 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 2, 2012 Share Posted February 2, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted February 3, 2012 Author Share Posted February 3, 2012 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted February 3, 2012 Share Posted February 3, 2012 Print out the MSAQ query after you build it to check what you're sending to the server. Link to comment Share on other sites More sharing options...
foxhound187 Posted March 1, 2012 Author Share Posted March 1, 2012 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted March 1, 2012 Share Posted March 1, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted March 2, 2012 Author Share Posted March 2, 2012 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 ? Link to comment Share on other sites More sharing options...
justsomeguy Posted March 2, 2012 Share Posted March 2, 2012 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". Link to comment Share on other sites More sharing options...
foxhound187 Posted March 15, 2012 Author Share Posted March 15, 2012 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 />"%> Link to comment Share on other sites More sharing options...
justsomeguy Posted March 15, 2012 Share Posted March 15, 2012 Your query still isn't formatted correctly, the error message says so. Check the tutorials for how to write an insert query. Link to comment Share on other sites More sharing options...
foxhound187 Posted March 15, 2012 Author Share Posted March 15, 2012 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted March 15, 2012 Share Posted March 15, 2012 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 Link to comment Share on other sites More sharing options...
foxhound187 Posted March 15, 2012 Author Share Posted March 15, 2012 (edited) 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 March 15, 2012 by foxhound187 Link to comment Share on other sites More sharing options...
justsomeguy Posted March 15, 2012 Share Posted March 15, 2012 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. Link to comment Share on other sites More sharing options...
foxhound187 Posted March 16, 2012 Author Share Posted March 16, 2012 ok then that is what it is, the data will have single quotes in it. The log contains all the various user input searches. So how do I sanitize the data ? never heard of that before Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now