FormView and SQL Datasource is awesome in my opinion. You can just bind your controls to data field in the markup, its great.
Becomes a bit of a challenge when you want all your data related stuff to be done in the Data Access Layer, but hey, you have to bind the data to the UI one way or the other, right.
Anyway, if you want to create your SQLDataSource in the code behind, you can easily do that. Here is an example:
Function GetSQLDataSource() As SqlDataSource
Dim sqlReturn As SqlDataSource = Nothing
Try
sqlReturn = New SqlDataSource
sqlReturn.ConnectionString = MyConnectionString
'Build Select Command and Parameter List
sqlReturn.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
sqlReturn.SelectCommand = "P_SELECT_SP"
With sqlReturn.SelectParameters
.Clear()
.Add(New Parameter("ID", DbType.Int32)
End With
'Build Update Command and Parameter List
sqlReturn.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure
sqlReturn.UpdateCommand = "P_UPDATE_SP"
With sqlReturn.UpdateParameters
.Clear()
.Add(New Parameter("ID", DbType.Int32)
'Build Delete Command and Parameter List
'Build Insert Command and Parameter List
' You get the idea...
Return sqlReturn
Catch ex As Exception
Throw ex
End Try
End Function
The problem is that how does databinding work? It won't work the same way as having the sql data source in the markup. You will not get any values in the update event. Well here is a trick to do it:
For example, to Update, handle the FormView's ItemUpdating event, and then loop through each value in the UpdateParameters list in the sqldatasource, and manually assign each value from e.NewValues list where the parameter name matches.
Private Sub frmData_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles frmData.ItemUpdating
Also, don't forget to assign the sqldatasource as the formview's datasource in each postback, otherwise your formview's datasource would be nothing.
Here is the interesting problem of the week... finally figured it out!
All of a sudden this Monday, our clients started getting "Service Unavailable" errors all of a sudden. I looked deeper in to it, and it turned out the Application Pool on IIS6 for the ASP .Net app had crashed. So I restarted it, and everything started working again.
But this started happening intermittently about twice daily. Big problem.
The event log on the server showed:
Application pool 'XXX' is being automatically disabled due to a series of failures in the process(es) serving that application pool.
This "crash" was preceeded by a few warnings showing:
A process serving application pool 'XXX' suffered a fatal communication error with the World Wide Web Publishing Service. The process id was '4280'. The data field contains the error number.
Data:
0000: 8007006d
So googling the errors at first, I came across msdn documents and blogs talking about installing debuggers and get stack trace and thread dumps, analyze and review them for errors in the code. Although this probably would be helpful in some cases, didn't help me. Nothing wrong with my code.
Anyway a couple of passed by and I installed microsoft's debug analyzer. btw, 2 good articles there are:
http://blog.whitesites.com/Debugging-Faulting-Application-w3wp-exe-Crashes__634424707278896484_blog.htm
http://support.microsoft.com/kb/919789
Didn't find anything.
Finally today looking at it again, we came across a setting "Enable rapid-fail protection" in the Application Pool properties. It was enabled and set to 5 failures. Looking back at the event logs, I realized that there was a pattern. Every crash had 5 failures (warnings) right before it. Always exactly 5. Same as the example above, where basically a process errored out and the process id was logged. There were errors before too in the log, but at the same time it was a max of only 3 together at the same time. These errors basically seem to be unhandled exceptions in the Asp .net app, a lot of them coming from the framework, at times they were timeout errors, some times file not found, and other things.
So basically we were telling the server that if you get 5 errors together within a span of 5 minutes, just shut down. It's now increased to 10, so that gives us some breathing room. I don't want to completely turn it off, b/c then if some legit issue causes widespread process crashes, the server will get overloaded and the whole machine will come down. Right now, it prevents that by shutting down the application pool if a bunch of worker processes fail back to back. Aha, it all makes sense now... almost!
I already have global exception handling with logging turned on in the ASP .Net app, so the next step would be to figure out why it's causing the process to fail, instead of just handling the error and dying off peacefully. But at least it's not causing the dreaded service unavailable error anymore. Yay!
I have been so busy with programming and project management, that I forgot all about this blog. I still get plenty of challenges and interesting issues every other day. I think it's time to start blogging about it.
Just an update, I have moved to full ASP .Net web developement in the past few years, so no more WinForm stuff from me, except that Telerik Controls suck, and they have an unbelievable overhead and plenty of memory leaks.
That's also true for their ASP .Net controls. It's horrible! For example, just the RadGrid control has a javascript file that's 300+K!!! Did you know that? If you don't set your application to properly compress and cache it, imagine the extra over head you get on every single page view! And don't even get me started how their controls add to the viewstate... Every postback is over 100K. Avoid them if you can.
Here is a function I wrote to check and see if a field exists in a table or not:
''' <summary>
''' Checks to see if a field exists in table or not.
''' </summary>
''' <param name="tblName">Table name to check in</param>
''' <param name="fldName">Field name to check</param>
''' <param name="cnnStr">Connection String to connect to</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function DoesFieldExist(ByVal tblName As String, ByVal fldName As String, ByVal cnnStr As String) As Boolean
'For Access Connection String, use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & accessFilePathAndName
' Open connection to the database
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()
Dim dbTbl As New DataTable
' Get the table definition loaded in a table adapter
Dim strSql As String = "Select TOP 1 * from " & tblName
Dim dbAdapater As New OleDbDataAdapter(strSql, dbConn)
dbAdapater.Fill(dbTbl)
' Get the index of the field name
Dim i As Integer = dbTbl.Columns.IndexOf(fldName)
If i = -1 Then
'Field is missing
DoesFieldExist = False
Else
'Field is there
DoesFieldExist = True
End If
dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()
End Function
Here is a function I wrote to check if a table exists in database or not:
''' <summary>
''' Checks to see if a table exists in Database or not.
''' </summary>
''' <param name="tblName">Table name to check</param>
''' <param name="cnnStr">Connection String to connect to</param>
''' <returns>Works with Access or SQL</returns>
''' <remarks></remarks>
Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean
'For reference on GetSchema see: http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx
'For Access Connection String, use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & accessFilePathAndName
' Open connection to the database
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()
Dim restrictions(3) As String
restrictions(2) = tblName
Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions)
If dbTbl.Rows.Count = 0 Then
'Table does not exist
DoesTableExist = False
Else
'Table exists
DoesTableExist = True
End If
dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()
End Function
We all drop and use Rich Text Boxes control in our VB apps. It is a rich control (hence the name RichTextBox), with many options to format text. However, all options have to be used through coding in the background.
I have also worked with RTBs in several different applications, so I wrote this small extended control for the RichTextBox that displays a toolbar on top, with some common options that the user can perform. One cool option that I added is the spell check option.
So instead of dropping the RichTextBox in your form, drop the Extended RichTextBox :)
This my first code submission on CodeProject.com... finally! After getting valuable help from this site for years, and planning to give back for a while, finally I posted a small but hopefully useful little Extension to a great control.
Here is the link: http://www.codeproject.com/KB/edit/ExtendedRTB.aspx
I was perplexed for days with a weird scenario. If I maximized my MDI child form, after browsing through a few forms, it would start displaying multiple control boxes on top.
I searched for a solution for a while, but no luck. Found this article, which describes almost the same problem but no response: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.general/2004-08/1063.html. Maybe this guy will come across this post and it might help him (after 4 years! =)
Anyway, the fix is actually very simple, and turned out to be an oversight. The "Main Menu Strip" property on the parent form needs to be set to the menu strip. If it's not, then you will get the wierd behaviour, which is probably a bug in Visual Studio.
On the subjet of menu strips, if you don't want your mdi child menu items to "merge" with the mdi parent menu strip, just set the "AllowMerge" property to False.
If you want to concatenate (append to end) a string to a field in SQL, there is a very easy and simple update statement for that:
Update [tableName] Set [myField] = [myField] + 'whatever' Where [id] = 1
The + operator works fine for strings, as long as the field is varchar or nvarchar data type. If it is text or ntext, you cannot use the + operator to join strings, and you will get the follow sql error:
"Invalid operator for data type. Operator equals add, type equals text"
Which is pretty annoying. Anyway thats how Micrsoft designed it (see msdn documentation: http://msdn2.microsoft.com/en-us/library/aa276862(SQL.80).aspx).
There is a work around. You have to use the SQL UpdateText command instead of the Update command, which is a bit more complicated than Update. The following is a stored procedure that you can use to append a string to end of a text field. The UpdateText Command updates only one row at a time, so the following proceduce opens a cursor to go through all rows that need to append, and appends the given text.
-- =============================================
-- Author: Syed,Razi
-- Description: Appends to the AgNotes field
-- Assumptions: You have a SQL Database table myTable, with
-- field 'ID' that is used to select rows and
-- field 'textField' as a field of data type text
-- =============================================
CREATE PROCEDURE [spAppendToTextField]
@TextToAdd nvarchar(255), -- Text to append to the end of the field
@ID int -- Primary ID of the field
AS
BEGIN
DECLARE @myPtr binary(16) -- This will be used to store a TEXTPTR to text field
DECLARE @n int -- This will be used to store the length of the text field
DECLARE myCursor CURSOR FOR
-- Select a text pointer to the text data type field, and get the current length
SELECT TEXTPTR(textField), DATALENGTH(textField)
FROM myTable
WHERE ID= @ID
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @myPtr, @n
WHILE @@FETCH_STATUS = 0
-- Go through each row to update
BEGIN
-- Append the text to the end of currently selected row
-- UpdateText Syntax:
-- UPDATETEXT [table].[field] [TEXTPTR] [Start_Insert_Position] [Num_to_Delete] [TextToInsert]
UPDATETEXT myTable.textField @myPtr @n 0 @TextToAdd
FETCH NEXT FROM myCursor
INTO @myPtr, @n
END
END
CLOSE myCursor
DEALLOCATE myCursor
To run this stored procedure just use:EXEC spAppendToTextField 'Append This Text', 4
Is it just me, or did Microsoft completely screw up the data conversion utility in SQL 2005? I do a lot of conversions from SQL to Access, and the one in SQL 2000 worked perfectly. In 2005, I ALWAYS encounter stupid errors... either in pre-Execute, or prepare for Execute or when it is actually executing... What is the point of having all these useless steps anyway? Also, the converter in 2005 is extremely slow!
It frustrates me so much that they threw away such a nice utility that worked perfectly in 2000, redesigned it completely just so that it won't work anymore :( Way to go Microsoft!
Just one example: and the description is "Unspecified error". Do you think that helps? aargh!
.png)
Just to get around it, I first converted from SQL 2005 to SQL 2000, and then converted to Access. The Conversion from SQL 2000 to Access ran without any problems whatsoever. It copied over all the data in less than 3 minutes. In contrast, it took SQL 2005 42 minutes to copy data over to SQL 2000. Yikes! Did I mention SQL 2005 conversion is slow also?
