Why my insert doesnt insert to database..?

Question

violette 0 Junior Poster in Training

12 Years Ago

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

vb.net

3 Contributors
6 Replies
199 Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by Netcode

All 6 Replies

debasisdas 580 Posting Genius

12 Years Ago

In which line of code you are inserting into database ?

I can't find.

what i see is two select and one update statement in your entire code.

Edited 12 Years Ago by debasisdas because: n/a

Netcode 33 Veteran Poster

12 Years Ago

is it the insert code similar with update code?

Insert and update are not the same, they're two different things. When inserting, you're placing data into a new row but updating means making changes to an already existing record.

So do you want to insert or update?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

violette 0 Junior Poster in Training · Answer 1 · 2011-10-11T12:30:46+00:00

In which line of code you are inserting into database ?
I can't find.
what i see is two select and one update statement in your entire code.

sory-sory...im make a mistake. actually i cant update the database.is my update wrong? is it the insert code similar with update code?

Netcode 33 Veteran Poster · Answer 2 · 2011-10-12T10:58:09+00:00

is it the insert code similar with update code?

Insert and update are not the same, they're two different things. When inserting, you're placing data into a new row but updating means making changes to an already existing record.

So do you want to insert or update?

violette 0 Junior Poster in Training · Answer 3 · 2011-10-12T13:17:24+00:00

Oh.thanks..now i knew the difference.i want the update code for my code.thank you Netcode

Netcode 33 Veteran Poster · Answer 4 · 2011-10-13T20:14:36+00:00

This is the general syntax so all you need do is to fit in your table name, column and search condition:

UPDATE <table_or_view_name> 
SET column_name = {expression | DEFAULT | NULL} [ ,...n ]
WHERE <search_condition>

Why my insert doesnt insert to database..?

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers