C# & SQL - Stored Procedures 1

[cyberjay82]

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

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.

 

[Alcar]

be very careful now. If only one of your where clause is false then you will get no records. I would proceed debugging it this way:

1) check the SQL SELECT Statement using the Query Analizer

2) check all the .ToString() results of your controls. Some might return something different. Probably the Date one is returning the date in a format that won't match. Try a ToShortDateString().

ps: have you considered <NULL> values?

hope this helps

 

[cyberjay82]

I have tried it in the query analizer, and it works when I pug in the params with my own data, but when I use the params and pass the text to them it doesn't work, its as if the text is not getting passed right.

 

[Alcar]

then you might want to check what exactely is being passed to the parameters using the debug window:

using System.Diagnostics;

Debug.Write(&quot;Description: &quot; + descS.ToString());
//repeat for all the fields

and check them out

 

[cyberjay82]

I did what you said to do and they are all holding the right values but I still get no records. I have also don't have any more <NULL> values being selected either. That didn't fix it either. I don't know what is wrong I must have something either set wrong or the stored procedure is wrong or not passing the values right. Thanks for all the help.

 

[Alcar]

make sure you have setted the behaviour of your SqlCommand:

sqlDataAdapter1.SelectCommand.UpdatedRowSource = UpdateRowSource.Both;

other than that, it must a little thing.

good luck!

 

[cyberjay82]

Sorry, I still get nothing. I just can't figure this one out. I'll keep trying and if you have any more advice, I'll be happy to listen. Thanks.

 

[Alcar]

Last suggestion: make the command wizard create you SqlCommand object. When you drag a new SqlDataAdapter on your form, the wizard will pop out. Give them the Search Stored Procedure as your Select Stored Procedure and let it create your SqlCommand Object for you.

Before you fill the dataset, pass the parameters values to the select command object:

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

That should work. If you still get no result, than it might be that you are doing something wrong when you try to get the values out of your DataRow.

Good Luck

 

[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.

 

[Alcar]

no. The Select Statement already returns a set of records. You don't need to add a return parameter.

 

[cyberjay82]

I decided to ExecuteNonQuery like this right before I call the fill method:

int counterR = 0;

sqlConnection1.Open();

counterR = sqlDataAdapter1.SelectCommand.ExecuteNonQuery();

MessageBox.Show(counterR.ToString());

sqlConnection1.Close();

I got -1 records affected. Something is not right because I should get some rows affected.

 

[cyberjay82]

I have also did ExecuteReader method with:

connection.Open();
SqlDataReader reader;
reader = sqlDataAdapter.SelectCommand.ExecuteReader();

while(reader.Read())
{
MessageBox.Show(reader.GetString(0));
}

MessageBox.Show(reader.GetString(0)); //to make sure

reader.Close();
connection.Close();

After that I also got nothing skipped right over the messagebox. When it hit the second message box it gave me an error of no data present.

 

[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;