Insert and Update query

[Butterfly]

Im trying to do an insert an an update in the same function,but it only allow an upate only if a record exist.
Here is my code:
Sub insert(ByVal UserSelection, ByVal Grand_Prix_ID)

'The date function
'IF the 2 or more days left before the race then
' Do the insert
CompareDates(User)
Dim mysql As String
Dim strConn As String
Dim MUser_ID = Request.QueryString("UserID")
Dim Nickname = Request.QueryString("name")
Dim LastGP_ID As Integer = Grand_Prix_ID - 1
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Server.MapPath("App_Data\FantasyF1.mdb") & ";"

If TeamSelection_ID(Grand_Prix_ID) Then

mysql = "INSERT INTO TeamSelection(Grand_Prix_ID, User_ID, Driver_ID1, Driver_ID2, Driver_ID3, Driver_ID4, Driver_ID5, Driver_ID6)" & "VALUES(@Grand_Prix_ID ,@User_ID, @Driver_ID1, @Driver_ID2, @Driver_ID3, @Driver_ID4, @Driver_ID5, @Driver_ID6)"

'else if already selected before do the update
Else
MsgBox("You have made the selection before and You are about to update", MsgBoxStyle.YesNo)
mysql = "UPDATE TeamSelection SET Grand_Prix_ID = @Grand_Prix_ID, User_ID = @User_ID, Driver_ID1 = @DRIVER_ID1, Driver_ID2 = @DRIVER_ID2, Driver_ID3 = @DRIVER_ID3, Driver_ID4 = @DRIVER_ID4, Driver_ID5 = @DRIVER_ID5, Driver_ID6 = @DRIVER_ID6" & ""
mysql = mysql & " WHERE User_ID =" & MUser_ID
mysql = mysql & " AND Grand_Prix_ID =" & UserSelection(7) & ""

Dim Myconn As New OleDbConnection(strConn)
Dim objComm As New OleDbCommand(mysql, Myconn)
Myconn.Open()

With objComm.Parameters
.Add(New OleDbParameter("@Grand_Prix_ID", UserSelection(7)))
.Add(New OleDbParameter("@User_ID", UserSelection(6)))
.Add(New OleDbParameter("@Driver_ID1", UserSelection(0)))
.Add(New OleDbParameter("@Driver_ID2", UserSelection(1)))
.Add(New OleDbParameter("@Driver_ID3", UserSelection(2)))
.Add(New OleDbParameter("@Driver_ID4", UserSelection(3)))
.Add(New OleDbParameter("@Driver_ID5", UserSelection(4)))
.Add(New OleDbParameter("@Driver_ID6", UserSelection(5)))

End With
objComm.ExecuteNonQuery()
Message.Text = "Your Selection has been successfully recieved"
Myconn.Close()
End If

End Sub

 

[Fifty2One]

I always get lost and hung up on trying to build SQL in VB... now I just build the queries in access and run them with the DoCmd.OpenQuery statement...

 

[Butterfly]

Resolved the issue

It worked but thanks for the advise

 

[TastyWheat]

I posted a single SQL statement that inserts and updates on a different website. I guess since I use this site more I'll post it here. This is a basic example of an insert/update query:

[B]UPDATE[/B] [Order Details] As OD1 [B]RIGHT JOIN[/B] (
       [B]SELECT[/B] 10248 As [OrderID], 11 As [ProductID],
              0 As [UnitPrice], 0 As [Quantity], 0 As [Discount]
       [B]FROM[/B] [Order Details]) AS OD2
[B]ON[/B] (OD1.OrderID=OD2.OrderID) AND (OD1.ProductID=OD2.ProductID)
[B]SET[/B] OD1.OrderID = 10248, OD1.ProductID = 11,
    OD1.UnitPrice = 11.30, OD1.Quantity = 5, OD1.Discount = 0.05;

Before somebody screams "bloody murder" I did get help from this article. However, that article does not explain how to update using values OR how to perform an insert/update using only one table.

The things you need to know about the above query are:

• The SELECT clause needs to have the same number of fields as your target table.
• The key values (OrderID and ProductID in this case) specify which record to update (like a WHERE clause would).
• The actual values that will be inserted/updated on the record come from the SET clause (hence the zeros in the SELECT clause).
• The target table must have at least one record for this to work.