passing input parameter to sql server sproc

  • Thread starter Thread starter wjer
  • Start date Start date
W

wjer

I have a very simple Stored Procedure that isn't working for me:

CREATE PROCEDURE FindTutors
(
@LastName NVarChar
)
As
(
SELECT fn, ln FROM main WHERE ln = @LastName
)
GO

Very simple; just trying to pass the @LastName parameter from my
ASP.NET code. Still, whenever I click the button that is supposed to
show all results in a datagrid, nothing comes up. I have altered the
Sproc to just be "SELECT fn, ln FROM Main WHERE ln = 'Damon' " and it
works fine then - so I know it's a matter of passing this parameter.
Here is my codebehind - anyone have any ideas?

Public Sub FindTutors(ByVal sender As Object, ByVal e As EventArgs)
Handles btnFind.Click

Dim cn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnString"))
Dim Cmd As SqlCommand = New SqlCommand("FindTutors", cn)

Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value() =
txtLastName.Text.ToString()
'Cmd.Parameters.Add("@LastName", txtLastName.Text)


cn.Open()

Dim rdrContent As SqlDataReader

gridTutors.DataSource = Cmd.ExecuteReader()
gridTutors.DataBind()

cn.Close()

End Sub
 
This is an easy one. First, get rid of the parens after the AS clause.
Next, consider that you can't pass in a string to populate an IN expression. That's a limitation of SQL Server. There are ways around this that some have suggested:
a.. Use an "EXEC" query that takes the whole SELECT statement and builds it on the server and executes it there. This is dangerous though as it opens your application to SQL injection attacks.
b.. Write a function that accepts a delimited string and returns a Table datatype that CAN be used in an IN expression. I like this approach better--so much so that I'm writing an article on it. Nope, it's not done yet and until it's published I can't reveal how I do it, but it's not that hard.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top