SQL Question

psnow1985 · March 20th, 2008, 09:33 AM

Hi,

I just a quick question regarding ASP.NET 2.0 (C#) using SQL Server 2005.

I have been getting "A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll" errors on some pages I have created. What I am trying to do is to get two values from two different tables (via 2 dropdown boxes) and to display them as text on two separate labels on the web form. These SQL commands are assigned to a button. When I debug the code and look at the immediate window and query the values in the two drop down boxes they are correct what seems to cause the problem is somewhere round the "if (reader.Read())" line. I don't know how to solve this problem, I'll put the code for the aspx page below:

Code:

<%@ Page Language="C#" MasterPageFile="~/Default.master" Title="Untitled Page" %>
<%@ import namespace="System" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>
<%@ import namespace="System.Configuration" %>
<%@ import namespace="System.Web" %>
<%@ import namespace="System.Web.Security" %>
<%@ import namespace="System.Configuration" %>
<%@ import namespace="System.Web.UI" %>
<%@ import namespace="System.Web.UI.WebControls" %>
<%@ import namespace="System.Web.UI.WebControls.WebParts" %>
<%@ import namespace="System.Web.UI.HtmlControls" %>



<script runat="server">

    protected void ViewDestinationBtn_Click(object sender, EventArgs e)
    {

            SqlConnection conn;
            SqlCommand comm;
            SqlDataReader reader;
            string connectionString =
                ConfigurationManager.ConnectionStrings[
                "ShippingSystemConnectionString1"].ConnectionString;
            conn = new SqlConnection(connectionString);
            comm = new SqlCommand(
                "SELECT Destination FROM Order " +
                "WHERE OrderID = @OrderID", conn);
            comm.Parameters.Add("@OrderID", System.Data.SqlDbType.Int);
            comm.Parameters["@OrderID"].Value = DropDownList1.SelectedValue;

            conn = new SqlConnection(connectionString);
            comm = new SqlCommand(
                "SELECT NextDest FROM Ship " +
                "WHERE ShipID = @ShipID", conn);
            comm.Parameters.Add("@ShipID", System.Data.SqlDbType.Int);
            comm.Parameters["@ShipID"].Value = DropDownList2.SelectedValue;


            try
            {
                conn.Open();
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    SDestinationLbl.Text = reader["ShipID"].ToString();
                    ODestinationLbl.Text = reader["OrderID"].ToString();
                }
                reader.Close();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

    }

    protected void OrderAllocationBtn_Click(object sender, EventArgs e)
    {
    SqlConnection conn;
        SqlCommand comm;
        string connectionString =
            ConfigurationManager.ConnectionStrings[
            "ShippingSystemConnectionString1"].ConnectionString;
        conn = new SqlConnection(connectionString);
        comm = new SqlCommand(
            "INSERT INTO Ship (ShipID)" +
            "AND Order (OrderID)" +
            "VALUES (@ShipID)" +
            "AND (@OrderID)", conn);
        comm.Parameters.Add("@ShipID", System.Data.SqlDbType.Int);
        comm.Parameters["@ShipID"].Value = Convert.ToInt32(DropDownList2.SelectedValue);
        comm.Parameters.Add("@OrderID", System.Data.SqlDbType.Int);
        comm.Parameters["@OrderID"].Value = Convert.ToInt32(DropDownList1.SelectedValue);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            Response.Redirect("Success.aspx");
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }

    }
</script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <table style="position: static">
        <tr>
            <td style="width: 178px; height: 21px;">
                Ship ID:</td>
            <td style="width: 204px; height: 21px;">
                Order ID:</td>
        </tr>
        <tr>
            <td style="width: 178px; height: 21px;">
                <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
                    DataTextField="ShipID" DataValueField="ShipID" Style="position: static">
                </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ShippingSystemConnectionString1 %>"
                    SelectCommand="SELECT [ShipID] FROM [Ship]"></asp:SqlDataSource>
            </td>
            <td style="width: 204px; height: 21px;">
                <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
                    DataTextField="OrderID" DataValueField="OrderID" Style="position: static">
                </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ShippingSystemConnectionString1 %>"
                    SelectCommand="SELECT [OrderID] FROM [Order]"></asp:SqlDataSource>
            </td>
        </tr>
        <tr>
            <td colspan="2" style="height: 21px">
                &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                &nbsp; &nbsp;<asp:Button ID="ViewDestinationBtn" runat="server" OnClick="ViewDestinationBtn_Click"
                    Style="position: static" Text="View Destinations" /></td>
        </tr>
        <tr>
            <td style="width: 178px">
                Ship Destination:</td>
            <td style="width: 204px">
                Order Destination:</td>
        </tr>
        <tr>
            <td style="width: 178px">
                <asp:Label ID="SDestinationLbl" runat="server" Style="position: static"
                    Width="169px"></asp:Label></td>
            <td style="width: 204px">
                <asp:Label ID="ODestinationLbl" runat="server" Style="position: static"
                    Width="195px"></asp:Label></td>
        </tr>
        <tr>
            <td colspan="2" style="height: 26px">
                &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                &nbsp;
                <asp:Button ID="OrderAllocationBtn" runat="server" Style="position: static" Text="Add Order to Ship" Width="158px" OnClick="OrderAllocationBtn_Click" /></td>
        </tr>
    </table>
</asp:Content>

Thanks for any help.

Regards,

Peter

planoie · March 20th, 2008, 09:46 AM

I see two problems:

1) You are initializing 2 different SQL commands but only executing 1
   comm = new SqlCommand(...);
   ...
   comm = new SqlCommand(...);

