Table not getting updated

[vivekanandaan]

Dim connectionString As String
        Dim cnn1 As OleDbConnection
        Dim cmd1 As OleDbCommand
        Dim sql1 As String
        connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = d:\sprrg\sprrg.mdb"
        sql1 = "update po_detail set po_number=" & txtPoNo.Text & " where po_number=0;"
        cnn1 = New OleDbConnection(connectionString)
        cnn1.Open()
        cmd1 = New OleDbCommand(sql1, cnn1)
        cmd1.ExecuteNonQuery()
        cmd1.Dispose()

The table is not getting updated. Kindly help...

[Mitja Bonca]

try like:

"update po_detail set po_number= '" & txtPoNo.Text & "' where po_number = 0"

or if your database column is type of integer:

"update po_detail set po_number= '" & Integer.Parse(txtPoNo.Text) & "' where po_number = 0"

[Mitja Bonca]

Otherwise you can do parameterised query:

sql1 = "update po_detail set po_number= @param1 where po_number=0;"
        cnn1 = New OleDbConnection(connectionString)
        cnn1.Open()
        cmd1 = New OleDbCommand(sql1, cnn1)
        cmd1.Parameters.Add("@param1", OleDbType.Int).Value = Integer.Parse(txtPoNo.Text) ' NOTE: CHECK THE TYPE OF YOUR COLUMN IN DB!!!
        cmd1.ExecuteNonQuery()

[Begginnerdev]

Is po_number an autonumber? Just curious.

[vivekanandaan]

Is po_number an autonumber? Just curious.

Yes ... PO number is autonumber

[Begginnerdev]

You can not alter autonumbers from queries. They are fixed values.

Change the column to integer.

That should fix your problem, but you can't have two matching numbers.

[Begginnerdev]

One easy way to do this would to be pre-query the database with the number that the user is wanting to insert. If the number doesn't exist, then allow them to alter, if it does, prompt them.

This will safeguard from those nasty error messages.