query sql database for specific value in vb.net

Question

I am trying to convert VBA code to vb.net, im having trouble trying to search the database for a specific value around an if statement. any suggestions would be greatly appriciated. thedatabase is called confirmation, type is the column and email is the value im looking for. could datasets work?

Function SendEmails() As Boolean

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment

    Dim intResponse As Integer

    Dim confirmation As New ADODB.Recordset
    Dim details As New ADODB.Recordset

    On Error GoTo Err_Execute


    Dim MyConnObj As New ADODB.Connection
    Dim cn As New ADODB.Connection()

    MyConnObj.Open( _
             "Provider = sqloledb;" & _
             "Server=myserver;" & _
             "Database=Email_Text;" & _
             "User Id=bla;" & _
             "Password=bla;")

    confirmation.Open("Confirmation_list", MyConnObj)
    details.Open("MessagesToSend", MyConnObj)


    If details.EOF = False Then

      confirmation.MoveFirst()
      Do While Not confirmation.EOF
          If confirmation![Type] = "Email" Then 

             ' Create the Outlook session.
              objOutlook = CreateObject("Outlook.Application")

             ' Create the message.
    End IF

Hi Matt the issue is confirmation![type] works in VBA but it is not recoginzed in vb.net. I need another way to search the database for the value. — Jonny M
– Jonny M, Commented Nov 4, 2013 at 11:40
i have attached more of the code, i have used ADODB, i am in the process of converting the code from vba to vb.net, this as far as i have been able to go. The sql part really has me stumped — Jonny M
– Jonny M, Commented Nov 4, 2013 at 11:54

Steve · Accepted Answer · 2013-11-04 12:36:29Z

If you want really convert your code to NET then I think you should remove the ADODB objects and use the System.Data.SqlClient classes. Of course some things are more difficult now because the ADO.NET really use a detached model to work with databases.

The semi-equivalent of your code using the ADO.NET classes

....
' Use Try/Catch not the On Error Goto....'
Try
    Dim conStr = "Server=myserver;Database=Email_Text;User Id=XXXX;Password=XXXXX;"
    Using MyConnObj = new SqlConnection(conStr)

    ' Getting all the details rows and columns, '
    ' but you should select only the columns needed here....'
    Using cmdDetails = new SqlCommand("SELECT * FROM MessagesToSend", MyConnObj)

    ' You need only the rows with Type=Email, so no need to retrieve all the rows'
    Using cmdConfirm = new SqlCommand("SELECT * FROM Confirmation_list " & _ 
                                      "WHERE [Type] = 'EMail' ", MyConnObj)
        MyConnObj.Open

        Using da = new SqlDataAdapter(cmdConfirm)
           Dim dt = new DataTable()
           da.Fill(dt)

           Using reader = cmdDetails.ExecuteReader()
              While reader.Read()
                 For Each row in dt.Rows 
                       ... ' The Outlook app should be instanced outside the loop'
                 Next
              Loop
          End Using
       End Using
    End Using          
    End Using
    End Using
Catch x As Exception 
    MessageBox.Show("Error:" & x.Message)
End Try

Matt Wilko · Accepted Answer · 2013-11-04 15:51:06Z

0

Possibly the usage of the Type column is causing an issue. You might be able to get this working by doing the following:

confirmation.Fields("Type").Value

instead of

confirmation![Type]

but I would recommend you look into ADO.NET isntead of using ADODB as this is quite old now.

answered Nov 4, 2013 at 15:51

Matt Wilko

27.4k10 gold badges99 silver badges146 bronze badges

Collectives™ on Stack Overflow

query sql database for specific value in vb.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related