how to add column in database at runtime.

[NetPointer]

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.

[Erland Sommarskog]

NetPointer (sh**************@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, so****@algonet.se

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