Error Message

Question

Error Message

Wanda

SSC Enthusiast

Points: 125
More actions
September 24, 2004 at 11:07 pm

#86615

I'm not sure if I'm posting this in the right place. I'm a newbie.
I get the following error message & I can't seem to fix it:
System.IndexOutOfRangeException: There is no row at position 0
Here is my code:
<%@ Page Language="VB" ASPCompat="True" Explicit="True" Debug="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim Connect As SqlConnection = New SqlConnection
Dim Adapter As SqlDataAdapter = New SqlDataAdapter
Dim CRCCB As SqlCommandBuilder
Dim CRCDS As DataSet = New DataSet
Dim ConnectString, SelectStatement, UpdateStatement, InsertStatement, DeleteStatement, GroupID As String
Dim Row As DataRow
Dim ItemNum, Counter As Integer

Sub Page_Load(Sender As Object, E As EventArgs)
'Identify which associate for whose information will be edited.
GroupID = Trim(Request.QueryString("Group"))
'If page is loaded before edits are made, pull associate data from database and use it to fill in form elements.
If Not Page.IsPostBack Then
  FillInGroupData()
End If
End Sub
'Repeatedly Used Functions and Subroutines
'-----------------------------------------
Sub ConnectToDatabase(PassedSelectStatement As String, DataSetTable As String)
'If the connection string ever changes, do it in the global.asax file.
Connect.ConnectionString = Application("ConnStrCRC")

'Establish the commands to database.
Adapter.SelectCommand = new SqlCommand(PassedSelectStatement, Connect)

'Allow commands to update, insert, and delete records to work if needed.
CRCCB = New SqlCommandBuilder(Adapter)

'Fill data set named in the function.
Adapter.Fill(CRCDS,DataSetTable)
End Sub
Function GetCorpID(AssociateNameSent As String) As String
Dim ThisCorpID As String = ""

'Grab the CorpID from the Associate Profile table.
SelectStatement = "SELECT asoc_corp_id, asoc_fst_nm + ' ' + asoc_lst_nm AS FULL_NM " & _
       "FROM t_crcap_assoc_prfl"
ConnectToDatabase(SelectStatement,"GetCorpID")
For Counter = 0 To CRCDS.Tables("GetCorpID").Rows.Count - 1
  If Trim(AssociateNameSent) = Trim(CRCDS.Tables("GetCorpID").Rows(Counter).Item("FULL_NM")) Then
   ThisCorpID = CRCDS.Tables("GetCorpID").Rows(Counter).Item("asoc_corp_id")

   CRCDS.Tables("GetCorpID").Clear
   Return ThisCorpID
  End If
Next
End Function
'Functions and Subroutines used BEFORE changes are made
'------------------------------------------------------
Sub FillInGroupData()
'Pull the correct group data from the Group Profile table.
SelectStatement = "SELECT * " & _
       "FROM t_crcap_assoc_prfl " & _
       "WHERE bus_grp_nm='" & GroupID & "'"
ConnectToDatabase(SelectStatement,"BusinessGroup")

'Insert data into Row element for in-memory access.
Row = CRCDS.Tables("BusinessGroup").Rows(0)
'Fill in various text boxes with group data.
GroupName.Text = Row.Item("bus_grp_nm")
' GroupDesc.Text = Row.Item("bus_grp_desc_x")

'Create a check box list for all possible consultants.
BindCheckBoxList()

'Pre-check the associate boxes that currently support this group.
For ItemNum=0 To AssociatesChBxList.Items.Count - 1
  AssociatesChBxList.Items(ItemNum).Selected = CheckSupportingAssociates(AssociatesChBxList.Items(ItemNum).Text)
Next
End Sub
Sub BindCheckBoxList()
'Pull all the assoiates names from Associate Profile table.
SelectStatement = "SELECT asoc_lst_nm, asoc_fst_nm + ' ' + asoc_lst_nm AS FULL_NM " & _
       "FROM t_crcap_assoc_prfl"
ConnectToDatabase(SelectStatement,"AssociatesList")

'Display the results in a checkbox list.
Dim Source As DataView = CRCDS.Tables("AssociatesList").DefaultView
Source.Sort = "asoc_lst_nm ASC"

