Factory Restore From The Dell Recovery Partition When The Repair Option Does Not Work

Recently someone gave me their Dell Mini 1018 that didn’t work.  It was Windows 7, so, according to Dell, the instructions to perform a Factory Restore were to tap F8 when the Dell logo appears and click Repair Computer (BTW Windows XP is Ctl-F11 after you first turn on the computer).  This is great if the computer boots into the recovery partition, but if it doesn’t you’re almost out of luck.  There could be 2 reasons why your computer might not boot into the recovery partition, one is that you erased the recovery partition, in which case I think you may have to order the recovery CDs from dell and get an external CD drive or put the CD’s onto a bootable USB stick. The other one is that for whatever reason the partition is there but the computer cannot boot to it.

NOTE: These instructions will completely wipe out your data.  You should have someone who is familiar with installing and repairing operating systems. I take no responsibility for lost data or screwing up your computer any more than it already is.  I assume if you are performing these steps you are unable to even boot your computer into Safe Mode.  If you need to get your data (pics, music) off first, a good solution would be to boot from a Ubuntu Live CD, click Try Ubuntu, and then copy all your files to a USB stick. Or if you can boot into safe mode (it will give you the option if you keep hitting F8 after the dell logo disappears) you can get your files off with a USB stick, copying to a network folder, or maybe burning a disk I will try to create instructions on how to do this in the near future and put a link here.

One of the most important parts of this is to make sure that you have not wiped out your recovery partition. One way to figure out if you have the recovery partition is when you first go into the command prompt detailed in the instructions below, try typing in d:  and then type in dir.  it should say “Volume in drive D is RECOVERY” and display a couple of files. (if you want to see all files try dir /a)

You will need to boot into recovery mode in Windows, you can do this using a Windows CD or a bootable Windows 7 USB stick, and getting to a command prompt, by clicking on repair your computer, and going to recovery options.

I received these commands from the following site:

http://answers.microsoft.com/en-us/windows/forum/windows_vista-windows_install/dell-factory-image-restore-help/62fa4436-e2e0-4f8b-b9ab-f5b7f47be673

AGAIN THIS WILL ERASE EVERYTHING THAT”S CURRENTLY ON YOUR HARDDRIVE

1. Boot into the Windows 7 Setup CD or USB stick by pressing F12 during the dell logo.
2. After the CD loads choose your language, and then on the screen wher it says Install Now, on the bottom left there should be an option to “Repair Now” click this
3. System Recovery Options now comes up and it will search for Windows Installations, when it’s finished make sure “use recoevery tools that can help fix problems…” is selected and click next
4. You should now come up to a screen that has a bunch of selections, one of them being  “command prompt” click this
5. Now it should come up with a prompt that says  X:\Sources. Type “diskpart”

X:>diskpart

1. The prompt should change to DISKPART>, type “list disk”
   1. DISKPART>list disk
   2. You should then get something that looks like this:

DISK ###              STATUS                      SIZE                  FREE                      DYN       GPT
Disk 0                    Online                   232 GB                  0 B
Disk 1                    No Media                 0 B                     0 B

1. Now you want to choose your disk. It will MOST LIKELY be the disk that is the largest, . In my case above it is “disk 0” at 232GB.  Yours may be different, for instance if you have an external drive attached or 2 hard drives or a USB stick plugged in.  Type the following at the DISKPART prompt replacing the disk that you have:
   1. DISKPART>select disk 0

It will say: Disk 0 is now the selected disk

1. Now type “list partitions” to list the partitions on the selected disks
   1. DISKPART>list partitions
   2. You should get something that looks like this:

Partition ###                     Type                      Size               Offset
Partition 1                   OEM                      100 MB                 1024 KB
Partition 2                  Primary                    9 GB                  101 GB
Partition 3                   Primary                  223 GB                    9 GB

