I'm using the sqlclient namespace to access data in a sql server database. I set up the sqlcommand with two parameters; this is because the command text may vary (I want to use the same sqlcommand object for two different SELECT statements, so I don't end up with a huge multiplicity of commands, with slightly varying command texts):

Dim condb As New sqlconnection(connectionString)
Dim da As New SqlDataAdapter
Dim ds As New Dataset1
Dim cmdSelect As New SqlCommand

With cmdSelect
    .Connection = condb
    .CommandText = _
         "SELECT * FROM DeliveryDetails " & _
         "WHERE (JobNumber = @pJobNumber))"
    .CommandType = CommandType.Text
End With

With cmdSelect.Parameters
    .Add("@pDeliveryNumber", SqlDbType.BigInt, _
                8, "DeliveryNumber")
    .Add("@pJobNumber", SqlDbType.VarChar, 5, "JobNumber")
End With

cmdSelect.Parameters("@pJobNumber").Value = "35000"
da.SelectCommand = cmdSelect
da.Fill(ds, "DeliveryDetails")

Dim dr As DataRow
For Each dr In ds.Tables("DeliveryDetails").Rows
    MessageBox.Show(dr.Item("DeliveryNumber"))
Next

However, this code throws a 'system error' (very helpful!) However, if I specify a value for the @pDeliveryNumber parameter, the code runs through smoothly. Is it truly the case that if you do not specify values for every single parameter belonging to a command then it will throw an exception, even if the valueless parameter in question is not used in the command text?

Yes.

Why do you add a parameter you aren't using?

Peter
------------------------------------------------------
Work smarter, not harder.

The code I showed is basically a simplified version of what I'm doing. What I am doing is setting up a load of command objects in a 'databaseobjects' class, which will be references by a 'databaseoperations' class. I have three different select queries I want to carry out against one table - one to select by a job number, one to select by job number/delivery date, and one to select by delivery number. Rather than create three separate commands, it would be useful if I could create just one command, that had the three required parameters ('@pJobNumber', '@pDeliveryDate', '@pDeliveryNumber'). I could then change the command text in the command when a request was made to the databaseobjects object for a DeliverySelectCommand according to the 'CommandType' value passed in (which could be one of 'JobNumber', 'JobNumberAndDeliveryDate', 'DeliveryNumber') and serve it to the client.

What is the method in your "databaseobjects" class returning? A command object or the query results?

Peter
------------------------------------------------------
Work smarter, not harder.

databaseobjects simply returns the command object - databaseoperations actually uses that command to run the query and return a filled dataset back to its client

I recommend setting up a case statement to handle the different types of queries you are going to use, and build each query and its parameters based on the type. Everything else can be the same. In your case, you can just append the appropriate where condition based on the type.

Dim sSql As New System.Text.StringBuilder()
...

sSql.Append("SELECT * FROM DeliveryDetails WHERE ")
Select Case <Command Type>
    Case JobNumber
        sSql.Append("JobNumber = @pJobNumber")
        cmdSelect.Parameters.Add("@pJobNumber", SqlDbType.VarChar, 5).Value = <search value>
    ...
End Select
cmdSelect.CommandText = sSql.ToString()
cmdSelect.CommandType = CommandType.Text
...

Peter
------------------------------------------------------
Work smarter, not harder.

I've actually found a much neater way of doing this.

* The 'objects' class, initialises a collection with all of the parameters I will need.
* I then have a 'GenerateCommand' function, which accepts one parameter, that of the command text. It passes this into an 'Identify Parameters' function, that examines the command text for all parameters, identified by starting characters of '@p'.
* Once 'GenerateCommand' has an array of all parameters, it adds them to a new command object, using the collection initialised earlier. It also sets the connection and the command type, then passes back the dynamically generated command.

This has meant that when I require a new command, I don't have to worry about initialising parameters for it - the object class just returns the 'GenerateCommand' return value.

If anyone wants the code for this then let me know via email. It's fairly simple to do, but no point in duplicating effort.

Hi,

I'm interested in seeing your code.

Thanks

Renee C. Walker

Ok, just didn't want to post if no-one was interested...

One thing to note is that when writing your SQL commands, you must have spaces around any comparison operators. For example,

 SELECT * FROM pubs WHERE pubid = @pPubId

would work, whereas

 SELECT * FROM pubs WHERE pubid=@pPubId

wouldn't. This is because when parameters are being located, a split command is used to separate out all words in the sql statement.

