ASP Issues

foxhound187 · 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  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 " " Response.Write "<center>Converting log data " Response.Write "<center>into database " SQL = "INSERT INTO logdb(data) VALUES Contents" Set rs = Server.CreateObject("ADODB.Recordset") rs.open SQL Response.Write " <center>Complete! " Response.AddHeader "Refresh", "5;URL=http://www.lgsuper.com.au/displaylog.asp" TextStream.Close Set TextStream = nothing Response.Redirect("displaylog.asp") Else Response.Write "<center>Failed!, Could not did not convert into database " End If Set FSO = nothing%> displaylog.asp<% Option Explicit  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 "<center>Failed!, did not convert into database " %> <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">Data searched</td><td align="left">Times searched</td></tr><tr><td><%=SQL%></td><td><%=SQL2%></td></tr></table> </div>

justsomeguy · 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

foxhound187 · January 19, 2012

sorry the connection string is in the include at the top of the page

foxhound187 · 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 ?

justsomeguy · 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.

foxhound187 · 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

foxhound187 · 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 ExplicitDim 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 " " Response.Write "<center>Converting log data " Response.Write "<center>into database " MSAQ = "INSERT INTO lgsdatalog ([logdata]) VALUES ([ & Contents & ])" Set rs = Server.CreateObject("ADODB.Recordset") rs.open MSAQ Response.Write " <center>Complete! " 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

justsomeguy · 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.

foxhound187 · 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 ?

justsomeguy · 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.

foxhound187 · 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.

justsomeguy · 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.

foxhound187 · 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

justsomeguy · February 3, 2012

Print out the MSAQ query after you build it to check what you're sending to the server.

foxhound187 · 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 ** <% 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 " " Response.Write "<center>Converting log data " Response.Write "<center>into database " MSAQ = "INSERT INTO lgsdatalog (logdata) VALUES ('"&Contents&"')" Set rs = Server.CreateObject("ADODB.Recordset") response.Write("MSAQ="&MSAQ&"; ") rs.open MSAQ <---- Line 37 Response.Write " <center>Complete! " Response.AddHeader "Refresh", "5;URL=http://" TextStream.Close Set TextStream = nothing Response.Redirect("displaylog.asp") Else Response.Write " " Response.Write "<center>Failed!, did not convert into database " 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

justsomeguy · 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.

foxhound187 · 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 ?

justsomeguy · 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".

foxhound187 · 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 & "... ")arrLines = ReadTextFile(Server.MapPath(FILE_NAME))Response.Write("amount of lines: " & (UBound(arrLines)+1) & " ")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) & " ")'NextResponse.Write("<hr />") Response.Write " " Response.Write "<center>Converting log data " Response.Write "<center>into database " Response.Write " <center>Complete! " Response.AddHeader "Refresh", "5;URL=http://" Response.Redirect("displaylog.asp") If conn.execute = False Then _ Response.Write " " Response.Write "<center>Failed!, did not convert into database "%>

justsomeguy · 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.

foxhound187 · 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.

justsomeguy · 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

foxhound187 · March 15, 2012

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

justsomeguy · 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.

foxhound187 · 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

ASP Issues

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in