define excel table in vb 2005 using oledb excel file


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: define excel table in vb 2005 using oledb excel file

Hybrid View

  1. #1
    Join Date
    Jul 2005
    Posts
    24

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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2005
    Posts
    24

    doesn't help

    Quote Originally Posted by 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

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

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Could you explain what you mean by "define the tables"? Maybe you could provide an example?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Jul 2005
    Posts
    24

    here are some examples

    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.........................................

  6. #6
    Join Date
    Jul 2005
    Posts
    24

    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

  7. #7
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  8. #8
    Join Date
    Jul 2005
    Posts
    24

    concerning format

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

  9. #9
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 3
    Last Post: 06-08-2006, 10:19 PM
  2. How to create Setup.exe file given a Vb Project
    By cooldude.i2k in forum VB Classic
    Replies: 3
    Last Post: 08-05-2005, 04:13 AM
  3. .bat file exuted from VB
    By David in forum VB Classic
    Replies: 1
    Last Post: 09-04-2001, 08:38 AM
  4. Save figure table to Excel file
    By Mahendra Gunawan in forum VB Classic
    Replies: 1
    Last Post: 05-02-2001, 08:55 AM
  5. Adding Columns to comboboxes through API
    By Lori Taylor in forum VB Classic
    Replies: 20
    Last Post: 06-14-2000, 12:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center