CODEWITHSUNDEEP
vb.netms-accessdatagridview
shaban elmogy
shaban elmogy

Reputation: 1

I want to Avoid duplicated Insert data from vb.net to Access Database

I want to avoid Duplicat what the correct syntax to make this ?

Dim StrInsert As String = "Insert Into Job (Name) Select @OtherInfo30 
                           WHERE @OtherInfo30 not in (select name from job)"

but from where query wrong

Upvotes: 0

Views: 227

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49159

I don't believe that Access SQL supports a where clause on insert, and worse it for sure does not support a sub-query.

Now in a "lot" of cases, if the row does exist, then we often want to update that row, and I can post the code for that.

However, in this case? We test for the row exist, and if not, then run a insert command.

Yes, this does mean two SQL statements, but we DO GET to re-use the parameter!! (so this is not too much code, and not "too" bad.

So this:

    Dim strAddHotel As String = "Zoo5"

    Using cmdSQL As New OleDbCommand("SELECT ID FROM tblHotels WHERE HotelName = @Hotel",
                    New OleDbConnection(My.Settings.TESTAce))

        cmdSQL.Parameters.Add("@Hotel", OleDbType.VarWChar).Value = strAddHotel
        cmdSQL.Connection.Open()
        If cmdSQL.ExecuteScalar = 0 Then
            cmdSQL.CommandText = "INSERT into tblHotels (HotelName) VALUES(@Hotel)"
            cmdSQL.ExecuteNonQuery()
        End If

    End Using

Upvotes: 1

Related Questions