Hi list
Im using webservice to return the dataset for the sql string i provide.
When i loop thro each row of the dataset, i receve the below error when it
retrieves the value from an autonumber field from MySQL table.
An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll
Additional information: Cast from type 'UInt32' to type 'Integer' is not
valid.
I tried using cstr(eRow("sid")) and cint(..) nothing solved - what should i
do.
Thanks
Ilyas
----------
Imports System.Web
Public Class dispsts
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents txtDisp As System.Windows.Forms.TextBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.txtDisp = New System.Windows.Forms.TextBox()
Me.SuspendLayout()
'
'txtDisp
'
Me.txtDisp.BackColor = System.Drawing.Color.Black
Me.txtDisp.Font = New System.Drawing.Font("Arial", 8.0!,
System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0,
Byte))
Me.txtDisp.ForeColor = System.Drawing.SystemColors.HighlightText
Me.txtDisp.Multiline = True
Me.txtDisp.Name = "txtDisp"
Me.txtDisp.ReadOnly = True
Me.txtDisp.Size = New System.Drawing.Size(456, 112)
Me.txtDisp.TabIndex = 0
Me.txtDisp.Text = "TextBox1"
'
'dispsts
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(456, 109)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.txtDisp})
Me.Name = "dispsts"
Me.Text = "Steel Process Emailer"
Me.ResumeLayout(False)
End Sub
#End Region
Dim strSql As String
Private Sub dispsts_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
txtDisp.Clear() 'clears the txt box
Dim e_crntDay, e_crntMonth, e_crntYear As Integer
Dim e_crntDate As String
e_crntDay = Microsoft.VisualBasic.Day(Date.Today)
e_crntMonth = Microsoft.VisualBasic.Month(Date.Today)
e_crntYear = Microsoft.VisualBasic.Year(Date.Today)
e_crntDate = e_crntYear
If Len(e_crntMonth.ToString) = 1 Then
e_crntDate = e_crntDate & "-" & "0" & e_crntMonth
Else
e_crntDate = e_crntDate & "-" & e_crntMonth
End If
If Len(e_crntDay.ToString) = 1 Then
e_crntDate = e_crntDate & "-" & "0" & e_crntDay
Else
e_crntDate = e_crntDate & "-" & e_crntDay
End If
txtDisp.Text = "Current Date: " & e_crntDate & vbCrLf
txtDisp.Text = txtDisp.Text & "Running posted tasks reminders...." &
vbCrLf
' Call posted_tasks(e_crntDay)
txtDisp.Text = txtDisp.Text & "------- Posted tasks done --------" &
vbCrLf
txtDisp.Text = txtDisp.Text & "Running approved tasks reminders...." &
vbCrLf
' Call aPProved_tasks(e_crntDay)
txtDisp.Text = txtDisp.Text & "------- Approved tasks done --------" &
vbCrLf
txtDisp.Text = txtDisp.Text & "Running NOT approved tasks
reminders...." & vbCrLf
Call noT_approved_tasks()
txtDisp.Text = txtDisp.Text & "------- NOT approved tasks done
--------" & vbCrLf
txtDisp.Text = txtDisp.Text & "All done - you can close me now ;-)"
' End
End Sub
Private Sub txtDisp_TextChanged(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles txtDisp.TextChanged
End Sub
Sub posted_tasks(ByVal crntDate)
Dim emlBody As String
Dim totWt As Double, recExists As Boolean
'strSQL = ""
'strSQL = strSQL & " SELECT cdsteeljob.requser, cdsteeljob.sid,
cdsteeljob.forclient,"
'strSQL = strSQL & " cdsteeljob.reqDate, cdsteeljob.jobref,
cdsteeljob.mtrlorigin,"
'strSQL = strSQL & " cdsteeljob.tallyref, cdsteeljob.qlty,
cdsteeljob.jobmaterial,"
'strSQL = strSQL & " SUM(cdsteeljob_master.netWt) AS postedWeight"
'strSQL = strSQL & " FROM(cdsteeljob) INNER JOIN cdsteeljob_master on"
'strSQL = strSQL & " (cdsteeljob.sid = cdsteeljob_master.mainID)"
'strSQL = strSQL & " GROUP by cdsteeljob.requser, cdsteeljob.sid,"
'strSQL = strSQL & " cdsteeljob.forclient, cdsteeljob.reqDate,
cdsteeljob.jobref,"
'strSQL = strSQL & " cdsteeljob.mtrlorigin, cdsteeljob.tallyref,
cdsteeljob.qlty,"
'strSQL = strSQL & " cdsteeljob.jobmaterial"
'strSQL = strSQL & " HAVING DATE(cdsteeljob.reqdate)='" & crntDate & "'"
'strSQL = strSQL & " ORDER BY cdsteeljob.jobref"
strSql = ""
strSql = strSql & " SELECT steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " Sum(steel_parent_details.p_priqty_gross) AS
posted_pqty,"
strSql = strSql & " Sum(steel_parent_details.p_secqty_gross) AS
posted_sqty,"
strsql = strsql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname"
strSql = strSql & " FROM ((steel_parent INNER JOIN dxb_products ON
steel_parent.material_id = dxb_products.prodID)"
strSql = strSql & " INNER JOIN steel_parent_details ON steel_parent.sID
= steel_parent_details.parentID)"
strSql = strSql & " LEFT JOIN dbcms_mw_clients_pointer ON
steel_parent.forClient = dbcms_mw_clients_pointer.pointer_companyID"
strSql = strSql & " GROUP BY steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname"
strSql = strSql & " HAVING DATE(steel_parent.reqDate)='" & crntDate &
"'"
strSql = strSql & " ORDER BY steel_parent.jobref"
Dim emlWS As New com.mysite.www.service_stock()
emlWS = New com.mysite.www.service_stock()
Dim eRow As DataRow
Dim eSet As DataSet
eSet = emlWS.return_dataset(strSql)
totWt = 0
recExists = False
emlBody = ""
emlBody = emlBody & form_header("Steel process tasks posted on " &
Date.Today) 'forms the header part
For Each eRow In eSet.Tables(0).Rows
recExists = True
emlBody = emlBody & "<tr>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("reqUser") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size: 8pt'>"
emlBody = emlBody & "<a target='_blank'
href='http://www.mysite.com/dxb/spjob_print.asp?job="; & eRow("sid") & "'>"
emlBody = emlBody & eRow("jobRef") & "</a></font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & FormatDateTime(eRow("reqDate"), DateFormat.ShortDate) & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("pointer_firma") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("prodName") & "(" & eRow("mtrlOrigin") & ")" & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("Qlty") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("TallyRef") & "</font></td>"
emlBody = emlBody & "<td align='right'><font face='Arial'
style='font-size: 8pt'>" & eRow("posted_pqty") & "</font></td>"
emlBody = emlBody & "</tr>"
totWt = totWt + eRow("posted_pqty")
Next
eSet.Dispose()
emlBody = emlBody & form_footer(totWt)
If recExists Then Call send_email("Steel process tasks posted on " &
Date.Today, emlBody) Else Call send_email("Steel process tasks posted on " &
Date.Today, "No data")
End Sub
Function form_header(ByVal header_subj As String) As String
Dim strBody As String
strBody = ""
strBody = strBody & "<table border='1' cellspacing='1' id='table1'
style='border-collapse: collapse'>"
strBody = strBody & "<tr>"
strBody = strBody & "<td colspan='8' bgcolor='#F5E9A0'>"
strBody = strBody & "<p align='center'><span style='font-weight: 700'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt'
color='#800080'>"
strBody = strBody & header_subj
strBody = strBody & "</font></span></td>"
strBody = strBody & "</tr>"
strBody = strBody & "<tr>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Trader</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Job reference</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Job Date</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Client</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Material (origin)</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Quality</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Tally Reference</font></td>"
strBody = strBody & "<td bgcolor='#000000'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt;
font-weight: 700' color='#FFFFFF'>Net Weight (MT)</font></td>"
strBody = strBody & "</tr>"
form_header = strBody
End Function
Function form_footer(ByVal tot_weight As Double) As String
Dim strBody As String
strBody = ""
strBody = strBody & "<tr>"
strBody = strBody & "<td bgcolor='#000000' colspan='7'>"
strBody = strBody & "<p align='right'><span style='font-weight: 700'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt'
color='#FFFFFF'>Totals</font></span></td>"
strBody = strBody & "<td bgcolor='#000000' align='right'><span
style='font-weight: 700'>"
strBody = strBody & "<font face='Arial' style='font-size: 8pt'
color='#FFFFFF'>"
strBody = strBody & tot_weight & "</font></span></td>"
strBody = strBody & "</tr>"
strBody = strBody & "</table>"
form_footer = strBody
End Function
Sub send_email(ByVal email_subject As String, ByVal email_body As String)
'---- Instantiate MailMessage -------------
Dim myEmail As System.Web.Mail.MailMessage = New
System.Web.Mail.MailMessage()
'---- Set Message Properties -------------
myEmail.From = "[EMAIL PROTECTED]"
myEmail.To = "[EMAIL PROTECTED]"
myEmail.Subject = email_subject
myEmail.BodyFormat = System.Web.Mail.MailFormat.Html
If email_body = "No data" Then
myEmail.Body = "Nothing today"
Else
myEmail.Body = email_body
End If
'---- Send Message -------------------------
' System.Web.Mail.SmtpMail.SmtpServer = "mail.parawebic.com"
' System.Web.Mail.SmtpMail.SmtpServer.Insert(0, "localhost")
System.Web.Mail.SmtpMail.SmtpServer.Insert(0, "192.168.0.14")
System.Web.Mail.SmtpMail.Send(myEmail)
End Sub
Sub aPProved_tasks(ByVal crntDate)
Dim emlBody As String
Dim totWt As Double, recExists As Boolean
strSql = ""
strSql = strSql & " SELECT steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " Sum(steel_parent_details.p_priqty_gross) AS
posted_pqty,"
strSql = strSql & " Sum(steel_parent_details.p_secqty_gross) AS
posted_sqty,"
strSql = strSql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname,"
strSql = strSql & " steel_parent.stkTransfer_Date"
strSql = strSql & " FROM ((steel_parent INNER JOIN dxb_products ON
steel_parent.material_id = dxb_products.prodID)"
strSql = strSql & " INNER JOIN steel_parent_details ON steel_parent.sID
= steel_parent_details.parentID)"
strSql = strSql & " LEFT JOIN dbcms_mw_clients_pointer ON
steel_parent.forClient = dbcms_mw_clients_pointer.pointer_companyID"
strSql = strSql & " GROUP BY steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname,"
strSql = strSql & " steel_parent.stkTransfer_Date"
strSql = strSql & " HAVING DATE(steel_parent.stkTransfer_date)='" &
crntDate & "'"
strSql = strSql & " ORDER BY steel_parent.jobref"
Dim emlWS As New com.mysite.www.service_stock()
emlWS = New com.mysite.www.service_stock()
Dim eRow As DataRow
Dim eSet As DataSet
eSet = emlWS.return_dataset(strSQL)
totWt = 0
recExists = False
emlBody = ""
emlBody = emlBody & form_header("Steel process tasks approved by
accounts on " & Date.Today) 'forms the header part
For Each eRow In eSet.Tables(0).Rows
recExists = True
emlBody = emlBody & "<tr>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("reqUser") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size: 8pt'>"
emlBody = emlBody & "<a target='_blank'
href='http://www.mysite.com/dxb/spjob_print.asp?job="; & eRow("sid") & "'>"
emlBody = emlBody & eRow("jobRef") & "</a></font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & FormatDateTime(eRow("reqDate"), DateFormat.ShortDate) & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("pointer_firma") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("prodName") & "(" & eRow("mtrlOrigin") & ")" & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("Qlty") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("TallyRef") & "</font></td>"
emlBody = emlBody & "<td align='right'><font face='Arial'
style='font-size: 8pt'>" & eRow("posted_pqty") & "</font></td>"
emlBody = emlBody & "</tr>"
totWt = totWt + eRow("posted_pqty")
Next
eSet.Dispose()
emlBody = emlBody & form_footer(totWt)
If recExists Then Call send_email("Steel process tasks approved by
accounts on " & Date.Today, emlBody) Else Call send_email("Steel process tasks
approved by accounts on " & Date.Today, "No data")
End Sub
Sub noT_approved_tasks()
Dim emlBody As String
Dim totWt As Double, recExists As Boolean
strSql = ""
strSql = strSql & " SELECT steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " Sum(steel_parent_details.p_priqty_gross) AS
posted_pqty,"
strSql = strSql & " Sum(steel_parent_details.p_secqty_gross) AS
posted_sqty,"
strSql = strSql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname,"
strSql = strSql & " steel_parent.stkTransfer_Done,
steel_parent.jobCancelled"
strSql = strSql & " FROM ((steel_parent INNER JOIN dxb_products ON
steel_parent.material_id = dxb_products.prodID)"
strSql = strSql & " INNER JOIN steel_parent_details ON steel_parent.sID
= steel_parent_details.parentID)"
strSql = strSql & " LEFT JOIN dbcms_mw_clients_pointer ON
steel_parent.forClient = dbcms_mw_clients_pointer.pointer_companyID"
strSql = strSql & " GROUP BY steel_parent.sID, steel_parent.reqUser,"
strSql = strSql & " steel_parent.reqDate, steel_parent.jobRef,"
strSql = strSql & " steel_parent.mtrlOrigin, steel_parent.TallyRef,"
strSql = strSql & " steel_parent.Qlty, steel_parent.forClient,"
strSql = strSql & " steel_parent.material_id, dxb_products.prodName,"
strSql = strSql & " dbcms_mw_clients_pointer.pointer_firma,
dbcms_mw_clients_pointer.pointer_shortname,"
strSql = strSql & " steel_parent.stkTransfer_Done,
steel_parent.jobCancelled"
strSql = strSql & " HAVING steel_parent.stkTransfer_Done <> 1"
strSql = strSql & " AND steel_parent.jobCancelled <> 1"
strSql = strSql & " AND YEAR(steel_parent.reqDate) > 2004"
strSql = strSql & " ORDER BY steel_parent.jobref"
Dim emlWS As New com.mysite.www.service_stock()
emlWS = New com.mysite.www.service_stock()
Dim eRow As DataRow
Dim eSet As DataSet
eSet = emlWS.return_dataset(strSql)
totWt = 0
recExists = False
emlBody = ""
emlBody = emlBody & form_header("Steel process tasks NOT approved by
accounts on " & Date.Today) 'forms the header part
For Each eRow In eSet.Tables(0).Rows
recExists = True
emlBody = emlBody & "<tr>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("reqUser") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size: 8pt'>"
emlBody = emlBody & "<a target='_blank'
href='http://www.mysite.com/dxb/spjob_print.asp?job="; & eRow("sid") & "'>"
emlBody = emlBody & eRow("jobRef") & "</a></font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & FormatDateTime(eRow("reqDate"), DateFormat.ShortDate) & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("pointer_firma") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("prodName") & "(" & eRow("mtrlOrigin") & ")" & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("Qlty") & "</font></td>"
emlBody = emlBody & "<td><font face='Arial' style='font-size:
8pt'>" & eRow("TallyRef") & "</font></td>"
emlBody = emlBody & "<td align='right'><font face='Arial'
style='font-size: 8pt'>" & eRow("posted_pqty") & "</font></td>"
emlBody = emlBody & "</tr>"
totWt = totWt + eRow("posted_pqty")
Next
eSet.Dispose()
emlBody = emlBody & form_footer(totWt)
If recExists Then Call send_email("Steel process tasks NOT approved by
accounts on " & Date.Today, emlBody) Else Call send_email("Steel process tasks
NOT approved by accounts on " & Date.Today, "No data")
End Sub
End Class
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
