Please Help! I am a System Developer at a private company and I encounter this error while I was playing at my codes. I am using Visual Studio 2010 and my database is MSSQL 2008 R2. I keep on searching on the net for the answer but I failed. Please help me with this one and Thank you in advance.
The process is so simple that is only to populate the datagridview with data then save it to the database. Then on the process of saving the data, I encounter an error message “No mapping exists from object type SLI_Payroll.Dat aSetLibrary+DTC ontactsDataTabl e to a known managed provider native type.”
The data of the datagridview will be transfered to a dataset datatable. The dataset is not hard code and it was added as a New Item.
Sorry but i will put the whole code of the form.
This is the code of my SQL
But if you will remove the comment at solution 2 then comment solution 1. The saving will be successful.
Please help
The process is so simple that is only to populate the datagridview with data then save it to the database. Then on the process of saving the data, I encounter an error message “No mapping exists from object type SLI_Payroll.Dat aSetLibrary+DTC ontactsDataTabl e to a known managed provider native type.”
The data of the datagridview will be transfered to a dataset datatable. The dataset is not hard code and it was added as a New Item.
Sorry but i will put the whole code of the form.
Code:
Public Class frmContacts
'------------------------------------------------------------------------------------
Private Function sp_Contacts(ByVal DT As DataTable)
zResultReturnBoolean = Nothing
Try
zCommand = SetSQLCommand("sp_tabContacts")
CON.Open()
With zCommand.Parameters
.AddWithValue("@DT", DT)
End With
zCommand.CommandType = CommandType.StoredProcedure
If zCommand.ExecuteNonQuery() = 0 Then : zResultReturnBoolean = False : Else : zResultReturnBoolean = True : End If
CON.Close()
Catch ex As Exception
CON.Close()
MessageBox.Show(ex.Message, "SQL Error!")
zResultReturnBoolean = False
End Try
Return zResultReturnBoolean
End Function
'------------------------------------------------------------------------------------
Private Sub frmContacts_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Call TempDataTable()
End Sub
'------------------------------------------------------------------------------------
Private Sub btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click, btnSave.Click
Select Case sender.name
Case btnClose.Name
Me.Close()
Case btnSave.Name
Call SaveProcedure()
End Select
End Sub
'------------------------------------------------------------------------------------
Private Sub SaveProcedure()
Dim DTContacts As New DataSetLibrary.DTContactsDataTable
For Each dgRow As DataGridViewRow In dgList.Rows
'--Solution 1---
DTContacts.AddDTContactsRow(Val(dgRow.Cells(c_ID.Index).Value),
dgRow.Cells(c_name.Index).Value,
Format(CDate(dgRow.Cells(c_bday.Index).Value), "MM/dd/yyyy"),
dgRow.Cells(c_address.Index).Value,
dgRow.Cells(c_telNo.Index).Value)
'---------------
''--Solution 2---
'zTempDataTable.Rows.Add(Val(dgRow.Cells(c_ID.Index).Value),
' dgRow.Cells(c_name.Index).Value,
' Format(CDate(dgRow.Cells(c_bday.Index).Value), "MM/dd/yyyy"),
' dgRow.Cells(c_address.Index).Value,
' dgRow.Cells(c_telNo.Index).Value)
''---------------
Next
'--solution 1
If sp_Contacts(DTContacts) Then
''--solution 2
'If sp_Contacts(zTempDataTable) Then
MessageBox.Show("Successfully Saved!")
End If
End Sub
'------------------------------------------------------------------------------------
Private Sub DisplayRecord()
Dim DT As New DataTable
DT = GetDataTable("SELECT * FROM tabContacts A ORDER BY A.ID")
dgList.Rows.Clear()
For Each dRow As DataRow In DT.Rows
dgList.Rows.Add()
With dgList.Rows(dgList.RowCount - 1)
.Cells(c_ID.Index).Value = Val(dRow.Item("ID").ToString)
.Cells(c_name.Index).Value = dRow.Item("name").ToString
.Cells(c_bday.Index).Value = Format(CDate(dRow.Item("bday").ToString), "MM/dd/yyyy")
.Cells(c_address.Index).Value = dRow.Item("address").ToString
.Cells(c_telNo.Index).Value = dRow.Item("telno").ToString
End With
Next
End Sub
'------------------------------------------------------------------------------------
Public Sub TempDataTable()
zTempDataTable = New DataTable
With zTempDataTable.Columns
.Add(New DataColumn("ID", GetType(Integer)))
.Add(New DataColumn("name", GetType(String)))
.Add(New DataColumn("bday", GetType(DateTime)))
.Add(New DataColumn("address", GetType(String)))
.Add(New DataColumn("telno", GetType(String)))
End With
End Sub
End Class
Code:
CREATE TABLE [dbo].[tabContacts]( [ID] [int] NOT NULL, [name] [varchar](50) NULL, [bday] [date] NULL, [address] [varchar](50) NULL, [telno] [nchar](10) NULL, CONSTRAINT [PK_tabContacts] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] '------------------------------------------------------------------------------------ CREATE PROCEDURE [dbo].[sp_tabContacts] @DT dbo.type_tabContacts READONLY AS BEGIN SET NOCOUNT ON; MERGE INTO tabContacts as A USING(SELECT ID,name,bday,address,telno FROM @DT) AS B ON A.ID = B.ID WHEN MATCHED THEN UPDATE SET A.name = B.name, A.bday = B.bday, A.address = B.address, A.telno = B.telno WHEN NOT MATCHED THEN INSERT ( ID,name,bday,address,telno ) VALUES ( B.ID,B.name,B.bday,B.address,B.telno ); END '------------------------------------------------------------------------------------ CREATE TYPE [dbo].[type_tabContacts] AS TABLE( [ID] [int] NOT NULL, [name] [varchar](50) NULL, [bday] [date] NULL, [address] [varchar](50) NULL, [telno] [varchar](10) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ) GO
Please help