Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
-3 down vote favorite

 
I'm working on my app which creates a Crystal Report. However, when i run the application I get the following error:
 
The number of parameters does not match number of values for stored procedure
 
Can anyone help me solve this?
 
Here is the stored procedure...
ALTER procedure [dbo].[spcustomer]
    @ContactName nvarchar(30),
    
as
 
   select * 
   from Customers 
   where ContactName=@ContactName 
...and here is the function...
public static CrystalReport1 getcutomer(string ContactName)
{
    Database db = DatabaseFactory.CreateDatabase("myconn");
 
    DataSet ds = db.ExecuteDataSet("spcustomer", new object[] { ContactName}));
    ds.Tables[0].TableName = "Customers";
    CrystalReport1 report = new CrystalReport1();
    report.SetDataSource(ds);
 
    return report;
}
The code to execute the report is...
protected void Button1_Click(object sender, EventArgs e)
{
    CrystalReportViewer1.ReportSource = ClassLibrary4.Class1.getcutomer(TextBox1.Text);
    CrystalReportViewer1.DataBind();
}
 
here i want to know that
 
i have also other columns in table customer i,e ContactTitle,Address and city
then how i write in store procedure according to this and i seaerch through name in crystal report when i enter name then their contacttitle address and city is appear in report???how i do this...
Posted 24-Mar-13 3:35am
Edited 24-Mar-13 4:46am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hello,
 
You can write the stored procedure as shown below
CREATE PROCEDURE spcustomer
    @ContactName    NVARCHAR(30) = NULL,
    @ContactTitle   NVARCHAR(40) = NULL,
    @Address        NVARCHAR(80) = NULL,
    @City           NVARCHAR(30) = NULL
AS
    SELECT * 
    FROM customers c
    WHERE (c.ContactName = @ContactName OR @ContactName IS NULL)
          AND (c.ContactTitle = @ContactTitle OR @ContactTitle IS NULL)
          AND (c.Address = @Address OR @Address IS NULL)
          AND (c.City = @City OR @City IS NULL);
NotePlease not that passing and empty string to this procedure may not return rows as empty string is not same as NULL value.
 
Here is a code snippet depicting one of the possible way for calling the report
public static CrystalReport1 getcutomer(string ContactName, string strTitle, string strAddress, string strCity)
{
    string[] arrParams = new string[4];
    arrParams[0] = (String.IsNullOrEmpty(ContactName) ? null : ContactName);
    arrParams[1] = (String.IsNullOrEmpty(strTitle) ? null : strTitle);
    arrParams[2] = (String.IsNullOrEmpty(address) ? null : strAddress);
    arrParams[3] = (String.IsNullOrEmpty(strCity) ? null : strCity);
 
    Database db = DatabaseFactory.CreateDatabase("myconn");
 
    DataSet ds = db.ExecuteDataSet("spcustomer",  arrParams);
    ds.Tables[0].TableName = "Customers";
    CrystalReport1 report = new CrystalReport1();
    report.SetDataSource(ds);
 
    return report;
}
  Permalink  
v2
Comments
ariesareej at 24-Mar-13 10:22am
   
thankx ur rreply
can u plz tell me why we mark in null...nd waht is te concept of this
Prasad Khandekar at 24-Mar-13 10:36am
   
That's the default value for parameter. If you do not pass any value for the parameter then the default is treated as null. This is done so that our condition in the SQL Statement work correctly.
ariesareej at 25-Mar-13 6:15am
   
but in your store procodure has all parameters define and i search only thorugh name...
so there is one parameter define in sp...am i rite???
Prasad Khandekar at 25-Mar-13 6:26am
   
lets say if you want to search by Address then while you call this stored procedure you will ony pass the value for parameter named @Address.
ariesareej at 25-Mar-13 6:58am
   
ok i try this sp
\now the sp is
CREATE PROCEDURE spcustomer
@ContactName NVARCHAR(30) = NULL,
@ContactTitle NVARCHAR(40) = NULL,
@Address NVARCHAR(80) = NULL,
@City NVARCHAR(30) = NULL
AS
SELECT *
FROM customers c
WHERE (c.ContactName = @ContactName OR @ContactName IS NULL)
AND (c.ContactTitle = @ContactTitle OR @ContactTitle IS NULL)
AND (c.Address = @Address OR @Address IS NULL)
AND (c.City = @City OR @City IS NULL);
 
function is
public static CrystalReport1 getcutomer(string ContactName)
{
Database db = DatabaseFactory.CreateDatabase("myconn");
 

DataSet ds = db.ExecuteDataSet("spcustomers", new object[] { ContactName});
ds.Tables[0].TableName = "Customers";
CrystalReport1 report = new CrystalReport1();
report.SetDataSource(ds);
 
return report;
 

and button code is
 
protected void Button1_Click(object sender, EventArgs e)
{
CrystalReportViewer1.ReportSource = ClassLibrary4.Class1.getcutomer(TextBox1.Text);
CrystalReportViewer1.DataBind();
}
 

when i try this i always face error ....
ariesareej at 25-Mar-13 6:59am
   
now the error always coming

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 25 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100