Click here to Skip to main content
12,399,362 members (58,240 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# SQL-Server C#4.0 , +
-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 2:35am
Updated 24-Mar-13 3: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 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 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 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 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 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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 25 Mar 2013
Copyright © CodeProject, 1999-2016
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