Hi,
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:
Code:
Dim strsql As String = "INSERT INTO tbl_ImagesDetails ( ImageID )
SELECT tbl_Images.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));"
Debug.Print(strsql)
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()
Return iResult
What it does:
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.
My questions:
1. Is the confirmation dialog getting in the way? If so how do I override using
vb code?
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.
Insert multiple rows using ExecuteNonQuery in vb.net
