Showing posts with label csv. Show all posts
Showing posts with label csv. Show all posts

Friday, March 9, 2012

how to add column in database at runtime.

Hello all,

I m trying to add a column in my database (it is a csv file)
but it is giving me following exception.
--exception----
{System.Data.OleDb.OleDbException}
ErrorCode: -2147467259
Errors: {System.Data.OleDb.OleDbErrorCollection}
HelpLink: Nothing
InnerException: Nothing
Message: "Operation not supported on a table that contains data."
Source: "Microsoft JET Database Engine"
StackTrace: " at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Here is my code for this.
---Code-----
Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Dim myCon As New OleDbConnection(ConnectString)

Try

myCon.Open()
'Debug.WriteLine("Connection Opened")

Dim cmd As New OleDbCommand

cmd.CommandText = "ALTER TABLE [sample.csv] ADD mycol VARCHAR(50) NULL"
cmd.Connection = myCon
cmd.ExecuteNonQuery()

Catch ex As OleDbException
debug.WriteLine(ex.Message)
Finally
myCon.Close()
End Try

Any known reasons and workarounds??
Thanks & Regards.NetPointer (shaileshx.s.shah@.intel.com) writes:
> Message: "Operation not supported on a table that contains data."
> Source: "Microsoft JET Database Engine"
>...
> myCon.Open()
> 'Debug.WriteLine("Connection Opened")
> Dim cmd As New OleDbCommand
> cmd.CommandText = "ALTER TABLE [sample.csv] ADD mycol VARCHAR(50) NULL"
> cmd.Connection = myCon
> cmd.ExecuteNonQuery()
> Catch ex As OleDbException
> debug.WriteLine(ex.Message)
> Finally
> myCon.Close()
> End Try
> Any known reasons and workarounds??

The reason should be pretty clear from the message.

For a workaround, you may look for another newsgroup. This group is
focused on SQL Server, and we don't know that much about the Jet engine
or Excel here. Maybe they know more in comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, February 19, 2012

How to access to excel,csv or other formatted file using OLE DB(SQL Native Client)?

Hi~.

First, is it possible to access(read/write) to the excel,csv or my-own-formatted file using OLE DB(SQL Native Client)?

If possible, how?

You can create linked server to the excel spreadsheet as shown in : http://msdn2.microsoft.com/en-us/library/ms190479.aspx

You can use OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

or OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.

Providing an example using VB6 / ADO using SQLNCLI

Sub main()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=SQLNCLI;Data Source=<datasourcename>;Integrated Security=SSPI;"

'Refer to table using linked server
rs.Open "select * from <linkedsrvname>...TestTable", cn

'OpenRowset
Set rs = cn.Execute("select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\TestBook.xls','select * from [TestTable]')")

'OpenQuery
Set rs = cn.Execute("select * from OpenQuery(ExcelShare,'select * from TestTable')")

While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields.Item(i)
Next i
rs.MoveNext
Wend

Set rs = Nothing
cn.Close

End Sub