Wednesday, June 26, 2013

Epson Workforce 310 setup on Windows 8 64-bit

I set up Epson Workforce 310 printer drivers on my wife's new Windows 8 computer last week.  It did not go too smoothly.  It started off well.... I found the setup program on the Epson site easily enough and downloaded it.  When I ran the setup it progressed for a few minutes, said it was looking for the printer, found it and displayed its MAC address, and then it gave me this error:

"Some files necessary for setup are not installed. Run setup again from the software CD-ROM."

As I mentioned, I didn't have a CD-ROM, I had downloaded it. And foolishly, I had just selected "Run" instead of "Save" after I had downloaded it, so I hadn't saved a copy. So now I had to download it again so I could try running it again. I downloaded again, this time chose "Save" to save the setup file, ran it again, and got the exact same error at the same part.

I started doing some research and saw some recommendations to add through Control Panel.  When I went to Control Panel, I was surprised to see that the printer was showing up there...twice!  Apparently the install worked despite the error.  I tried printing with it, and it worked.  

But when I tried to run Epson Scan, it gave me an error.  I didn't save that one, but something like that it was not installed properly.  I thought we were going to have to just live with not using the scanner on that computer.  Then I ran Epson Scan Settings, a different program than Epson Scan.  Somehow, that fixed the scanning issue, and now everything is working properly.



Wednesday, May 15, 2013

Simulating Microsoft Access recordset in ASP.Net with SQL Server (MovePrevious)

Despite all of the problems that Microsoft Access has, there are some things that it can do more conveniently than Microsoft SQL Server.  One of them is that it allows you to go forward and backward on a record set using MoveNext and MovePrevious.  In ASP.Net, using a SqlDataReader, you can only go forwards on a record set.  I was recently converting over an Access database to ASP.Net using SQL Server.  The Access code had a whole lot of places where a table was loaded to the record set, the cursor was moved to the last record with MoveLast, then the code stepped through the set backwards using MovePrevious, then reset itself to the end again with MoveLast.  I wanted to try to duplicate this as closely as possible .

After doing some reading, my first plan was to make a DataSet. Once you load data into a DataSet, you can iterate forwards and backwards.  The main reason I didn't want to do it that way is because you have to load all of the data into the DataSet at once that you might need to iterate through.  In my case, the amount of records I needed would vary depending on the data, and I didn't want to load a lot more than I had to in order to be sure I had all I needed.

What I ended up doing was based on a fairly simple idea.  I just sorted the records in the opposite order so that I could iterate through them forward instead of backward.  When MoveLast is called, I just re-ran the query to reset the DataReader's cursor back to the beginning.

The fancier parts were:

  • I made a class to encapsulate it all so that I could use the same method names that the Access record set uses.  That way there was less code to change, so I was hoping that would lead to less potential errors.
  • Access also lets you insert a record by calling AddNew to get a new record, changing values on the record, and then calling Update.  I added some methods to simulate that as well.
The class is not currently generalized to use different tables, but that shouldn't be too hard. The code is below:


    Dim m_newSqlFieldList As String = ""
    Dim m_newSqlValueList As String = ""


    Private Class AccessSimulator
        Dim m_connStr As String = ""
        Dim m_rateCheckingConnection As SqlConnection
        Dim m_rateCheckingCommand As SqlCommand
        Dim m_rateCheckingReader As SqlDataReader
        Dim m_fieldList As Hashtable
        Public Sub New(connStr As String)
            m_connStr = connStr
            m_rateCheckingConnection = New SqlConnection(m_connStr)
            m_rateCheckingConnection.Open()

            m_fieldList = New Hashtable
        End Sub
        Public Sub CloseConnection()
            m_rateCheckingConnection.Close()
        End Sub
        Public Sub MoveLast()
            ' This is to simulate MoveLast on the table
            ' TOP could also  be a select WHERE date goes back a certain amount from a given date

            ' always assume connection is open because the Reader is being used by the calling procedure
            ' it will only be closed if CloseConnection is explicitly called
            m_rateCheckingConnection.Close()
            m_rateCheckingConnection.Open()

            Dim sql As String = "SELECT TOP 50 * FROM MyTable ORDER BY ID DESC"
            m_rateCheckingCommand = New SqlCommand(sql, m_rateCheckingConnection)
            m_rateCheckingReader = m_rateCheckingCommand.ExecuteReader()
            m_rateCheckingReader.Read()
        End Sub
        Public Sub MovePrevious()
            ' since the records are in descending order now, this is really just a Read to get to next record
            m_rateCheckingReader.Read()
        End Sub
        Public Function GetFieldValue(fieldName As String) As String
             ' this is a substitute for the exclamation point when referencing a value of a record
            Return m_rateCheckingReader(fieldName).ToString()
        End Function
        Public Sub AddNew()
            m_fieldList.Clear()
        End Sub
        Public Sub StoreValueForInsert(fieldName As String, fieldValue As String)
             ' this is a substitute for the exclamation point when setting a value of a record
            m_fieldList(fieldName) = fieldValue
        End Sub
        Public Sub Update()
            ' in my case, Update is only used on this class after doing an AddNew
            ' so since we aren't actually inserting in the AddNew method, do the insert now, based on fields in the field list
            ' changes would need to be made to also support actually updating records
            Dim insertFieldList As String = ""
            Dim insertValueList As String = ""
            For Each storedField As DictionaryEntry In m_fieldList
                ' if insertFieldList is non-empty, then value list is too
                If insertFieldList <> "" Then
                    insertFieldList = insertFieldList + ", "
                    insertValueList = insertValueList + ", "
                End If
                insertFieldList = insertFieldList + "[" + storedField.Key + "]"
                insertValueList = insertValueList + "'" + storedField.Value + "'"
            Next

            Dim insertSQL = "INSERT INTO MyTable(" + insertFieldList + ") VALUES (" + insertValueList + ")"
            Dim conn As SqlConnection = New SqlConnection(m_connStr)
            conn.Open()
            Dim cmd As SqlCommand = New SqlCommand(insertSQL, conn)
            cmd.ExecuteNonQuery()
            conn.Close()

            ' clear the list when done
            m_fieldList.Clear()
        End Sub
    End Class

