Reading data from SQL

[Teder]

Hi all, I've been designing a program in VB6 to extract certain rows of data from a SQL Database and shoot them out over email. Problem is, the farthest I can get is to printing them on the VB form, and can't do much else. I get an error when I try to send the the data out over the Outlook script, claiming it doesn't support it. So I need someway to get this data into a more managable form before I email it. I'd also like to be able to display it in a text/label box rather than just the Print command.

[choudhuryshouvi]

Hi all, I've been designing a program in VB6 to extract certain rows of data from a SQL Database and shoot them out over email. Problem is, the farthest I can get is to printing them on the VB form, and can't do much else. I get an error when I try to send the the data out over the Outlook script, claiming it doesn't support it. So I need someway to get this data into a more managable form before I email it. I'd also like to be able to display it in a text/label box rather than just the Print command.

I'm attaching an application that is capable in register users into a sql database and sending mails to them using each of the users email address. Check whether this application helps you to get your answer or not. I've used an USER-DSN connection to communicate with the back-end server and i've used Microsoft Sql Server 7.0 . I've assumed that you are already familier in how to create an user-dsn.

For your consideration i've also included the script file of the sql database that i'd used. But to proceed in working with the database you have to create a user login named "STUDENT" with password "STUDENT" using the sql server enterprise manager. I'm also assuming that you know how to create a security login in sql server. After extracting from the zip file, run the file "script.sql" using sql server query analyzer to create the table structure. Consider the file "DSN.txt" to see the connection string which the application uses.

For your consideration i've also attached two snapshots of the application. Plz check those out too.

Don't forget to know me your feedback.
I'm waiting to hear some good news from you.

Good Luck

Best Regards
Shouvik

[Teder]

Thank you for the response, but in the end I just decided to store it in a label and then shoot it out. The code looks something like this:

Private Sub Command1_Click()
Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset
Rec = 1

'SQL Connection
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source=FRISCH08\SQLEXPRESS;" & _
"Initial Catalog=WWALMDB;" & _
"User ID=sa;" & _
"Password=sqlpass;"

sqlStr = "select * from dbo.Events"

myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

'EOF Non Looping
While Not myRecSet.EOF

If myRecSet(8) = 1 Then Label1 = myRecSet(4) & " , " & myRecSet(19)
Rec = 2

myRecSet.MoveNext

Wend

'Email Send
If Rec = 2 Then

Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(Outlookmailitem)

OutlookMail.To = "[e-mail]"
OutlookMail.Subject = "Alarm Notification"
OutlookMail.Body = Label1

OutlookMail.Send

Set OutlookMail = Nothing

Rec = 1

End If

End Sub

The program now successfuly scans the SQL database and sends an email if the conditions are met. I do have a new question now; is it possible to keep this program continously running to check the SQL database and see if any new tags have popped up matching the conditions?

[choudhuryshouvi]

what did you mean by "tags"?
is it row in the table you are accessing???

you can use a timer object to accomplish this task.
call the procedure you've written to check the table for rows meeting a certain condition under the timer1_timer event and initialize the timer event from form_load.

hope this will do something good to you.

regards
Shouvik