1. If you look above, the obvious partition that stores all the files is Partition 3, because it is extremely large (223GB)  Again Be careful, if you have an external hard drive plugged in it may show up here and you could wipe it out.  I would suggest unplugging anything else beforehand just in case. Now type “select partition 3”
   1. DISKPART>select partition 3
   2. Now that you have selected the correct partition, you need to format it.  This step will wipe out your data and can take a very long time.  If you are not sure what you’re doing then type exit, if you want to continue then type “format”
      1. DISKPART>format

You are not done yet, all we did here was wipe out the data on your drive. Now that the data is wiped out, you need to apply the Dell Factory Restore image to the drive.  Here are the instructions for this.  I

1. Type exit to leave the DISKPART> prompt  Your new prompt should look like this
   1. X:\Sources>
   2. You will now want to switch to your Recovery partition, which will most likely be drive D:\.  Type in the following 2 commands
      1. X:\Sources>d:
      2. D:\>dir
      3. You should see the following

Volume in drive D is RECOVERY
Volume Serial Number is 3ADB-70AC (this will vary)

04/18/2012                         4:54    AM                4,106     ADRInfos.xml
12/25/2010                         12:28 AM             <DIR>    Recovery
                                                  1 File(s)           4,106 bytes
                                                  1 Dir(s)    5,184,368,640 bytes free

If you see the “volume in Drive D is Recovery” then you are definitely in the right drive, if not you want to try to E: F: G: H:  etc and then do a dir until you are on the recovery drive.

