Solved

Executing a Stored Procedure in a Winform

Posted on 2010-09-14
10
431 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
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…

773 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