Q: changing the datatype

[Geoff Jones]

Hi

Can anybody tell me how to change the datatype of a field in a database
using VB.net code?

For example, suppose that the database has a field initially specified as
Text, how do I change it to DateTime from within VB.net?

Geoff

 

[Paul Clement]

¤ Hi
¤
¤ Can anybody tell me how to change the datatype of a field in a database
¤ using VB.net code?
¤
¤ For example, suppose that the database has a field initially specified as
¤ Text, how do I change it to DateTime from within VB.net?

There isn't really a direct method to do this. Essentially you need to issue several DDL statements
and then use ADOX to rename the column. If it's a SQL Server database then you can probably rename
the column using DDL. The following example uses Access and assumes you have kosher date values.

Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOConnection.Execute "ALTER TABLE [Table1] ADD COLUMN [NewDateColumn] DATETIME"
ADOConnection.Execute "UPDATE Table1 SET NewDateColumn = DateColumn"
ADOConnection.Execute "ALTER TABLE Table1 DROP COLUMN DateColumn"

ADOXCatalog.ActiveConnection = ADOConnection

ADOXCatalog.Tables("Table1").Columns("NewDateColumn").NAME = "DateColumn"

ADOConnection.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Cor Ligthert]

Geoff,

Have a look at this page. And use than the OleDb method I have showed you
already for creating tables and columns, and try with that to archieve what
you want.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

I hope this helps?

Cor