Please help.why my insert doesn't insert into mysql database
Imports FISTv2.mdlDataConn
Imports MySql.Data.MySqlClient
Imports System.Data
Partial Public Class DSO
Inherits System.Web.UI.Page
Private cmd As New MySqlCommand
Dim myCommand As New MySqlCommand
Private dr As MySqlDataReader
Dim activity, issue_id As String
Dim pperiod As String
Dim bboard As String
Dim byear1 As String
Dim byear2 As String
Dim qter1 As String
Dim qter2 As String
Dim stockCode As Label
Dim total1 As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles search.Click
Try
ConnectDatabase()
'TitleSummary.Visible = True
pperiod = PeriodDropDown.SelectedValue
byear1 = SelectYear1.SelectedValue
byear2 = SelectYear2.SelectedValue
qter1 = SelectQuarter1.SelectedValue
bboard = AlertColourDropDown.SelectedValue
datareset()
If (pperiod = "annual") Then
BindDataYear()
Else
BindDataQuater()
End If
Insertdata()
btnExport.Visible = True
activity = "" & AlertColourDropDown.SelectedItem.ToString & " color. "
insert_auditTrail(activity)
Catch myerror As Exception
MsgBox("Error : " & myerror.Message)
Finally
DisconnectDatabase()
End Try
End Sub
Protected Sub BindDataYear()
Dim strSQL As String
strSQL = "SELECT para1, a.stock_code, current_name, board_name, sector_name, sub_sector_name, e.year_id, e.receivable, e.revenue, (e.receivable*e.revenue/360) AS DSO,e.period_id"
strSQL = strSQL & " FROM ref_alert_parameter, qiddb.company_info a "
strSQL = strSQL & " LEFT JOIN ref_board b ON b.board_code=a.board_code "
strSQL = strSQL & " LEFT JOIN ref_sector c ON c.sector_code=a.sector_code"
strSQL = strSQL & " LEFT JOIN ref_sub_sector d ON d.sub_sector_code=a.sub_sector_code"
strSQL = strSQL & " LEFT JOIN company_financial e ON e.stock_code=a.stock_code"
strSQL = strSQL & " LEFT JOIN ref_financial_year f ON f.year_id=e.year_id"
strSQL = strSQL & " LEFT JOIN company_financial g ON g.stock_code=a.stock_code"
strSQL = strSQL & " WHERE alert_id='DSO'AND e.year_id BETWEEN '" & byear1 & "' AND '" & byear2 & "'"
cmd = New MySqlCommand(strSQL, conn)
cmd.CommandTimeout = 0
dr = cmd.ExecuteReader()
'*** BindData to Repeater ***'
repeaterAlert.DataSource = dr
repeaterAlert.DataBind()
dr.Close()
dr = Nothing
End Sub
Protected Sub BindDataQuater()
Dim strSQL As String
strSQL = "SELECT para1, a.stock_code, current_name, board_name, sector_name, sub_sector_name, e.year_id, e.receivable, e.revenue, e.period_id"
strSQL = strSQL & " FROM ref_alert_parameter,qiddb.company_info a "
strSQL = strSQL & " LEFT JOIN ref_board b ON b.board_code=a.board_code "
strSQL = strSQL & " LEFT JOIN ref_sector c ON c.sector_code=a.sector_code"
strSQL = strSQL & " LEFT JOIN ref_sub_sector d ON d.sub_sector_code=a.sub_sector_code"
strSQL = strSQL & " LEFT JOIN company_financial e ON e.stock_code=a.stock_code"
strSQL = strSQL & " LEFT JOIN ref_financial_year f ON f.year_id=e.year_id"
strSQL = strSQL & " LEFT JOIN company_financial g ON g.stock_code=a.stock_code"
strSQL = strSQL & " WHERE alert_id='DSO' AND e.year_id BETWEEN '" & byear1 & "'AND '" & byear2 & "' AND e.period_id='" & qter1 & "'"
cmd = New MySqlCommand(strSQL, conn)
cmd.CommandTimeout = 0
dr = cmd.ExecuteReader()
'*** BindData to Repeater ***'
repeaterAlert.DataSource = dr
repeaterAlert.DataBind()
dr.Close()
dr = Nothing
End Sub
Protected Sub Insertdata()
Dim command As New MySqlCommand("UPDATE company_alert SET alert_value = @alert_value, created_date = @chg_pwd_date where stock_code = @stockcode", conn)
command.Parameters.AddWithValue("@alert_value", total1.Text)
command.Parameters.AddWithValue("@stockcode", stockCode.Text)
command.Parameters.AddWithValue("@chg_pwd_date", Format(Now(), "yyyy-MM-dd"))
command.ExecuteNonQuery()
End Sub
Protected Sub repeaterAlert_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs) Handles repeaterAlert.ItemDataBound
stockCodeheader.Text = " Stock Code "
plcNameheader.Text = " PLC Name "
boardheader.Text = " Board "
sectorheader.Text = " Sector "
subSectorheader.Text = " Sub Sector "
yearheader.Text = " Year "
total.Text = " DSO "
Dim stockCode As Label = CType(e.Item.FindControl("stockCode"), Label)
If Not IsNothing(stockCode) Then
stockCode.Text = e.Item.DataItem("stock_code").ToString
End If
Dim plcName As Label = CType(e.Item.FindControl("plcName"), Label)
If Not IsNothing(plcName) Then
plcName.Text = e.Item.DataItem("current_name").ToString
Else
End If
Dim board As Label = CType(e.Item.FindControl("board"), Label)
If Not IsNothing(board) Then
board.Text = e.Item.DataItem("board_name").ToString
Else
End If
Dim sector As Label = CType(e.Item.FindControl("sector"), Label)
If Not IsNothing(sector) Then
sector.Text = e.Item.DataItem("sector_name").ToString
Else
End If
Dim subSector As Label = CType(e.Item.FindControl("subSector"), Label)
If Not IsNothing(subSector) Then
subSector.Text = e.Item.DataItem("sub_sector_name").ToString
Else
End If
Dim year As Label = CType(e.Item.FindControl("year"), Label)
If Not IsNothing(year) Then
year.Text = e.Item.DataItem("year_id").ToString
Else
End If
Dim result1 As Label = CType(e.Item.FindControl("result1"), Label)
If Not IsNothing(result1) Then
result1.Text = e.Item.DataItem("revenue").ToString
Else
End If
Dim result2 As Label = CType(e.Item.FindControl("result2"), Label)
If Not IsNothing(result2) Then
result2.Text = e.Item.DataItem("receivable").ToString
Else
End If
Dim total1 As Label = CType(e.Item.FindControl("total1"), Label)
total1.Text = e.Item.DataItem("receivable").ToString * 365 / e.Item.DataItem("revenue").ToString
Dim compval As Double = total1.Text
total1.Text = Math.Round(compval, 2)
If total1.Text >= e.Item.DataItem("para1").ToString Then
total1.BackColor = Drawing.Color.Red
ElseIf total1.Text <= e.Item.DataItem("para1").ToString Then
total1.BackColor = Drawing.Color.Yellow
End If
End Sub
Protected Sub PeriodDropDown_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodDropDown.SelectedIndexChanged
If PeriodDropDown.Text = "annual" Then
PeriodDropDown.Enabled = True
SelectYear1.Visible = True
SelectYear2.Visible = True
SelectQuarter1.Visible = False
SelectQuarter2.Visible = False
AlertColourDropDown.Visible = True
ElseIf PeriodDropDown.Text = "quarter" Then
PeriodDropDown.Enabled = True
SelectYear1.Visible = True
SelectYear2.Visible = True
SelectQuarter1.Visible = True
SelectQuarter2.Visible = True
AlertColourDropDown.Visible = True
End If
End Sub
Protected Sub datareset()
stockCodeheader.Text = " No Record "
plcNameheader.Text = " No Record "
boardheader.Text = " No Record "
sectorheader.Text = " No Record "
subSectorheader.Text = " No Record "
yearheader.Text = " No Record "
total.Text = " No Record "
End Sub
Protected Sub account_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles account.Click
Response.Redirect("../Account/AccountMain.aspx", False)
End Sub
Protected Sub homepage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles homepage.Click
Response.Redirect("../User/Home.aspx", False)
End Sub
Protected Sub references_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles references.Click
Response.Redirect("../Reference/Reference.aspx", False)
End Sub
End Class
