Click here to Skip to main content
15,076,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All, I'm a newbie here and would like to seek help from you guys on the following. Thanks in Advance!

I would like to retrieve data from GridView and Text Label to be put inside my database.
However I am receiving the following error:

System.InvalidOperationException: 'Mappings must be either all name or all ordinal based.'

What I have tried:

The following is my Page Load Page:
VB
Public Class UserCfmOffer
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lblOwner.Text = Session("UserID").ToString()
        lblItemID.Text = Session("ItemID").ToString()
        lblItemName.Text = Session("ItemName").ToString()
        lblItemDesc.Text = Session("ItemDesc").ToString()
        lblItemBrand.Text = Session("ItemBrand").ToString()
        lblItemCategory.Text = Session("ItemCategory").ToString()
        lblItemValue.Text = Session("ItemValue").ToString()
        lblItemConditions.Text = Session("ItemConditions").ToString()

  ' Over here we fetch the datatable from session i.e. stored in previous page and passed here 
        Dim dtselectedRows As DataTable = TryCast(Session("dtgetselectedRecords"), DataTable)
            grvSelectedItem.DataSource = dtselectedRows
            grvSelectedItem.DataBind()


The behavior of my button click to push the data from the current page to database:

VB
   Protected Sub btnConfirm_Click(sender As Object, e As EventArgs) Handles btnConfirm.Click
        Dim dt As New DataTable()

        dt.Columns.AddRange(New DataColumn(0) {New DataColumn("ExItem", GetType(String))})

For Each row As GridViewRow In grvSelectedItem.Rows
<pre>Dim ExItem As String = row.Cells(2).Text
            dt.Rows.Add(ExItem)
        Next

        If dt.Rows.Count > 0 Then
  Dim consString As String = ConfigurationManager.ConnectionStrings("BarterTradeDBConnectionString").ConnectionString
            Using conn As New SqlConnection(consString)
                Using sqlBulkCopy As New SqlBulkCopy(conn)

                    'Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.Offer"

                    sqlBulkCopy.ColumnMappings.Add(lblOwner.Text, "Offer_ToUsr")
                    sqlBulkCopy.ColumnMappings.Add(lblItemID.Text, "Offer_ForItemID")
                    sqlBulkCopy.ColumnMappings.Add(vbNull, "Offer_ForSvcID")
                    sqlBulkCopy.ColumnMappings.Add("ExItem", "Offer_ExItemID")
                    sqlBulkCopy.ColumnMappings.Add(vbNull, "Offer_ExSvcID")
                    sqlBulkCopy.ColumnMappings.Add(Session("UserName"), "UserID")

                    conn.Open()
                    sqlBulkCopy.WriteToServer(dt)
                    conn.Close()

                End Using
            End Using

        End If
Posted
Updated 8-Jan-21 21:22pm

Session("UserName"), I assume, returns a number (i.e. "index"); in other cases, you reference a column by name (via .Text).

The message says you can't mix "ordinals" (i.e. indexes) and (column) names.
   
Hi Gerry, thanks for the solution.

However when I changed my code on the Session("UserName") to lblOwner.Text to do a tryout to test the same error message re-appear. Is there anything more I could provide to help you understand more?

Not sure does this helps, but the codes below are my DB settings of the dbo.Offer that I trying to push my data into:
CREATE TABLE [dbo].[Offer](
	[OfferNo] [int] IDENTITY(5000,1) NOT NULL,
	[Offer_Date] [datetime] NOT NULL,
	[Offer_ToUsr] [int] NOT NULL,
	[Offer_ForItemID] [int] NULL,
	[Offer_ForSvcID] [int] NULL,
	[Offer_ExItemID] [int] NULL,
	[Offer_ExSvcID] [int] NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_Offer_OfferNo] PRIMARY KEY CLUSTERED 
(
	[OfferNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Offer] ADD  DEFAULT (getdate()) FOR [Offer_Date]
GO

ALTER TABLE [dbo].[Offer]  WITH CHECK ADD  CONSTRAINT [FK_Offer_UserID] FOREIGN KEY([UserID])
REFERENCES [dbo].[UserInfo] ([UserID])
GO

ALTER TABLE [dbo].[Offer] CHECK CONSTRAINT [FK_Offer_UserID]
GO
   
v2
Comments
AvALaNcE Chen 23-Jan-21 3:14am
   
Anyone have proposed solution?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900