1. This is the part where you apply the image.  You will type the following:
   1. D:\>imagex /apply d:\dell\image\factory.wim 1 c:\
   2. The above command should apply the dell image to your C drive and restore your drive to factory defaults.
   3. After it’s done just type “Exit” to leave the command prompt and then click the restart button.
   4.   There could be a problem here. We’re assuming that c: is the drive that you are installing this to (check this by switching to c:, and performing a dir, there should be no data on it because we wiped it out before.
   5. Another problem (and I ran to this problem on mine) is that there is no imagex program. You will get the error message: “ ‘imagex’ is not recognized as an internal or external command, operable prgram or batch file”  In this case you will have to download the program “imagex” off of the internet (do a google search) and copy it to the D: drive.  What you can do is copy it to a USB stick, plug it into the computer.  Then figure out what directory the USB stick is named by trying the different letters E: F: G: H: I: J:, and doing a Dir to see if you see the file.  If after a dir you see it then just type xcopy imagex.exe d:’, switch back to the D drive, and run the command in step A again.  Mine happened to be located on drive H: but I also had an external CDROM plugged.

Another note is that I got the imagex program by following the links from this post: http://www.techsupportforum.com/forums/f217/opening-wim-files-and-where-to-find-imagex-214731.html

I hope this works flawlessly for you. If you’re familiar with Windows commands it should be relatively simple, if not you may have your tech savvy friend perform the steps for you.

Zune HD not recognized Windows 7 64bit

What happens here is that the Zune HD driver does not install in 64bit version of Windows for some machines no matter what you do. So if you install the software via WindowsUpdate, or manually install the software via download it says the drivers are not installed.  I’m going off the top of my head but I think you should be able to figure this out by using this as a guide.

I dont’ have screenshots for this because I fixed it on someone’s home computer, and I don’t own a zune,  but in order to get your Zune going on the 64bit version. I first had to download and install the full version of the Zune HD software which i downloaded from Microsoft Here http://www.zune.net/en-us/products/software/download/.

Install the software, and connect the Zune after, it doesn’t install the drivers correctly for whatever reason.  What I did to fix this was to go to Device Manager (right click on computer, and then choose manage, then device manager),  find your zune on the right hand side, and Double click on the Zune, go to the driver tab, click update driver, click browse my computer, then click browse. As for the location you want to choose, go to  the directory c:\program files\Zune\Drivers or something like that.  Then choose the folder with your languange (En for english) and choose the driver located in there. Then pretty much next your way through everything.  This enabled me to get the drivers to install properly for the zune.

If that doesn’t work, try to connect the zune, go to device manager, uninstall it (and delete software), reboot, and repeat the steps above.

Sonicpoint Becomes Unresponsive

I have a Sonicpoint N using POE (not sure if this matters) The problem is that everything works ok for about a day but then it becomes unresponsive. In order to get it back to a working condition i would have to unplug the power (ethernet) and then plug it back in. Apparently there are a lot of people who have had this problem as evidenced by this post. According to the post the solution to this is to set the Radio Band to “Standard – 20Mhz Channel” I’ve had it working for 2 weeks now with no problem.

Here’s the steps to get to the Radio Band setting on a Sonicwall NSA240:

1. Log into your Sonicwall (not your Sonicpoint)
2. On the left hand side, expand Sonicpoint.
3. Select Sonicpoints
4. Under Sonicpoint Provisioning Profiles, On the right hand side click “Configure”

5. Choose the 802.11N Radio Tab
6. Next to Radio Band select “Standard – 20MHz Channel

7. Click OK on the bottom.

That should be it. Hope this keeps you from smashing the Sonicpoint against the wall like I was going to do!

Excel – Delete Duplicate Rows

I do a lot of work with excel for manipulating data to import into databases and such. One thing I have had to do in the past is delete duplicate rows. After a little bit of searching I have discovered an easy way to do this programmatically without manually deleting them. Thanks to this site http://www.rondebruin.nl/delete.htm for providing the script. He doesn’t have a comment section on his website, so Ron,  if you are reading this,  Thank you! I appreciate it. You have saved me tons of time and headaches!  I thought I would re-post his script with an explanation of how I was able to apply his script to my excel documents.

So the scenario that I ran into was that I had 21000 rows of items where I could not have duplicates. To make this easy lets just include one column of the data and around 20 rows, and we will say that the column is A.

1. What you first do is sort the column by A, this way the duplicates that you have  appear one after another. The icon to sort looks like this,  you most likely want to sort A-Z and sort anything that looks like a number as a number.  It is on the far right-hand side of the ribbon on the Home Tab in Excel 2007.
2. Then in column B1 you would do the following formula (those are double quotes)
   1. =IF(A2=A1,”YES”,”")
   2. Basically what you are saying above is if the CellA2 (Widget1) is equal to CellA1(Widget2), then place a “YES” in column B1, so we know that cell A1(widget1) is  a duplicate of cell A2 (widget1).  This will help the script tell whether or not this is a row that we have to delete.
   3. So with that logic, if you look at my example above, Cell A1(widget1) is the same as A2(widget1), and Cell A2(widget1) is the same as A3(widget1), but A3(widget1) is different from A4(widget2) so it does not put a “YES”  in Cell B3, but a blank space instead.
   4. Now we copy that formula to all cells by selecting cell B1, and copying that formula.  Easiest way to do this is, after you have cell B1 selected, in the bottom right-hand corner of the cell is a small black square that when you just put your cursor over it, it will turn into a plus symbol. Double-click when it turns into a plus and it should copy it to all cells in the B column. You could also drag that plus down B as well (I realize this is basic excel but perhaps there is someone on here who is fairly new to excel)
   5. Now we need to go to the Developer Tab in Excel so we can paste and execute the script.  If its not already there, it can be shown by viewing the following link (different versions of excel have different ways of doing this so I will refer you to this link) http://msdn.microsoft.com/en-us/library/bb608625.aspx  The Developer Tab should now appear on the ribbon.
   6. Click on the Developer tab that appeared, then Click the Visual Basic icon on the left-hand side of the ribbon which will bring up a separate Visual Basic window.
   7.  Assuming that you are working on Sheet1 then Double-Click on the “Sheet1” icon in the left-hand pane of the Visual Basic window.  This will create a blank white page in the right hand pane.
   8. Now here’s where we use the VB code,  which I borrowed from the following site http://www.rondebruin.nl/delete.htm .   The most important part of this code is the items that I have in bold.  We basically tell it, if cell B has a “YES” in it then delete the entire row.  You can change these 2 values to adapt the code to your excel sheet if you have a different cell or different wording.  Another thing is that I think this is case-sensitive so if nothing happens then double-check that you have the correct row in there.

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the B column in this example
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = "YES" Then .EntireRow.Delete
'This will delete each row with the Value "YES"
'in Column A, case sensitive.
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

9. Now here’s where we use the code from http://www.rondebruin.nl/delete.htm to find cells in row B that have a YES in them, and then delete the column. Copy the code above   from “Sub Loop Example ()” to “End Sub” .   The most important part of this code is the items that I have bolded.  We basically tell it, if cell B has a “YES” in it then delete it.  You can change these 2 values to adapt the code to your excel sheet.  Another thing is that I think this is case-sensitive so if nothing happens then double-check that you have the correct Column in there and correct case-sensitive “YES”.

10. When you are confident that you have the correct values in there then you run the script by clicking the “Play” (run) button which is located on the top of the screen under the “debug” menu option (see below).

11. Did it run? Switch back to the excel document and you should see the following with the duplicates deleted.

12. Now you can just select the entire column B (where you had the YES’s previously and it now says “#REF!”) and delete the entire column.  You should how have gotten rid of all your duplicates

In a real world scenario you probably have more than one column, all you have to do is insert a new blank row next to the column that you want to check for duplicates and place the formula there, then modify the VB Script above to use whatever cell it created, so instead of  With .Cells(Lrow, “B”) use, say, With .Cells(Lrow, “D”) to check column D for the “YES”.

If you do not see that it deleted duplicate rows (#REF!) make sure that you have the correct column in the script, and that the  case that you have entered in the formula in Column B is the same as in the part of the VB code that you modified to say “YES”

That should be it.  Also visit his site http://www.rondebruin.nl/tips.htm  for other tips and examples of doing tricky things in excel.

acer aspire jerky/jumpy elan touchpad

To help anyone who runs into this problem. I just purchased an Acer Aspire 5552-7474.  I booted up the laptop and everything worked OK. Then after a little while the trackpad started to act screwy.  I tried reinstalling 5 versions of different drivers for the elantech touchpad. I then installed the PS/2 driver and that helped a little but it still didn’t work great (accuracy and responsiveness was off)

I tried a factory reset,  then tried different drivers again, nothing resolved the problem.   I then called up Acer to figure out what the problem was.  They had me reinstall the drivers and do a factory reset.   I called Acer back with the same problem and then figured it out. After  I typed the support ID number from the bottom of the laptop, I noticed that the mouse was working flawlessly.   The problem was that when I had the power plugged into the laptop the trackpad was jerky/jumpy. For instance, if I just held my finger on the trackpad without moving it, the cursor would bounce all around in a 1/2″ radius, and it would do so when trying to  click on a link, however when the power adapter was unplugged the touchpad worked find. Doing this several times reproduced/fixed the problem.  I called up Acer and they said that I need to do a repair on the laptop.  Luckily I purchased the laptop from Newegg.com and since I still had the original boxing, they were able to do a replacement for me. The problem is that newegg determined it to be functioning fine, so they send it to me with the same problem and again I return it for a replacement.

So Guess what happened? I order a similar Acer laptop, from buy.com, same Elan touchpad and the same thing happened.  So I returned it, no problem whatsover, I’ll never buy an Acer again.  Found a really good deal around christmas on a samsung and I haven’t had a problem yet.

I hope this saves someone the time of trying to figure out what is going on.

Gridview With Subtotals

When it comes to subtotals in Gridviews, I could never find a great writeup to help me out so I used to complete it using Crystal Reports which made it easy.  I finally had some time  to do some more research (I’m not a programmer by trade, I just whip up some reports via asp.net) so this may not be the smartest way to do this.  I found great instructions in C HERE, so I converted it to VB and modified to fit my needs.  If you program in C it might be easier to look at that one, but i have tried to add as many comments as I could here.   There are 2 things we do here:

1.)    Figure out when to add the subtotal. In my example, when it becomes a new year. I need to insert a subtotal row before it creates a new row.  I do this in the Gridview’s RowDataBound.  This is where you would normally add totals to the footer.

2.)    Use a Function to return a row, and insert that row into the gridview before it writes the next year.

 

Here’s the layout of my table, as you can see, the way that I have the query(table) ordered is by year, then month. As the gridview reads the data and writes the rows, it needs to determine when the year changes so it can write the totals

Year,month,sales,cost
2011,12,10000,9000
2011,11,11000,5000
2011,10,4000,3000
2010,12,3000,2000
2010,11,3000,1500
2009,12,6000,5000
2009,11,2000,1000
 

Here’s how to accomplish this:
 
First I start off by figuring out what year it is:  I create 2 global variables (top of the page) like so:

Dim strLastYear, strCurYear As String

Then in my page Load I set them both to the current year. This will make sure that it does not add a row at the beginning of the gridview.

strCurYear = Year(Date.Now).ToString
strLastYear = Year(Date.Now).ToString

 

Then I needed some global variables to hold my totals and subtotals. 

Dim decTotalCost, decTotalSales, decSubCost, decSubSales As Decimal

 

Now in The Gridview’s rowdatabound, as it’s reading data from the database,  I need to first make sure it’s a data row, then I grab the year from the current row that it is reading from my table, and find out what year it is.

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
'determine if we need to subtotal, so if the year hasn't changed, then we just need to add the subtotals, not create the subtotal row
strCurYear = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Year"))
If strCurYear = strLastYear Then
decsubSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sales"))
decsubCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"))

 

Now we need to account for if it’s a new year.  If it’s a new year we want to do a few things:

1. 1.      Create a temporary table and set it equal to the gridview
2. 2.      Get the index (row) that we are on.
3. 3.      Create the new row using a function called CreateGridviewRow  which will match the color, and use the cost, num of columns (see below)
4. 4.      Add the new row to the current Gridview at the correct location
5. 5.      Clear the subtotals so we can start summing up next years totals, then start adding the new years totals

 

Else
'if it's a new year then we create a temporary table and set it to the gridview, after that we grab the row, then we use the function to create a new row
Dim tbltemp As Table = Me.GridView1.Controls(0)
Dim intindex = tbltemp.Rows.GetRowIndex(e.Row)
Dim gvrSubTotal As GridViewRow = CreateGridviewRow(4, intindex, "#F7F6F3", 2, "Total for " + strLastYear, 20, decsubSales.ToString("c"), decsubCost.ToString("c"))

'add the row to the gridview
tbltemp.Controls.AddAt(intindex, gvrSubTotal)

'zero out the subtotals from last year since we are done
decsubSales = 0
decsubCost = 0

'since the year has changed and we haven't yet added the subtotals we need to add them after the previous years totals have been zeroed out (above)
decsubSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sa-Sales"))
decsubCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"))
End If

 

Now  add the running Totals that will be used in the footer

'Add TotalSales to the running Total Variables
decSales += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Sa-Sales"))
decCost += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"))
strLastYear = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "sqlyear"))

 

