We help IT Professionals succeed at work.

Insert Into Syntax question

Medium Priority
242 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

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

Kalyanum Deepak KumarSenior Technical Lead

Commented:
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

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.