Hi again.
It's a kind of tricky to save the dataset or datatable to database using SQLClient or OleDB objects.
Here is a sample from my DLL library. I strongly advise you to make these kind of stuff as functions in a dll, so that you easily use them in your projects without any effort.
In the function below connection object parameter is optional because if nothing comes to function I use dll class's connection instead.
I think I have to explain what is going on here.
Until the line of stars it's just connection issues.
TempDS is a temporary dataset. I am using it while I am getting
shema to the data adapter. SaveDA.FillSchema is the important trick here. By getting the schema to data adapter I can save my dataset to the database. The SqlSelectStr parameter is something like "SELECT TOP 1 * FROM TargetTable"
Remember I don't need that data but I strongly need schema.
Then another most important part, the CommandBuilder line : it seems meaningless. We create the object but never use but this line is essential. If you omit it you can't save your dataset.
Good luck.
Public Sub SQLSaveData(ByVal
DS As Data.DataSet, ByVal
TableName As String, ByVal
SqlSelectStr As String, Optional ByVal
Connection As Data.OleDb.OleDbConnection = Nothing)
If Connection Is Nothing Then
Connection = Conn
End If
If Connection.State <> ConnectionState.Open Then
Try
Connection.Open()
Catch ex As Exception
MsgBox("Error in Database Connection. ", MsgBoxStyle.Critical, "Pool")
End Try
End If
' ***************************
Dim
TempDS As New Data.DataSet
Dim
SaveDA As New System.Data.OleDb.OleDbDataAdapter
SaveDA.SelectCommand = New System.Data.OleDb.OleDbCommand(
SqlSelectStr,
Connection)
SaveDA.
FillSchema(
TempDS,
System.Data.SchemaType.Source,
TableName)
SaveDA.Fill(
TempDS,
TableName)
Dim cb As New System.Data.OleDb.OleDbCommandBuilder(
SaveDA)
SaveDA.Update(
DS,
TableName)
End Sub
