update value by date.today in mdb

[kerek2]

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

[Reverend Jim]

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.

[kerek2]

Ok thank you bro,, you awesome...problem solve...luv u all