Is this stored procedure code right? 4

[cyberjay82]

Please let me know if my stored procedure code is right.
Here is my stored procedure in my sql database:

CREATE PROCEDURE [dbo].[Search] (@param0 nvarchar, @param1 nvarchar,@param2 nvarchar,@param3 nvarchar,@param4 nvarchar,@param5 nvarchar,@param6 nvarchar,@param7 nvarchar)
AS
SET NOCOUNT ON;
SELECT Number, PartNumber, Description, Cost, Quote, Quanity, PaymentType, Employee, Vendor, VRepresentative, ShippedFrom, Customer, Notes, QuanityReceived, DateReceived, ReceivedBy, InvoiceNumber, Pending, DateOrdered FROM [Order/Received] WHERE Description LIKE @param0 AND PartNumber LIKE @param1 AND ShippedFrom LIKE @param2 AND Customer LIKE @param3 AND DateOrdered LIKE @param4 AND Employee LIKE @param5 AND Vendor LIKE @param6 AND PaymentType LIKE @param7 ORDER BY Description;
GO

===========================================================
If you also know any C# code is this what is causing the problem?

Here is my code in my C# form(vs.net):

sqlDataAdapter1.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

sqlDataAdapter1.SelectCommand.CommandText = "Search";
sqlDataAdapter1.SelectCommand.Parameters.Clear();
sqlDataAdapter1.SelectCommand.Parameters.Add("@param0", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param0"].Value = descS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param1", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param1"].Value = partS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param2", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param2"].Value = shipS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param3", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param3"].Value = custS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param4", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param4"].Value = dateS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param5", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param5"].Value = empS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param6", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param6"].Value = venS.ToString();

sqlDataAdapter1.SelectCommand.Parameters.Add("@param7", System.Data.SqlDbType.NVarChar);
sqlDataAdapter1.SelectCommand.Parameters["@param7"].Value = payS.ToString();

sqlDataAdapter1.Fill(ord);

DataTable dtO = ord.Tables[0];

foreach(DataRow drO in dtO.Rows)
{

etc...

Am I doing stomething wrong because I keep getting no records in my datarows. This is the first time I am using stored procedures with C# and I don't know if I am doing this right. Any advice will be helpful. Thanks.

 

[SQLSister]

Nothing immediately comes to mind except - are you sure the parameters you are passing in would have a result in your data? You do have an awful lot of conditions to meet to retrieve a record, so there is a good possibility no records actually matched. To see if your problem is the stored procedure or the C# code, start by executing the stored procedure from query analyzer with sample values. If results come up then your stored procedure works. Then use your program to send those same values though the C# code (Put breakpoints on the code to see if you are populating each variable with the info you thought you were populating it with.)

If none of this helps you ID what is wrong, then try a simpler stored procedure (nmaybe only one parameter) and C# code until you can get this working and know exactly how to call the procedure from C#. Then go back and rebuild this one a little bit at a time.

One thing I did just now notice is that you are passing all the values as strings, if all your datatypes are not strings that might be your problem.

Good luck

Judy

 

[codestorm]

If you're after records where the input params are in the fields, but don't have to be ALL that is in the fields, try:

SELECT Number, PartNumber, Description, Cost, Quote, Quanity, PaymentType, Employee, Vendor, VRepresentative, ShippedFrom, Customer, Notes, QuanityReceived, DateReceived, ReceivedBy, InvoiceNumber, Pending, DateOrdered FROM [Order/Received] WHERE Description LIKE '%' + @param0 + '%' AND PartNumber LIKE '%' + @param1 + '%' AND ShippedFrom LIKE '%' + @param2 + '%' AND Customer LIKE '%' + @param3 + '%' AND DateOrdered LIKE '%' + @param4 + '%' AND Employee LIKE '%' + @param5 + '%' AND Vendor LIKE '%' + @param6 + '%' AND PaymentType LIKE '%' + @param7 + '%' ORDER BY Description; codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[cyberjay82]

All my data types are nvarchar and I have put it through the query analyzer and it works. I have made sure my variables are holding the right data then set the params in my c# code and then checked the params to make sure. But I still get no records. I just can't seem to figure this out. Thanks for all the advice and any more would be helpful. Thanks.

 

[SQLSister]

Have you tried posting this is in the C# forum? The link is:
forum732

 

[cyberjay82]

Yes I have and I'm getting good advice but still nothing is working right. I don't know. I'll keep trying. If you get any more advice, I'll be happy to listen. Thanks.

 

[sunila7]

Hi,

I this stored proc, u have not set the length for each of this input vaiables, i think that is the problem

CREATE PROCEDURE [dbo].[Search] (@param0 nvarchar(20), @param1 nvarchar(20),@param2 nvarchar(20),@param3 nvarchar(20),@param4 nvarchar(20),@param5 nvarchar(20),@param6 nvarchar(20),@param7 nvarchar(20))
AS

I have put it as 20 change it to the same char length as in ur DB for each field

Sunil

 

[cyberjay82]

I haven't done anything with a return value or anything like that, does the fill method take care of that with the dataset or is that where my error is.

 

[cyberjay82]

I have changed things just like you said to match my sizes and still nothing happens.

 

[sunila7]

Hi,

Can u put the New SP and a Sample value, u r using to call the SP.


Sunil

 

[cyberjay82]

I still don't get any records but here is an update of code.

This is the new and Updated Stored procedure:

CREATE PROCEDURE [dbo].[Search] (@param0 nvarchar(100), @param1 nvarchar(50),@param2 nvarchar(100),@param3 nvarchar(100),@param4 nvarchar(100),@param5 nvarchar(100),@param6 nvarchar(50))
AS
SET NOCOUNT ON;
SELECT Number, PartNumber, Description, Cost, Quote, Quanity, PaymentType, Employee, Vendor, VRepresentative, ShippedFrom, Customer, Notes, QuanityReceived, DateReceived, ReceivedBy, InvoiceNumber, Pending, DateOrdered FROM [Order/Received] WHERE Description LIKE @param0 AND PartNumber LIKE @param1 AND ShippedFrom LIKE @param2 AND Customer LIKE @param3 AND Employee LIKE @param4 AND Vendor LIKE @param5 AND PaymentType LIKE @param6 ORDER BY Description;
GO

This is the update of code in my form:

sqlDataAdapter1.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

sqlDataAdapter1.SelectCommand.CommandText = &quot;Search&quot;;


sqlDataAdapter1.SelectCommand.Parameters[&quot;@param0&quot;].Value = descS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param1&quot;].Value = partS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param2&quot;].Value = shipS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param3&quot;].Value = custS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param4&quot;].Value = empS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param5&quot;].Value = venS.ToString();

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param6&quot;].Value = payS.ToString();

