Stored Procedure Calls Information to Visual Studio 2010 Windows Form

Question

I'm trying to create a Windows form in Visual Studio 2010 that will call information from a stored procedure and display it on the windows form. I created a view that is being used for the stored procedure that provides all of the needed information.

Basically, I want to look up a person by part or all of a name and then have it display on my Visual Studio Form:

CREATE PROCEDURE
dbo.Talisma_Search
(@Talisma_Search NVARCHAR(255))
AS
SELECT
[dbo].[tblTalisma_Lookup].[tName]
, [dbo].[tblTalisma_Lookup].[tSSN]
, [dbo].[tblTalisma_Lookup].[tEmail]
FROM
[dbo].[tblTalisma_Lookup]
WHERE
[dbo].[tblTalisma_Lookup].[tName] LIKE '%'+@Talisma_Search + '%'
OR [dbo].[tblTalisma_Lookup].[tSSN] LIKE '%'+@Talisma_Search + '%'
OR [dbo].[tblTalisma_Lookup].[tEmail] LIKE '%'+@Talisma_Search + '%'
ORDER BY
[dbo].[tblTalisma_Lookup].[tName]

EXEC dbo.talisma_Search @talisma_Search ='savage'

What I want is a button on a Windows form that allows me to type in a name or SSN or email and then displays the result at the bottom of the form. I have been researching this and have been unable to find any information on this type of project.

This is my first attempt at creating an application using Visual Studio. I am very much a beginner so any help would be appreciated.

Thank you,

Mary

Pravin More · Answer

Hi Mary here i have written one function according to your requirment it will help you to call stored procedure and bind it to your form......... private void LoadData(Int32 Talisma_Search) { SqlConnection conn = new SqlConnection(Data Source='SHAWHP\SQLEXPRESS';Initial Catalog=SoftwareInventory;Integrated Security=SSPI); // change this coonection string according to you settings of sql server SqlCommand cmd = new SqlCommand(dbo.Talisma_Search conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@Talisma_Search  Talisma_Search); SqlDataAdapter adapter = new SqlDataAdapter(cmd); dataSet1.Tables.Clear(); adapter.Fill(dataSet1 Table); //now you can show this dataset table on window form how you want like i have shown to Grid below MyGrid.DataSource = dataSet1.Tables[Table]; bindingSource1 = new BindingSource(dataSet1 Table); bindingNavigator1.BindingSource = bindingSource1; MyGrid.DataSource = bindingSource1; } Thanksi hope it will help you Pravin. Mark  Accepted answer  if this post help you.

Mary Bergeron · Answer

I did some research and found some code that I thought would work however I'm getting multiple errors. Below is the code and the error list. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Talisma_Lookup4 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender EventArgs e) { this.BackColor = Color.DarkOrange; } private void LoadData(Int32 Talisma_Search) { SqlConnection conn = new SqlConnection(Data Source='talismadb';Initial Catalog=tlMain;Integrated Security=True); // change this connection string according to you settings of sql server SqlCommand cmd = new SqlCommand(dbo.Talisma_Search conn); cmd.CommandText = Talisma_Search; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@Talisma_Search  Talisma_Search); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet dataSet1 = new DataSet(); dataSet1.Tables.Clear(); adapter.Fill(dataSet1 Table); //now you can show this dataset table on window form how you want like i have shown to Grid below //MyGrid.DataSource = dataSet1.Tables[Table]; //bindingSource1 = new BindingSource(dataSet1 Table); //bindingNavigator1.BindingSource = bindingSource1; //MyGrid.DataSource = bindingSource1; // Data is accessible through the DataReader object here. } } } //cmd is your sqlcommand object //TextBox1.Text=Convert.ToString(cmd.ExecuteScalar()); //Data Source=talismadb;Initial Catalog=tlMain;Integrated Security=True);SqlCommand cmd = new SqlCommand(); Error 1 } expected C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 76 14 Talisma_Lookup4 Error 2 Invalid token 'catch' in class struct or interface member declaration C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 91 13 Talisma_Lookup4 Error 3 ; expected C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 91 34 Talisma_Lookup4 Error 4 Invalid token '(' in class struct or interface member declaration C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 93 32 Talisma_Lookup4 Error 5 Invalid token ')' in class struct or interface member declaration C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 93 74 Talisma_Lookup4 Error 6 A namespace cannot directly contain members such as fields or methods C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 96 4 Talisma_Lookup4 Error 7 Expected catch or finally C:\Users\mbergeron\Documents\Visual Studio 2010\Projects\Talisma_Lookup4\Talisma_Lookup4\Form1.cs 77 12 Talisma_Lookup4

Pravin More · Answer

ok then take 1 textbox... suppose its name is textBox1 then call your method on event(button click or textchange) you want..... like..... LoadData(textBox1.Text) like above send text in textbox to your method that will give result by searching with that string... Thanks. Pravin.

Mary Bergeron · Answer

That binds the grid to the application but then how do I add some open text boxes that call the Executable statement? EXEC dbo.talisma_Search @talisma_Search ='savage' I want to be able to type any name in a text box and have the results display on the grid. Thank you Mary

Pravin More · Answer

Hi Mary please declare dataset at top of code n try ... like below............ DataSet dataSet1=new DataSet() Thanks Pravin. Mark  Accepted answer  if this post help you.

Mary Bergeron · Answer

Most of the function is set up and appears to be working through out most of the build. However when I reache the below line: dataSet1.Tables.Clear(); the error list displays 10 errors similar to the one below: The name 'dataSet1' does not exist in the current context Here is a copy of the code: SqlConnection conn = new SqlConnection(Data Source='talismadb';Initial Catalog=tlMain;Integrated Security=True); // change this connection string according to you settings of sql server SqlCommand cmd = new SqlCommand(dbo.Talisma_Search conn); cmd.CommandText = Talisma_Search; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@Talisma_Search  Talisma_Search); SqlDataAdapter adapter = new SqlDataAdapter(cmd); dataSet1.Tables.Clear(); adapter.Fill(dataSet1 Table); //now you can show this dataset table on window form how you want like i have shown to Grid below MyGrid.DataSource = dataSet1.Tables[Table]; bindingSource1 = new BindingSource(dataSet1 Table); bindingNavigator1.BindingSource = bindingSource1; MyGrid.DataSource = bindingSource1; Any help would be appreciated. Thank you Mary

Dipal Choksi · Answer

You can find some info here on invoking a sql SP from your windows forms app and also some links on that page which may be helpful for you. good luck!