HI,
I already have coded in the way what you have told me. I'm using Oledbparameters to insert data into excel sheet.
**The code to Build parameters is as follows:**
Public Function BuildNetAccessParams() As OleDbCommand
If olecomm Is Nothing Then
olecomm = New OleDbCommand()
End If
olecomm.CommandText = "Insert into [" & sheetname & "$] " & _
"(ObjectID,ObjectName,ObjectType,Parent,UserID,UserName,UserType,Inherited,NetAccess,Category) values (@ObjectID,@ObjectName,@ObjectType,@Parent,@UserID,@UserName,@UserType,@Inherited,@NetAccess,@Category)"
'olecon.ConnectionString = connstring
olecomm.Connection = olecon
Param = olecomm.Parameters.Add("@ObjectID", OleDbType.Integer)
Param.SourceColumn = "ObjectID"
Param = olecomm.Parameters.Add("@ObjectName", OleDbType.VarChar)
Param.SourceColumn = "ObjectName"
Param = olecomm.Parameters.Add("@ObjectType", OleDbType.VarChar)
Param.SourceColumn = "ObjectType"
Param = olecomm.Parameters.Add("@Parent", OleDbType.VarChar)
Param.SourceColumn = "Parent"
Param = olecomm.Parameters.Add("@UserID", OleDbType.Integer)
Param.SourceColumn = "UserID"
Param = olecomm.Parameters.Add("@UserName", OleDbType.VarChar)
Param.SourceColumn = "UserName"
Param = olecomm.Parameters.Add("@UserType", OleDbType.VarChar)
Param.SourceColumn = "UserType"
Param = olecomm.Parameters.Add("@Inherited", OleDbType.VarChar)
Param.SourceColumn = "Inherited"
Param = olecomm.Parameters.Add("@NetAccess", OleDbType.VarChar)
Param.SourceColumn = "NetAccess"
Param = olecomm.Parameters.Add("@Category", OleDbType.VarChar)
Param.SourceColumn = "Category"
Return olecomm
End Function
**The connection string is as:**
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelFilePath & ";Extended Properties=""Excel 8.0;HDR=YES;"""
**The code to insert data:**
Public Sub InsertNetAccessToExcel(ByRef righttable As DataTable, ByRef olecomm As OleDbCommand)
Dim ds As New DataSet
oleAdap = New OleDbDataAdapter((New OleDbCommand("Select * from [" & sheetname & "$]", olecon)))
oleAdap.Fill(ds, sheetname)
For Each row As DataRow In righttable.Rows
Dim dr As DataRow = ds.Tables(sheetname).NewRow
dr(Utilities.RightsStr.objid) = row(Utilities.RightsStr.objid) ** 'Integer column**
dr(Utilities.RightsStr.ObjectName) = row(Utilities.RightsStr.ObjectName)
dr(Utilities.RightsStr.ObjectType) = row(Utilities.RightsStr.ObjectType)
dr(Utilities.RightsStr.Parent) = row(Utilities.RightsStr.Parent)
dr(Utilities.RightsStr.UserID) = row(Utilities.RightsStr.UserID) ** 'Integer column**
dr(Utilities.RightsStr.UserName) = row(Utilities.RightsStr.UserName)
dr(Utilities.RightsStr.UserType) = row(Utilities.RightsStr.UserType)
If row(Utilities.RightsStr.Inherited).ToString.ToLower = "true" Then
dr(Utilities.RightsStr.Inherited) = Boolean.TrueString ** 'Boolean column**
Else
dr(Utilities.RightsStr.Inherited) = Boolean.FalseString
End If
dr(Utilities.RightsStr.NetAccess) = row(Utilities.RightsStr.NetAccess)
dr(Utilities.RightsStr.Category) = row(Utilities.RightsStr.Category)
ds.Tables(sheetname).Rows.Add(dr)
oleAdap = New OleDbDataAdapter()
oleAdap.InsertCommand = olecomm
oleAdap.Update(ds, sheetname)
Next
End Sub
This is the way I have coded. I have no problem in inserting data. But the numeric cell values are marked as error column as I have mentioned in my previous post.
Thanks,
Bhuvan R
Enter your message below
Sign in or Join us (it's free).