Inception

Another sick error of the day, When you try to create connection string from SQLDatasource or even want to bind existing connection string. You might see a Message box saying

Could not load file or assembly ‘Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

This error comes when you are trying to access SQL 2005 stuff from Visual Studio 2008.  So go to the link and download the following stuff

• Microsoft SQL Server System CLR Types
• Microsoft SQL Server 2008 Management Objects
• Microsoft SQL Server 2008 Native Client

Many of us face a problem with programming in .NET and SQL server that when a new record is inserted into the table with a primary key that is auto generated/ identity seed. How do we find out the auto generated ID of the last inserted record?

Using Data Adapter is a common practise and the architecture of ADO.Net which takes the Data off line and refreshes only when we perform a requery or update on the data adapter. Since newly inserted records will not have and auto-generated identity key, which is fetched only after an update is executed on the data set.

On a multi-user environment there are many users accessing the same database table and adding information at the same time. So when an insert is executed by us and there are very high likely chances that there are other users also who are executing an insert on the database. So when our data adapter is refreshed the requery will return all the records from the table which includes your newly added record and the records added by other users.

If we use simple logic and run to the last record in the data table to find the value of primary key, there are chances that this record may not be the one that we inserted and may lead to wrong information being fetched.

So how do I get the value of my primary key which is an identity seed?

Well its very simple T-SQL provides a command @@IDENTITY that returns the value of identity which was last generated by an insert command.

So after you have executed your data adapter update statement, just execute this set of statement to get the identity that was generated.

C# code

The variable Id will contain the value of last generated identity seed.

@@IDENTITY returns value for each connection so you need worry about fetching the value of someone else’s insert statement.

Another T-SQL command SCOPE_IDENTITY() returns the last generated identity value for a current scope. I will cover this more in detail in my next article.

One of my ex-colleague sent me this request

Hi

I have a problem with DevExpress.Web.AspxGridView 9.2.5 used in our website

Following is the scenario where I am having strange behaviour of the grid while updating grid row

Machine 1 Machine2

Name: SERVER1 SERVER2

OS : Windows 2003 SP2 Windows 2003 SP2

Web Server: IIS 6 IIS 6

SQL Server: SQL Server Express 2005 SQL Server Express 2005

SQL Server Instance: SERVER1\SQLEXPRESS SERVER1\SQLEXPRESS

Database Name: MyDB1 MyDB1 (Copied from Server1)

Start Website installed in Server1 from same machine

1. Connect to SERVER1\SQLEXPRESS database MyDB1, insert update and edit update of the grid works fine.

2. Connect to SERVER2\SQLEXPRESS database MyDB1 ( it’s an exact copy of MyDB1 in Server 1), insert update or edit update of grid hangs

Start Website installed in Server2 from same machine

1. Connect to SERVER1\SQLEXPRESS database MyDB1, insert update and edit update of the grid works fine.

2. Connect to SERVER2\SQLEXPRESS database MyDB1 , insert update or edit update of grid hangs

It reveals that the problem is with updating data into SERVER2\SQLEXPRESS from the grid even if the database used in this SQLServer is an exact copy from SERVER1\SQLEXPRESS

I tried my best to compare the SQLServer Installations of both machines and all looks fine.

You help asap will be highly appreciated

Regards

Jos

This is how he solved it

Hi Zaheed

Thanks for the reply ,  Finally nailed it. It was due to the Language settings in one SQL Server (as US English) which in turn accepts date format in mm/dd/yyyy for stored procedure parameters. One of our .NET stored procedure call was passing  dd/mm/yyyy format date thus causing a  failure at the backend. Unfortunately this error didn’t propagate to the frontend  Xtab grid and grid seems like hanged. I changed the stored procedure input parameter date formatting and implemented some error handlings for ASPxGridView and its now working fine. Thank you so much for your help. Hope your work and project going fine.  Will catch up with you later

Regards

Jos

TEST for Junior Programmer

Design a Windows based application using .Net and SQL Server. The following functionality has to be achieved in this application.

A company dealing in software development pays its employees on hourly basis for the work that they do. If the employee has put in 10 hours per day he will be paid for the 10 hours. The payments are made on weekly basis. Employees have different rates depending on their designation in the organization.

Develop a solution that will help you capture this information and process weekly pay sheets for individual employee. There must be a form to capture how many hours for a particular date that an employee has worked.

Tasks

1. Design / Create Database
2. Develop Windows Form Application

If you want to connect to SQL server programmatically which is running on a port other than 1433, your connection string should look something like this.

cst = “Provider=SQLOLEDB;” & _
“Data Source=<x.x.x.x>,<port number>;” & _
“Initial Catalog=<dbname>;” & _
“Network=DBMSSOCN;” & _
“User Id=<uid>;” & _
“Password=<pwd>”

In my next article i will try and post connection strings for different databases engines.

This code come straight from another current posting, I take no credit -

To enumerate all network-visible SQL servers using SQL-DMO objects,
create a
new standard EXE project and add a reference to sqldmo.rll. This file
can be
found in \Binn\Resources\1033\sqldmo.rll under the SqlServer70
directory.
Now add this code and declaration in your form’s code:

Private Function GetAllSqlServerCollection(colSqlServers As Collection)
As
Boolean
Dim intIndex As Integer
Dim oApplication As SQLDMO.Application
Dim oNameList As SQLDMO.NameList

Set oApplication = New Application
With oApplication
Set oNameList = .ListAvailableSQLServers
With oNameList
For intIndex = 1 To .Count
colSqlServers.Add (oNameList.Item(intIndex))
Next
End With
End With
Set oApplication = Nothing
GetAllSqlServerCollection = True
End Function

This code quickly fetches a list of SQL servers and can be put inside a
combo box’s drop-down event to always get a refreshed list of SQL
servers on
your form.

I am amazed with the things that we can achieve with the DevEx XtraGrid control in a .NET application. But some silly and small bugs can eat in a lot of your time because you just cannot find where the error is.

This is one of the most silliest errors that I have come across on a control’s property box.

The XtraGrid control allows you to specify a fieldname from the binded datasource that needs to assigned to a column in the grid.

I was surprised to find out that the fieldname to be specified in the grid was case sensitive.  So for example the field in your select statement is in lower case than in the grid also you will have to specify the fieldname in the same case.

Funny? yeah and that too with VB.NET

Declaration

Imports System.IO

Code

Public Function isFileExists(ByVal FilePath As String) _
As Boolean

Dim f As New IO.FileInfo(FilePath)
Return f.Exists

End Function

Public Function isFolderExists(ByVal FolderPath As String) _
As Boolean

Dim f As New IO.DirectoryInfo(FolderPath)
Return f.Exists

End Function