define excel table in vb 2005 using oledb excel file

[lew26]

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()

[pclement]

You might want to look at Jet SQL. I don't know what limitations there are but I believe it should work if you're using the Jet OLEDB Provider and Excel ISAM in your connection string.

http://msdn.microsoft.com/library/de.../acfundsql.asp

[lew26]

You might want to look at Jet SQL. I don't know what limitations there are but I believe it should work if you're using the Jet OLEDB Provider and Excel ISAM in your connection string.

http://msdn.microsoft.com/library/de.../acfundsql.asp

It only explain what i already know it doen't really explain any thing about the what to use to define the tables

[pclement]

Could you explain what you mean by "define the tables"? Maybe you could provide an example?

[lew26]

basically what i what to happen is the code above will check to see if the excel.xls file is there. Using this commandline it will create the table and the data type for each column.


so basically i am trying to create a excel.xls file with so many column and just like when you are creating an access.mdf or sql.mdf file you have to to name the data type for the like below i only what to learn how to set the data type for the columns. --> e.g. name varchar(20).....etc. I know how to set the data type for access.mdf file what i am trying to find out is what are the data types for excel files.

time(time) ?
date(date)?
name varchar(20)? or name(text)? or name memo? don't really know what data type are used for excel.xls file


--> cmd1.CommandText = "CREATE TABLE Interviews (ClientId (autoNumber), lastName varchar(20), firstName varchar(20))" ' and so on.........................................

[lew26]

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

[pclement]

What does your connection string look like?

Just an FYI, Excel supports a subset of the Jet data types. There are certain data types and properties that will not be supported under Excel. Excel supports Double, Date, Currency, YesNo (Boolean), Text and Memo.

[lew26]

hi,

well I trying to figure out the yes/no value how do i write that?





Also I have one last question, I am using a calendar when i select a date it will fill mutiple single lined textboxes with the values, including a time comboBox, i what to happen is after i select a date using the calendar, I want to be able to select the interviews by time.

The way i have it set up now is when i do the above it show the info for the for the time, but the original times that were filled into the comboBox disappear. So then i would have to reselect the date all over again reselect the time. I want be able to select the time and be able to select another time with out having to reselect the date al over again just to get the information for that schedule time for that day.



Code:


Calendar:

Private Sub MonthCalendar1_DateChanged(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DateRangeEventArgs) _
Handles MonthCalendar1.DateChanged

Try
' used to hole the value of the select date
Dim userSelDate As String

If userSelDate <> Nothing Then _
SelUserDate = userSelDate

' Show the start and end dates in the text box.
userSelDate = e.Start.ToShortDateString()

' extra
'userSelDate = "Date Selected: Start = " + _
' e.Start.ToShortDateString() + " : End = " + e.End.ToShortDateString()
Me.SchedeuleTableAdapter.FillByDate(Me.DsDatabaseMain.Schedeule, userSelDate)
' Me.SchedeuleTableAdapter.FillByDate(Me.DatabaseDataSet.Schedule, userSelDate)

Catch OleDBException As System.Data.OleDb.OleDbException

MessageBox.Show(OleDBException.Message, _
"OLEDB Error", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub


Catch OleDBException As Exception

Throw OleDBException

MessageBox.Show(OleDBException.Message, "OLEDB Error", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

End Try

End Sub ' MonthCalendar1_DateChanged()





Time Selected:

' handles the index change for the combo box
Private Sub cmbTime_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles cmbTime.SelectedIndexChanged

Try
Dim dateTime As String
dateTime = cmbTime.Text
' cmbTime.SelectedText = value

' fill dataset with date by date time
Me.SchedeuleTableAdapter.FillByTime(Me.DsDatabaseMain.Schedeule, dateTime)
' Me.SchedeuleTableAdapter.FillBYTime(Me.DatabaseDataSet.Schedule, dateTime)

' remove later
' MessageBox.Show("Index: " & dateTime.ToString())

Catch saveException As Exception

Throw saveException

End Try
End Sub ' cmbTime_SelectedIndexChanged()

[pclement]

The data type is YesNo so that is what you would use to create the column. It won't create a check box in the column if that is what you are expecting.

You might want to post your second question in the .NET Technical forum.