Database

ALTER   TRIGGER [TRG_INS_CLIENTS] ON [dbo].[Clients] 
FOR INSERT
AS
DECLARE @ClientName VARCHAR(50)
SELECT @ClientName = ClientName+'_ParseTable' FROM inserted

CREATE TABLE @ClientName (
	[PrimaryKey] [decimal](18, 0) IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
	[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Target] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Steps] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) 

<br />
' Create a table<br />
Dim table As New System.Data.DataTable("Customer")<br />
<br />
With table .Columns()<br />
    .Add("id", GetType(System.Int32));<br />
    .Add("name", GetType(System.String));<br />
    .Add("status", GetType(System.Char));<br />
End With<br />
<br />
' Other code to create and open the connection / dataadapter<br />
...<br />
<br />
' Create connection / command / dataadapter<br />
Dim cn As New SqlConnection("connectionstring")<br />
cn.Open()<br />
<br />
Dim cm As New SqlCommand("SELECT id, name, status FROM Customer", cn)<br />
cm.CommandType = CommandType.Text<br />
<br />
Dim da As New SqlDataAdapter(cm)<br />
<br />
' I add a handler to catch my exception<br />
AddHandler da.FillError, New FillErrorEventHandler(AddressOf Helper.FillError)<br />
<br />
table.BeginLoadData()<br />
da.Fill(table)<br />
table.EndLoadData()<br />

<br />
Public Shared Sub FillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)<br />
	Dim table As System.Data.DataTable<br />
	Dim row As System.Data.DataRow<br />
	Dim values As Object()<br />
<br />
	Dim valueType As System.Type<br />
	Dim tableType As System.Type<br />
<br />
	' Try to solve exception<br />
	If args.Errors.GetType() Is Type.GetType("System.ArgumentException") Then<br />
		' There was an error during the fill<br />
		table = args.DataTable<br />
		values = args.Values<br />
<br />
		If args.Values.Length = table.Columns.Count Then<br />
			row = table.NewRow<br />
<br />
			' Try not to get here, not good for the performance<br />
			For i As Integer = 0 To table.Columns.Count - 1<br />
				valueType = values(i).GetType<br />
				tableType = table.Columns(i).DataType<br />
<br />
				If valueType.Equals(tableType) OrElse values(i) Is System.DBNull.Value Then<br />
					row(i) = values(i)<br />
				Else<br />
					row(i) = Convert.ChangeType(values(i), tableType)<br />
				End If<br />
			Next<br />
<br />
			table.Rows.Add(row)<br />
<br />
			' Continue without error<br />
			args.[Continue] = True<br />
		Else<br />
			' Throw error<br />
			args.[Continue] = False<br />
		End If<br />
	End If<br />
End Sub<br />