locked
Error: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects RRS feed

  • Question

  • User1768460350 posted
    I am getting the following error from my web form:

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

    And it's hanging up on the first parameter on the:

    dbCommand.Parameters.Add("prmDeptID") line.

    I've been working on this all day and am stumped!

    And it's coming from this block of code from the abstraction layer:

    Public Function Create_Content(ByVal DeptID As Int32, ByVal Title As String, _

    ByVal PubDate As Date, ByVal RemDate As Date, ByVal Category As Int32, ByVal Body As String, _

    ByVal Author As Int32, ByVal Image As Boolean, ByVal Attach As Boolean, ByVal HasParent As Boolean, _

    ByVal ParentID As String, ByVal Phase As Int32) As String

    Dim dbConnection As New SqlConnection(ConfigurationSettings.AppSettings("DbConn"))

    Dim dbCommand As New SqlCommand("sp_add_content", dbConnection)

    dbCommand.CommandType = CommandType.StoredProcedure

    Dim prmDeptID As New SqlParameter("@DepartmentID", SqlDbType.Int, 4)

    prmDeptID.Direction = ParameterDirection.Input

    prmDeptID.Value = DeptID

    dbCommand.Parameters.Add("prmDeptID")

    Dim prmTitle As New SqlParameter("@contenttitle", SqlDbType.VarChar, 100)

    prmTitle.Value = Title

    dbCommand.Parameters.Add("prmTitle")

    Dim prmPubDate As New SqlParameter("@pubdate", SqlDbType.DateTime)

    prmPubDate.Value = PubDate

    dbCommand.Parameters.Add("prmPubDate")

    Dim prmRemDate As New SqlParameter("@remdate", SqlDbType.DateTime)

    prmRemDate.Value = RemDate

    dbCommand.Parameters.Add("prmRemDate")

    Dim prmCategory As New SqlParameter("@category", SqlDbType.Int, 4)

    prmCategory.Value = Category

    dbCommand.Parameters.Add("prmCategory")

    Dim prmBody As New SqlParameter("@body", SqlDbType.Text)

    prmBody.Value = Body

    dbCommand.Parameters.Add("prmBody")

    Dim prmAuthor As New SqlParameter("@author", SqlDbType.Int)

    prmAuthor.Value = Author

    dbCommand.Parameters.Add("prmAuthor")

    Dim prmHasParent As New SqlParameter("@hasparent", SqlDbType.Bit)

    If HasParent = Nothing Then

    prmHasParent.Value = 0

    Else

    prmHasParent.Value = HasParent

    End If

    dbCommand.Parameters.Add("prmHasParent")

    Dim prmParentID As New SqlParameter("@parentid", SqlDbType.VarChar, 10)

    If ParentID = Nothing Then

    prmParentID.Value = DBNull.Value

    Else

    prmParentID.Value = ParentID

    End If

    dbCommand.Parameters.Add("prmParentID")

    Dim prmPhase As New SqlParameter("@phase", SqlDbType.Int)

    If Phase = Nothing Then

    prmPhase.Value = DBNull.Value

    Else

    prmPhase.Value = Phase

    End If

    dbCommand.Parameters.Add("prmphase")

    Try

    dbConnection.Open()

    dbCommand.ExecuteNonQuery()

    dbConnection.Close()

    Return "Record Inserted"

    Catch ex As Exception

    Return ex.ToString()

    End Try

    End Function

    With the following code from my Web Form:

    Private Sub btnCreateItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    If Page.IsValid Then

    Dim create_item As New DataAccess.Data_Input

    Dim DeptID As Int32

    DeptID = CInt(1)

    lblStatus.Text = create_item.Create_Content(DeptID, txtTitle.Text, datePub.SelectedDate, dateRem.SelectedDate, ddlcategories.SelectedValue, txtBody.Text, CInt(txtAuthor.Text), False, False, False, 0, 1)

    End If

    End Sub

    HELP!

    Wednesday, July 27, 2005 5:43 PM

All replies

  • User-1804824805 posted
    Rather than:

    Dim prmDeptID As New SqlParameter("@DepartmentID", SqlDbType.Int, 4)
    prmDeptID.Direction = ParameterDirection.Input
    prmDeptID.Value = DeptID
    dbCommand.Parameters.Add("prmDeptID")


    Do this:

    Dim prmDeptID As New SqlParameter("@DepartmentID", SqlDbType.Int, 4)
    prmDeptID.Direction = ParameterDirection.Input
    prmDeptID.Value = DeptID
    dbCommand.Parameters.Add(prmDeptID)



    Wednesday, July 27, 2005 10:36 PM
  • User1768460350 posted

    Wow.  Do I feel stupid. 

    Captain Obvious is using his flex time this week.  I'm trying to get by.

    Thanks again!  Works wonderfully now.

    Thursday, July 28, 2005 12:06 PM
  • User-1377001266 posted

     I have the same error message, but I don't know where the problem is. Here's the code:

     
                SqlParameter[] param = new SqlParameter[4];
    param[0] = new SqlParameter("@Book_Title", SqlDbType.NVarChar, 100, ParameterDirection.Input.ToString());
    param[0].Value = entCatalog.BookTitle;
    param[1] = new SqlParameter("@Author_ID", SqlDbType.Int, 4, ParameterDirection.Input.ToString());
    param[1].Value = entCatalog.AuthorID;
    param[2] = new SqlParameter("@ISBN", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
    param[2].Value = entCatalog.BookISBN;
    param[3] = new SqlParameter("@Category_ID", SqlDbType.Int, 4, ParameterDirection.Input.ToString());
    param[3].Value = entCatalog.CategoryID;

    SqlCommand com = new SqlCommand();
    com.Connection = new SqlConnection(...some connection string);
    com.CommandType = CommandType.StoredProcedure;
    com.CommandText = "sp_InsertNewRecord";
    com.Parameters.Add(param); //Error: The SqlParameterCollection only accepts non-null SqlParameter type objects
    com.ExecuteNonQuery();
     

    How do I go about this?

    Wednesday, March 11, 2009 12:17 AM
  • User1867929564 posted

    Hi,

    Debug and check the value you are passing to each param.
    I have never use ParameterDirection.Input.ToString()
    Also check if your table allow any null value.

     

    Saturday, September 19, 2009 3:07 AM
  • User481247336 posted
    SqlParameter[] param = new SqlParameter[4];
                param
    [0] = new SqlParameter("@Book_Title", SqlDbType.NVarChar, 100, ParameterDirection.Input.ToString());
                param
    [0].Value = entCatalog.BookTitle;
                param
    [1] = new SqlParameter("@Author_ID", SqlDbType.Int, 4, ParameterDirection.Input.ToString());
                param
    [1].Value = entCatalog.AuthorID;
                param
    [2] = new SqlParameter("@ISBN", SqlDbType.NVarChar, 50, ParameterDirection.Input.ToString());
                param
    [2].Value = entCatalog.BookISBN;
                param
    [3] = new SqlParameter("@Category_ID", SqlDbType.Int, 4, ParameterDirection.Input.ToString());
                param
    [3].Value = entCatalog.CategoryID;

               
    SqlCommand com = new SqlCommand();
                com
    .Connection = new SqlConnection(...some connection string);
                com
    .CommandType = CommandType.StoredProcedure;
                com
    .CommandText = "sp_InsertNewRecord";
                for(int i=0;i<
    param.length();i++))
    com.Parameters.Add(param[i]); 
                com.ExecuteNonQuery();

    now try this
    Thursday, April 4, 2013 3:08 AM