Solved

Set identity insert

Posted on 2014-04-11
6
262 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
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.
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 goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

813 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

14 Experts available now in Live!

Get 1:1 Help Now