oledbcommands-return all values ?

[markhorgan1]

ive got command below to query db for result = false.I want the switch names returned to be displayed in a textbox. the PROBLEM is only the first result is being returned. I think this is because ive chosen execute scalar but none of the other options seem to work.

  Using con2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Network Map.mdb")
                Using command As New OleDb.OleDbCommand("SELECT SwitchName FROM tblSwitch where Result = false", con2)
                    con2.Open()

                    Dim faileddevices As String = CStr(command.ExecuteScalar)
                    RichTextBox5.Text = faileddevices
                  
                    con2.Close()

                End Using
            End Using

[wild_bill]

You are correct that executescalar will only return a single value. Google for examples of using OleDb.OleDbDataReader

[Shaggy Hiker]

I would use datareader, as well, but it will not be a one line solution, nor is there one.

To use datareader, you would get a datareader:

Dim dr as OleDBDataReader = cmd.ExecuteReader

Do While dr.Read
 faileddevices &= dr.Item(0).ToString & ","
Loop

There are issues with that code that you should consider. The first is that using a StringBuilder would be somewhat more efficient. Another is that I never actually check that the reader has rows, but that may be fine. Yet another is that the code assumes that the data you want is in the first column returned. For the SQL string you used, that assumption is fine, since there is only one column. And finally, this will just create a comma separated string, which will be pretty ugly. Using Environment.Newline rather than the comma might be somewhat better, but that's up to you.

[jmcilhinney]

Follow the CodeBank link in my signature and check out my Saving & Retrieving Data thread, which contains code examples of the most common ADO.NET scenarios, including this one. It shows you what classes you need to use for what scenarios.

[markhorgan1]

thanks guys,

rep goes to shaggy hiker on this one for his amazing mind reading skills, i was just googling to find out how to seperate the results with a comma when i said id check back here first. what are the advantages of a stringbuilder over tostring ? i literally just need to return one column with values returned separated by comma. - will size be an issue with tostring ? is there a character limit ?

[Shaggy Hiker]

Size shouldn't be an issue.

A string can't change sizes. Whenever you think you are changing the size of a string you are actually throwing out the original string and creating a new one. This will happen with some concatenations (though not all) and other alterations that change the length of a string. The stringbuilder was added to speed this up. It's basically a variable size string object. You would call Append rather than concatenating to it, and when you are done you can turn it into a string with a single call to .ToString.

So is StringBuilder a good idea? That's not all that easy to answer. It is faster, as long as you are doing more than four or five concatenations, or so, but in some ways it is not as easy to work with. Furthermore, faster is a very relative term. If the code appears to run instantaneously, do you really care whether it takes 0.01 seconds rather than 0.05 seconds?

I would be inclined to use a stringbuilder in this case if the expected number of records is greater than three or four, otherwise I wouldn't. It would look something like this:

Dim dr as OleDBDataReader = cmd.ExecuteReader
dim sb as New System.Text.StringBuilder

Do While dr.Read
 sb.Append(dr.Item(0).ToString & ",")
Loop

faileddevices = sb.ToString

Technically, the & "," could be split into a second append, but in this case, a string literal is being appended onto a string variable, and I expect that the compiler will generate more efficient code the way I have it written.

[markhorgan1]

thanks shaggy,new problem tho: MyMailMessage.To.Add() wont accept recipiants, which is a string of a valid email address.

the .txt files have already been integrated into other parts of program so i want to stick with this method of populating the MyMailMessage.To.Add("email address").
heres entire code of sub

any help would be greatly appreciated as its a college project and deadline is fast approaching.

Public Sub Notify()

        Dim ioFile As New StreamReader("H:\FYP-trial\NetworkMap\ExamplePingProgram\mail.txt")
        Dim ioLinem As String ' Going to hold one line at a time 
        Dim ioLinesm As String ' Going to hold whole file 

        iolinem = ioFile.ReadLine
        ioLinesm = ioLinem

        While Not ioLinem = ""
            ioLinem = ioFile.ReadLine
            ioLinesm = ioLinesm & vbCrLf & ioLinem

        End While

        Dim ioFilemms As New StreamReader("H:\FYP-trial\NetworkMap\ExamplePingProgram\mms.txt")
        Dim ioLinemms As String ' Going to hold one line at a time 
        Dim ioLinesmms As String ' Going to hold whole file 

        ioLinemms = ioFilemms.ReadLine
        ioLinesmms = ioLinemms

        While Not ioLinemms = ""
            ioLinemms = ioFilemms.ReadLine

            ioLinesmms = ioLinesmms & vbCrLf & ioLinemms
        End While

        Dim MyMailMessage As New MailMessage
        MsgBox(ioLinesm)

        If CheckBoxEmail.Checked = True Then

            Dim recipiants As String
            recipiants = ioLinesm
            MyMailMessage.From = New MailAddress("[email protected]") ' sendermail 
            recipiants = ioLinesm
            MyMailMessage.To.Add(recipiants)
            '   MyMailMessage.To.AddRange(recipiants)

            ' recievermail 
            MyMailMessage.Subject = "Network Status"
            MyMailMessage.Body = RichTextBox5.Text

        ElseIf CheckBoxMMS.Checked = True Then
            Dim recipiantsMMS As String
            recipiantsMMS = ioLinesmms
            MyMailMessage.From = New MailAddress("[email protected]") ' sendermail 
            MyMailMessage.To.Add(recipiantsMMS) '[email protected]
            MyMailMessage.Subject = "Network Status"
            MyMailMessage.Body = RichTextBox5.Text

        Else
            'both
            MyMailMessage.From = New MailAddress("[email protected]") ' sendermail 
            MyMailMessage.To.Add("[email protected]")
            MyMailMessage.To.Add("[email protected]")
            '[email protected]

            MyMailMessage.Subject = "Network Status"
            MyMailMessage.Body = RichTextBox5.Text

        End If

        Dim SMTP As New SmtpClient("smtp.live.com")
        SMTP.Port = 587
        SMTP.EnableSsl = True
        SMTP.Credentials = New System.Net.NetworkCredential("[email protected]", "*********")
        SMTP.Send(MyMailMessage)
        'RichTextBoxBody.Text = ""
        MsgBox("Mail was sent", MsgBoxStyle.Information)
        ' Catch ex As Exception
        ' End Try

    End Sub

[techgnome]

that's because you can't add a string of recipients as one string... you need to add each one one by one... or read them into an array, then the AddRange will work.

Hint - .ReadAllLines might be of use. It reads in the entire file in one go.And if I remember correctly, the results is a string array.

-tg

[markhorgan1]

but at the moment the txt file just contains one email address. and even if it didnt they would be on the same line and only separated by a comma would this not all be one string ? -excuse the stupidity i haven left the screen in 10 hours so concentration levels arent ideal.

also when i output recipiants in a msg box i get = [email protected].
for example you can add multiple addresses at once when hardcoding
ie
MyMailMessage.To.Add("[email protected],[email protected]")

ive even also tried saving it as "[email protected]" in the txt file so it includes the inverted commas that are required when hard coding the address but no luck.

[techgnome]

ioLinesm = ioLinesm & vbCrLf & ioLinem <--- you're inserting a linefeed... not a comma.

-tg