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

Set identity insert

set identity insert file_name on

Is this option not available in VB6?


I get a syntax error message????
0
Jeff_Kingston
Asked:
Jeff_Kingston
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Jeff_KingstonAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "identity_insert" is sql, not vb code, so you need to put it to the sql text
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sSql = "SET IDENTITY_INSERT bm_onlinepass_subscription ON   Insert into bm_onlinepass_subscription" _ .....
0
 
Jeff_KingstonAuthor Commented:
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
 
Jeff_KingstonAuthor Commented:
Super
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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