2) In the query you are actually executing, you are selecting a single field which doesn't map to the data you are trying to get from the reader

  "SELECT NextDest FROM Ship " ...;
  ...
  if (reader.Read())
  {
     SDestinationLbl.Text = reader["ShipID"].ToString();
     ODestinationLbl.Text = reader["OrderID"].ToString();
  }

The reader is throwing back an exception because it can't find "ShipDI" or "OrderID".

-Peter
peterlanoie.blog

psnow1985 · March 20th, 2008, 09:51 AM

Quote:

quote:Originally posted by planoie
I see two problems:

1) You are initializing 2 different SQL commands but only executing 1
   comm = new SqlCommand(...);
   ...
   comm = new SqlCommand(...);

2) In the query you are actually executing, you are selecting a single field which doesn't map to the data you are trying to get from the reader

  "SELECT NextDest FROM Ship " ...;
  ...
  if (reader.Read())
  {
     SDestinationLbl.Text = reader["ShipID"].ToString();
     ODestinationLbl.Text = reader["OrderID"].ToString();
  }

The reader is throwing back an exception because it can't find "ShipDI" or "OrderID".

-Peter
peterlanoie.blog

Cheers for the advice I'll play around with the code and come back with the results.

psnow1985 · March 20th, 2008, 10:31 AM

I have made some changes as per below. But now I get a "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll" error. At a guess I would say that I'm going about this the wrong way?

Code:

protected void ViewDestinationBtn_Click(object sender, EventArgs e)
    {

            SqlConnection conn;
            SqlCommand comm;
            SqlDataReader reader;
            string connectionString =
                ConfigurationManager.ConnectionStrings[
                "ShippingSystemConnectionString1"].ConnectionString;
            conn = new SqlConnection(connectionString);
            comm = new SqlCommand(
                "SELECT Destination FROM Order " +
                "WHERE OrderID = @OrderID", conn);
            comm.Parameters.Add("@OrderID", System.Data.SqlDbType.Int);
            comm.Parameters["@OrderID"].Value = DropDownList1.SelectedValue;

            SqlConnection conn2;
            SqlCommand comm2;
            conn2 = new SqlConnection(connectionString);
            comm2 = new SqlCommand(
                "SELECT NextDest FROM Ship " +
                "WHERE ShipID = @ShipID", conn);
            comm2.Parameters.Add("@ShipID", System.Data.SqlDbType.Int);
            comm2.Parameters["@ShipID"].Value = DropDownList2.SelectedValue;


            try
            {
                conn.Open();               
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    SDestinationLbl.Text = reader["NextDest"].ToString();

                }
                reader.Close();

                conn2.Open();
                reader = comm2.ExecuteReader();
                if (reader.Read())
                {
                    ODestinationLbl.Text = reader["Destination"].ToString();
                }
                reader.Close();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
                conn2.Close();
            }

    }

psnow1985 · March 20th, 2008, 01:51 PM

I guess that combining the two queries into one would be a better way? Oh and I have altered the two lines:

Code:

  SDestinationLbl.Text = reader["ShipID"].ToString();
        ODestinationLbl.Text = reader["OrderID"].ToString();

To say

Code:

  SDestinationLbl.Text = reader["NextDest"].ToString();
        ODestinationLbl.Text = reader["Destination"].ToString();

But with no luck so far.

planoie · March 20th, 2008, 02:57 PM

You'll need to post the details of the exception so we can tell where the problem is.

Generally you should try to do single operations when hitting databases. Get one value, then get another value. You can use the same connection instead of opening up two so you don't tie up more resources than you need to.

One shortcut you can use is the "using" keyword. It is basically a try/finally wrapper around a resource to ensure that the resource is properly disposed of if an exception is thrown. You could use it this way:

using(SqlConnection conn = new SqlConnection(connectionString)){
//do stuff in here with the connection
}

-Peter
peterlanoie.blog

psnow1985 · March 20th, 2008, 03:01 PM

Thanks, I'll give it a try. I'll start off like you said trying to get one value working first.

Thanks again,

Peter

psnow1985 · March 20th, 2008, 03:23 PM

Right I've tried to get only one value working. I know I can do it since I have created another page called EditCusmtomer.aspx which takes a value (CustomerID) from a dropdownbox and then returns the customers details in a series of text boxes. And since that is what needs to be done I'm sure I can do it. After abit more debugging the problem seems to be with the if (reader.Read()); line. I put a breakline where it says "comm.Parameters["@OrderIDSelected"].Value =" and use the "step over" button to go through the lines of code. When I am doing this I go to the immediate window and type "?Dropdownbox1.Text" to make sure that the value in Dropdownbox1 is the right one. When it hits that line and I press "Step Over" it jumps to the line where it says "catch". Throwing up the error "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll" in the Output window. Every

planoie · March 20th, 2008, 07:43 PM

It sounds like there's something wrong with the value from the dropdownlist control. You'll want to check that value and see what it's trying to assign to the parameter.

-Peter
peterlanoie.blog

psnow1985 · March 21st, 2008, 06:05 AM

Quote:

quote:Originally posted by planoie
It sounds like there's something wrong with the value from the dropdownlist control. You'll want to check that value and see what it's trying to assign to the parameter.

-Peter
peterlanoie.blog

Sorry for a stupid question, but how would I check the value and see what it's trying to assign to the parameter? Since I know how to check the value of the the dropdownbox whilst debugging. Do you mean the value of ODestinationLbl.Text? Below is a link to a screenshot showing the debugging screen of VS 2005 pro just before the next step over where the error occurs. In the immediate window I have tried to get the assigned value for ODestinationLbl.Text but all I get is " ".

http://img227.imageshack.us/img227/5884/sqlerrorju0.jpg

After I the next time I press step over and type ?ODestinationLbl.Text in the immediate window I still get " ".

Thanks for your help,

Peter