0

Hi All's,

Really need your help, i'm trying to update existing data in MDB database by date.today but cannot update . Here my coding

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source= PTGDB.mdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM ST WHERE [DATE]=@DATE  ", con)
        cmd.Parameters.Add(New OleDbParameter("@Date", OleDbType.Date)).Value = Date.Today
        Try

            con.Open()

            Dim sdr As OleDbDataReader = cmd.ExecuteReader()




            If sdr.Read = True Then

                TextBox1.Text = "True"
                Label3.Text = sdr.Item("CLICK")

                Dim B As Integer



                B = Val(Label3.Text) + 1


                ' Dim CMD3 As OleDbCommand = New OleDbCommand("UPDATE ST SET CLICK= (@CLICK) WHERE  [DATE] = @date ", con)
                Dim CMD3 As OleDbCommand = New OleDbCommand("UPDATE ST SET CLICK = (@CLICK) WHERE [DATE]= 'today' ", con)
                CMD3.Parameters.Add(New OleDbParameter("'@NEW'", OleDbType.Date)).Value = Date.Today
                 CMD3.Parameters.AddWithValue("@CLICK", B)



                CMD3.ExecuteNonQuery()






                Label3.Text = sdr.Item("CLICK")
                con.Close()

            ElseIf sdr.Read = False Then
                TextBox1.Text = "False"
                Dim inc As Integer = +1
                Dim CMD1 As OleDbCommand = New OleDbCommand("INSERT INTO ST ([DATE],[DATETIME],CLICK) VALUES (@DATE,@DATETIME,@CLICK)", con)
                CMD1.Parameters.Add(New OleDbParameter("@DATE", OleDbType.Date)).Value = Today
                CMD1.Parameters.Add(New OleDbParameter("@DATETIME", OleDbType.Date)).Value = Date.Now
                CMD1.Parameters.Add("@CLICK", OleDbType.Integer).Value = "" & Label1.Text & ""

                CMD1.ExecuteNonQuery()

                Label3.Text = sdr.Item("CLICK")
                con.Close()


            End If

        Catch ex As Exception
            con.close()
        End Try

Please help.....for inserting working fine but for update nothing happen..

Tq

2
Contributors
2
Replies
10
Views
3 Years
Discussion Span
Last Post by kerek2
0

I'm not sure about a couple of things. First, I don't know why you have the single quotes arount @NEW in

CMD3.Parameters.Add(New OleDbParameter("'@NEW'", OleDbType.Date)).Value = Date.Today

I'm also confused as to why you don't get an error by putting "@" type parameters in the query. On my system (vb.net 2010) I get the error

Must declare the scalar variable "@name".

where I put @name in the query string. This is how I would build the query using OleDb

Dim CMD3 As New OleDbCommand("UPDATE ST SET CLICK = ? WHERE [DATE]= ?",con)

CMD3.Parameters.AddWithValue("@parm1", Label1.Text)
CMD3.Parameters.AddWithValue("@parm2", Today)

where each replaceable parameter is indicated by ? and parameters must be added in the order in which they appear in the query. So try that and let me know how it goes.

Edited by Reverend Jim

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.