visual basic 2008 - Fax numbers are stored in database.

[tgallina]

I have just finished creating a simple fax program - that can transmit, connect to the SQL DB, and query the customers table. How would I go about looping throught the list of phone numbers to transmit our availability to each customer. We have close to 1000 customer fax numbers after the query. Currently I've exported the list to a spreadsheet for testing.

Our fax server is a windows 2003 server with a rocket port modem and we have four fax lines. I would prefer after the query to transmit the faxes using two phone lines which can be configured on the server.. I would like to transmit the list in order - so a pause would have to be in place and if the distant line is busy to retry two times - then go to the next number. If anyone has any idea's it would greatly be appreciated.


Code:
<
Option Explicit On
Imports System
Imports System.Data
Imports System.Data.Sql Client
Imports Microsoft.Offic e.Interop

Public Class frm1
Inherits System.Windows. Forms.Form
'Declare variables for Fax server faxcomex library
Dim objFaxDocument As New FAXCOMEXLib.Fax Document
Dim objFaxServer As New FAXCOMEXLib.Fax Server
Dim JobID As Object
'Dim devices As FAXCOMEXLib.Fax Device



'Declare varibles for DB connection
Dim conn As New ADODB.Connectio n ' This declares the name of the connection
Dim rst As New ADODB.Recordset ' This declares the name of the recordset
Dim sSQL As String ' Variable used to open the recordset
Dim DevNursery As String ' Connection string variable
Dim appExcel As Excel.Applicati on
Dim wbkReport As Excel.Workbook
Dim wksReport As Excel.Worksheet
Dim intField As Integer, intRow As Integer
Dim dbstate As Integer



Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles btntransmitt.Cl ick




DevNursery = "Provider=SQLOL EDB;" & _
"DRIVER={SQLSer ver};" & _
"SERVER=Server3 500\NurserySQL; " & _
"DATABASE=DevNu rsery;" & _
"Trusted_Connec tion=yes"




conn.Open(DevNu rsery)

dbstate = conn.State
sSQL = "SELECT SName,SAvailabi lityFax FROM CustomersTbl where StoreNameID=25 and SAvailabilityFa x is not null"
rst.Open(sSQL, conn)
rst.MoveFirst()
If Not rst.EOF Then
Do Until rst.EOF
MessageBox.Show ("First record is: " & rst.Fields.Item ("SName").Value )
rst.MoveNext()
Loop
Else
End If

rst.Close()
conn.Close()
rst = Nothing


objFaxServer.Co nnect("serverfa x") 'Connect to the fax server

objFaxDocument. Body = "\\testPC\c$\te st.txt" 'Document to transmit


objFaxDocument. Recipients.Add( "11111111", "Todd") 'Number to dial for testing




JobID = objFaxDocument. ConnectedSubmit (objFaxServer)

MsgBox("The Job ID is :" & JobID(0))

'Set the cover page type and the path to the cover page
objFaxDocument. CoverPageType = FAXCOMEXLib.FAX _COVERPAGE_TYPE _ENUM.fcptSERVE R

'objFaxDocument .CoverPageType. FAXCOMEXLib.FAX _COVERPAGE_TYPE _ENUM.fcptNONE( )
objFaxDocument. CoverPage = "generic"

'Provide the cover page note
objFaxDocument. Note = "Availabili ty Sheet Nurseries"

'Provide the address for the fax receipt
objFaxDocument. ReceiptAddress = "emailaddre ss"

'Set the receipt type to email

objFaxDocument. ReceiptType = FAXCOMEXLib.FAX _RECEIPT_TYPE_E NUM.frtMAIL


'Set the receipt type to email
objFaxDocument. ReceiptType = FAXCOMEXLib.FAX _RECEIPT_TYPE_E NUM.frtMSGBOX

Exit Sub

End Sub

Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles btnclose.Click
Close()
End Sub

Private Sub Label1_Click(By Val sender As System.Object, ByVal e As System.EventArg s)

End Sub

Private Sub frm1_Load(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load

End Sub

Private Sub Button1_Click_1 (ByVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click



DevNursery = "Provider=SQLOL EDB;" & _
"DRIVER={SQLSer ver};" & _
"SERVER=Server3 500\NurserySQL; " & _
"DATABASE=DevNu rsery;" & _
"Trusted_Connec tion=yes"

conn.Open(DevNu rsery)
dbstate = conn.State
sSQL = "SELECT SName,SAvailabi lityFax FROM CustomersTbl where SAvailabilityFa x is not null and SAvailabilityFa x <> '0000000000'"



appExcel = New Excel.Applicati on
appExcel.Visibl e = True
wbkReport = appExcel.Workbo oks.Add
wksReport = wbkReport.Works heets(1)

rst.Open(sSQL, conn)
rst.MoveFirst()

If rst.EOF <> True Then
rst.MoveFirst()
intRow = 1
Do
For intField = 0 To rst.Fields.Coun t - 1
wksReport.Cells (intRow, intField + 1) = rst.Fields(intF ield).Value
'Name of column = .Name
'rst.Fields(int Field).Name & "=" &
Next intField
rst.MoveNext()
intRow = intRow + 1
Loop Until rst.EOF = True
End If
Exit Sub


rst.Close()
conn.Close()
rst = Nothing
End Sub
End Class
>