We’re not out of the water yet, we not only have to write the subtotal above the footer, and then put our running totals in the footer as well. 

 

So when we get to the footer, the first thing we want to do is write the subtotals for the last year.  So we basically use the same code as above where we use the function to create the last subtotal row before the gridview writes the footer

 

ElseIf e.Row.RowType = DataControlRowType.Footer Then

'For the Footer Display the Running Totals
Dim tbltemp As Table = Me.GridView1.Controls(0)
Dim intindex = tbltemp.Rows.GetRowIndex(e.Row)

'this is for the last subtotal row
Dim gvrSubTotal As GridViewRow = CreateGridviewRow(4, intindex, "#F7F6F3", 2, "Total for " + strLastYear, 20, decsubSales.ToString("c"), decsubCost.ToString("c"))
tbltemp.Controls.AddAt(intindex, gvrSubTotal)

'zero out the subtotals
decsubSales = 0
decsubCost = 0

'write the running totals and a description to the footer
e.Row.Cells(2).Text = decSales.ToString("c")
e.Row.Cells(2).HorizontalAlign = HorizontalAlign.Right
e.Row.Cells(3).Text = decCost.ToString("c")
e.Row.Cells(3).HorizontalAlign = HorizontalAlign.Right

'zero out the running totals
decSales = 0
decCost = 0

