Q: changing the datatype

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

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
 
¤ 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)
 
Back
Top