Any probs, just ask.

Code as follows ( I've formatted as best as I can for this box, but still some overflows):

Imports System.Data
Imports System.Data.SqlClient

' a class to set up all connections and commands and serve them to the
' operations class.
Public Class Objects

    ' procedure for adding a new command:
    ' 1. Add the new sql statement into GlobalVariables.
    ' 2. Add a new element to the relevant enumeration.
    ' 3. Check the parameter is initialised in InitialiseParameters()
    ' 4. Add the additional 'case' statement to the relevant read-only
    ' property (or create a new read only property to serve up the
    ' command.) and serve up the command using the 'GenerateCommand'
    ' function.

#Region "Class Level Declarations"

    ' connection string is picked up from an application configuration
    ' file...
    Dim mConDespatch As New SqlConnection( _
    Configuration.ConfigurationSettings.AppSettings( _
    "Connection String"))
    ' collection to hold all parameter objects...
    Dim mDespatchParameters As New Collection

    ' created various enumerations here for purposes of returning similar
    ' commands...
    Public Enum ExampleCommandEnum
        SelectCommand1
        SelectCommand2
    End Enum

#End Region

    Public Sub New()
        InitialiseParameters()
    End Sub

#Region "General Database Objects"

    Public ReadOnly Property DespatchConnection()
        Get
            Return mConDespatch
        End Get
    End Property

#End Region

#Region "Dynamic Command Generation"
    Private Function GenerateCommand(ByVal CommandText As String) As _
        SqlCommand
        ' dimension and set up command...
        Dim cmdNew As New SqlCommand(CommandText)
        With cmdNew
            .Connection = mConDespatch
            .CommandType = CommandType.Text
        End With

        ' get necessary parameters...
        Dim Params() As String = GetParameters(CommandText)
        Dim Param As String
        If Not (Params Is Nothing) Then
            For Each Param In Params
                ' check parameter not already present...
                If cmdNew.Parameters.IndexOf(Param) = -1 Then
                    Try
                        cmdNew.Parameters.Add( _
                            mDespatchParameters(Param).ParameterName, _
                            mDespatchParameters(Param).SqlDbType, _
                            mDespatchParameters(Param).Size, _
                            mDespatchParameters(Param).SourceColumn)
                    Catch ex As Exception
                        Throw New Exception("Parameter " & Param & _
                            " is not part of DespatchParameters " & _
                            "collection")
                    End Try
                End If
            Next
        End If
        Return cmdNew

    End Function

    Private Function GetParameters(ByVal CommandText As String) _
        As String()
        Dim words() As String
        Dim params() As String
        Dim cursor As Integer = 0, counter As Integer = 0
        Dim remove() As Char = {",", ")", "("}

        ' split text into an array, each element containing one
        ' word...
        words = CommandText.Split(" ")

        ' go through array, and add all parameters(beginning with
        ' '@p') to params array...
        counter = 0
        For cursor = 0 To (words.GetLength(0) - 1)
            If words(cursor).TrimStart(remove).StartsWith("@p") Then
                ReDim Preserve params(counter)
                ' remove leading and trailing commas and opening and
                ' closing brackets...
                params(counter) = _
                    words(cursor).TrimEnd(remove).TrimStart(remove)
                counter += 1
            End If
        Next

        Return params
    End Function

    Private Sub InitialiseParameters()
        ' initialise mDespatchParameters...

        ' initialise all required parameters (I have one parameter
        ' for each field in my database).
        ' As I add them to the collection, they get a string key
        ' which is the same as the parameter name, which allows them
        ' to be easily called by the GenerateCommand function...
        mDespatchParameters.Add(New SqlParameter("@pFieldName", _
            SqlDbType.Int, 4, "FieldName"), "@pFieldName")
    End Sub
#End Region

    Public ReadOnly Property ExampleCommand(ByVal Type As _
        ExampleCommandEnum) As SqlCommand
        Get
            Select Case Type
                Case 0
                    ' I define all sql commands in a separate module,
                    ' following added just for readability
                    Dim ExampleSQLCommand As String = _
                        "SELECT * FROM PUBS"
                    Return GenerateCommand(ExampleSQLCommand)
                Case 1
                    Dim ExampleSQLCommand As String = _
                        "SELECT TOP 1 * " & _
                        "FROM PUBS"
                    Return GenerateCommand(ExampleSQLCommand)
            End Select
        End Get
    End Property
End Class