'reset the curyear and last year
strCurYear = Year(Date.Now).ToString
strLastYear = Year(Date.Now).ToString
End If
End Sub

 

Now we can look at the Function Called CreateGridviewRow . This will take parameters like subtotal, Color, num of columns and create a table row to be returned to the gridview. We need to take into account the following:

1. 1.      IntNumCells = how many cells are in the gridview – I don’t use this here, but it would be used to figure out how many  cells are in the gridview, so you can ignore it
2. 2.      Icurrentindex = what row are we on in the gridview
3. 3.      Strtablebackcolor = match the color of the gridvew
4. 4.      Incolumnspan = this is used for the text in the footer, so for instance, if our first column in the gridview is year (column 0), and our second column is month (column 1) we would set the column span to 2 so the text (strtabletext) spans across 2 rows
5. 5.      Strtabletext = this is what is in the footer, so for instance “Totals for 2011”
6. 6.      inTableHeight = what is the height of the table
7. 7.      strSales and StrCost = The subtotal values that we will write to the row

 

 

Private Function CreateGridviewRow(ByVal intNumCells As Integer, ByVal icurrentindex As Integer, ByVal strtablebackcolor As String, ByVal intColumnspan As Integer, ByVal strTableText As String, ByVal intTableHeight As Integer, ByVal strSales As String, ByVal strCost As String) As GridViewRow
'this function creates a subtotal row for the gridview

