Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: stored procedure works in QA but not on web site

    I am trying to run a stored procedure and the display the results in a datagrid.

    Dim Confirmed As String
    Dim Filter As String
    Dim SearchString As String

    Confirmed = ddlConfirmed.SelectedItem.Value
    Filter = ddlFilter.SelectedItem.Value
    SearchString = txtSearch.Text

    Dim strConn As String = Commonf.dbConnect()
    Dim Conn As SqlConnection = New SqlConnection(strConn)
    Dim objSPComm As New SqlCommand("ContactsSearch2", Conn)
    objSPComm.CommandType = CommandType.StoredProcedure

    Dim objParameter1 As New SqlParameter("@Confirmed", SqlDbType.NChar, 50)
    objSPComm.Parameters.Add(objParameter1)
    objParameter1.Direction = ParameterDirection.Input
    objParameter1.Value = Confirmed

    Dim objParameter2 As New SqlParameter("@Filter", SqlDbType.NChar, 50)
    objSPComm.Parameters.Add(objParameter2)
    objParameter2.Direction = ParameterDirection.Input
    objParameter2.Value = Filter

    Dim objParameter3 As New SqlParameter("@SearchTerm", SqlDbType.NChar, 50)
    objSPComm.Parameters.Add(objParameter3)
    objParameter3.Direction = ParameterDirection.Input
    objParameter3.Value = SearchString


    Conn.Open()

    dgContacts.DataSource = objSPComm.ExecuteReader()
    dgContacts.DataBind()

    Conn.Close()

    when I step into the program when I get to this line dgContacts.DataSource = objSPComm.ExecuteReader()
    and look at the records affected it says -1, which I would assume means that the SP failed to retrieve any results. When I execute the SP in Query Analyzer it seems to work fine

    SP looks like this

    CREATE PROCEDURE dbo.ContactsSearch2
    (
    @SearchTerm nvarchar(50) = NULL,
    @Filter nvarchar(50) = NULL,
    @OrderBy nvarchar(50) = Null,
    @Confirmed nvarchar(50) = NULL
    )

    AS
    SET NOCOUNT ON

    IF (@Filter = "ALL")

    SELECT * FROM vContactsAndCoaches WHERE
    (LastName LIKE @SearchTerm) OR
    (FirstName LIKE @SearchTerm) OR
    (Email LIKE @SearchTerm) OR
    (ReferredBy LIKE @SearchTerm) OR
    (HomePhone LIKE @SearchTerm) OR
    (StockNo LIKE @SearchTerm) OR
    (SerialNo LIKE @SearchTerm) OR
    (Address LIKE @SearchTerm) OR
    (City LIKE @SearchTerm) OR
    (State LIKE @SearchTerm) OR
    (Location LIKE @SearchTerm) OR
    (MiddleName LIKE @SearchTerm) OR
    (Zip LIKE @SearchTerm) OR
    (Email2 LIKE @SearchTerm) OR
    (Origin LIKE @SearchTerm) OR
    (Location LIKE @SearchTerm) OR
    (EnteredBy LIKE @SearchTerm) OR
    (UpdatedBy LIKE @SearchTerm) OR
    (SpouseFirstName LIKE @SearchTerm) OR
    (CONVERT(varchar, ContactID) LIKE @SearchTerm)
    GO

    Any ideas?
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    First step is to verify the parameters.
    Try using the profiler to get the call and then running that.

    You can also create a trace table and log the parameters at the beginning of the SP.

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    records affected that your FE captures will be -1 unless you scroll all the way to the end. it is not indicative of the sp failure.

  4. #4
    Join Date
    Jul 2003
    Posts
    23
    Thanks you guys, my parameters were not of the same type. Nigelrivett how do you create a trace tables? Is that where you insert variable values into a spare table at certian times?

    D--
    "Everything is possible, somethings are just less likely then others"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •