Solved

Set identity insert

Posted on 2014-04-11
6
269 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

705 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