What is code if no record found?

[J Trahair]

Here is code to find all Blue T468's and to change their price to £16.50. This works fine.
If no Blue T468's are found, what is the equivalent to the old
'If rsRecordset.EOF = True Then' so I can add the INSERT INTO code?

PS. In the ObjDa.Fill(objDs, "xxx") and in the Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn) I have put 'xxx' as it doesn't seem to matter what that string is, or am I missing something?

Thanks for your patience!
Jonathan

Dim objConn As OleDbConnection
Dim objDs As DataSet
Dim ObjDa As OleDbDataAdapter
Dim objRow As DataRow

Try
    mstrSQL = "SELECT * FROM ItemsSold WHERE ProductCode = 'T468' AND Colour = 'Blue'"
    objDs = New DataSet()
    ObjDa = New OleDbDataAdapter(mstrSQL, mstrOLEConnectionString)
    ObjDa.Fill(objDs, "xxx")
    For Each objRow In objDs.Tables(0).Rows
        objConn = New OleDb.OleDbConnection(mstrOLEConnectionString)
        objConn.Open()
        Dim cmd As New System.Data.OleDb.OleDbCommand("xxx", objConn)
        cmd.CommandTimeout = 60
        cmd.Connection = objConn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "UPDATE ItemsSold SET UnitPrice = '16.50' WHERE RecNo = " & objRow("RecNo")
        cmd.ExecuteScalar()
    Next

    objDs.Dispose()
    ObjDa.Dispose()

    Exit Sub

Catch f As Exception
    'Code here is executed only on any error not caught so far ...
    MsgBox(f.Message & vbCrLf & f.StackTrace, MsgBoxStyle.Information, mCompanyName)

End Try

 

[ggunter]

I'm kinda new to Dot Net but wouldn't getting the total number of rows in the datatable do it?

If dataTable.Rows.Count > 0 Then
For Each Loop Here
code here
End For
End If

 

[cjard]

whoa.. did you not realise that an UPDATE statement can update more than one row?

i.e. there is no sense in doing this:

SELECT * FROM tblPerson WHERE name = 'Smith'
for each result, UPDATE tblPerson SET age = 20 WHERE personID = (result.personID)

If you want to update everyone called Smith to have an age of 20, you do:

UPDATE tblPerson SET age = 20 WHERE name = 'Smith'


When you ExecuteNonQuery this, you will get a return value of the number of rows that were updated. If the return value is 0, then you can insert!

Dim numUpd as Int = myCommand.ExecuteNonQuery()
If numUpd = 0 Then
... do the INSERT query


-

Though I have to say my usual line here, youre doing your data access a very old, and bad way and youre not making use of the tools that the IDE has to help you. Go and read the DW2 link in my signature, section of Creating a Simple Data App, and work from there

 

[J Trahair]

If numUpd = 0 Then
... do the INSERT query...

That's the answer I was looking for. However, I have changed things a bit now, and will look at your link, thank you. PS. The code I used came straight from a book on VB.Net/ADO.Net and several on-line tutorials and answers to other developers' queries.

 

[cjard]

The code I used came straight from a book on VB.Net/ADO.Net and several on-line tutorials and answers to other developers' queries.

OK, well.. My tutorials come directly from the horse's mouth (microsoft) so i'd go as far as to say your several other sources are leading you down the garden path