Click here to Skip to main content
11,500,106 members (57,923 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Excel VB.NET OleDb
Forgive the noob.

Excel 2010 + VB.net 2010 express

I have a vb.net project that connects to an excel workbook (oledb) and fills it with user entered data on a daily basis.

It works great!

The excel sheet has 15 Columns and the data expands downward as users input data every day. Each row is a different record.

One of my columns, (N) is one titled "CallbackRequired" which will only have "Yes" or "No" and another column (O) is "WhenCall" which saves the date the customer is to be called back on.

In my vb.net application, I would like to put either a label or a textbox on a form that will display at runtime all the records in that sheet under the column "CallbackRequired" that have a "Yes" that match only the current date in the "WhenCall" column.

I would like to display the entire row of 15 columns (the record) that match the critera I just described on a number delimited list, 1 per row when the program is run.

I figure this is most likely done with a SQL statement but I know not how to construct one. I am sure that I could muddle through the statement itself
but I am not sure, (even if I had the correct syntax), how to display or return the data I am requesting.
Although I don't think you need to see the code to help me with this question I have included it anyway for reference.
Any help is greatly appreciated.

Try
    olecon = New OleDb.OleDbConnection
    olecon.ConnectionString = connstring
    olecomm = New OleDb.OleDbCommand
    olecomm.CommandText = _
       "Select TodaysDate, CustomersName, CustomersPhoneNumber, Last4ofSS, Billing, UpgradeAvailable, HowMany, HPC, MBB, GWG, SellDevice, HowManyTwo, Comments, CallbackRequired, WhenCall from [Traffic Monitor$]"
    olecomm.Connection = olecon
    olecomm1 = New OleDb.OleDbCommand
    olecomm1.CommandText = "Insert into [Traffic Monitor$] " & _
        "(TodaysDate, CustomersName, CustomersPhoneNumber, Last4ofSS, Billing, UpgradeAvailable, HowMany, HPC, MBB, GWG, SellDevice, HowManyTwo, Comments, CallbackRequired, WhenCall) values " & _
        "(@tDate, @tName, @tNumb, @tLast, @tBill, @tUp, @tHow, @tHPC, @tMBB, @tGWG, @tDevice, @tHowTwo, @tComm, @tCall, @tWhen)"
    olecomm1.Connection = olecon
    pram = olecomm1.Parameters.Add("@tDate", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "TodaysDate"
    pram = olecomm1.Parameters.Add("@tName", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "CustomersName"
    pram = olecomm1.Parameters.Add("@tNumb", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "CustomersPhoneNumber"
    pram = olecomm1.Parameters.Add("@tLast", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "Last4ofSS"
    pram = olecomm1.Parameters.Add("@tBill", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "Billing"
    pram = olecomm1.Parameters.Add("@tUp", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "UpgradeAvailable"
    pram = olecomm1.Parameters.Add("@tHow", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "HowMany"
    pram = olecomm1.Parameters.Add("@tHPC", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "HPC"
    pram = olecomm1.Parameters.Add("@tMBB", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "MBB"
    pram = olecomm1.Parameters.Add("@tGWG", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "GWG"
    pram = olecomm1.Parameters.Add("@tDevice", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "SellDevice"
    pram = olecomm1.Parameters.Add("@tHowTwo", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "HowManyTwo"
    pram = olecomm1.Parameters.Add("@tComm", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "Comments"
    pram = olecomm1.Parameters.Add("@tCall", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "CallbackRequired"
    pram = olecomm1.Parameters.Add("@tWhen", OleDb.OleDbType.VarChar)
    pram.SourceColumn = "WhenCall"
    oleadpt = New OleDb.OleDbDataAdapter(olecomm)
    ds = New DataSet
    olecon.Open()
    oleadpt.Fill(ds, "Traffic Monitor")
    
 
'Then I fill the data from the form....

'After I get the data from the form....

                ds.Tables(0).Rows.Add(dr)
                oleadpt = New OleDb.OleDbDataAdapter
                oleadpt.InsertCommand = olecomm1
                Dim i As Integer = oleadpt.Update(ds, "Traffic Monitor")
  End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            olecon.Close()
            olecon = Nothing
 
            olecomm = Nothing
            oleadpt = Nothing
            ds = Nothing
            dr = Nothing
            pram = Nothing
        End Try
' Then I init everything to clear the form.
Posted 19-Nov-12 13:15pm
Comments
Sergey Alexandrovich Kryukov at 19-Nov-12 19:40pm
   
Having trouble does not mean a question. Do you have a question?
--SA
Michael s Mcleer at 20-Nov-12 10:48am
   
Yes.
I thought that I made it clear that I was asking for help in passing data that is stored in Excel that match a certain criteria.

I am not sure if I need to use SQL to do this or some other knowledge I do not possess.

MY QUESTION IS:
Do I need SQL to do this and if so what would be the proper syntax and location in my code to pull the data I need and display that data that is passed into a label or text box in my VB application at runtime?

Should I use DataGridView? I don't know?
I hope that clears up any ambiguity
Michael s Mcleer at 20-Nov-12 10:49am
   
Notes at the end of this page:
When answering a question please:
1.Read the question carefully.
2.Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
3.If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Yes. The easiest way to display information like that in a Windows Forms project is to use a DataGridView[^].

And yes. SQL is going to be your best way to pull this information. You already have the start in that code that you supplied...
olecomm.CommandText = _
       "Select TodaysDate, CustomersName, CustomersPhoneNumber, Last4ofSS, Billing, UpgradeAvailable, HowMany, HPC, MBB, GWG, SellDevice, HowManyTwo, Comments, CallbackRequired, WhenCall from [Traffic Monitor$]"
This SQL is pulling 15 columns of data from a table, or in your case excel sheet, called Traffic Monitor$. In SQL if you want to filter the data, you add a WHERE clause. In your case it would look something like this:
olecomm.CommandText = _
       "Select TodaysDate, CustomersName, CustomersPhoneNumber, Last4ofSS, Billing, UpgradeAvailable, HowMany, HPC, MBB, GWG, SellDevice, HowManyTwo, Comments, CallbackRequired, WhenCall from [Traffic Monitor$] WHERE CallbackRequired='Yes' AND WhenCall='" & Now.ToString("MM/dd/yyyy") & "'""
Here I am saying to only grab the records if they are required to be called back and the call back is supposed to be today. The Now bit is pulling the system's date/time and the .ToString part is formatting it into a string. This might not be 100% correct because it appears that your WhenCall is a string field and I'm not sure how you are formatting your dates. But it should give you an idea of where to start. There are plenty of good SQL tutorials out on the web that can help you with other SQL basics. Try google[^]. Or w3school[^] is one that I have used in the past.

Hope this helps.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 345
1 Maciej Los 290
2 DamithSL 230
3 Peter Leow 181
4 OriginalGriff 167
0 Sergey Alexandrovich Kryukov 335
1 Maciej Los 290
2 DamithSL 220
3 Peter Leow 181
4 OriginalGriff 167


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 20 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100