Tuesday, March 19, 2013

SQL - Parsing date from text / varchar field

I had a fun little SQL task to do today.  We have a large field of text that contains some comments and next to each comment is a date.  We wanted to find the most recent comment and sort the records by the date that is next to that comment in the text.  Luckily, we build that text field with the most recent dates at the top, so in this case, I just needed to parse out the first date that I could find.

I can find the year easily enough by searching for "/201" (actually that will only work for 7 more years, oops).  The main complication in my case is that the dates don't use leading zeroes, so they aren't all the same length.  Some are 2/2/2013 (8 characters) and some are 11/12/2013 (10 characters).  So I couldn't just find the "/201" and go back 5 characters.  And I didn't want to just search for the first slash, just in case there was a slash used for a different reason.  So I looked for "/201", then went back 5 characters and found the first slash before that, then went back 2 characters and built a substring from there.  Again the month really could be one or two characters, so in many cases my substring now had a leading character.  In my case, it was usually a space or newline character, so then I stripped newlines using REPLACE.

The last complication is that I wanted to sort by this date, and alphabetical sort won't work because of the lack of leading zeroes.  I had to do a CONVERT() to a datetime in order to sort correctly.  That will fail though if it finds some improperly formatted date (which we have in the text) so I had to use a CASE statement with an IsDate check.  Here's the final query:

SELECT
CASE 
    WHEN IsDate(REPLACE(REPLACE(REPLACE(SUBSTRING(myTable.Comments, CHARINDEX('/', myTable.Comments, CHARINDEX('/201', myTable.Comments) - 5)-2, 10), CHAR(10), ''), CHAR(13), ''), CHAR(9), '')) = 1 
    THEN CONVERT(datetime, REPLACE(REPLACE(REPLACE(SUBSTRING(myTable.Comments, CHARINDEX('/', myTable.Comments, CHARINDEX('/201', myTable.Comments) - 5)-2, 10), CHAR(10), ''), CHAR(13), ''), CHAR(9), '')) 
    ELSE NULL 
END As LastCommentDate
FROM myTable

ISSUES:
1) Not sure how fast this is, it seems like it may be slow
2) Once I find the start point, I just make a substring of length 10.  That works for me for our data, but more safe/accurate would be to do a difference between the start index and the index of the "201" to know for sure how long the month and day part is.

Tuesday, January 29, 2013

Epson Workforce Incorrect Paper Jam Error

Most of the things I post are programming or software issues.  I had an interesting hardware issue last night.  I have an Epson Workforce 310 printer.  Recently it started giving me erroneous Paper Jam errors when there was no paper jam.  It would just start loading a piece of paper, then almost immediately say "Paper Jam", feed the rest of the paper through without printing on it, and cancel the print job.  I thought maybe my printer was finally done for.

So then I started trying random things -- take the paper out and put it back in, clean the nozzles, run the alignment check -- but still no luck.  Then I started thinking, "why did it just start doing this when it was printing fine a minute ago?".  Well I'd just turned the paper around in the feeder because I was printing on hole-punched paper and the hole punches were on the right side of the page and I wanted them on the left.  I tried turning the paper back around and it started printing fine again!  Apparently the printer has some sensor on one side and the holes were confusing it into thinking there was a paper jam.

On further searching, looks like Epson subtly acknowledges this issue in their FAQ regarding paper feeding. It simply says:

"Do not load paper with holes punched in it for insertion in a binder."