'create the gridview row using the parameters we passed to this function
Dim gvrTemp As New GridViewRow(icurrentindex, icurrentindex, DataControlRowType.Separator, DataControlRowState.Normal)

'now we create the cells to be added to the gvrTemp above
'Create The title of the row spanning 2 columns, using the parameters above
Dim celltemp As TableCell = New TableCell 'title of row
celltemp.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor)
celltemp.Font.Bold = True
celltemp.ColumnSpan = intColumnspan
celltemp.HorizontalAlign = HorizontalAlign.Left
celltemp.Text = strTableText
celltemp.Height = Unit.Pixel(intTableHeight)
gvrTemp.Cells.Add(celltemp)

'Create the sales cell
Dim celltemp2 As TableCell = New TableCell 'title of row
celltemp2.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor)
celltemp2.Font.Bold = True
celltemp2.ColumnSpan = 1
celltemp2.HorizontalAlign = HorizontalAlign.Right
celltemp2.Text = strSales.ToString
celltemp2.Height = Unit.Pixel(intTableHeight)
gvrTemp.Cells.Add(celltemp2)

'Create the Cost cell
Dim celltemp3 As TableCell = New TableCell 'title of row
celltemp3.BackColor = System.Drawing.ColorTranslator.FromHtml(strtablebackcolor)
celltemp3.Font.Bold = True
celltemp3.ColumnSpan = 1
celltemp3.HorizontalAlign = HorizontalAlign.Right
celltemp3.Text = strCost
celltemp3.Height = Unit.Pixel(intTableHeight)
gvrTemp.Cells.Add(celltemp3)

'now that we have all cells, added to the gridview row return the row
Return gvrTemp
End Function

 

That should be it. You should get something that looks like this (created this in excel but it’s what the gridview should look like). Hope this helps

 

 

ASP.NET and SQL – Populate Gridview Programmatically To Solve “Timeout Expired”

It can be really frustrating in ASP.NET when you get the  timeout expired.  By default SQL server allows a 30sec timeout for queries.  The problem is that sometimes we have queries that pull large amounts of data on an old server and it is just going to take  >30 seconds to execute.  For instance, I have a few reports that I created for the end of the year that have to crunch a years worth of data that can take a long time.   Other thing is if you’re using a Sqldatasource there is no great way to increase the timeout.  This is a simple way to use codebehind to populate your gridview while giving you the flexibility to increase the timeout to whatever you want.

