Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Executing a Stored Procedure in a Winform

Posted on 2010-09-14
10
Medium Priority
?
451 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 1500 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 500 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Integration Management Part 2
Screencast - Getting to Know the Pipeline

577 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