• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Insert Into Syntax question

I am using Visual Basic 2008 to copy one table to another. Both tables contain an Identity column. I am trying to set the Identiy_Insert to ON but I seem to be having difficulty.

When I copy my code to a New Query Window in SSMS, add the GO statement, it executes fine.

But the VB code below does not execute. No errors are generated but no records are inserted.

 Please take a look at my code and let me know what I am missing.

thanks,
pat
 
ParmString = "truncate table " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory"
                DestinationCmd.CommandText = ParmString
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
                '
                ParmString = "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory ON "
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery

                '
                ParmString = "Insert Into " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" & _
                "(IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel) " & _
                "Select IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel " & _
                "From WeberScientific.dbo.WSWebCategory"
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
                '
                ParmString = "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory OFF "
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery

Open in new window

0
mpdillon
Asked:
mpdillon
1 Solution
 
käµfm³d 👽Commented:
Don't call ExecuteNonQuery() after the "SET IDENTITY_INSERT" lines. Make the whole thing one query. For example:
cmd.CommandText = "SET IDENTITY_INSERT OFF INSERT INTO myTable VALUES (1,2,3) SET IDENTITY_INSERT OFF"
cmd.ExecuteNonQuery()

Open in new window

0
 
Kalyanum Deepak KumarCommented:
Yes, make a consolidated query and run only one ExecuteNonquery(). Thanks.

Se below : Also a GO statement after every SQL statement woule givegood results:

ParmString = "truncate table " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" &"Set Identity_Insert " &
PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory ON  GO"  &"Insert Into " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" & _
                "(IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel) " & _
                "Select IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel  " & _
                "From WeberScientific.dbo.WSWebCategory GO" & "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory OFF GO"
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
                DestinationCmd.CommandText = ParmString
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
0
 
mpdillonAuthor Commented:
Thank you. The semi colon seperators were what I was missing.
For Others reading this post, I also FORGOT to set the CommandText Property:
DestinationCmd.CommandText = ParmString
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now