The error you usually get is the following:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

To accomplish this you may need to put Imports statements up at the very top of the page like so:

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes

Then create your SQL String like you normally would.

Strsql1 = “SELECT dbo.[ridiculous_amt_of_data].[moundofdata] FROM dbo.[ridiculous_amt_of_data]”

Then add the following obviously substituting your information.

Dim MyConnection As System.Data.SqlClient.SqlConnection
Dim MyCommand As System.Data.SqlClient.SqlCommand
Dim MyDataReader As System.Data.SqlClient.SqlDataReader
MyConnection = New System.Data.SqlClient.SqlConnection("server=YourIPAddressOrHostname; initial catalog=YourDatabaseNameHere;uid=YourUsername;pwd=YourPassword")
MyConnection.Open()
MyCommand = New System.Data.SqlClient.SqlCommand
MyCommand.Connection = MyConnection

Set your sql string here

MyCommand.CommandText = strsql1

Adjust your timeout here

MyCommand.CommandTimeout = "120"
MyDataReader = MyCommand.ExecuteReader

Change These To Your Gridview Name  And Then Close Everything Out  

GridView1.DataSource = MyDataReader
GridView1.DataBind()
MyDataReader.Close()
MyDataReader = Nothing
MyCommand = Nothing
MyConnection.Close()
MyConnection = Nothing

Another reason that this error may happen, according to MS, is that there are uncommitted transactions in the database. Here’s some more information on the topic.

http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx

Sonicpoint Setup To Access From WLAN Zone to LAN

I  purchased a Sonicpoint NE to hook up to my Sonicwall NSA240.  What I like about these is the central configuration.  What I don’t like about them is that that you cannot just attach this to your Sonicwall and expect to access your LAN after connecting it to an interface.   As I understand it, the way Sonicpoints are  are meant to work is to be setup so you can get Internet Access, but for the local LAN access you would need to use a VPN connection.  This is not always ideal for all setups, but is probably the most secure. Here’s a quick writeup on how to get your Sonicpoint WLAN network to communicate with your LAN without having to VPN in.  You should always make sure that this will conform to your company’s security policy’s for wireless access because they may need to comply with the Payment Card Industry standards for security or some other policy.    For this writeup, my LAN is on the X0 interface which is 192.168.1.1, and I am placing my Sonicpoint on the X8 interface.

 

Initial Setup

1. Plug your Sonicpoint into your desired port.  I plugged mine into X8. Then make sure that the Sonicwall recognizes it by going to the Sonicpoint section on the Sonicwall menu, then looking under the SonicpointN’s section, (or Sonicpoints section if you don’t have a Sonicwall N device) to make sure that it recognizes it.  If it doesn’t you may need to upgrade the firmware in the Sonicwall. Sometimes when a Sonicwall gets shipped to you it’s using a really old Firmware and it needs to be upgraded to recognize newer devices.
2. Configure the Provisioning profile so you can apply this to all of your Sonicpoints that you will place in this zone.  There are 2 of these, one for Wireless N and one for A/G.
   1. On the “Settings Tab”, name it, and click the box to “Enable Sonicpoint”
   2. On the “802.11n Radio Tab” (if you’re using an N device), set all your security including SSID and password, for this I’m using WPA2 Personal – PSK with AES.  (You can also use EAP if you would like to configure this with a RADIUS server  or Windows IAS.)

 

Setup to Allow Access From WLAN Zone to LAN

1. Login to your Sonicwall, then in the address bar change the address from https://192.168.1.1/main.html  to https://192.168.1.1/diag.html
2. Click “Internal Settings” on the left hand side of the page.
3. About 3/4 down the page you will see the “Wireless Settings” section. Under that check the setting that says ”Enable Local Wireless Zone Traffic To Bypass Gateway Firewalling” then click “Accept” and go back to main.html by clicking close.
4. Under Network go to “Zones”
   1. Under the General tab make sure that “Allow Interface Trust” is checked
   2. Under the Wireless Tab.  Uncheck “Only allow traffic generated by a Sonicpoint/SonicpointN” and Check “Enforce local wireless zone traffice to bypass gateway firewalling”  then click OK.
   3. Now go to your interface where you plugged the Sonicpoint into, in my case I will go to configure X8.
      1. Change the Zone to “WLAN”
      2. Change the IP Assignment to “Layer 2 Bridged Mode”
      3. Change “Bridged To” to  “X0” or whatever your LAN is.

 

