How do I retrieve data from gridview and text.label and put into DB?

Question

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 7-Jan-21 15:53pm

AvALaNcE Chen

Updated 8-Jan-21 21:22pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2021-01-07T20:01:00

Solution 1

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.

Posted 7-Jan-21 20:01pm

Gerry Schmitz

AvALaNcE Chen · Answer 2 · 2021-01-08T21:22:00

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