Solved

Set identity insert

Posted on 2014-04-11
6
261 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
  • 3
  • 3
6 Comments
 
LVL 142

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 142

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS expression Issue finding a string 10 65
wordappend challenge 8 143
Problem to start Neon 20 83
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 45
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 clear a vector as well as how to detect empty vectors in C++.

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now