MadhuMenong
asked on
ASP.Net GridView ObjectDataSource Issue
Dear Friends,
I am working on a GridView with ObjectDataSource and getting the following error.
"ObjectDataSource 'MyDataSource' could not find a non-generic method 'SelectMethod' that has parameters: MaxRows, StartRow. "
Please help to sort this issue. The complete code is given below:
.aspx -- Only the gridview and objectdatasource is given but there are other controls.
<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 ">
</asp:GridView>
<asp:ObjectDataSource ID="MyDataSource" runat="server" TypeName="OMS.DAL.Supplier BL"
SelectCountMethod="SelectC ountMethod " SelectMethod="SelectMethod " EnablePaging="true" StartRowIndexParameterName ="StartRow " MaximumRowsParameterName=" MaxRows">
</asp:ObjectDataSource>
</div>
-------------------------- ---------- ---------- ---------- ---------- -------
OMS.DAL.SupplierBL
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()
{
return totalRows;
}
}
}
-------------------------- ---------- ---------- ---------- ---------- ----------
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()
{
con = new SqlConnection(Configuratio nManager.C onnectionS trings["Co nStr"].ToS tring());
connectionString = ConfigurationManager.Conne ctionStrin gs["ConStr "].ToStrin g();
}
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("connectionS tring"))
{
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);
cmd1.Parameters.AddWithVal ue("@Total Row", 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);
}
}
totalRows = int.Parse(cmd1.Parameters[ "@TotalRow s"].ToStri ng());
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
}
}
__________________________ __________ _____
using System;
using System.Collections.Generic ;
using System.Collections.ObjectM odel;
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; }
}
}
public class SupplierCollection : Collection<Supplier> { }
}
-------------------------- ---------- ---------- ---------- ---------- -----
Stored Procedure:
USE [OMS]
GO
/****** Object: StoredProcedure [dbo].[SupplierSearch] Script Date: 11/25/2010 11:36:41 ******/
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,
@TotalRow 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
SET @StringCity = '%'
ELSE
SET @StringCity = CONVERT(VARCHAR(20),@Suppl ierCity)
IF RTRIM(@SupplierCountry)='' OR @SupplierCountry = 0
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 @TotalRow = 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 Row) + ' - Total Rows'
END
I am working on a GridView with ObjectDataSource and getting the following error.
"ObjectDataSource 'MyDataSource' could not find a non-generic method 'SelectMethod' that has parameters: MaxRows, StartRow. "
Please help to sort this issue. The complete code is given below:
.aspx -- Only the gridview and objectdatasource is given but there are other controls.
<div id="divGrid" runat="server">
<asp:GridView ID="gvGrid" runat="server" AllowPaging="true" AllowSorting="false" PageSize="4"
DataKeyNames="SupplierID" AutoGenerateColumns="false
</asp:GridView>
<asp:ObjectDataSource ID="MyDataSource" runat="server" TypeName="OMS.DAL.Supplier
SelectCountMethod="SelectC
</asp:ObjectDataSource>
</div>
--------------------------
OMS.DAL.SupplierBL
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()
{
return totalRows;
}
}
}
--------------------------
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()
{
con = new SqlConnection(Configuratio
connectionString = ConfigurationManager.Conne
}
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("connectionS
{
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
cmd1.Parameters.AddWithVal
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);
}
}
totalRows = int.Parse(cmd1.Parameters[
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
}
}
__________________________
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; }
}
}
public class SupplierCollection : Collection<Supplier> { }
}
--------------------------
Stored Procedure:
USE [OMS]
GO
/****** Object: StoredProcedure [dbo].[SupplierSearch] Script Date: 11/25/2010 11:36:41 ******/
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,
@TotalRow 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
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 @TotalRow = 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi buraksarica,
U R right i forgot to define the <selectParameters> and have done so as below:
<asp:GridView ID="gvGrid" runat="server" AllowPaging="true" AllowSorting="false"
PageSize="4" DataKeyNames="SupplierID" AutoGenerateColumns="false " CellPadding="2"
DataSourceID="MyDataSource ">
</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>
But now i am faced with a new problem.
__________________________ __________ __________ ______
SupplierDataSource -- > below method
" totalRows = int.Parse(cmd1.Parameters[ "@TotalRow s"].ToStri ng());" at this line getting error "Input string not in correct format." . Can you advise please.
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);
cmd1.Parameters.Add("@Tota lRows", SqlDbType.Int);
cmd1.Parameters["@TotalRow s"].Direct ion = ParameterDirection.Output;
cmd1.Parameters["@TotalRow s"].Value = 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);
}
}
totalRows = int.Parse(cmd1.Parameters[ "@TotalRow s"].ToStri ng());
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
U R right i forgot to define the <selectParameters> and have done so as below:
<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>
But now i am faced with a new problem.
__________________________
SupplierDataSource -- > below method
" totalRows = int.Parse(cmd1.Parameters[
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
cmd1.Parameters.Add("@Tota
cmd1.Parameters["@TotalRow
cmd1.Parameters["@TotalRow
//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);
}
}
totalRows = int.Parse(cmd1.Parameters[
con1.Close();
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " -- " + ex.InnerException);
}
finally
{
}
return suc;
}
ASKER
Thanks buraksarica.
That's because you add the parameter as input parameter on this line,
cmd1.Parameters.AddWithVal ue("@Total Row", 0);
You should add this parameter as an output parameter like :
SqlParameter parameter = cmd1.Parameters.Add(
"@TotalRow", SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
then you should get the value correctly.
cmd1.Parameters.AddWithVal
You should add this parameter as an output parameter like :
SqlParameter parameter = cmd1.Parameters.Add(
"@TotalRow", SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
then you should get the value correctly.
"ObjectDataSource 'MyDataSource' could not find a non-generic method 'SelectMethod' that has parameters: MaxRows, StartRow. "
It would appear that the compiler is looking for a method like this:
public void SelectMethod(int MaxRows, int StartRow)
{
//Code.....
}
Since those are the only two parameters you define in your asp markup