Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Stored Procedure based on user input parameters

    I created a SQL stored procedure which picks up values from a VB.NET application.

    Code:
    USE [Traffic]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [CENTRAL\TIMOVKP].[spReturnValue]
    (@table varchar(100),@field_name varchar(100),@field_value varchar(100))
    as
    SET nocount on
    DECLARE @sql varchar(8000)
    set @sql='SELECT COUNT(*) FROM '+@table+' WHERE '+@field_name+' = '+char(39)+@field_value+char(39)
    EXEC (@sql)

    From the VB.NET app, the user will enter the tablename, field, and value. Then a record count will be displayed to the user. However I only get a 0 count. It must be the syntax used in my SP. Any ideas?

  2. #2
    Join Date
    Oct 2009
    Posts
    19
    Try deleting the line --SET nocount on
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    Still get the same result

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This code strikes me as "wrong" on so many levels!

    First of all, you REALLY need to read up on SQL Injection. You absolutely would never get approval from me to put this code into production because it puts your whole database at risk.

    Well down on the list but still important is that this code assumes that the user running the code has SELECT permissions on every object that they need to query. You can't always depend on that. This might well be why you're getting a zero value. To test this theory, run the code with a valid set of parameters first as an end user, then again with those parameters as a database owner.

    Another guess would be that you're missing the schema qualifier from your table name argument, but there are so many ways this could go bad that I'd like you to try these choices first.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2010
    Posts
    4
    Only dbo's will be using this for testing purposes. After testing this in sql with valid parameters, it works. So now I assume the problem is in my VB.net code. After making a few modifications I came up with the following:

    Code:
    Dim connectionString As String = "Data Source=develop;Initial Catalog=Traffic;Integrated Security=True;"
            Dim connection As SqlConnection = New SqlConnection(connectionString)
    
            Dim command As SqlCommand = New SqlCommand("spReturnValue")
            command.CommandType = CommandType.StoredProcedure
       
            command.Parameters.AddWithValue("@field_name", field_name.Text)
            command.Parameters.AddWithValue("@field_value", field_value.Text)
            command.Parameters.Add("@sql", SqlDbType.VarChar, 8000)
            command.Parameters("@sql").Direction = ParameterDirection.Output
    
            command.Connection = connection
            connection.Open()
    
            command.ExecuteNonQuery()
    
            txtResult.Text = command.Parameters("@sql").Value
    
            connection.Close()
    Now the txtResult, instead of displaying my count output. It just displays my query with the parameters from the textboxes:

    SELECT COUNT(*) FROM tblSpeedStudy WHERE strRoadName = 'US45'

  6. #6
    Join Date
    Oct 2009
    Posts
    19
    You should use the ExecuteScalar method to retrieve a single value

    ExecuteNonQuery is used for SQL statements that don't return any data like (INSERT, UPDATE, DELETE)
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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