Blog

If you are running a query that needs to make changes to quite a few rows within a database (in my case 8700 rows). You might get an error exception pop up which says the following:

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

After some investigating I found that there are two ways to overcome this error:

1. Use Database Engine Query (or formally known as Query Analyser) to run your query instead of using SQL Panel in table view. I think that the SQL Query panel within table view is only used for smaller query operations. Thats just my guess because it didn't work for me. When using Database Engine query there is pretty much no limit to the amount of data you need.

2. Override connection timeout in SQL Server Options:

I found an option to change the timeout limit in the least place I expected. Under the "Designers" selection within SQL Server Options you will be able to change the timeout amount. But you cannot uncheck the override amount, you will have to specify the amount of seconds you require. 

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

One of my work colleagues at work showed me something that I never knew about UNION SQL statement. By default the UNION statement does the exact same thing as a SELECT DISTINCT on the resulting data. But what if we want to carry out a UNION without distincting the data result? Thats where the UNION ALL comes into play.

Example

Table 1: tblCars-UK

Table 2: tblCars-US

| Car_ID | Car**_Name** | | 1 | Pontiac | | 2 | Chrysler | | 3 | Chevrolet | | 4 | Dodge | | 5 | BMW |

If we used a UNION statement, the results would be as follows:

Select car_name from tblCars-UK
UNION
Select car_name from tbleCars-US 

| Car_Name | | Volvo | | Volkswagen | | Chevrolet | | Nissan | | BMW | | Pontiac | | Chrysler | | Dodge |

As you can see from the results above that the duplicate car entries have been removed and only displayed once. Now this is what will happen if we use UNION ALL statement:

Select car_name from tblCars-UK
UNION ALL
Select car_name from tbleCars-US

| Car_Name | | Volvo | | Volkswagen | | Chevrolet | | Nissan | | BMW | | Pontiac | | Chrysler | | Chevrolet | | Dodge | | BMW |

The SCOPE_IDENTITY() function are used in Insert queries to return the last identity value within your table. However, I never knew how to retrieve the ID value when using this function in my code.

Create an Insert Query in your Data Access Layer called "InsertUser". For example:

INSERT INTO Users (FirstName, LastName, DateOfBirth, Email, City) VALUES (@FirstName, @LastName, @DateOfBirth, @Email, @City);
SELECT SCOPE_IDENTITY() 

When you return to the DataSet Designer you'll see that the "InsertUser" method has been created. If this new method doesn't have a parameter for each column in the table, chances are you forgot to terminate the INSERT statement with a semi-colon. Configure the "InsertUser" method and ensure you have a semi-colon delimiting the INSERT and SELECT statements.

By default, insert methods issue non-query methods, meaning that they return the number of affected rows. However, we want the "InsertUser" method to return the value returned by the query, not the number of rows affected. To accomplish this, adjust the "InsertUser" method's ExecuteMode property to Scalar (this can be found in the Properties panel on the right).

The following code will put your new Insert Query into action:

int intUserID = Convert.ToInt32(BLL.InsertUser("John", "Doe", "16/06/1985", "joe@hotmail.com", "Oxford"));
//Output new User ID
Response.Write("New User ID Inserted: " + intUserID); 

For more info regarding the use of Data Access in ASP.NET 2.0 go to: http://msdn2.microsoft.com/en-us/library/Aa581778.aspx