SQL INSERT Question

[Ralph]

vb.net 2003 Coding..........

Dim SQL As String =
"INSERT INTO worktable ( hostname ) " & _
"SELECT [ftpimport].[hostname] " & _
"FROM ftpimport " & _
"GROUP BY [ftpimport].[hostname] " & _
"HAVING (((Count(ftpimport.hostname)) > " & Hits & "));"

******** Coding for 1st attempt****************
Dim rs As ADODB.Recordset = New ADODB.Recordset
rs.Open(SQL, DBconn, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic)
******************************************
******** Rewrote code and tried again **********
Dim cmd As New OleDbCommand
cmd.Connection = DBconn
cmd.CommandText = SQL
Dim records As Integer = cmd.ExecuteNonQuery
******************************************

'worktable' (hostname) is indexed w/ no dupes allowed. The ftpimport table
is not changed, it remains static.

This works fine the first run through. Worktable matchs ftpimport exactly
after its done. If I delete 5 rows in 'worktable' and then run it again, 0
records are returned. Shouldn't the 5 rows that were deleted be restored
after the second run ? Where is my error?
Thanks

 

[William Ryan eMVP]

Ralph:

I responded over at the VB.nET group but it looks like my post isn't showing
up. I'm not sure I understand the question, would you mind elaborating a
little more about the exact nature of the problem?

Thanks,

Bill

 

[Ralph]

Sure thing.

What I want to accomplish is...

Example: Worktable is empty. ftpimport (readonly) contains 10 rows with 1st
row [ftpimport].[hostname] ="FRED". The code is run and [worktable] now has
10 rows of data with 1 row [worktable].[hostname] ="FRED". If I delete row
"FRED" from [worktable] and run the code again, I should get the 'record
currently exists error' for rows 2-10 in worktable and "FRED" should be
re-inserted since he no longer exists in worktable and therefore won't
generate the dupe record error. When I run the query in Access, it generates
the error and the option to go ahead with the action query anyway. When I do
this, the data appears as I expected.

Ralph

Ralph:

I responded over at the VB.nET group but it looks like my post isn't showing
up. I'm not sure I understand the question, would you mind elaborating a
little more about the exact nature of the problem?

Thanks,

Bill

vb.net 2003 Coding..........

Dim SQL As String =
"INSERT INTO worktable ( hostname ) " & _
"SELECT [ftpimport].[hostname] " & _
"FROM ftpimport " & _
"GROUP BY [ftpimport].[hostname] " & _
"HAVING (((Count(ftpimport.hostname)) > " & Hits & "));"

******** Coding for 1st attempt****************
Dim rs As ADODB.Recordset = New ADODB.Recordset
rs.Open(SQL, DBconn, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic)
******************************************
******** Rewrote code and tried again **********
Dim cmd As New OleDbCommand
cmd.Connection = DBconn
cmd.CommandText = SQL
Dim records As Integer = cmd.ExecuteNonQuery
******************************************

'worktable' (hostname) is indexed w/ no dupes allowed. The ftpimport table
is not changed, it remains static.

This works fine the first run through. Worktable matchs ftpimport exactly
after its done. If I delete 5 rows in 'worktable' and then run it

again,

0
records are returned. Shouldn't the 5 rows that were deleted be restored
after the second run ? Where is my error?
Thanks

 

[Ralph]

After playing around with the code I found what I needed in another section.
By adding the 2 together it worked. So Far!

"INSERT INTO worktable ( hostname ) " & _
"SELECT ftpimport.hostname " & _
"FROM Ftpimport " & _
"LEFT JOIN worktable " & _
"ON Ftpimport.hostname = worktable.hostname " & _
"WHERE (((worktable.hostname) Is Null)) " & _
**** ADDED THE 2 LINES BELOW *******
"GROUP BY [ftpimport].[hostname] " & _
"HAVING (((Count(ftpimport.hostname)) > " & Hits & "));"

Thanks for the responses!!!