0

hi sir,

     i have vb.net project connected with msaccess ,in that when i inserting a value to one table i want to update the value in another table is it possible ,in my code it is not showing any error but it is not updating only inserting can u check my code and correct it this is my code
     CONNECT()
            CMD = New OleDbCommand("SELECT * FROM SOLDOUTITEM WHERE ITEM_NO='" & TextBox1.Text & "'", CON)
            DR = CMD.ExecuteReader
            If DR.Read Then
                MsgBox("ITEM NO ALREADY EXISTING", MsgBoxStyle.OkOnly, "INFORMATION")
                clear()
            Else
                CMD = New OleDbCommand("INSERT INTO SOLDOUTITEM VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & DateTimePicker1.Value & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & Label13.Text & "')", CON)
                CMD.ExecuteNonQuery()

                S = "SOLDOUT"
                CMD1 = New OleDbCommand("UPDATE NEWITEM SET ITEM_STATUS='" + S + "' WHERE ITEM_NO='" & TextBox1.Text & "'", CON)
                CMD1.ExecuteNonQuery()


                MsgBox("ITEM ADDED SUCCESSFULLY", MsgBoxStyle.OkOnly, "NEW ITEM")
                clear()

            End If
            CON.Close()


 CONNECT()
        CMD = New OleDbCommand("SELECT * FROM SOLDOUTITEM WHERE ITEM_NO='" & TextBox1.Text & "'", CON)
        DR = CMD.ExecuteReader
        If DR.Read Then
            MsgBox("ITEM NO ALREADY EXISTING", MsgBoxStyle.OkOnly, "INFORMATION")
            clear()
        Else
            CMD = New OleDbCommand("INSERT INTO SOLDOUTITEM VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & DateTimePicker1.Value & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & Label13.Text & "')", CON)
            CMD.ExecuteNonQuery()

            S = "SOLDOUT"
            CMD1 = New OleDbCommand("UPDATE NEWITEM SET ITEM_STATUS='" + S + "' WHERE ITEM_NO='" & TextBox1.Text & "'", CON)
            CMD1.ExecuteNonQuery()


            MsgBox("ITEM ADDED SUCCESSFULLY", MsgBoxStyle.OkOnly, "NEW ITEM")
            clear()

        End If
        CON.Close()
2
Contributors
1
Reply
12
Views
3 Years
Discussion Span
Last Post by xrj
0

Who knows when a security issue may overcome, but take a look at Click Here
Then, I think this other way may help:

                objConn = New OleDbConnection(sconn)
                objConn.Open()
                Dim AccessCommand As OleDb.OleDbCommand
                ' Set the sql string with 2 parameters:
                Dim sql As String = "UPDATE [NewItem] SET ITEM_STATUS=? WHERE ITEM_NO=?"
                AccessCommand = New OleDbCommand(sql)
                ' Fill in the parameters:
                Dim qParam(1) As OleDb.OleDbParameter
                qParam(0) = New OleDb.OleDbParameter("@ITEM_STATUS", OleDb.OleDbType.LongVarWChar)
                qParam(0).Value = "S"
                qParam(1) = New OleDb.OleDbParameter("@ITEM_NO", OleDb.OleDbType.LongVarWChar)
                qParam(1).Value = textbox1.text
        ' Add the parameters to the access command:
                For i = 0 To qParam.Length - 1
                    AccessCommand.Parameters.Add(qParam(i))
                Next
                ' Link the access cmd to the connection:
                AccessCommand.Connection = objConn
                Dim n As Int32 = AccessCommand.ExecuteNonQuery()
                objConn.Close()
                If n = 0 Then
                    ' err (No registers got updated)
        Else
            ' OK ('n' registers were updated)
        End If
This topic has been dead for over six months. 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.