The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

Question

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim ds As DataSet = New DataSet()
    Dim param As SqlParameter = New SqlParameter("@user_code", SqlDbType.Char, 4)
    param.Value = "0016"
    Const sqlstr As String = "select sectors.sector_code,sector_name   from user_sectors inner join sectors on user_sectors.sector_code = sectors.sector_code  where user_code = @user_code  and sectors.sector_code not in ('z')"
    Dim da As SqlDataAdapter = New SqlDataAdapter(sqlstr, _con)
    da.SelectCommand.Parameters.Add("@user_code")
    da.Fill(ds)
    ddl.DataValueField = "sector_code"
    ddl.DataTextField = "sector_name"
    ddl.DataSource = ds.Tables(0)
    ddl.DataBind()    
End Sub

This is not phrased as a question, just a statement, we have to guess what you want. — Jodrell
– Jodrell, Commented Oct 4, 2012 at 9:17
see my answer to do whole parameter instantiation in one line. — Jodrell
– Jodrell, Commented Oct 4, 2012 at 9:26

jbl · Accepted Answer · 2012-10-04 09:13:50Z

2

I guess line

da.SelectCommand.Parameters.Add("@user_code")

should be

da.SelectCommand.Parameters.Add(param)

answered Oct 4, 2012 at 9:13

jbl

15.5k3 gold badges37 silver badges104 bronze badges

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

Comments

John Woo · Accepted Answer · 2012-10-04 09:14:45Z

1

You should pass param in add

da.SelectCommand.Parameters.Add(param)

answered Oct 4, 2012 at 9:14

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Jodrell · Accepted Answer · 2012-10-04 09:24:43Z

1

Thats because its a SqlParameterCollection and not a string collection.

If you call the appropriate Add overload it will however make the instance for you, if given enough information.

Like this perhaps,

Dim newParam = da.SelectCommand.Parameters.Add("@user_code", sqlDbType.Char, 4)
newParam.Value = "0016"

or, replace several lines like this

da.SelectCommand.Parameters.Add( _
    New SqlParameter("@user_code", SqlDbType.Char, 4) With { .Value = "0016" }))

or, if you want to use many lines, just add

da.SelectCommand.Parameters.Add(param)

edited Oct 4, 2012 at 9:24

answered Oct 4, 2012 at 9:11

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Collectives™ on Stack Overflow

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related