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.SqlClient
Imports Microsoft.Office.Interop
Public Class frm1
Inherits System.Windows.Forms.Form
'Declare variables for Fax server faxcomex library
Dim objFaxDocument As New FAXCOMEXLib.FaxDocument
Dim objFaxServer As New FAXCOMEXLib.FaxServer
Dim JobID As Object
'Dim devices As FAXCOMEXLib.FaxDevice
'Declare varibles for DB connection
Dim conn As New ADODB.Connection ' 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.Application
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(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntransmitt.Click
DevNursery = "Provider=SQLOLEDB;" & _
"DRIVER={SQLServer};" & _
"SERVER=Server3500\NurserySQL;" & _
"DATABASE=DevNursery;" & _
"Trusted_Connection=yes"
conn.Open(DevNursery)
dbstate = conn.State
sSQL = "SELECT SName,SAvailabilityFax FROM CustomersTbl where StoreNameID=25 and SAvailabilityFax 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.Connect("serverfax") 'Connect to the fax server
objFaxDocument.Body = "\\testPC\c$\test.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.fcptSERVER
'objFaxDocument.CoverPageType.FAXCOMEXLib.FAX_COVE RPAGE_TYPE_ENUM.fcptNONE()
objFaxDocument.CoverPage = "generic"
'Provide the cover page note
objFaxDocument.Note = "Availability Sheet Nurseries"
'Provide the address for the fax receipt
objFaxDocument.ReceiptAddress = "emailaddress"
'Set the receipt type to email
objFaxDocument.ReceiptType = FAXCOMEXLib.FAX_RECEIPT_TYPE_ENUM.frtMAIL
'Set the receipt type to email
objFaxDocument.ReceiptType = FAXCOMEXLib.FAX_RECEIPT_TYPE_ENUM.frtMSGBOX
Exit Sub
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
Close()
End Sub
Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub frm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DevNursery = "Provider=SQLOLEDB;" & _
"DRIVER={SQLServer};" & _
"SERVER=Server3500\NurserySQL;" & _
"DATABASE=DevNursery;" & _
"Trusted_Connection=yes"
conn.Open(DevNursery)
dbstate = conn.State
sSQL = "SELECT SName,SAvailabilityFax FROM CustomersTbl where SAvailabilityFax is not null and SAvailabilityFax <> '0000000000'"
appExcel = New Excel.Application
appExcel.Visible = True
wbkReport = appExcel.Workbooks.Add
wksReport = wbkReport.Worksheets(1)
rst.Open(sSQL, conn)
rst.MoveFirst()
If rst.EOF <> True Then
rst.MoveFirst()
intRow = 1
Do
For intField = 0 To rst.Fields.Count - 1
wksReport.Cells(intRow, intField + 1) = rst.Fields(intField).Value
'Name of column = .Name
'rst.Fields(intField).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
>