Move ASPNETDB from local machine to a SQL Server (quick way)

Leave a comment

I had a ASPNETDB database with a bunch of users, roles, etc that I setup that I had been using for quite a few years, and it functioned fine using SQL Express.  It would just sit in the App_Data folder and any time I needed to work on the asp.net site I would just make sure not to overwrite the one on the server.   Not wanting to spend a lot of time on this I needed a quick and easy way to move this to a central location where I wouldn’t be in danger of accidentally overwriting the current  ASPNETDB  .  I also needed it to be properly be backed up accessed remotely.  Basically I just attached it to a production SQL Server 2008 R2 that I had onsite and changed my connection string in my web.config.  Here’s the instructions:

  1. Copy the most up to date ASPNETDB.mdf and ASPNETDB_log files up to your server and place them in whatever directory  you want .
  2. Open SQL Management Studio.
  3. Attach the aspnetdb.mdf file by right clicking on databases and clicking attach.
  4. I think by default it names the database with the full path of the database, so I just renamed mine to ASPNETDB.
  5. Go to your web.config file and put in the following string under the connectionstrings section:


<remove name="LocalSqlServer"></remove>


<add name="LocalSqlServer" connectionString="Data Source=MySQLServer;Initial Catalog=ASPNETDB;Persist Security Info=True;User ID=myusername;Password=mypassword" providerName="System.Data.SqlClient" />

That should be it. I loaded up my ASP.net pages and it functioned like it normally does.

Advertisements

Gridview With Subtotals

2 Comments

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”

Leave a comment

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

asp.net tab index

Leave a comment

here’s a quick one. I created a web page with 2 textboxes, called them textbox1 and textbox2. The problem was that when i tabbed from the first one to the second one it would jump somewhere else on the page. I’ve created pages in the past, but it always just worked correctly in all browsers. It could have been because I added textbox2 after, but I’ve inserted controls in between 2 controls and the tab index has worked automatically. I went about and set the tabindex property of the control to 0 and 1 respectively. It still didn’t work. So after getting frustrated i tried setting them to 1 and 2 (instead of 0 and 1) and it worked perfectly. Hope this helps someone out.

Deploying An Asp.Net Site To Network Solutions With Membership

13 Comments

Tools Used:

Visual Studio 2008 Professional Edition

SQL Server Management Studio

Recently I was developing an ASP.NET site which worked fine on my computer, but when I went to deploy it to Network Solutions, it did not work, and their Support was not very helpful.  They told me  that membership could not be used. They also told me that asp.net, while supported, could not be used on the server since it is shared hosting environment.  Further they told me that they could not create an “Application” like I would using IIS also because it is a shared hosting environment.  They also do not have an option (in windows) for VPS that would allow full control.  All the stuff they told me  is basically not true or only partially true.

First off in order to get your asp.net site up and running (without membership) all you have to do is copy your aspx files to the root of the htdocs folder that they supply for you.  This in itself is an application directory and because of this you can run ASP.NET pages.

Second, you can use membership by going to their admin pages, creating a database, exporting your data and schema from your current membership database, and then running a script in query analyzer. Here’s how you do this:

1.       To start off you should have a site with membership that allows you to login successfully from your local computer. There are plenty of tutorials online that will help you out with this but it’s beyond the scope of this post.

2.       Now we create the sql database on Network Solutions.  Go to Manage Account>>My Hosting Packages>>Click on Web Hosting Toolkit>>Go down to the bottom of the page and click Database Manager >>Click Microsoft SQL Server (Note: if nothing pops up and you get “sessionId=0”, logout using the link on the top right hand side of the page.)

3.       Click Add to add a new DB . Then enter your information for the new database.  Just make a note of what you entered here:

4.       Now that we have a DB, lets connect to it using SQL Server Management Studio.  Network Solutions gives you the address to which to connect to after you create the database.  When you are in database manager , select your newly created database and then click edit to view it. Open SQL Server Management Studio and enter the ip address, your username and password to connect.

5.       Now that you are connected, minimize SQL Management Studio, and then open up your website in Visual Studio. We will now we will create the .SQL script to recreate the schema and data on the network solutions database we created above.

6.        In Server Explorer, Right Click on aspnetdb.mdf and click “Publish To Provider”, then follow the wizard. This will create a sql script replicating the aspnetdb.mdf database. here’s what my screen looked like.

7.       Now that you have the sql script created, open it using your favorite text editor, and then copy all of it’s contents.

