Error Codes and How to Debug Them

In the .NET Framework, error handling has been completely revamped. There is such an enormous amount of errors to cover that you could dedicate a whole book to just this subject. Instead, we have tried to focus on the more common areas where you might run into problems and explain the errors that you will probably encounter.

Access Denied

One of the most common errors could be this one:

Access Denied: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
 denied. 

You are trying to connect to a database, and you get this error message. This message seems vague: Is it a security issue or a networking issue? You would think they could have figured this out by now and identify whether it is one or the other. Well, you know that the database exists, and you have all the proper permissions to use it. So what’s wrong? Here are the steps you should take to debug this problem:

Let’s take a close look at the connection string. Microsoft has been meddling quite a lot with the ADO portion of .NET, so you need to pay extra attention to details here. Table 16.2 gives you a list of acceptable keywords.

Here are a couple of examples of what a basic connection string could look like:

SqlConnection ("server=digital-laptop;uid=sa;pwd=;database=northwind) 

SqlConnection("Data Source=localhost; Integrated Security=SSPI;Initial Catalog=northwind") 

SELECT Permission Denied

Another common error is exemplified here:

SELECT permission denied on object 'Product', database 'Northwind', owner 'dbo'. 

This error is typically easy to fix. In this case, we were trying to perform a SELECT statement on the product table with the different account. If you take a look at Listings 16.8 and 16.9, you will notice that we’re trying to connect to the database with a user named testuser, not system administrator. Because the system administrator account has privileges to do anything that the owner wants, there are really no restrictions. This causes serious problems if you try to change the user connecting to the database.

One of the first things you should do when setting up a new database-driven web site is create a SQL user account for your web site and not use the system administrator account. This way you can control what that user has access to right from the beginning, and you will avoid major headaches if you try to switch the user account that is logging into the database.

Listing 16.8. Connecting to the Database (C#)

SqlConnection Conn = new SqlConnection(); 
Conn.ConnectionString = "server=digital-world;initial catalog = northwind;user id=
 testuser; pwd=password"; 
Conn.Open(); 
DataSet ds = new DataSet(); 

Listing 16.9. Connecting to the Database (Visual Basic .NET)

Dim Conn as SqlConnection = new SqlConnection() 
Conn.ConnectionString = "server=digital-world;initial catalog = northwind;user id=
 testuser; pwd=password" 
Conn.Open() 
Dim ds as DataSet = new DataSet() 
Dim dr as SqlDataAdapter = new SqlDataAdapter("Select * from product",Conn) 

To see who has permissions on the product table, you will need to do the following steps:

If you take a look at Figure 16.2, you will notice that there are no permissions set for any user. What you need to do is check the testuser SELECT column and save the changes. Then everything will work fine.

Now that the problem has been identified, let’s look at how to solve it. There are several ways to fix this type of problem, including the following:

• Give the testuser select permissions on the product table.

• Create a new role called WebUsers in SQL server, and then add the testuser to that role. Grant the WebUsers role select permissions on the product table.

• Call a stored procedure and grant execute permission to testuser on that stored procedure.

IndexOutOfRangeException

Here is an error message that you might run into if you are not careful:

An exception of type System.IndexOutOfRangeException was thrown. 
[Response.Write("<br>" ++ myReader.GetValue(2)); 

This is a prime example of stepping outside the boundaries of an array. So how can you prevent this from happening? Let’s take a look at your options for this situation. If you will use the GetValue method to retrieve the data from a field, you can use the fieldcount property to identify how many fields were returned. Using that value, you can initiate a loop to iterate through the fields and read them. This is exemplified in Listings 16.10 and 16.11.

Listing 16.10. Reading Values from the SqlDataReader (C#)

SqlConnection nwindConn = new SqlConnection("Data Source=localhost; 
Integrated Security=SSPI;Initial Catalog=northwind"); 

      nwindConn.Open(); 
      SqlCommand myCommand2 = new SqlCommand("SELECT CategoryID, CategoryName FROM
 Categories", nwindConn); 


      SqlDataReader myReader = myCommand2.ExecuteReader(); 
      int i; 
      while (myReader.Read()) 
            for (i=0;i<myReader.FieldCount;i++) 
            {
                  Response.Write("<br>" + myReader.GetValue(i)); 
            } 
      myReader.Close(); 

      nwindConn.Close(); 

Listing 16.11. Reading Values from the SqlDataReader (Visual Basic .NET)

Dim nwindConn = New SqlConnection("Data Source=localhost; Integrated 
Security=SSPI;Initial Catalog=northwind") 

         nwindConn.Open() 
         Dim myCommand2 = New SqlCommand("SELECT CategoryID, CategoryName FROM Categories"
, nwindConn) 
         Dim dsCustomer = New DataSet() 

         Dim myReader = myCommand2.ExecuteReader() 
         Dim i As Int32 

         While myReader.Read() 
             For i = 0 To myReader.FieldCount 
                 Response.Write("<br>" + myReader.GetValue(i)) 
             Next 
         End While 
         myReader.Close() 

         nwindConn.Close() 

Using this method, you avoid trying to access an array element outside the valid range.

Another method is to access each field by name. This could require more code than the last example, but it gives you that extra level of manipulation and control.

You still might run into an error if you do not spell the column name correctly or provide an invalid column. If this happens, you should get an error that looks similar to the message shown in Figure 16.3.

Make sure that if you use the column names, you have the spelling correct. And if you change the name of a column in the database schema, you will have to make sure that you reflect that change in your code as well.

Listings 16.12 and 16.13 show how you would use the column name instead of the index number.

Listing 16.12. Accessing Data by Column Name (C#)

         While (myReader.Read()) 
{
             Response.Write(myReader["CategoryID"]. ToString()); 
             Response.Write(myReader["CategoryName"]. ToString()); 
             Response.Write("<br>"); 
} 

Listing 16.13. Accessing Data by Column Name (Visual Basic .NET)

       Do While myReader.Read() 
           Response.Write(myReader("CategoryID"). ToString()) 
           Response.Write(myReader("CategoryName"). ToString()) 
           Response.Write("<br>") 
       Loop 

Invalid Data Source

You can assign a data source to your data control in a couple different ways. This section focuses on just working with the data grid control. While we were working with data grids, we were amazed at how flexible the data source property was. You can pass it a dataset or various types of lists or collections.

When setting up a data grid, you can define the data source property in the aspx code, but you don’t have to. You can also set that value in the code behind the aspx page.

If you are working in Visual Studio .NET, you can drag and drop command objects, connection objects, and even datasets. But you need to assign the data source to your data grid. This opens up the possibilities of assigning an incorrect data source if you are not familiar with the process.

If you look at the following code, you will see that it is trying to assign the sqlcommand1 object as the data source.

<asp:DataGrid id="Datagrid2" style='Z-INDEX: 101; LEFT: 25px; POSITION: absolute; 
TOP: 59px" runat="server" DataSource="<%# sqlCommand1 %>"</asp:DataGrid> 

Because the data source is looking for a dataset or a list object, you would need to create a dataset and assign that to the DataSource property. Another option is to set the data source in the code behind the page where you do all the database work and initialize your page components. This enables you to add additional debugging code, such as writing to a debug listener or a file to help you with any problems that you might run across. Listings 16.14 and 16.15 illustrate that you make the call to the database during the load process and then bind the results to the data grid.

Listing 16.14. Setting the data source from behind the aspx page(C#)

private void Page_Load(object sender, System.EventArgs e) 
{
      SqlConnection Conn = new SqlConnection(); 
      Conn.ConnectionString = "server=localhost;initial catalog = 
northwind;user id= sa; pwd="; 
      Conn.Open(); 
      DataSet ds2 = new DataSet(); 
      SqlDataAdapter dA2 = new SqlDataAdapter("Select * from products",Conn); 

      //Populate the dataset 
      dA2.Fill(ds2,"Products"); 
      DataGrid1.AllowSorting=true; 
      DataGrid1.BackColor = System.Drawing.Color.AliceBlue; 

      //Create the association between the data grid and the dataset 
      DataGrid1.DataSource = ds2; 
      DataGrid1.DataBind(); 
      //Make sure to close the connection and free unused resources 
      Conn.Close(); 
} 

Listing 16.15. Setting the data source from behind the aspx page (Visual Basic .NET)

Private Sub Page_Load(sender as object, e as System.EventArgs) 
      Dim Conn = new SqlConnection() 
      Conn.ConnectionString = "server=localhost;initial catalog =northwind;user id= sa; pwd=" 
      Conn.Open() 

      Dim ds2 = new DataSet() 

      Dim dA2 = new SqlDataAdapter("Select * from products",Conn) 
      dA2.Fill(ds2) 

      DataGrid1.AllowSorting=true 
      DataGrid1.BackColor = System.Drawing.Color.AliceBlue 

      DataGrid1.DataSource = ds2 
      DataGrid1.DataBind() 

      Conn.Close() 
End Sub 

No Data in the Data Grid

What if your data does not show up in your data grid and you don’t get any error messages?

If this is the case, you might want to look at your code and make sure that you are using the DataBind method after you set your data source. The DataBind method initiates a sequence of events that binds the control and all its child controls to the specified data source. This is illustrated in Listings 16.16–16.18 .

Listing 16.16. Databind method (aspx)

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" 
AutoEventWireup="false" Inherits="adoconnection.WebForm1" %> 

<HTML> 
  <HEAD> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > 
  </HEAD> 
  <body MS_POSITIONING="GridLayout"> 

    <form id="WebForm1" method="post" runat="server"> 
asp:DataGrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 25px; POSITION: 
absolute; TOP: 59px" runat="server">"></asp:DataGrid> 
    </form> 
  </body> 
</HTML> 

Listing 16.17. Databind method (C#)

private void Page_Load(object sender, System.EventArgs e) 
      {
      // Put user code to initialize the page here 

      SqlConnection Conn = new SqlConnection(); 
Conn.ConnectionString = "server=localhost;initial catalog = northwind;user id= sa; pwd="; 
      Conn.Open(); 

      DataSet ds = new DataSet(); 
      SqlDataAdapter dA = new SqlDataAdapter("Select * from 
      products",Conn); 

      dA.Fill(ds,"Products"); 

      DataGrid1.AllowSorting=true; 
      DataGrid1.BackColor = System.Drawing.Color.AliceBlue; 

      DataGrid1.DataSource = ds; 
      DataGrid1.DataBind(); 
      Conn.Close(); 
            } 

Listing 16.18. Databind method (Visual Basic .NET)

private void Page_Load(object sender, System.EventArgs e) 


      ' Put user code to initialize the page here 

      Dim Conn = new SqlConnection() 
      Conn.ConnectionString = "server=localhost;initial catalog = northwind;user id= sa;
 pwd=" 

      ' Open the connection 
      Conn.Open() 

      'Create a new dataset to hold the records 
      Dim ds = new DataSet() 
      Dim dA = new SqlDataAdapter("Select * from products",Conn) 
      'Populate the dataset by using the dataAdapters Fill method 
      dA.Fill(ds,"Products") 

      DataGrid1.AllowSorting=true 
      DataGrid1.BackColor = System.Drawing.Color.AliceBlue 

      DataGrid1.DataSource = ds 
      DataGrid1.DataBind() 
      Conn.Close() 

This applies only to aspx pages, not windows within windows applications.

Problems with Connections

One of the problems that you might run into first involves working with connections. Microsoft has designed the .NET framework to function a little differently than previous versions of ADO. Figure 16.4 provides an example of an error that you might get when trying to work with multiple connections.

This is a good sign that your connection is in use and that you need to either complete the current database operation or close the connection. If you are getting this error, check to see if you have closed the DataReader object before executing another operation on the same connection.

This might occur when you are trying to read from one table and then trying to perform an operation on another table. Solutions include closing the DataReader before continuing or creating a second connection object and running your process against that.

You will notice in Listings 16.19 and 16.20 that one data reader is open and that we have read the first row of information from it. Then a second command object is created and more data is read from a different table. As soon as the command is executed against the database, however, we get an exception. This is because the connection is right in the middle of reading data from the first SELECT statement. At this point, the only thing that can be done is to finish reading the data or close the DataReader object.

Listing 16.19. DataReader problem(C#)

SqlConnection Conn = new SqlConnection(); 
Conn.ConnectionString = "server=localhost;initial catalog = northwind;user id= sa; pwd="; 

Conn.Open(); 
//Create a SqlCommand using the connection that was just created 
SqlCommand com1 = new SqlCommand("select * from products",Conn); 

//Create a datareader 
SqlDataReader dr; 
dr = com1.ExecuteReader(); 

//Now start reading a row at a time 
dr.Read(); 

//Now create a second command that using the same connection 
SqlCommand com2 = new SqlCommand("select * from jobs",Conn); 

//Create a second datareader 
SqlDataReader dr2; 
//now try to execute this command on the existing connection in use 
dr2 = com2.ExecuteReader(); //This line will throw and exception! 
dr2.Read(); 

Listing 16.20. DataReader problem (Visual Basic .NET)

Dim Conn = new SqlConnection() 
Conn.ConnectionString = "server=localhost;initial catalog = 
northwind;user id= sa; pwd=" 
Conn.Open() 
//Create a SqlCommand using the connection that was just created 
Dim com1 = new SqlCommand("select * from products",Conn) 

//Create a datareader 
Dim dr as SqlDataReader 
dr = com1.ExecuteReader() 

//Now start reading a row at a time 
dr.Read() 

//Now create a second command that using the same connection 
Dim com2 = new SqlCommand("select * from Orders",Conn) 

//Create a second datareader 
Dim dr2 as SqlDataReader 
//now try to execute this command on the existing connection in use 
dr2 = com2.ExecuteReader() //This line will throw and exception! 
dr2.Read() 

If you need to persist the data to work with it, you will need to pull it into a dataset or some other form. That way you can keep it in memory while you make another connection to the database.