MadhuMenong
asked on
ASP.Net GridView With ObjectDataSource. GridView not displaying data.
Dear Friends,
I am using GridView with ObjectDataSource to display some suppliers information. But inspite of all efforts the gridview is not displaying any data. Though in the debug mode I could see 3 records being fetched from the database and Totalrow count at 14. I fail to understand what the problem is and would like some assistance in the matter. The complete code is given below:
.ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SupplierInfo.a spx.cs" Inherits="OMSWeb.SupplierI nfo" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div id="divSearch" runat="server">
<fieldset>
<legend>Supplier Search</legend>
<table id="tblSearch" runat="server" cellpadding="5" cellspacing="0" border="0" width="90%">
<tr>
<td>
<asp:Label ID="lblSupplierNameS" runat="server" Text="Supplier Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierNames" runat="server" MaxLength="50"></asp:TextB ox>
</td>
<td>
<asp:Label ID="lblSupplierEmailS" runat="server" Text="Supplier Email"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierEmailS" runat="server" MaxLength="50"></asp:TextB ox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblCountryS" runat="server" Text="Country"></asp:Label >
</td>
<td>
<asp:DropDownList ID="ddlCountryS" runat="server" DataTextField="CountryName " DataValueField="CountryID" >
</asp:DropDownList>
</td>
<td>
<asp:Label ID="lblCityS" runat="server" Text="City"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlCityS" runat="server" DataTextField="CityName" DataValueField="CityID">
</asp:DropDownList>
</td>
</tr>
</table>
</fieldset>
</div>
<br />
<div id="divEdit" runat="server">
<fieldset>
<legend>Supplier Info</legend>
<table id="tblEdit" runat="server" cellpadding="2" cellspacing="0" border="0" width="60%">
<tr>
<td>
<asp:Label ID="lblSupplierID" runat="server" Text="Supplier ID :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierID" runat="server" MaxLength="50" ReadOnly="true"></asp:Text Box>
</td>
<td>
<asp:Label ID="lblSupplierName" runat="server" Text="Supplier Name :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierName" runat="server" MaxLength="50"></asp:TextB ox>
</td>
<td width="25%" rowspan="3">
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblAddress1" runat="server" Text="Address :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbAddress1" runat="server" MaxLength="50"></asp:TextB ox>
</td>
<td>
<asp:Label ID="lblAddress2" runat="server" Text="Address :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbAddress2" runat="server" MaxLength="50"></asp:TextB ox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblCountry" runat="server" Text="Country"></asp:Label >
</td>
<td>
<asp:DropDownList ID="ddlCountry" runat="server" DataTextField="CountryName " DataValueField="CountryID" >
</asp:DropDownList>
</td>
<td>
<asp:Label ID="lblCity" runat="server" Text="City"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" DataTextField="CityName" DataValueField="CityID">
</asp:DropDownList>
</td>
</tr>
</table>
</fieldset>
</div>
<br />
<br />
<div id="divButton" runat="server">
<table id="tblButton" runat="server" width="90%" cellpadding="2" cellspacing="0"
border="0">
<tr>
<td width="10%">
</td>
<td>
<asp:Button ID="btnNew" runat="server" Text="New" />
</td>
<td>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
</td>
<td>
<asp:Button ID="btnReset" runat="server" Text="Reset" />
</td>
<td>
<asp:Button ID="btnUpdate" runat="server" Text="Update" />
</td>
<td>
<asp:Button ID="btnDelete" runat="server" Text="Delete" />
</td>
<td>
<asp:Button ID="btnCancel" runat="server" Text="Cancel" />
</td>
<td width="10%">
</td>
</tr>
</table>
</div>
<br />
<br />
<div id="divGrid" runat="server">
<asp:GridView ID="gvGrid" runat="server" AllowPaging="true" AllowSorting="false"
PageSize="4" DataKeyNames="SupplierID" AutoGenerateColumns="false " CellPadding="2"
DataSourceID="MyDataSource " onrowdatabound="gvGrid_Row DataBound" >
</asp:GridView>
<asp:ObjectDataSource ID="MyDataSource" runat="server" TypeName="OMS.DAL.Supplier BL"
SelectCountMethod="SelectC ountMethod " SelectMethod="SelectMethod " EnablePaging="true"
StartRowIndexParameterName ="StartRow " MaximumRowsParameterName=" MaxRows">
<SelectParameters>
<asp:Parameter Name="StartRow" Type="Int32" />
<asp:Parameter Name="MaxRows" Type="Int32" />
<asp:ControlParameter ControlID="tbSupplierNames " Name="supplierName" PropertyName="Text" />
<asp:ControlParameter ControlID="tbSupplierEmail S" Name="supplierEmail" PropertyName="Text" />
<asp:ControlParameter ControlID="ddlCountryS" Name="countryID" PropertyName="SelectedValu e" />
<asp:ControlParameter ControlID="ddlCityS" Name="cityID" PropertyName="SelectedValu e" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
__________________________ __________ __________ __________ __________
.ASPX.CS
using System;
using System.Collections.Generic ;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace OMSWeb
{
public partial class SupplierInfo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void gvGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
// This part was used for debugging purpose and is not relevant and will be deleted.
// This part of the code is not reached, found during debugging.
int xy = 10;
int zz = xy;
int ab = xy + zz;
int bd = ab + xy + zz;
}
}
}
__________________________ __________ __________ __________ __________ __________
SupplerBL.cs
using System;
using System.Collections.Generic ;
using System.Linq;
using System.Text;
using OMS.Entity;
using OMS.DAL;
namespace OMS.DAL
{
public class SupplierBL
{
public SupplierBL()
{
}
private int totalRows;
public SupplierCollection SelectMethod(int MaxRows, int StartRow, string supplierName, string supplierEmail, int countryID, int cityID)
{
try
{
Supplier sup = new Supplier();
SupplierCollection suc = new SupplierCollection();
sup.SupplierName = supplierName;
sup.SupplierEmail = supplierEmail;
sup.SupplierCountry = countryID;
sup.SupplierCity = cityID;
SupplierDataSource sd = new SupplierDataSource();
int totRows = 0;
suc = sd.SupplierSearch(sup, StartRow, MaxRows, ref totRows);
totalRows = totRows;
return suc;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
}
}
public int SelectCountMethod(int MaxRows, int StartRow, string supplierName, string supplierEmail, int countryID, int cityID)
{
return totalRows;
}
}
}
__________________________ __________ __________ __________ __________ __________
SupplierDataSource.cs
using System;
using System.Collections.Generic ;
using System.Collections.ObjectM odel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OMS.Entity;
namespace OMS.DAL
{
public class SupplierDataSource
{
private SqlConnection con;
private SqlCommand cmd;
private DataSet ds;
private SqlDataAdapter da;
private SqlDataReader dr;
private string connectionString;
public SupplierDataSource()
{
try
{
con = new SqlConnection(Configuratio nManager.C onnectionS trings["OM SCS"].ToSt ring());
connectionString = ConfigurationManager.Conne ctionStrin gs["OMSCS" ].ToString ();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Int32 SupplierAdd(Supplier sup)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt ring))
{
using (SqlCommand cmd1 = new SqlCommand("SupplierInsert ", con1))
{
cmd1.CommandType = CommandType.StoredProcedur e;
cmd1.Parameters.AddWithVal ue("@Suppl ierID", sup.SupplierID);
cmd1.Parameters.AddWithVal ue("@Suppl ierName", sup.SupplierName);
cmd1.Parameters.AddWithVal ue("@Suppl ierAddress 1", sup.SupplierAddress1);
cmd1.Parameters.AddWithVal ue("@Suppl ierAddress 2", sup.SupplierAddress2);
cmd1.Parameters.AddWithVal ue("@Suppl ierCity", sup.SupplierCity);
cmd1.Parameters.AddWithVal ue("@Suppl ierCountry ", sup.SupplierCountry);
cmd1.Parameters.AddWithVal ue("@Suppl ierPinCode ", sup.SupplierPinCode);
cmd1.Parameters.AddWithVal ue("@Suppl ierEmail", sup.SupplierEmail);
cmd1.Parameters.AddWithVal ue("@Suppl ierTelepho ne", sup.SupplierTelephone);
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public int SupplierUpdate(Supplier sup)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt ring))
{
using (SqlCommand cmd1 = new SqlCommand("SupplierUpdate ", con1))
{
cmd1.CommandType = CommandType.StoredProcedur e;
cmd1.Parameters.AddWithVal ue("@Suppl ierID", sup.SupplierID);
cmd1.Parameters.AddWithVal ue("@Suppl ierName", sup.SupplierName);
cmd1.Parameters.AddWithVal ue("@Suppl ierAddress 1", sup.SupplierAddress1);
cmd1.Parameters.AddWithVal ue("@Suppl ierAddress 2", sup.SupplierAddress2);
cmd1.Parameters.AddWithVal ue("@Suppl ierCity", sup.SupplierCity);
cmd1.Parameters.AddWithVal ue("@Suppl ierCountry ", sup.SupplierCountry);
cmd1.Parameters.AddWithVal ue("@Suppl ierPinCode ", sup.SupplierPinCode);
cmd1.Parameters.AddWithVal ue("@Suppl ierEmail", sup.SupplierEmail);
cmd1.Parameters.AddWithVal ue("@Suppl ierTelepho ne", sup.SupplierTelephone);
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public int SupplierDelete(int supID)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt ring))
{
using (SqlCommand cmd1 = new SqlCommand("SupplierDelete ", con1))
{
cmd1.CommandType = CommandType.StoredProcedur e;
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public Supplier SupplierFetchByID(int supID)
{
Supplier sup = new Supplier();
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt ring))
{
using (SqlCommand cmd1 = new SqlCommand("SupplierFetchB yID", con1))
{
cmd1.CommandType = CommandType.StoredProcedur e;
cmd1.Parameters.AddWithVal ue("Suppli erID", supID);
con1.Open();
//Int32 rowsAffected = cmd1.ExecuteNonQuery();
SqlDataReader dr = cmd1.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
sup.SupplierID = dr.GetInt32(0);
sup.SupplierName = dr.GetString(1);
sup.SupplierAddress1 = dr.GetString(2);
sup.SupplierAddress2 = dr.GetString(3);
sup.SupplierCity = dr.GetInt32(4);
sup.SupplierCountry = dr.GetInt32(5);
sup.SupplierPinCode = dr.GetString(6);
sup.SupplierEmail = dr.GetString(7);
sup.SupplierTelephone = dr.GetString(8);
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return sup;
}
public SupplierCollection SupplierSearch(Supplier sup, int startRowIndex, int maximumRows, ref int totalRows)
{
SupplierCollection suc = new SupplierCollection();
Supplier su = new Supplier();
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt ring))
{
using (SqlCommand cmd1 = new SqlCommand("SupplierSearch ", con1))
{
cmd1.CommandType = CommandType.StoredProcedur e;
//cmd1.Parameters.AddWithV alue("@Sup plierID", sup.SupplierID);
cmd1.Parameters.AddWithVal ue("@Suppl ierName", sup.SupplierName);
// cmd1.Parameters.AddWithVal ue("@Suppl ierAddress 1", sup.SupplierAddress1);
//cmd1.Parameters.AddWithV alue("@Sup plierAddre ss2", sup.SupplierAddress2);
cmd1.Parameters.AddWithVal ue("@Suppl ierCity", sup.SupplierCity);
cmd1.Parameters.AddWithVal ue("@Suppl ierCountry ", sup.SupplierCountry);
// cmd1.Parameters.AddWithVal ue("@Suppl ierPinCode ", sup.SupplierPinCode);
cmd1.Parameters.AddWithVal ue("@Suppl ierEmail", sup.SupplierEmail);
cmd1.Parameters.AddWithVal ue("@Start RowIndex", startRowIndex);
cmd1.Parameters.AddWithVal ue("@Maxim umRows", maximumRows);
// The Below OutParameter is used to retrieve the output parameters from sqldatareader.
// Any other style failed in retrieving the output parameter values.
SqlParameter OutParameter = new SqlParameter("@TotalRows", SqlDbType.Int, 4);
OutParameter.Direction = ParameterDirection.Output;
cmd1.Parameters.Add(OutPar ameter);
//cmd1.Parameters.Add("@To talRows", SqlDbType.Int);
//cmd1.Parameters["@TotalR ows"].Dire ction = ParameterDirection.Output;
//cmd1.Parameters["@TotalR ows"].Valu e = 0;
//cmd1.Parameters.AddWithV alue("@Tot alRows", 0);
SqlDataReader dr;
con1.Open();
dr = cmd1.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
su = new Supplier();
su.SupplierID = dr.GetInt32(0);
su.SupplierName = dr.GetString(1);
su.SupplierAddress1 = dr.GetString(2);
su.SupplierAddress2 = dr.GetString(3);
su.SupplierCity = dr.GetInt32(4);
su.SupplierCountry = dr.GetInt32(5);
su.SupplierPinCode = dr.GetString(6);
su.SupplierEmail = dr.GetString(7);
su.SupplierTelephone = dr.GetString(8);
suc.Add(su);
}
}
dr.Close();
// totalRows = int.Parse(cmd1.Parameters[ "@TotalRow s"].ToStri ng());
totalRows = (int)OutParameter.Value;
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
}
}
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Stored Procedure
USE [OMS]
GO
/****** Object: StoredProcedure [dbo].[SupplierSearch] Script Date: 11/25/2010 12:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SupplierSearch]
(
@SupplierName VARCHAR(50) = NULL, -- default to NULL
@SupplierCity INT = NULL,
@SupplierCountry INT = NULL,
@SupplierEmail VARCHAR(50) = NULL,
@StartRowIndex INT,
@MaximumRows INT,
@TotalRows INT OUTPUT
)
AS
BEGIN
DECLARE @first_id int, @startRow INT
DECLARE @StringCity VARCHAR(20)
DECLARE @StringCountry VARCHAR(20)
IF @StartRowIndex > 0
SET @StartRowIndex = (@StartRowIndex - 1) * @MaximumRows
IF @StartRowIndex = 0
SET @StartRowIndex = 1
--PRINT @StartRowIndex
SET ROWCOUNT @StartRowIndex
IF RTRIM(@SupplierName)='' OR @SupplierName IS NULL
SET @SupplierName = '%'
ELSE
SET @SupplierName = @SupplierName + '%'
IF RTRIM(@SupplierEmail)='' OR @SupplierEmail IS NULL
SET @SupplierEmail = '%'
ELSE
SET @SupplierEmail = @SupplierEmail + '%'
IF RTRIM(@SupplierCity)='' OR @SupplierCity = 0 OR @SupplierCity IS NULL
SET @StringCity = '%'
ELSE
SET @StringCity = CONVERT(VARCHAR(20),@Suppl ierCity)
IF RTRIM(@SupplierCountry)='' OR @SupplierCountry = 0 OR @SupplierCountry IS NULL
SET @StringCountry = '%'
ELSE
SET @StringCountry = CONVERT(VARCHAR(20),@Suppl ierCountry )
-- Append % for LIKE operator search (if not NULL)
-- IF @SupplierName IS NULL
-- SET @SupplierName = @SupplierName + '%'
--
-- IF @SupplierEmail IS NULL
-- SET @SupplierEmail = @SupplierEmail + '%'
--
-- PRINT Convert(varchar(30),@Start RowIndex) + ' - @StartRowIndex'
-- PRINT Convert(varchar(30),@Maxim umRows) + ' - @MaximumRows'
--
-- PRINT Convert(varchar(30),@Suppl ierName) + ' - @SupplierName'
-- PRINT Convert(varchar(30),@Strin gCity) + ' - @SupplierCity'
-- PRINT Convert(varchar(30),@Strin gCountry) + ' - @StringCountry'
-- PRINT Convert(varchar(30),@Suppl ierEmail) + ' - @SupplierEmail'
--
SET ROWCOUNT @StartRowIndex
SELECT @first_id = SupplierID FROM Supplier Where
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
--(SupplierCity = IsNull(@SupplierCity,Suppl ierCity)) AND
--(SupplierCountry = IsNull(@SupplierCountry,Su pplierCoun try)) AND
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)
ORDER BY SupplierID
-- PRINT Convert(varchar(30),@first _id) + ' - First ID'
SET ROWCOUNT @MaximumRows
SELECT * FROM Supplier WHERE
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
-- (SupplierCity = IsNull(@SupplierCity,Suppl ierCity)) AND
-- (SupplierCountry = IsNull(@SupplierCountry,Su pplierCoun try)) AND
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail) AND
(SupplierID > @first_id) ORDER BY SupplierID
SET ROWCOUNT 0
SELECT @TotalRows = COUNT(*) FROM Supplier
WHERE
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
-- (SupplierCity = IsNull(@SupplierCity,Suppl ierCity)) AND
-- (SupplierCountry = IsNull(@SupplierCountry,Su pplierCoun try)) AND
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)
PRINT Convert(varchar(30),@Total Rows) + ' - Total Rows'
END
__________________________ __________ __________ _________
Please help sort this issue. Thank you very much.
I am using GridView with ObjectDataSource to display some suppliers information. But inspite of all efforts the gridview is not displaying any data. Though in the debug mode I could see 3 records being fetched from the database and Totalrow count at 14. I fail to understand what the problem is and would like some assistance in the matter. The complete code is given below:
.ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SupplierInfo.a
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div id="divSearch" runat="server">
<fieldset>
<legend>Supplier Search</legend>
<table id="tblSearch" runat="server" cellpadding="5" cellspacing="0" border="0" width="90%">
<tr>
<td>
<asp:Label ID="lblSupplierNameS" runat="server" Text="Supplier Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierNames" runat="server" MaxLength="50"></asp:TextB
</td>
<td>
<asp:Label ID="lblSupplierEmailS" runat="server" Text="Supplier Email"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierEmailS" runat="server" MaxLength="50"></asp:TextB
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblCountryS" runat="server" Text="Country"></asp:Label
</td>
<td>
<asp:DropDownList ID="ddlCountryS" runat="server" DataTextField="CountryName
</asp:DropDownList>
</td>
<td>
<asp:Label ID="lblCityS" runat="server" Text="City"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlCityS" runat="server" DataTextField="CityName" DataValueField="CityID">
</asp:DropDownList>
</td>
</tr>
</table>
</fieldset>
</div>
<br />
<div id="divEdit" runat="server">
<fieldset>
<legend>Supplier Info</legend>
<table id="tblEdit" runat="server" cellpadding="2" cellspacing="0" border="0" width="60%">
<tr>
<td>
<asp:Label ID="lblSupplierID" runat="server" Text="Supplier ID :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierID" runat="server" MaxLength="50" ReadOnly="true"></asp:Text
</td>
<td>
<asp:Label ID="lblSupplierName" runat="server" Text="Supplier Name :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbSupplierName" runat="server" MaxLength="50"></asp:TextB
</td>
<td width="25%" rowspan="3">
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblAddress1" runat="server" Text="Address :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbAddress1" runat="server" MaxLength="50"></asp:TextB
</td>
<td>
<asp:Label ID="lblAddress2" runat="server" Text="Address :"></asp:Label>
</td>
<td>
<asp:TextBox ID="tbAddress2" runat="server" MaxLength="50"></asp:TextB
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblCountry" runat="server" Text="Country"></asp:Label
</td>
<td>
<asp:DropDownList ID="ddlCountry" runat="server" DataTextField="CountryName
</asp:DropDownList>
</td>
<td>
<asp:Label ID="lblCity" runat="server" Text="City"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" DataTextField="CityName" DataValueField="CityID">
</asp:DropDownList>
</td>
</tr>
</table>
</fieldset>
</div>
<br />
<br />
<div id="divButton" runat="server">
<table id="tblButton" runat="server" width="90%" cellpadding="2" cellspacing="0"
border="0">
<tr>
<td width="10%">
</td>
<td>
<asp:Button ID="btnNew" runat="server" Text="New" />
</td>
<td>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
</td>
<td>
<asp:Button ID="btnReset" runat="server" Text="Reset" />
</td>
<td>
<asp:Button ID="btnUpdate" runat="server" Text="Update" />
</td>
<td>
<asp:Button ID="btnDelete" runat="server" Text="Delete" />
</td>
<td>
<asp:Button ID="btnCancel" runat="server" Text="Cancel" />
</td>
<td width="10%">
</td>
</tr>
</table>
</div>
<br />
<br />
<div id="divGrid" runat="server">
<asp:GridView ID="gvGrid" runat="server" AllowPaging="true" AllowSorting="false"
PageSize="4" DataKeyNames="SupplierID" AutoGenerateColumns="false
DataSourceID="MyDataSource
</asp:GridView>
<asp:ObjectDataSource ID="MyDataSource" runat="server" TypeName="OMS.DAL.Supplier
SelectCountMethod="SelectC
StartRowIndexParameterName
<SelectParameters>
<asp:Parameter Name="StartRow" Type="Int32" />
<asp:Parameter Name="MaxRows" Type="Int32" />
<asp:ControlParameter ControlID="tbSupplierNames
<asp:ControlParameter ControlID="tbSupplierEmail
<asp:ControlParameter ControlID="ddlCountryS" Name="countryID" PropertyName="SelectedValu
<asp:ControlParameter ControlID="ddlCityS" Name="cityID" PropertyName="SelectedValu
</SelectParameters>
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
__________________________
.ASPX.CS
using System;
using System.Collections.Generic
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace OMSWeb
{
public partial class SupplierInfo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void gvGrid_RowDataBound(object
{
// This part was used for debugging purpose and is not relevant and will be deleted.
// This part of the code is not reached, found during debugging.
int xy = 10;
int zz = xy;
int ab = xy + zz;
int bd = ab + xy + zz;
}
}
}
__________________________
SupplerBL.cs
using System;
using System.Collections.Generic
using System.Linq;
using System.Text;
using OMS.Entity;
using OMS.DAL;
namespace OMS.DAL
{
public class SupplierBL
{
public SupplierBL()
{
}
private int totalRows;
public SupplierCollection SelectMethod(int MaxRows, int StartRow, string supplierName, string supplierEmail, int countryID, int cityID)
{
try
{
Supplier sup = new Supplier();
SupplierCollection suc = new SupplierCollection();
sup.SupplierName = supplierName;
sup.SupplierEmail = supplierEmail;
sup.SupplierCountry = countryID;
sup.SupplierCity = cityID;
SupplierDataSource sd = new SupplierDataSource();
int totRows = 0;
suc = sd.SupplierSearch(sup, StartRow, MaxRows, ref totRows);
totalRows = totRows;
return suc;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
}
}
public int SelectCountMethod(int MaxRows, int StartRow, string supplierName, string supplierEmail, int countryID, int cityID)
{
return totalRows;
}
}
}
__________________________
SupplierDataSource.cs
using System;
using System.Collections.Generic
using System.Collections.ObjectM
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OMS.Entity;
namespace OMS.DAL
{
public class SupplierDataSource
{
private SqlConnection con;
private SqlCommand cmd;
private DataSet ds;
private SqlDataAdapter da;
private SqlDataReader dr;
private string connectionString;
public SupplierDataSource()
{
try
{
con = new SqlConnection(Configuratio
connectionString = ConfigurationManager.Conne
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Int32 SupplierAdd(Supplier sup)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt
{
using (SqlCommand cmd1 = new SqlCommand("SupplierInsert
{
cmd1.CommandType = CommandType.StoredProcedur
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public int SupplierUpdate(Supplier sup)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt
{
using (SqlCommand cmd1 = new SqlCommand("SupplierUpdate
{
cmd1.CommandType = CommandType.StoredProcedur
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public int SupplierDelete(int supID)
{
Int32 rowsAffected = 0;
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt
{
using (SqlCommand cmd1 = new SqlCommand("SupplierDelete
{
cmd1.CommandType = CommandType.StoredProcedur
con1.Open();
rowsAffected = cmd1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return rowsAffected;
}
public Supplier SupplierFetchByID(int supID)
{
Supplier sup = new Supplier();
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt
{
using (SqlCommand cmd1 = new SqlCommand("SupplierFetchB
{
cmd1.CommandType = CommandType.StoredProcedur
cmd1.Parameters.AddWithVal
con1.Open();
//Int32 rowsAffected = cmd1.ExecuteNonQuery();
SqlDataReader dr = cmd1.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
sup.SupplierID = dr.GetInt32(0);
sup.SupplierName = dr.GetString(1);
sup.SupplierAddress1 = dr.GetString(2);
sup.SupplierAddress2 = dr.GetString(3);
sup.SupplierCity = dr.GetInt32(4);
sup.SupplierCountry = dr.GetInt32(5);
sup.SupplierPinCode = dr.GetString(6);
sup.SupplierEmail = dr.GetString(7);
sup.SupplierTelephone = dr.GetString(8);
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return sup;
}
public SupplierCollection SupplierSearch(Supplier sup, int startRowIndex, int maximumRows, ref int totalRows)
{
SupplierCollection suc = new SupplierCollection();
Supplier su = new Supplier();
try
{
using (SqlConnection con1 = new SqlConnection(connectionSt
{
using (SqlCommand cmd1 = new SqlCommand("SupplierSearch
{
cmd1.CommandType = CommandType.StoredProcedur
//cmd1.Parameters.AddWithV
cmd1.Parameters.AddWithVal
// cmd1.Parameters.AddWithVal
//cmd1.Parameters.AddWithV
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
// cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
cmd1.Parameters.AddWithVal
// The Below OutParameter is used to retrieve the output parameters from sqldatareader.
// Any other style failed in retrieving the output parameter values.
SqlParameter OutParameter = new SqlParameter("@TotalRows",
OutParameter.Direction = ParameterDirection.Output;
cmd1.Parameters.Add(OutPar
//cmd1.Parameters.Add("@To
//cmd1.Parameters["@TotalR
//cmd1.Parameters["@TotalR
//cmd1.Parameters.AddWithV
SqlDataReader dr;
con1.Open();
dr = cmd1.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
su = new Supplier();
su.SupplierID = dr.GetInt32(0);
su.SupplierName = dr.GetString(1);
su.SupplierAddress1 = dr.GetString(2);
su.SupplierAddress2 = dr.GetString(3);
su.SupplierCity = dr.GetInt32(4);
su.SupplierCountry = dr.GetInt32(5);
su.SupplierPinCode = dr.GetString(6);
su.SupplierEmail = dr.GetString(7);
su.SupplierTelephone = dr.GetString(8);
suc.Add(su);
}
}
dr.Close();
// totalRows = int.Parse(cmd1.Parameters[
totalRows = (int)OutParameter.Value;
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
}
}
--------------------------
Stored Procedure
USE [OMS]
GO
/****** Object: StoredProcedure [dbo].[SupplierSearch] Script Date: 11/25/2010 12:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SupplierSearch]
(
@SupplierName VARCHAR(50) = NULL, -- default to NULL
@SupplierCity INT = NULL,
@SupplierCountry INT = NULL,
@SupplierEmail VARCHAR(50) = NULL,
@StartRowIndex INT,
@MaximumRows INT,
@TotalRows INT OUTPUT
)
AS
BEGIN
DECLARE @first_id int, @startRow INT
DECLARE @StringCity VARCHAR(20)
DECLARE @StringCountry VARCHAR(20)
IF @StartRowIndex > 0
SET @StartRowIndex = (@StartRowIndex - 1) * @MaximumRows
IF @StartRowIndex = 0
SET @StartRowIndex = 1
--PRINT @StartRowIndex
SET ROWCOUNT @StartRowIndex
IF RTRIM(@SupplierName)='' OR @SupplierName IS NULL
SET @SupplierName = '%'
ELSE
SET @SupplierName = @SupplierName + '%'
IF RTRIM(@SupplierEmail)='' OR @SupplierEmail IS NULL
SET @SupplierEmail = '%'
ELSE
SET @SupplierEmail = @SupplierEmail + '%'
IF RTRIM(@SupplierCity)='' OR @SupplierCity = 0 OR @SupplierCity IS NULL
SET @StringCity = '%'
ELSE
SET @StringCity = CONVERT(VARCHAR(20),@Suppl
IF RTRIM(@SupplierCountry)=''
SET @StringCountry = '%'
ELSE
SET @StringCountry = CONVERT(VARCHAR(20),@Suppl
-- Append % for LIKE operator search (if not NULL)
-- IF @SupplierName IS NULL
-- SET @SupplierName = @SupplierName + '%'
--
-- IF @SupplierEmail IS NULL
-- SET @SupplierEmail = @SupplierEmail + '%'
--
-- PRINT Convert(varchar(30),@Start
-- PRINT Convert(varchar(30),@Maxim
--
-- PRINT Convert(varchar(30),@Suppl
-- PRINT Convert(varchar(30),@Strin
-- PRINT Convert(varchar(30),@Strin
-- PRINT Convert(varchar(30),@Suppl
--
SET ROWCOUNT @StartRowIndex
SELECT @first_id = SupplierID FROM Supplier Where
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
--(SupplierCity = IsNull(@SupplierCity,Suppl
--(SupplierCountry = IsNull(@SupplierCountry,Su
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)
ORDER BY SupplierID
-- PRINT Convert(varchar(30),@first
SET ROWCOUNT @MaximumRows
SELECT * FROM Supplier WHERE
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
-- (SupplierCity = IsNull(@SupplierCity,Suppl
-- (SupplierCountry = IsNull(@SupplierCountry,Su
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail) AND
(SupplierID > @first_id) ORDER BY SupplierID
SET ROWCOUNT 0
SELECT @TotalRows = COUNT(*) FROM Supplier
WHERE
(@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
-- (SupplierCity = IsNull(@SupplierCity,Suppl
-- (SupplierCountry = IsNull(@SupplierCountry,Su
(SupplierCity like @StringCity) AND
(SupplierCountry like @StringCountry) AND
(@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)
PRINT Convert(varchar(30),@Total
END
__________________________
Please help sort this issue. Thank you very much.
How is SupplierCollection type defined?
ASKER
using System;
using System.Collections.Generic
using System.Collections.ObjectM
using System.Linq;
using System.Text;
namespace OMS.Entity
{
public class Supplier
{
private int supplierID;
private string supplierName;
private string supplierAddress1;
private string supplierAddress2;
private int supplierCity;
private int supplierCountry;
private string supplierPinCode;
private string supplierEmail;
private string supplierTelephone;
public int SupplierID
{
get { return supplierID; }
set { supplierID = value; }
}
public string SupplierName
{
get { return supplierName; }
set { supplierName = value; }
}
public string SupplierAddress1
{
get { return supplierAddress1; }
set { supplierAddress1 = value; }
}
public string SupplierAddress2
{
get { return supplierAddress2; }
set { supplierAddress2 = value; }
}
public int SupplierCity
{
get { return supplierCity; }
set { supplierCity = value; }
}
public int SupplierCountry
{
get { return supplierCountry; }
set { supplierCountry = value; }
}
public string SupplierPinCode
{
get { return supplierPinCode; }
set { supplierPinCode = value; }
}
public string SupplierEmail
{
get { return supplierEmail; }
set { supplierEmail = value; }
}
public string SupplierTelephone
{
get { return supplierTelephone; }
set { supplierTelephone = value; }
}
}
// The below is the SupplierCollection
public class SupplierCollection : Collection<Supplier> { }
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdatasource.selectmethod.aspx The select method has restrictions on return type. I am not sure how you defined but, can you try returning List<Supplier> in your methods? (change SupplierCollections wtih List<Supplier> I mean)
ASKER
Dear rkworlds,
I dont understand this, why you are asking to delete post? Have I dont something wrong, please advise.
I dont understand this, why you are asking to delete post? Have I dont something wrong, please advise.
ASKER
The AutoGenerateColumns="false " and columns were not defined which was identified and rectified by me. So the points are not awarded anybody else but me. Having said that and be fair to others I really appreciate the efforts of all members to help sort issues.
Thank you.
Thank you.