8.       Go back to SQL Management Studio, Find your database name under databases and (you can see all network solutions databases hosted on that shared server), single click it and then click “New Query”.  This will open up a sql window.

9.       Paste all the code that you copied in step 7, and execute the script.  This should recreate all your user information, roles, security, etc.  I did not have any errors when doing this so I can’t really help you out if it doesn’t work.

10.   Next modify your web.config file to include a new connection string to point to this new database. Here’s the string that I used in my web.config file.


<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=IPAddress;Initial Catalog=URDBName;User Id=URUsername;Password=***********;" providerName="System.Data.SqlClient" />
</connectionStrings>

11.   Next ftp all of your files up to Network Solutions server using the Manage Account>>FTP/ContentPublishing  provided by them.  I use filezilla, it’s easier.  I uploaded them to the root of the htdocs folder, and voila everything worked.

Another note, Network Solutions support was telling me that If I have an access database (which I do), I need create a new folder called “db” under the HTDocs folder and adjust my code to point to this folder.  They said this is due to permissions. This worked flawlessly.

Another note: If you use smtp in your asp.net, create your email address in network solutions Manage Account, and then add the following information to your web.config file:

<system.net>
<mailSettings>
<smtp from="myemailaddress@mydomain.com">
<network
host="localhost"
port="25"
userName="myemailaddress@mydomain.com"
password="*********" />
</smtp>
</mailSettings>
</system.net>

I would also like to give credit to this site for information on the Visual Studio schema export.

sql error “the text, ntext, and image data types…”

Leave a comment

The other day I was converting an existing sql query from 2000 to 2008.  This query is based off of a table that I import every night.  I created this table using the SQL Import query and it automatically determined the field data types from a Progress 10.1 database.  The only problem is when i tried to run a query on the new 2008 server I got the following error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

After doing some research, I found out that this occurred because sql made a field in one of my tables into a Text instead of a Varchar.  For whatever reason, on the import, it translated it differently.  Also, I only had this problem with this field when it was contained in a query with a “Group By” clause.  I could probably fix this on the import, but a quick and dirty fix was to convert it to a varchar on the fly.  Here’s an example  Let’s say my field is “[product-desc]”

SELECT     TOP (100) PERCENT CONVERT(varchar(200), [product-desc])
FROM         PUB.[my-table]
GROUP BY CONVERT(varchar(200), [product-desc])

That was all I needed to do to fix this problem.  You could also fix this by using CAST like so

 

SELECT     TOP (100) PERCENT  [product-desc]
FROM         PUB.[my-table]
GROUP BY cast([product-desc] as varchar(500))

ASP.NET Accessing SQL Data From Codebehind Without A Control

Leave a comment

I needed to access a SQL 2008 database from my codebehind without a control like a gridview.  After scouring the internet I put together the pieces to do so.

If you were familiar with the (oldschool) “non .NET”  scripting you remember doing something like the following:

Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open("ODBC-DSN", "sa", "**********") 
rs = Server.CreateObject("ADODB.Recordset") 
rs.Open(StrSQL, Conn) 
Do While Not rs.eof 
Urvariable=rs("whs-num").value 
rs.movenext() 
Loop

You can still do this in an ASPX page if you use aspcompat=”true”, but as many women have said to me throughout the years “It’s time we move on Vin” (or “no, it really IS you” but that doesn’t apply here)   So the replacement .NET code to do the same thing is…

Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes
Dim URConnection As System.Data.SqlClient.SqlConnection
Dim URCommand As System.Data.SqlClient.SqlCommand
Dim URDataReader As System.Data.SqlClient.SqlDataReader
Dim StrSQL, URVariable as String
'Your sql string
strSQL = "Select * From Sales"
'Set your Connections and your command
URConnection = New System.Data.SqlClient.SqlConnection("server=URSERVER; initial catalog=URDB;uid=sa;pwd=********")
URConnection.Open()
URCommand = New System.Data.SqlClient.SqlCommand
URCommand.Connection = URConnection
URCommand.CommandText = StrSQL
URDataReader = URCommand.ExecuteReader()

While (URDataReader.Read)
URVariable = URDataReader.Item("whs-num")
End While

URDataReader.Close()
URDataReader = Nothing
URCommand = Nothing
URConnection.Close()
URConnection = Nothing

If you want an explanation of the newer commands, you can Google  it or look on MS’s site.  For my purposes of creating a data driven web page every once in a while, the nitty gritty is not what I’m concerned about.  I just need it to work.  If you have an aspx page that uses old asp code now, it actually is not too difficult to convert it over using a few copy and pastes and Find and Replaces.

Older Entries