| paulchwd |
Aug 16th, 2007 3:29 PM |
Datagrid
Hello All, when I run my code, I get a datagrid with a column(correct), but no data in it......
code:
** Please note, all this code is launched from form_load
(in bold)
:
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Public Class qid_search
Public Function getQueryString() As String
Dim oApp As Microsoft.Office.Interop.Outlook.Application = New Microsoft.Office.Interop.Outlook.Application()
Dim oNS As Microsoft.Office.Interop.Outlook.NameSpace = oApp.GetNamespace("mapi")
Dim objNS As Microsoft.Office.Interop.Outlook._NameSpace = oApp.Session
Dim qidFolder As Microsoft.Office.Interop.Outlook.MAPIFolder = oNS.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox).Folders("QID")
Dim oItems As Microsoft.Office.Interop.Outlook.Items = qidFolder.Items
' Dim mail As Microsoft.Office.Interop.Outlook.MailItem
Dim mail As Object
Dim email_address As String
If (xlsFileLocation = "") Then
MsgBox("Please select an excel file from the main screen first, and try again. Thank you", MsgBoxStyle.Critical, "QID E-mailer")
Return Nothing
End If
Dim goHere As Boolean = True
Dim getQid As String = "SELECT QID FROM [Sheet1$] WHERE [Name] = "
Dim andVal As String = " and [Name] = "
Dim singleQuote As String = "'"
For i As Integer = 1 To oItems.Count
mail = oItems.Item(i)
'if its not a report item (returned mail) ... skip over it
If (TypeOf mail Is Microsoft.Office.Interop.Outlook.ReportItem) Then
email_address = extract_email_address(mail.Body, " ") ' stop char is a blank space
Dim name As String = get_name_from_email(email_address)
' the word and is not in the query string and thus its the first one
If (goHere) Then
getQid = getQid & singleQuote & name & singleQuote
goHere = False
Else
getQid = getQid & andVal & singleQuote & name & singleQuote
End If
End If
Next
Return getQid
End Function
Public Sub doDataGrid()
Dim grid As DataGrid
Dim table As String = "[Sheet1$]"
grid = New DataGrid
grid.Location = New Point(7, 39)
grid.Size = New Size(360, 276)
grid.Enabled = False
grid.DataSource = getDataSource()
grid.DataMember = table
Controls.Add(grid)
End Sub
Public Function getDataSource() As DataSet
Dim dataSetx As DataSet
Dim adapter As OleDbDataAdapter
Dim table As String = "[Sheet1$]"
If (conn Is Nothing) Then
connect_to_xls(Form1.xlsLocation.Text)
conn.Open()
ElseIf (conn.State <> ConnectionState.Open) Then
conn.Open()
End If
dataSetx = New DataSet()
adapter = New OleDbDataAdapter(getQueryString(), conn)
adapter.Fill(dataSetx, table)
Return dataSetx
End Function
Public Function extract_email_address(ByVal email As String, ByVal stopChar As String) As String
Dim idxOfAt As Integer
Dim idxofSpaceatBegin As Integer
Dim idxofSpaceatEnd As Integer
Dim currentIdx As Integer
Dim currentChar As Char = ""
Dim c As Char
Dim result As String
Dim NewResult As String
idxOfAt = email.IndexOf("@")
'find the @ in the string and keep going left untill you hit a space
idxofSpaceatBegin = email.LastIndexOf(" ", idxOfAt)
idxofSpaceatEnd = email.IndexOf(" ", idxOfAt)
result = email.Substring(idxofSpaceatBegin, idxofSpaceatEnd - idxofSpaceatBegin).ToString()
NewResult = ""
For Each c In result
Select Case c
Case "A" To "Z", "a" To "z", "0" To "9", "@", "."
NewResult = NewResult & c
Case Else
End Select
Next
Return NewResult
End Function
Public Function get_name_from_email(ByVal email_address As String) As String
Dim nameSeperator As String = "."
Dim splitArray(1) As String
Dim fName As String
Dim lName As String
'split the email address at the @ sign
splitArray = email_address.Split("@")
'split array will now = the split of the portion of the email before the @ (firstname.lastname)
splitArray = splitArray(0).Split(nameSeperator)
fName = splitArray(0)
lName = splitArray(1)
Return lName & ", " & fName
End Function
Private Sub qid_search_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
getQueryString()
doDataGrid()
End Sub
End Class
getQid:
"SELECT QID FROM [Sheet1$] WHERE [Name] = 'KICHAK, DENNIS' and [Name] = 'SPECK, SHARON' and [Name] = 'HILLMER, STEVEN' and [Name] = 'BERRY, DENISE' and [Name] = 'HAYTON, KAREN' and [Name] = 'FOLEY, JESSIE' and [Name] = 'HOPKINS, BARBARA' and [Name] = 'SELEMIDIS, VIRGINIA' and [Name] = 'CHAN, CONNIE' and [Name] = 'DELOREY, WENDY' and [Name] = 'STIPANCIC, ANNE' and [Name] = 'GORDON, JIM' and [Name] = 'PORTER, VIRGINIA' and [Name] = 'FRITH, BEVERLEY' and [Name] = 'GRATTON, SUSAN' and [Name] = 'VEGTER, BARBARA' and [Name] = 'POTTS, PAULINE' and [Name] = 'CALDWELL, DOUG' and [Name] = 'AGGARWAL, VANITA' and [Name] = 'BEATTY, JOANN' and [Name] = 'WOODRUFF, SUSAN' and [Name] = 'RICE, CHRIS' and [Name] = 'SMITH, CRAIG' and [Name] = 'SULLIVAN, SHERRY'" |
