Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Set identity insert

Posted on 2014-04-11
6
Medium Priority
?
271 Views
Last Modified: 2014-04-11
set identity insert file_name on

Is this option not available in VB6?


I get a syntax error message????
0
Comment
Question by:Jeff_Kingston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39994036
that syntax is for sql server (from what I know):
http://technet.microsoft.com/en-us/library/aa259221%28v=sql.80%29.aspx

so, if you write "vb6", you must either refer to sql code you run over for example adodb connection, or you need to clarify what you are trying to achieve...
0
 

Author Comment

by:Jeff_Kingston
ID: 39994057
I have a records that were incorrectly moved to an archive table.  I want to put them back to the live table, but there is an identity field in the rows(only in the live table), and I want to add them back with the original ID field
Here is the insert statement from the VB code:

sSql = "Insert into bm_onlinepass_subscription" _
  & "(id,clientID,startDate,endDate,period,pricePaid,isNewLawyer,isSponsor,isBaseSubscription,optedOut,sizeOfFirmSource," _
  & "baseAmount,transactionalDiscountAmount,priceToPayYear,initialpurchdate,monthlyamount,lastpayment,lastpaydate,isPromotion,isAutoEnrolled,otherDiscountAmount,isSoldThroughAdmin," _
  & "periodNextYear,subscriptionStatus,renewalFlag)" _
  & "Values (" & tid & "," & "'" & tclientID & "'" & "," & "'" & tstartDate & "'" & "," & "'" & tendDate & "'" & "," & tperiod & "," & tpricePaid & "," & tisNewLawyer & "," & tisSponsor & "," _
  & tisBaseSubscription & "," & toptedOut & "," & tsizeOfFirmSource & "," & tbaseAmount & "," & ttransactionalDiscountAmount & "," & tpriceToPayYear & "," & "'" & tinitialpurchdate & "'" & "," & tmonthlyamount & "," _
  & tlastpayment & "," & "'" & tlastpaydate & "'" & "," & tisPromotion & "," & tisAutoEnrolled & "," & totherDiscountAmount & "," & tisSoldThroughAdmin & "," & tperiodNextYear & "," & "'" & tsubscriptionStatus & "'" & "," _
  & "'" & trenewalFlag & "'" & ")"

If I type set Identity_Insert Table_Name on

The editor highlights Table_Name and I get "compile error Expected = "
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39994097
the "identity_insert" is sql, not vb code, so you need to put it to the sql text
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39994099
sSql = "SET IDENTITY_INSERT bm_onlinepass_subscription ON   Insert into bm_onlinepass_subscription" _ .....
0
 

Author Comment

by:Jeff_Kingston
ID: 39994133
Great,....... that prove brain cramps are real....

Now VB says:

Table does not have the identity property.

Attached is the table definition.

Somehow I don't think this should be this difficult..
identityproperty.jpg
0
 

Author Closing Comment

by:Jeff_Kingston
ID: 39994622
Super
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question