SqlParameter not supplied when using Parameters.AddWithValue

Question

This is how I set up my command. It stops with the first parameter, UpdateType. This code is being updated from VB.NET 2008 version.

Dim db As New DB()
Dim cmd As SqlCommand = New SqlCommand()

'Put into an object, and use AddWithValue due to Parameters.Add being deprecated.
Dim UpdateType As String = "PARAMETERS"

If IsNewJob Then
  cmd.CommandText = "sp_MB_AddJob"
Else
  cmd.CommandText = "sp_MB_UpdateJob"
  cmd.Parameters.AddWithValue("@UpdateType", SqlDbType.NVarChar).Value = UpdateType
  cmd.Parameters.AddWithValue("@OrigJobName", OrigJobName.ToString)
End If

cmd.Parameters.AddWithValue("@UserID", CInt(Utils.GetLoggedInUserID))
cmd.Parameters.AddWithValue("@ProjectName", ProjectName.ToString)

Add(String, Object) is deprecated, not Add(String, SqlDbType). Use Parameters.Add for your @UpdateType parameter (and the others if they aren't being treated as objects). SqlParameterCollection.Add Method. AddWithValue doesn't support supplying the datatype. — Thom A
– Thom A ♦, Commented Dec 13, 2019 at 17:10
You also are not setting the CommandType to StoredProcedure. And you really should use a different prefix than sp_, or even better no prefix at all. sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange
– Sean Lange, Commented Dec 13, 2019 at 17:18
One of your strings is probably nothing. If the db field allows null then use SqlString.Null when your strings are nothing. — Skye MacMaster
– Skye MacMaster, Commented Dec 13, 2019 at 17:20

Mark Schultheiss · Accepted Answer · 2019-12-14 12:12:40Z

2

You should use .Add instead with the type and for NVARCHAR, VARCHAR, or VARBINARY with the length. Here I show how to do the tings you have in the question, I made up lengths just for the example. Using AddWithValue can have negative impact on SQL performance and other things.

Some information to help you can be found in many places including here https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

Dim db As New DB()
Dim cmd As SqlCommand = New SqlCommand()
Dim UpdateType As String = "PARAMETERS"
cmd.CommandType = CommandType.StoredProcedure

If IsNewJob Then
  cmd.CommandText = "sp_MB_AddJob"
Else
  cmd.CommandText = "sp_MB_UpdateJob"
  cmd.Parameters.Add("@UpdateType", SqlDbType.NVarChar, 10).Value = UpdateType
  cmd.Parameters.Add("@OrigJobName", SqlDbType.NVarChar, 50).Value = OrigJobName.ToString
End If
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = CInt(Utils.GetLoggedInUserID)
cmd.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 30).Value = ProjectName.ToString

edited Dec 14, 2019 at 12:12

answered Dec 13, 2019 at 22:01

Mark Schultheiss

34.3k13 gold badges75 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Schultheiss Over a year ago

Note, I did not deal with the missing IsNewJob, made assumption there it exists

Deleted Over a year ago

The default value for SQLParameter.Size is inferred to the Value size so if you have a long length value the buffer will be more. So if you have a defined size it can also be truncated if the value is more than size.

Mary Over a year ago

In the .Add(String, SqlDbType, Int, String) method the final string is the source column name. Your code passes no values at all.

Mark Schultheiss Over a year ago

@Mary You are right, I fixed that, I almost never use this form, mostly setting the parameter properties individually and not VB in a while.

Mark Schultheiss Over a year ago

This really should be broken into one method for sp_MB_AddJob and one for sp_MB_UpdateJob

Mary · Accepted Answer · 2019-12-14 00:52:24Z

Keep your database objects local to the method where they are used so you can control that they are closed and disposed. `Using...End Using blocks take care of this for you. Note a single Using block is handling both the connection and the command.

The .Add method is NOT being deprecated. What is obsolute is the .Add(String, Object) overload. `.AddWithValue is certainly out of favor. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

I had to guess at the datatype and column size of your parameters. Check your database for the actual values and correct the code accordingly.

Private Sub OpCode()
    Dim UpdateType As String = "PARAMETERS"
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand()
        cmd.Connection = cn
        If IsNewJob Then
            cmd.CommandText = "sp_MB_AddJob"
        Else
            cmd.CommandText = "sp_MB_UpdateJob"
            cmd.Parameters.Add("@UpdateType", SqlDbType.NVarChar, 50).Value = UpdateType
            cmd.Parameters.Add("@OrigJobName", SqlDbType.NVarChar, 200).Value = OrigJobName.ToString
        End If

        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = CInt(Utils.GetLoggedInUserID)
        cmd.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 200).Value = ProjectName.ToString
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Collectives™ on Stack Overflow

SqlParameter not supplied when using Parameters.AddWithValue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related