I have tested the procedure inside the database and it is working
fine. The table inside the database has data that matches with the
parameter.
I have another procedure which adds another row to the table, which is
working fine inside the programming, and in the database.
However, I still cannot figure out what am I doing wrong with the
DataGrid configurations OR Database OR DataAdapter OR
SqlSelectCommand ... I really don't know yet and I have spent a lot of
time in this issue.
**On page Load a different page calls the method RefreshDataCriteria,
which it supposes to refresh the data in the DataGrid***
I have other pages that do the same thing:
when the user clicks on a certain link, in the main page, the link
makes visible the control ( .ascx ) and then calls the function to
refresh the data of the DataGrid. The other pages are working fine,
except for this one.
I have tried with:
Private Sub Page_Load
(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
If Not IsPostBack Then
RefreshDataCriteria()
End If
End Sub
But nothing happens.
Below is additional information. Any help is extremely appreciated:
----------------------------------------------------------
Code inside the HTML part:
Code: ( html4strict )
<asp:datagrid id=dataGridCriteria Width="466px" runat="server" Font-
Size="XX-Small" DataKeyField="CourseCriteriaID"
DataMember="procWebSelectCourseCriteriaForSyllabus " DataSource="<%#
DtsCourseCriterias31 %>" BorderColor="#3366CC" BorderWidth="1px"
BackColor="White" CellPadding="4" BorderStyle="None"
AutoGenerateColumns="False" PageSize="5" Font-Names="sans-serif"
AllowPaging="True" Height="120px">
<SelectedItemStyle Font-Bold="True"
ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
<ItemStyle Font-Size="XX-Small" Font-Names="Arial"
Font-Bold="True" ForeColor="#003399" BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="XX-Small" Font-Names="Arial"
Font-Bold="True" ForeColor="#CCCCFF" BackColor="#003399"></
HeaderStyle>
<FooterStyle ForeColor="#003399" BackColor="#99CCCC"></
FooterStyle>
<Columns>
<asp:BoundColumn DataField="ProfOrgCriteriaID"
HeaderText="ProfOrgCriteriaID"></asp:BoundColumn>
<asp:BoundColumn DataField="ProfOrgCriteriaCode"
HeaderText="ProfOrgCriteriaCode"></asp:BoundColumn>
<asp:BoundColumn DataField="ProfOrgCriteriaDescr"
HeaderText="ProfOrgCriteriaDescr"></asp:BoundColumn>
<asp:BoundColumn DataField="CourseCriteriaID"
HeaderText="CourseCriteriaID"></asp:BoundColumn>
<asp:ButtonColumn Text="Delete" CommandName="Delete">
<HeaderStyle Width="50px"></HeaderStyle>
</asp:ButtonColumn>
</Columns>
<PagerStyle NextPageText="Next Page"
PrevPageText="Previous Page" HorizontalAlign="Center"
ForeColor="#003399"
BackColor="#99CCCC"></PagerStyle>
</asp:datagrid>
-----------------------------------------------------------------------
Procedure:
Code: ( text )
CREATE PROCEDURE dbo.procWebSelectCourseCriteriaForSyllabus
@CourseCode varchar(20)
AS
SELECT ProfOrgCriteria.ProfOrgCriteriaID,
ProfOrgCriteria.ProfOrgCriteriaCode,ProfOrgCriteri
a.ProfOrgCriteriaDescr,CourseCriteria.CourseCriter iaID
FROM CourseCriteria INNER JOIN
ProfOrgCriteria ON
CourseCriteria.ProfOrgCriteriaID = ProfOrgCriteria.ProfOrgCriteriaID
WHERE (CourseCriteria.CourseCode = @CourseCode)
GO
---------------------------------------------------------
DataSet:
Code: ( html4strict )
<?xml version="1.0" standalone="yes"?>
<xs:schema id="dtsCourseCriterias3" targetNamespace="http://
www.tempuri.org/dtsCourseCriterias3.xsd" xmlns:mstns="http://
www.tempuri.org/dtsCourseCriterias3.xsd" xmlns="http://www.tempuri.org/
dtsCourseCriterias3.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="dtsCourseCriterias3" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="procWebSelectCourseCriteriaForSyllabus">
<xs:complexType>
<xs:sequence>
<xs:element name="ProfOrgCriteriaID"
msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProfOrgCriteriaCode" type="xs:string" /
><xs:element name="ProfOrgCriteriaDescr"
type="xs:string" />
<xs:element name="CourseCriteriaID"
msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ProfOrgCriteriaID"
msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProfOrgCriteriaCode" type="xs:string" /
><xs:element name="ProfOrgCriteriaDescr"
type="xs:string" />
<xs:element name="CourseCriteriaID"
msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="dtsCourseCriterias3Key1"
msdata:PrimaryKey="true">
<xs:selector xpath=".//
mstns:procWebSelectCourseCriteriaForSyllabus" />
<xs:field xpath="mstns:ProfOrgCriteriaID" />
<xs:field xpath="mstns:CourseCriteriaID" />
</xs:unique>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Table" />
<xs:field xpath="mstns:ProfOrgCriteriaID" />
<xs:field xpath="mstns:CourseCriteriaID" />
</xs:unique>
</xs:element>
</xs:schema>
-----------------------------------------
VB.NET Code:
Code: ( text )
Public Class CourseCriteriaGrid
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()Private Sub
InitializeComponent()
Dim configurationAppSettings As
System.Configuration.AppSettingsReader = New
System.Configuration.AppSettingsReader
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.sqlSelectCourseCriteria = New
System.Data.SqlClient.SqlCommand
Me.SqlCommandAddCourseCriteria = New
System.Data.SqlClient.SqlCommand
Me.DtsCourseCriterias31 = New
UniversalSyllabus.dtsCourseCriterias3
Me.SqlDataAdapterCourseCriteria = New
System.Data.SqlClient.SqlDataAdapter
Me.sqlCommandDeleteCourseCriteria = New
System.Data.SqlClient.SqlCommand
CType(Me.DtsCourseCriterias31,
System.ComponentModel.ISupportInitialize).BeginIni t()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
CType(configurationAppSettings.GetValue("SqlConnec tion1.ConnectionString",
GetType(System.String)), String)
'
'sqlSelectCourseCriteria
'
Me.sqlSelectCourseCriteria.CommandText =
"[procWebSelectCourseCriteriaForSyllabus]"
Me.sqlSelectCourseCriteria.CommandType =
System.Data.CommandType.StoredProcedure
Me.sqlSelectCourseCriteria.Connection = Me.SqlConnection1
Me.sqlSelectCourseCriteria.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.sqlSelectCourseCriteria.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CourseCode",
System.Data.SqlDbType.VarChar, 20))
'
'SqlCommandAddCourseCriteria
'
Me.SqlCommandAddCourseCriteria.CommandText =
"[procWebInsertCourseCriteria]"
Me.SqlCommandAddCourseCriteria.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlCommandAddCourseCriteria.Connection = Me.SqlConnection1
Me.SqlCommandAddCourseCriteria.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommandAddCourseCriteria.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CourseCode",
System.Data.SqlDbType.VarChar, 20))
Me.SqlCommandAddCourseCriteria.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProfOrgCriter iaID",
System.Data.SqlDbType.Int, 4))
'
'DtsCourseCriterias31
'
Me.DtsCourseCriterias31.DataSetName = "dtsCourseCriterias3"
Me.DtsCourseCriterias31.EnforceConstraints = False
Me.DtsCourseCriterias31.Locale = New
System.Globalization.CultureInfo("en-US")
'
'SqlDataAdapterCourseCriteria
'
Me.SqlDataAdapterCourseCriteria.SelectCommand =
Me.sqlSelectCourseCriteria
'
'sqlCommandDeleteCourseCriteria
'
Me.sqlCommandDeleteCourseCriteria.CommandText =
"[procWebDeleteCourseCriteria]"
Me.sqlCommandDeleteCourseCriteria.CommandType =
System.Data.CommandType.StoredProcedure
Me.sqlCommandDeleteCourseCriteria.Connection =
Me.SqlConnection1
Me.sqlCommandDeleteCourseCriteria.Parameters.Add(N ew
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.sqlCommandDeleteCourseCriteria.Parameters.Add(N ew
System.Data.SqlClient.SqlParameter("@CourseCriteri aID",
System.Data.SqlDbType.Int, 4))
CType(Me.DtsCourseCriterias31,
System.ComponentModel.ISupportInitialize).EndInit( )
End Sub
Protected WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
' Protected WithEvents DtsCourseCriterias1 As
UniversalSyllabus.dtsCourseCriterias3
Protected WithEvents sqlSelectCourseCriteria As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlCommandAddCourseCriteria As
System.Data.SqlClient.SqlCommand
Protected WithEvents DtsCourseCriterias31 As
UniversalSyllabus.dtsCourseCriterias3
Protected WithEvents SqlDataAdapterCourseCriteria As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents dataGridCriteria As
System.Web.UI.WebControls.DataGrid
Protected WithEvents buttonAddCriteria As
System.Web.UI.WebControls.Button
Protected WithEvents sqlCommandDeleteCourseCriteria As
System.Data.SqlClient.SqlCommand
'NOTE: The following placeholder declaration is required by the
Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form
Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
End Sub
------------------------------
'NEXT IS THE METHOD THAT SUPPOSES TO BIND DATA TO THE DATAGRID
------------------------------
Public Sub RefreshDataCriteria()
Me.sqlSelectCourseCriteria.Parameters("@CourseCode ").Value =
Session("CourseCode")
Me.SqlDataAdapterCourseCriteria.Fill(Me.DtsCourseC riterias31)
Me.dataGridCriteria.DataBind()
End Sub
---------------------------------
---------------------------------
Public Event addCriteria()
Private Sub buttonAddCriteria_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles buttonAddCriteria.Click
RaiseEvent addCriteria()
End Sub
Private Sub dataGridCriteria_ItemDataBound(ByVal sender As
System.Object, ByVal e As
System.Web.UI.WebControls.DataGridItemEventArgs)
'Enable javascript to alert about the delete
If e.Item.ItemType <ListItemType.Header And _
e.Item.ItemType <ListItemType.Footer And e.Item.ItemType <>
ListItemType.Pager Then
Dim deleteButton2 As LinkButton =
e.Item.Cells(2).Controls(0)
deleteButton2.Attributes("onclick") = "javascript:return "
& _
"confirm('Are you sure you want to delete Course Criteria:
" & _
DataBinder.Eval(e.Item.DataItem, "CriteriaDescription") &
" ?')"
End If
End Sub
Private Sub dataGridCriteria_PageIndexChanged(ByVal source As
System.Object, ByVal e As
System.Web.UI.WebControls.DataGridPageChangedEvent Args)
Me.dataGridCriteria.CurrentPageIndex = e.NewPageIndex
RefreshDataCriteria()
End Sub
Private Sub dataGridCriteria_ItemCommand(ByVal source As
System.Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs )
Select Case (CType(e.CommandSource, LinkButton)).CommandName
Case "Delete"
Me.sqlCommandDeleteCourseCriteria.Parameters("@Cou rseCriteriaID").Value
= Me.dataGridCriteria.DataKeys(e.Item.ItemIndex)
Me.SqlConnection1.Open()
Me.sqlCommandDeleteCourseCriteria.ExecuteNonQuery( )
Me.SqlConnection1.Close()
Me.dataGridCriteria.EditItemIndex = -1
If Me.dataGridCriteria.CurrentPageIndex >= 1 And
Me.dataGridCriteria.Items.Count = 1 Then
Me.dataGridCriteria.CurrentPageIndex =
Me.dataGridCriteria.CurrentPageIndex - 1
End If
RefreshDataCriteria()
End Select
End Sub
End Class
-------------------------------------
Thank you!,
NMM
