Getting & Passing a search to a stored procedure

Question

Good day, I have found a few examples that are close to what I want, such as Execute a SQL Stored Procedure and process the results however I am just struggling to see the wood for the trees on this one...

I have an SQL database & a stored procedure within there that has a variable @ModuleName

I want a user to type text into a text box and click search. When search is clicked the word typed into the search box is passed into @ModuleName i.e. Searchtext.txt = @ModuleName

This is then passed off to the StoredProcedure and used to create the SQL for Gridview called GridView1.

I have tried alot of techniques and am clearly missing something

Stored Procedure

ALTER PROCEDURE [dbo].[spModuleID]

@ModuleName char(50)

AS
 BEGIN
 select * from dbo.ModuleID where [ModuleName] = @ModuleName ORDER BY [ModuleName]

 END

Gridview1 links to SQL like this..

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" 
            DataKeyNames="ModuleID">
            <Columns>
                <asp:BoundField DataField="ModuleID" HeaderText="ModuleID" ReadOnly="True" 
                    SortExpression="ModuleID" />
                <asp:BoundField DataField="ModuleName" HeaderText="ModuleName" 
                    SortExpression="ModuleName" />
            </Columns>
        </asp:GridView>

I have some code under the searhc button now that hopefulyli s heading the right way...

Protected Sub Search(sender As Object, e As System.EventArgs) Handles btnSearch.Click

        Dim myds As New DataSet1

        MyConnection.ConnectionString = LearnConnectionString
        Dim disp As New SqlDataAdapter("spModuleName", MyConnection)
        disp.Fill(myds, "dev_display")

        'Below wants to be a datagrid

        txtDisplay.Text = myds.Tables("dev_display").Rows(0).Item("knownsoft")
        myds.Dispose()


    End Sub

I have got: Protected Sub SqlDataSource1_Selecting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting GridView1.DataSource = SqlDataSource1 GridView1.DataBind() End Sub but then get error: Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition. — indofraiser, Apr 22 '13 at 11:29
I can get the Datagrid to display by clicking on add connection but that rather defeats the idea of a search of course. — indofraiser, Apr 22 '13 at 11:33
Your stored proc is wrong. You have a parameter named @modulename but you aren't using it in the proc. What is the name of the table you are querying? — StrayCatDBA, Apr 22 '13 at 11:37

score 0 · Accepted Answer · answered Apr 23 '13 at 10:31

Imports System.Data.OleDb


Dim cmd as new Oledb.OleDbCommand
Dim connection As New SqlConnection("Connection String")


cmd.Connection = connection
cmd.CommandText = "SP_NAME"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PARAMETER_NAME", "PARAMETER_VALUE")

Dim adapter As New SqlDataAdapter(CMD)

 Dim DS as DataSet    
 adapter.Fill(DS)
 connection.Close() 
ds.Dispose()
ds = Nothing

Getting & Passing a search to a stored procedure

1 Answers1