Solved

Executing a Stored Procedure in a Winform

Posted on 2010-09-14
10
425 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard 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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now