Solved

Executing a Stored Procedure in a Winform

Posted on 2010-09-14
10
420 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

18 Experts available now in Live!

Get 1:1 Help Now