sqlDataAdapter1.Fill(ord);

DataTable dtO = ord.Tables[0];

string charLength = &quot;j&quot;;
string charTemp;
int length = 0;
int tempLength = 0;
string spaces = &quot;&quot;;
int tempL = 0;

foreach(DataRow drO in dtO.Rows)
{

charTemp = drO[&quot;Description&quot;].ToString();

if(charTemp.Length > charLength.Length)
{
charLength = charTemp.ToString();
length = charLength.Length;
}
}

length = length + 10;

foreach(DataRow drO in dtO.Rows)
{
spaces = &quot;&quot;;
charTemp = drO[&quot;Description&quot;].ToString();
tempLength = charTemp.Length;
tempL = charTemp.Length;

while(tempLength != length)
{
spaces = spaces + &quot; &quot;;
tempLength++;
}

lbResult.Items.Add(drO[&quot;Number&quot;].ToString() + &quot; &quot; + drO[&quot;Description&quot;].ToString() + spaces.ToString() + drO[&quot;Quote&quot;].ToString() + &quot; &quot; + drO[&quot;DateOrdered&quot;].ToString() + &quot; &quot; + drO[&quot;Vendor&quot;].ToString() + &quot; &quot; + drO[&quot;Customer&quot;].ToString() + &quot; &quot; + drO[&quot;Employee&quot;].ToString());
}

if(dtO.Rows.Count == 0)
lbResult.Items.Add(&quot;No Matching Orders Found&quot;

 

[codestorm]

If the .Net use of command objects, etc (or whatever they're called now) matches old ADO much, then I'd expect you'd need to set the size of each param.
A total guess would be something like

sqlDataAdapter1.SelectCommand.Parameters[&quot;@param6&quot;].Size = payS.ToString().length();
codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[codestorm]

Just poked around M$ site .. try:

sqlDataAdapter1.SelectCommand.Parameters.Add(&quot;@param1&quot;, System.Data.SqlDbType.NVarChar, partS.ToString().length())Value = partS.ToString();
codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[nshen]

Does your descS.ToString() contain the wildcard character &quot;%&quot;?
- Nancy

 

[cyberjay82]

Yes, they contain wildcard characters. The size of each param is greater than the string that is going into each param. Thanks.

 

[codestorm]

In classic ADO params there was a different between adding string params with a set size (larger than the actual param) and a variable size (to match the actual param).
Within the query, it would be like the different between:
select * from table1 where field1 like '%test%'
and
select * from table1 where field1 like '%test %' codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>