Newbie here and hoping this is the correct forum for my problem.
I'm trying to insert multiple rows to a table. If I run the query inside Access it first asks for confirmation and then it successfully adds the rows.
However, if I run the same sql string inside VB
.net it does not add any row. Here's the code:
What it does:
Dim strsql As String = "INSERT INTO tbl_ImagesDetails ( ImageID )
FROM tbl_Images LEFT JOIN tbl_ImagesDetails ON tbl_Images.[ImageID] = tbl_ImagesDetails.[ImageID]
WHERE (((tbl_Images.ImageID) Like 'ZOOMED*')
AND ((tbl_ImagesDetails.ImageID) Is Null));"
Dim MyConn As New OleDbConnection(connString)
Dim cmd As New OleDbCommand(strsql, MyConn)
cmd.CommandType = CommandType.Text
If Not MyConn.State = ConnectionState.Open Then MyConn.Open()
Dim iResult As Integer = cmd.ExecuteNonQuery()
The code above first checks for unmatched ImageID from the Images table against the ImageDetails table. Any ImageID that's missing in the details table is then added.
I use the same code pattern when inserting only single record and they run well.
1. Is the confirmation dialog getting in the way? If so how do I override using vb
2. Does ExecuteNonQuery() allow inserting multiple rows in one go?
Any help would be appreciated, thanks. My apologies if I posted in the wrong place.