That should be it. This should allow you to connect to your Sonicpoint, and access your local LAN resources.

 

Here’s some other info.

 

This person allows access to LAN and has pictures:

http://briandagan.com/fix-configuring-sonicpoint-aps-on-a-sonicwall

When you cannot connect  a Sonicwall directly to the port workaround:

http://www.brandontek.com/networking/solution-to-your-sonicpoint-wlan-woes/

Corporate VPN Setup for Guests and Employees

https://www.fuzeqna.com/sonicwallkb/consumer/kbdetail.asp?kbid=5801

SQL Long Date To Short Date

I always forget how to do this so I figured I would create a post on it. A lot of times when querying a database with a date i will get the long date like so:

2011-05-12 00:00:00

Simple way to solve this, especially if you’re sending this to someone as a report is to use convert like so:

CONVERT(VarChar, GetDate(), 101)

Of course you can replace “GetDate()” with a value from your database like:

CONVERT(VarChar, dbo.[mytable].[my-datebase-date], 101)

Here’s some information on the command including changing the dates into other formats
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Hope this helps someone.

Solve Proxy Bypass Problems

I noticed that a lot of people were bypassing my proxy using Firefox Portable and other standalone browsers.  I am using a proxy installed on a machine that is not directly attached to my default gateway, so I have them pick up the proxy address from wpad, and lockdown the proxy settings page in IE through Group Policy.

A simple solution to people bypassing your proxy, is to put an entry in your firewall to only allow HTTP and HTTPS traffic that originates from your proxy server.  If you’re not administering a very large network this is ideal.  Another advantage, since it is not specific to just Firefox, it will allow you to catch any browser that users throw at it and force them to put in proxy settings to use the internet.  This also solves the problem if they try to get around the proxy by modifying their “auto detect proxy” settings.  Since Group Policy cannot lockdown the proxy editing page of every browser, and people always find clever ways to install the multitude of internet browsers out there, this will not allow them to simply bypass it by turning the “don’t detect proxy” setting off .   Here’s what the entries look like in a Sonicwall router to allow only http/https traffic that comes from your proxy  (the proxy server in this example is called flserver4.)

Once you have a rule like the above setup, you can add a few exceptions in for your servers, etc. by placing an “allow” for those addresses with a lower priority than the deny.  In the example above, I would put an allow entry in where the source is my Server IP, the destination is any, and the service is http/https, at a priority of 4, which would get caught before the deny.  In Sonicwall you can create a Service Group containing both HTTP and HTTPS so you don’t have to put every exception in twice.

Obviously, The best way to use a proxy is to use two nics and place the server/device in front of your router, or use a content filtering solution that is installed on the router itself since all traffic leaving must go through this server/device to get out.  Sonicwall has one, Cisco has one on a certain series of routers.  However, if you have a proxy working on a standalone server that is not installed in front of your gateway so that all traffic has to pass through it, then the above solution should suffice. Everyone out there has a different setup so this will help when you do not have the luxury of having this type of setup.

There is a also an ADM out there that allows you to configure Firefox through Windows Group Policy, however, since Firefox standalone is not actually installed on the machine and does not contain registry entries, I’m not sure that this will work, so you might want to investigate that.  Another person has a great writeup on how to create your own.  If you need to enforce more than just proxy settings then you should take a look at it.

http://ilias.ca/blog/2005/03/locking-mozilla-firefox-settings/

Here’s another writeup I had on autoconfiguration setup a little while ago that addresses how to set it up and contains links at the bottom on troubleshooting and concerns with using wpad.