locked
No mapping exists from object type System.Web.UI.WebControls.ListItem to a known managed provider native type.' RRS feed

  • Question

  • User-1352156089 posted

    Hi All,

    I am trying to insert data into my RoomType table taking the Room Name from a textbox and the max occupancy Id from the value of a dropdownlist as follows:

    <asp:TextBox ID="TextBoxRoomName" runat="server"></asp:TextBox><br />
    <asp:Label id="Message" runat="server" /><br /><br />
             
    <asp:DropDownList ID="DDLMaxOccupancy" runat="server" AutoPostBack="True"></asp:DropDownList><br />
                      
    <asp:Button ID="BtnAddRoomType" runat="server" Text="Button" />
      Function AddRoomType(RoomName As String) As Integer
            Using con As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
                Dim dbCommand As New SqlCommand
                dbCommand.CommandText = "dbo.spAddRoomType_UsingExists"
                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Connection = con
                dbCommand.Parameters.Add(New SqlParameter("@RoomName", SqlDbType.NChar, 40))
                dbCommand.Parameters("@RoomName").Value = TextBoxRoomName.Text
                dbCommand.Parameters.Add(New SqlParameter("@OccupancyId", SqlDbType.Int))
                dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem)
                dbCommand.Parameters.Add(New SqlParameter("@Result", SqlDbType.Int))
                dbCommand.Parameters("@Result").Direction = ParameterDirection.ReturnValue
                Dim commandResult As Integer = 1
                Try
                    con.Open()
                    dbCommand.ExecuteNonQuery()
                    commandResult = CType(dbCommand.Parameters("@Result").Value, Integer)
                Catch ex As SqlException
                    commandResult = ex.Number
                Finally
                    con.Close()
                End Try
                Return commandResult
            End Using
        End Function

    The error is thrown at level of the following line of code:

    dbCommand.ExecuteNonQuery()

    Thank you in advance for your help with this one.

    Sunday, February 24, 2019 4:52 PM

Answers

  • User-1352156089 posted

    I managed by myself to understand where the error originated:

    I wrote 2 redundant lines of code:

    dbCommand.Parameters.Add(New SqlParameter("@OccupancyId", SqlDbType.Int))
    dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem.Value)

    while only 1 line of code was necessary: 

    dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem.Value)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 25, 2019 11:21 AM

All replies

  • User-893317190 posted

    Hi Claudio7810,

    You are using  a control of type  WebControls.ListItem as the value of SqlParameter in your line.

     dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem)

    Please change your DDLMaxOccupancy.SelectedItem to  

    DDLMaxOccupancy.SelectedItem.Value(if you want to use text please change Value to Text)

    to the value of your  SelectedItem's value.

    Best regards,

    Ackerly Xu

    Monday, February 25, 2019 2:13 AM
  • User-1352156089 posted

    Hi Ackerly,

    thank you for your feedback. 

    I changed the line as per your suggestion to now: 

     dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem.Value)

    But the query still fails. 

    Just in case if you need to see my stored procedure, it looks like as follows: 

    USE [MyDB]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    CREATE PROCEDURE [dbo].[spAddRoomType_UsingExists]
    (
         @RoomName nchar(40),
    	  @OccupancyId int
    )
    
    AS
    DECLARE @Result int
    BEGIN TRANSACTION
    IF EXISTS
    (
          SELECT
                NULL
          FROM
                dbo.RoomType WITH (UPDLOCK)
          WHERE
                RoomName = @RoomName
    ) 
          BEGIN
                SELECT @Result = -1
          END
    ELSE
          BEGIN
                INSERT INTO
                      dbo.RoomType
                (
                      RoomName,
    				  OccupancyId
                    )
                VALUES
                (
                      @RoomName,
    				  @OccupancyId
                )
                SELECT @Result = @@ERROR
          END
    IF @Result <> 0
          BEGIN
                ROLLBACK
          END
    ELSE
          BEGIN
                COMMIT
          END
    RETURN @Result
    GO

    The OccupancyId value comes from a dropdownlist.

    Thanks a lot,
    Claudio

    Monday, February 25, 2019 8:44 AM
  • User-893317190 posted

    Hi Claudio7810,

    Does it still show the same error?

    Could you share the error message?

    Best regards,

    Ackerly Xu

    Monday, February 25, 2019 10:05 AM
  • User-1352156089 posted

    I managed by myself to understand where the error originated:

    I wrote 2 redundant lines of code:

    dbCommand.Parameters.Add(New SqlParameter("@OccupancyId", SqlDbType.Int))
    dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem.Value)

    while only 1 line of code was necessary: 

    dbCommand.Parameters.AddWithValue("@OccupancyId", DDLMaxOccupancy.SelectedItem.Value)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 25, 2019 11:21 AM