Solved

Executing a Stored Procedure in a Winform

Posted on 2010-09-14
10
439 Views
Last Modified: 2012-08-14
Trying to execute a stored procedure but the only data returned is an integer and it is always 0 false? The stored proc works in management studio when executed but it doesn't seem to work in the windows form. How do I use a stored procedure in a windows form to return data not an integer.

string param1 = textBox1.Text;
            string param2 = comboBox1.Text;

           
            //Call to the Stored procedure this reutrns a value (0)
            var y = dc.GetInventoryByAnyValue(param1, param2);

           

                       

           
           //Code I've been using to test the rest of the program
           
            var q = (from item in dc.GetTable<MainInventory>()
                    where item.InventoryNumber.Contains(param1)                    
                     select new InventoryListResult
                     {
                                                   AWNum = item.AWInvNum ,
                                                   InvNum =item.InventoryNumber,
                                                   InvLocation = item.InventoryLocation,
                                                   Available = Convert.ToInt32(item.Available),
                                                   _upc = item.UPC,
                                                   _sku = item.SKU,
                                                   Image1 = item.ImageURL1,
                                                   Image2 = item.ImageURL2}).ToList<InventoryListResult>();



//Stored Procedure
USE [INVENTORY]
GO
/****** Object:  StoredProcedure [dbo].[GetInventoryByAnyValue]    Script Date: 09/14/2010 12:36:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetInventoryByAnyValue] @search varchar (75) , @category varchar(50)
AS
SET NOCOUNT ON

DECLARE @str nvarchar(2255)

SELECT @str = 'SELECT * ' + 'FROM MainInventory WHERE ' + @category + ' LIKE @search'

set @search = '%'+@search+'%'

EXEC sp_executesql @str, N'@search varchar(50)', @search
0
Comment
Question by:frtools
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 375 total points
ID: 33674610
procedures always return a number when executed.can you post the GetInventoryByAnyValue method. What do you excpet the proc to return a table ? if so then you should call Executereader
0
 
LVL 8

Assisted Solution

by:Gururaj Badam
Gururaj Badam earned 125 total points
ID: 33674830
0
 

Author Comment

by:frtools
ID: 33683137
Added the SQLcommand like both of you suggested. now recieve error message on the line---dataGridView2.DataSource = objCmd.ExecuteNonQuery();

SQLEXCEPTION was Unhandled
Invalid column name 'InventoryNumber'.
Invalid column name 'AWInvNum'.
Invalid column name 'InventoryNumber'.
Invalid column name 'Available'.
Invalid column name 'InventoryLocation'.
Invalid column name 'ImageURL1'.



Added code---

string param1 = textBox1.Text;
            string param2 = comboBox1.Text;

            SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
            objConn.ConnectionString = "server=SHPPING_COREI7\\SQLEXPRESS; Database=INVENTORY; Trusted_Connection=True;";

            SqlCommand objCmd = new SqlCommand("GetInventoryByAnyValue", objConn);
           

           
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.AddWithValue("@search", param1);
            objCmd.Parameters.AddWithValue("@category", param2);

            objConn.Open();
            dataGridView2.DataSource = objCmd.ExecuteNonQuery();
            objConn.Close();




0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 375 total points
ID: 33683207
is your proc running independently ? looks like you have some error in that. also change your code as below
string param1 = textBox1.Text;
            string param2 = comboBox1.Text;

            SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
            objConn.ConnectionString = "server=SHPPING_COREI7\\SQLEXPRESS; Database=INVENTORY; Trusted_Connection=True;";

            SqlCommand objCmd = new SqlCommand("GetInventoryByAnyValue", objConn);
            

            
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.AddWithValue("@search", param1);
            objCmd.Parameters.AddWithValue("@category", param2);

            objConn.Open();

SqlDataAdapter adp = new SqlDataAdapter(objCmd);
            DataTable table = new DataTable();
            adp.Fill(table);
            dataGridView2.DataSource = table;
            objConn.Close();

Open in new window

0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33683242
What I suspect is cos you're binding the results to DataGridView you may have defined the binding against the DataReader. The resultset returned by SQLReader may not contain required columns as defined in the DataGridView binding.
0
 

Author Comment

by:frtools
ID: 33683691
There was an error in the Stored Proc-- missing a space---Aghhh
0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33683785
This is not fair as I suggest you to use SQLCommand and others get the points?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688196
>>This is not fair as I suggest you to use SQLCommand and others get the points?<<
You may want to double check that, it looks like you got points.
0
 
LVL 8

Expert Comment

by:Gururaj Badam
ID: 33688611
Yes I've got Assisted Points but the idea to use SQLCommand was mine and I believe I deserve more points unless the Author thinks otherwise.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33689106
>> I believe I deserve more points unless the Author thinks otherwise.<<
Relax.  This too shall pass and tomorrow will be a brighter day. :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question