AssociatesChBxList.DataSource = Source
AssociatesChBxList.DataBind()

'Clear DataSet table -- This is Dan standard practice
CRCDS.Tables("AssociatesList").Clear
End Sub
Function CheckSupportingAssociates(AssociateName As String) As Boolean
'Store Group ID in variable to be used later.
Dim ThisAssociate As String = GetCorpID(AssociateName)
'Check to see if this associate supports this group.
SelectStatement = "SELECT * " & _
       "FROM t_crcap_assoc_prfl " & _
       "WHERE bus_grp_nm='" & GroupID & "'" & _
       "AND asoc_corp_id='" & ThisAssociate & "'"
ConnectToDatabase(SelectStatement,"FoundGroup")

'If they do, return true so the box will be checked. Otherwise, return false.
If CRCDS.Tables("FoundGroup").Rows.Count = 1 Then
  CRCDS.Tables("FoundGroup").Clear
  Return True
Else
  CRCDS.Tables("FoundGroup").Clear
  Return False
End If
End Function
'Functions and Subroutines used AFTER changes are made
'-----------------------------------------------------
Sub Submit_Click(Sender As Object, E As EventArgs)
'Pull the current group data from the Group Profile table.
SelectStatement = "SELECT * " & _
       "FROM t_crcap_assoc_prfl " & _
       "WHERE bus_grp_nm='" & GroupID & "'"
ConnectToDatabase(SelectStatement,"GroupProfileSubmit")

'Use Row element for in-memory access.
Row = CRCDS.Tables("GroupProfileSubmit").Rows(0)

'Use the user-entered information in the text boxes to fill in Row.
Row.Item("bus_grp_nm") = GroupName.Text
Row.Item("bus_grp_desc_x") = GroupDesc.Text

'Update actual database.
Adapter.Update(CRCDS,"GroupProfileSubmit")

'Loop through checkbox list to see which boxes are selected and make appropriate changes to database.
For ItemNum=0 To AssociatesChBxList.Items.Count - 1
  If AssociatesChBxList.Items(ItemNum).Selected = True Then
   AddAssociateToGroup(AssociatesChBxList.Items(ItemNum).Text, True)
  Else
   AddAssociateToGroup(AssociatesChBxList.Items(ItemNum).Text, False)
  End If
Next

If CRCDS.HasErrors Then
    Message.Text = "There was an error placing your group info update."
Else
    Message.Text = "Your group info changes have been made."
    Submit.Enabled = False
  GroupName.Enabled = False
  GroupDesc.Enabled = False
  AssociatesChBxList.Enabled = False
End If
End Sub
Sub AddAssociateToGroup(AssociateName As String, BelongsToGroup As Boolean)
'Store associate ID in variable to be used later.
Dim ThisAssociate As String = GetCorpID(AssociateName)

'Pull record that links the group to the associate. This may not always exist.
SelectStatement = "SELECT * " & _
       "FROM t_crcap_assoc_prfl " & _
       "WHERE bus_grp_nm=" & GroupID & " " & _
       "AND asoc_corp_id='" & ThisAssociate & "'"
ConnectToDatabase(SelectStatement,"AssociateInGroup")

DefineDataAdapterCommands()

'If the associate does not belong to this group currently...
If CRCDS.Tables("AssociateInGroup").Rows.Count = 0 Then
  'should he or she be added?
  If BelongsToGroup = True Then
   'If yes, then create a new record.
   Row = CRCDS.Tables("AssociateInGroup").NewRow

   'Fill the record with the relevant information.
   Row.Item("bus_grp_nm") = GroupID
   Row.Item("asoc_corp_id") = ThisAssociate

   CRCDS.Tables("AssociateInGroup").Rows.Add(Row)
   'Update the database.
   Adapter.Update(CRCDS, "AssociateInGroup")
  End If
Else If CRCDS.Tables("AssociateInGroup").Rows.Count = 1 Then
  'If the associate is already listed as supporting this group...
  'should he or she continue supporting the group?
  If BelongsToGroup = False Then
   'If no, remove them from table, because they no longer belong to this group.
   CRCDS.Tables("AssociateInGroup").Rows(0).Delete

   Adapter.Update(CRCDS, "AssociateInGroup")
  End If
