define excel table in vb 2005 using oledb excel file
Hi, i am making an application that will use a access database and an excel spread sheet. It will basically save data from mutiple text box inputted by the user into both excel and access. The part of the application i need help with is below what i am trying to do is learn how to define the table in the excel tables. I have looked around and have had no progress with defining tables field. Below i have included in the comments the definition type for each column. If possible I would like to have a complete list of excel definition for the field I can use with excel oledb. Any help will be surely appreciated. thx.
example:
dim fName as string = textbox1.text
dim lName as string = textbox2.text
dim ClientID as string = textbox3.text
'==========================================================================
' Create a excel workbook with a table named Interviews. The table has 10
' fields: ClientID (autoNumber), lastName (char 20), firstName(20),
' phone(number), email(20), date(date), time(time), Voicemail(true/false),
' callBack(true/false), Scheduled(true/false).
'==========================================================================
Dim conn As New OleDbConnection()
conn.ConnectionString = m_sConn1
conn.Open()
Dim cmd1 As New OleDbCommand()
cmd1.Connection = conn
cmd1.CommandText = "CREATE TABLE Interviews (ClientId autoNumber, lastName varchar(20), firstName varchar(20))" ' and so on.........................................
cmd1.ExecuteNonQuery()
cmd1.CommandText = "INSERT INTO Interviews (ClientId, lastName, firstName) values (" & ClientID & lName & fName & ")" ' and so on............................................
cmd1.ExecuteNonQuery()
conn.Close()
Here is an update that will show what happens
hi,
I having problem with a oledb project. What i am trying to do is to be able to create a excel workbook and read data into the field. The below code the first section the code causes an error saying "santax error in field definition". The second part make the file but when i try to open it in excel it say its a corupt or invalid format. If any one know anything about this and can help me i would sure appreciate it.
'==========================================================================
' Create a excel workbook with a table named Interviews. The table has 10
' fields: ClientID (autoNumber), lastName (char 20), firstName(20),
' phone(number), email(20), date(date), time(time), Voicemail(true/false),
' callBack(true/false), Scheduled(true/false).
'==========================================================================
Dim conn As New OleDbConnection()
conn.ConnectionString = m_sConn1
conn.Open()
Dim cmd1 As New OleDbCommand()
cmd1.Connection = conn
cmd1.CommandText = "CREATE TABLE Interviews (ClientId char(10), " & _
"firstName char(20), lastName char(20), phone char(20), " & _
"email char(20), date char(10), time char(12))"
cmd1.ExecuteNonQuery()
cmd1.CommandText = "INSERT INTO Interviews (ClientId, " & _
"firstName, lastName, phone, email, date, time) values ('1', 'Andrew', 'willis'," & _
"'727-502-0648', 'lew26@msn.com','12/4/1955', '6:30')"
cmd1.ExecuteNonQuery()
conn.Close()
'==========================================================================
' Create a workbook with a table named InventoryData. The table has 3
' fields: Product (char 255), Qty (float) and Price (currency).
'==========================================================================
conn.ConnectionString = m_sConn2
conn.Open()
Dim cmd2 As New OleDbCommand()
cmd2.Connection = conn
cmd2.CommandText = "CREATE TABLE InventoryData (Product char(255), Qty float, Price currency)"
cmd2.ExecuteNonQuery()
cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Cola', 200, 1.35)"
cmd2.ExecuteNonQuery()
cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Chips', 550, 0.89)"
cmd2.ExecuteNonQuery()
conn.Close()
' NOTE: You can ALTER and DROP tables in a similar fashion.
End Sub
