The EDX Software Blog

Intro. In my last entry I discussed changing the session state mode from the default InProc which stores Session variables in memory to SQLServer which stores Session variables in a sql database.

The Problem. I had a lot of problems after doing that. Data that I was storing in session variables wasn't being saved. It turns out that the sql session does not work exactly like the memory session, although I never saw this documented anywhere.

The Solution. After diagnosing it and trying things for a few days, I ended up writing a program to store/retrieve variables to/from my own sql table.

By the way, here are some electronics components we have for sale on our website: LMH6645MF, EEU-FC1V101, ZHB6718TA, IRFI9Z34GPBF, CD74HC109E, NLV32T-100J-PF, 1N5242B-T.

Intro. One of our subscription websites saves quite a bit of information in Session variables. This website is now ready to go to beta test, so it is time to change the session state mode from the default InProc which stores Session variables in memory to SQLServer which stores Session variables in a sql database.

The Problem. I followed the instructions I found on msdn.microsoft.com, but that resulted in several errors sending me to google to search for possible solutions. One error that I got was:

Failed to login to session state SQL server for user 'TESTSERVER\ASPNET'

where TestServer is our test webpage server. This error reminded me that TestServer is using IIS 5, so I moved the test to our production webpage server, Laurel, which is running IIS 6. The new error was:

Failed to login to session state SQL server for user 'NT AUTHORITY\NETWORK SERVICE'

That's the IIS 6 error, and it was solved by setting up the sessionState tag correctly, as shown below.

The Solution. Here are the steps that worked for me. Note this usage: our webpage server is Laurel, sql database server is Hardy, MyUser is an existing user on Hardy with db_owner access to the databases used by the new website, MyPass is MyUser's password. Also note that our web.config does not use impersonation and that our authentication mode="Forms".

1. Open a command window on Laurel, change directories, and run the setup:

    cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

    aspnet_regsql.exe -S Hardy -E -ssadd

This created a database on Hardy named ASPState which contains all the stored procedures needed to do SQLServer session state. The data will actually be stored in tempdb, a system database.

2. We already had this in the web.config within the system.web and configuration tags:

< sessionState timeout="60" />

So, I changed it to:

< sessionState timeout="60" mode="SQLServer" sqlConnectionString="Data Source=HARDY;User Id=MyUser;Password=MyPass;" />

3. I used SQL Server Management Studio on Hardy to go to Security | Logins | MyUser | Properties | User Mapping. I checked the boxes for ASPState and db_owner, and tempdb and db_owner, and clicked OK.

I browsed to the webpage and everything worked fine.

Intro. Management purchased a new piece of software which came with a SQL Server database.  I needed to write a program to extract data from the database to use on our website.  The database includes many well-named stored procedures, so I used a code snippet that I use often to call a stored procedure to fill a table with data that I need to manipulate before displaying.

Dim dr As DataRow
Dim dt As New DataTable
Dim conn As New OleDbConnection(MyConnectionString)
Dim da As New OleDbDataAdapter("MyStoredProcedure", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim sparam0 As OleDbParameter
sparam0 = New OleDbParameter
da.SelectCommand.Parameters.Add("@MyParam", sparam0)
da.SelectCommand.Parameters("@MyParam").Value = "ALL"
da.Fill(dt)
I = 0
While I < dt.Rows.Count
    dr = dt.Rows(I)
    'Insert the processing for each row here.
    I = I + 1
End While

The Problem. The datatable never got filled.  I could connect using the same connection string and run the stored procedure from Visual Studio, and this snippet worked on any of the stored procedures that I wrote, so what was different about the new stored procedures?

The Solution.  The new stored procedures used Temp Tables and did not include "SET NOCOUNT ON".  This means that the stored procedures first pass back the count of rows and then the select results.  Why this happens only if the stored procedure uses Temp Tables, I don't know, but it means that my da.Fill(dt) never sees the rows from the select.  Including "SET NOCOUNT ON in the body of the stored procedure fixed my problem, but I did not want to modify the new stored procedures. 

The real solution was to swap out my datatable for a dataset.

Dim dr As DataRow
Dim ds As New DataSet
Dim conn As New OleDbConnection(MyConnectionString)
Dim da As New OleDbDataAdapter("MyStoredProcedure", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim sparam0 As OleDbParameter
sparam0 = New OleDbParameter
da.SelectCommand.Parameters.Add("@MyParam", sparam0)
da.SelectCommand.Parameters("@MyParam").Value = "ALL"
da.Fill(ds)
I = 0
While I < ds.Tables(0).Rows.Count
    dr = ds.Tables(0).Rows(I)
    'Insert the processing for each row here.
    I = I + 1
End While

I don't know what the dataset does with the count of rows, but the first table has the select results that I need.

Intro. I have an MSSQL inventory table (Parts) that holds all the master info about the electronic parts that we sell.  This table is used extensively on our company website:

http://www.edxelectronics.com/InStock/InStockPage1.aspx

The Task. I needed a count of all consignment electronic parts that have a valid price.  Unfortunately, the Price column is a varchar field.  Here is the sql I started with:

SELECT COUNT(*) AS HasPrice FROM Parts p
WHERE p.Type = 'CONSIGN'
AND CONVERT(NUMERIC(18,4),p.Price) > 0

This gave me nothing but this error: 

"Error converting data type varchar to numeric."

and that's because some rows have alpha characters in the Price column.

So, I added an ISNUMERIC test, thinking that MSSQL would now not try to convert the non-numeric data.

SELECT COUNT(*) AS HasPrice FROM Parts p
WHERE p.Type = 'CONSIGN'
AND ISNUMERIC(p.Price) = 1
AND CONVERT(NUMERIC(18,4),p.Price) > 0

Same error! 

The Solution. At first I thought that MSSQL was doing something stupid like evaluating the second part of the AND statement even though the first part evaluated to False.  But then I calmed down and pursued finding data that passes the ISNUMERIC test, but fails the CONVERT.

To get a clue as to what row had non-converting data, I replaced "COUNT(*) AS HasPrice" with "ID, Partnumber, Price".  This displayed the rows in ID order up to the bad row.  I then displayed the bad row and found the characters that caused my problem.  Here's the final SQL:

SELECT COUNT(*) AS HasPrice FROM Parts p
WHERE p.Type = 'CONSIGN'
AND ISNUMERIC(p.Price) = 1
AND CONVERT(NUMERIC(18,4),REPLACE(REPLACE(p.Price,'$',"),',',")) > 0

Here is a code snippet that I use quite frequently when I need info from my database in table form: (more…)