End If

CRCDS.Tables("AssociateInGroup").Clear
End Sub
Sub DefineDataAdapterCommands()
'Define the UPDATE SqlCommand
UpdateStatement = "UPDATE t_crcap_assoc_prfl " & _
              "SET bus_grp_nm = @bus_grp_nm, " & _
       "asoc_corp_id = @asoc_corp_id " & _
       "WHERE bus_grp_nm = @bus_grp_nm " & _
       "AND asoc_corp_id = @asoc_corp_id"
Adapter.UpdateCommand = new SqlCommand(UpdateStatement, Connect)
'Define the parameters specified in the UPDATE command.
Dim param As New SqlClient.SqlParameter("@bus_grp_nm", SqlDbType.Int)
Adapter.UpdateCommand.Parameters.Add(param)
param.SourceColumn = "bus_grp_nm"
param.SourceVersion = DataRowVersion.Original
Adapter.UpdateCommand.Parameters.Add("@asoc_corp_id", SqlDbType.NVarChar, 7, "asoc_corp_id")
Adapter.UpdateCommand.Parameters.Add("@bus_grp_nm", SqlDbType.Int, 3, "bus_grp_nm")
'Define the INSERT SqlCommand
InsertStatement = "INSERT t_crcap_assoc_prfl (bus_grp_nm,asoc_corp_id) " & _
       "VALUES (@bus_grp_nm, @asoc_corp_id)"
    Adapter.InsertCommand = new SqlCommand(InsertStatement, Connect)

    'Define the parameters specified in the INSERT command.
    Adapter.InsertCommand.Parameters.Add("@asoc_corp_id", SqlDbType.NVarChar, 7, "asoc_corp_id")
    Adapter.InsertCommand.Parameters.Add("@bus_grp_nm", SqlDbType.Int, 3, "bus_grp_nm")
'Define the DELETE SqlCommand
DeleteStatement = "DELETE t_crcap_assoc_prfl " & _
              "WHERE bus_grp_nm = @bus_grp_nm " & _
       "AND asoc_corp_id = @asoc_corp_id"
Adapter.DeleteCommand = new SqlCommand(DeleteStatement, Connect)

'Define the parameters specified in the DELETE command.
Dim deleteParam As New SqlClient.SqlParameter("@bus_grp_nm", SqlDbType.Int)
Adapter.DeleteCommand.Parameters.Add(deleteParam)
deleteParam.SourceColumn = "bus_grp_nm"
deleteParam.SourceVersion = DataRowVersion.Original
Adapter.DeleteCommand.Parameters.Add("@asoc_corp_id", SqlDbType.NVarChar, 7, "asoc_corp_id")
End Sub
</script>
<html>
<head>
<link rel="stylesheet" href="crc.css" type="text/css">
<title>Consulting Resource Center :: <%= GroupName.Text %></title>
</head>
<body bgcolor="#52718C">
<table width="90%" border="1" bordercolor="#EFE3B5" cellspacing="0" bgcolor="#FFFFFF" cellpadding="5" align="center">
<tr>
    <td>
