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

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.

Answer 1

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

Answer 2

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

Answer 3

User-893317190 posted

Hi Claudio7810,

Does it still show the same error?

Could you share the error message?

Best regards,

Ackerly Xu

Answer 4

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)