<p><font face="Tahoma" size="5"><b>Consulting Resource Center :: <font color="#52718C"><%= GroupName.Text %></font></b></font></p>
<asp:Label id="Instructions" Font-Name="Tahoma" Font-Size="11pt" runat="server">Please make your changes as completely as possible and click the button to submit. If you don't want to make edits to this group, simply <a href="view_bus_groups.aspx">click here to go back to the previous page</a> or close the window.</asp:Label><br><br>
<form runat="server">
   <table width="100%" cellspacing="5" cellpadding="3" border="1" bordercolor="#FFFFFF">
    <tr valign="middle" bordercolor="#52718C" bgcolor="#D2DEE3">
   <td align="center">
    <font face="Tahoma" size="2"><b>Group Name:</b></font><br>
    <asp:textbox id="GroupName" font-name="Tahoma" runat="server" textmode="SingleLine" columns="50" /><br>
    <asp:requiredfieldvalidator Font-Name="Tahoma" Display="Dynamic" Font-Size="10 pt" ForeColor="#DD8F34" id="GroupNameRequired" ControlToValidate="GroupName" runat="server">Please enter the Group Name.</asp:requiredfieldvalidator>
    <asp:regularexpressionvalidator id="GroupNameLimit" Display="Dynamic" Font-Name="Tahoma" Font-Size="10 pt" ForeColor="#DD8F34" ControlToValidate="GroupName" ValidationExpression="[\s\S]{0,60}" runat="server">Please limit the Group Name to 60 characters.
    </asp:regularexpressionvalidator>
    </tr>
    <tr valign="middle" bordercolor="#52718C" bgcolor="#D2DEE3">
    <td align="center">
    <font face="Tahoma" size="2"><b>Group Description:</b></font><br>
    <asp:textbox id="GroupDesc" font-name="Tahoma" runat="server" textmode="MultiLine" rows="5" columns="60" /><br>
    <asp:requiredfieldvalidator id="GroupDescRequired" Display="Dynamic" Font-Name="Tahoma" Font-Size="10 pt" ForeColor="#DD8F34" ControlToValidate="GroupDesc" runat="server">Please enter the group's description</asp:requiredfieldvalidator>
    <asp:regularexpressionvalidator id="GroupDescLimit" Display="Dynamic" Font-Name="Tahoma" Font-Size="10 pt" ForeColor="#DD8F34" ControlToValidate="GroupDesc" ValidationExpression="[\s\S]{0,900}" runat="server">Limit description to 900 characters</asp:regularexpressionvalidator>
   </td>
    </tr>
    <tr valign="middle" bordercolor="#52718C" bgcolor="#D2DEE3">
   <td align="center">
    <font face="Tahoma" size="2"><b>Supporting Associates:</b></font><br>
    <asp:CheckBoxList RepeatColumns="4" AutoPostBack="false" Font-Name="Tahoma" Font-Size="10 pt" DataValueField="FULL_NM" ID="AssociatesChBxList" runat="server" TextAlign="right" />
   </td>
    </tr>
          <tr>
            <td align="center">
    <asp:button id="Submit" Font-Name="tahoma" runat="server" text="Post this Group's Info" OnClick="Submit_Click" /><br>
                <asp:label id="Message" Font-Name="tahoma" ForeColor="#DD8F34" runat="server" font-italic="true" font-size="13 pt" />
   </td>
          </tr>
        </table>
</form>
<p align="center"><font face="Tahoma" size="2"><a href="javascript:void(0)" onClick="opener.location.href=opener.location.href;parent.close()">close window</a></font></p>
</td>
</tr>
</table>
Thanks...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

vadba SSChampion Points: 11133 More actions · Answer 1

You don't say where the error is occurring. However, you have two procedures that don't check for the existance of at least 1 row following a SELECT statement:

Sub FillInGroupData()

Sub Submit_Click(Sender As Object, E As EventArgs)

Add an If statement before trying to access a row:

If CRCDS.Tables("BusinessGroup").Rows.Count > 0 Then

'Retrieved at least one row

Else

'No rows retrieved

End If

Sub FillInGroupData()

'Pull the correct group data from the Group Profile table.

SelectStatement = "SELECT * " & _

"FROM t_crcap_assoc_prfl " & _

"WHERE bus_grp_nm='" & GroupID & "'"

ConnectToDatabase(SelectStatement,"BusinessGroup")

If CRCDS.Tables("BusinessGroup").Rows.Count > 0 Then

'Insert data into Row element for in-memory access.

Row = CRCDS.Tables("BusinessGroup").Rows(0)

Else

...

End If

Sub Submit_Click(Sender As Object, E As EventArgs)

'Pull the current group data from the Group Profile table.

SelectStatement = "SELECT * " & _

"FROM t_crcap_assoc_prfl " & _

"WHERE bus_grp_nm='" & GroupID & "'"

ConnectToDatabase(SelectStatement,"GroupProfileSubmit")

If CRCDS.Tables("GroupProfileSubmit").Rows.Count > 0 Then

'Use Row element for in-memory access.

Row = CRCDS.Tables("GroupProfileSubmit").Rows(0)

Else

...

End If