Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Chieh Zhong
  Where is Chieh Zhong?
 Montreal
 Canada
 Chieh Zhong
 Tags
Subject: RE: How to create Identity field in SQL Server
Thread ID: 104402 Message ID: 104454 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: ODBC, ADO and OLEDB
Date: Wednesday, August 16, 2006 5:12:11 PM         
   


> > > HI:
> > >
> > > I am trying to create a new empty table from existing table and couple
> > > of new fields for SQLEXEC() to execute, I am kind of stuck on how
> > > to create the identity column.
> > >
> > > Here's my select statement...
> > >
> > > lcSQL = "Select Customer.Name, Customer.OrdNumber, " + ;
> > > "CAST(0 as Numeric(5,2)) as Amount, " +
> > > "CAST(0, as IDENTITY" +"(1,1)" + ") AS UniqueNum " + ;
> > > " Into TempC From Customer where 0=1"
> > >
> > > The part for creating UniqueNum as identity column is failing,
> > > I think it's to do with my syntax.
> > >
> > > Any ideas?
> > >
> > > Thanks
> > > Chieh Zhong.

> >
> >
> > TEXT TO lcSQL NOSHOW
> >      SELECT Customer.Name,
> >             Customer.OrdNumber,
> >             CAST(0 as Numeric(5,2)) AS Amount,
> >             IDENTITY(int, 1,1)      AS UniqueNum
> >      INTO TempC
> >      FROM Customer
> >      WHERE 0=1
> > ENDTEXT
> > 

>
> HI Borislav:
>
> I tried the following... I am getting -1 back from SQLEXEC() function
> Any idea?
>
> Here's my test
> ==============
>
> TEXT TO lcSQL NOSHOW
> SELECT SPACE(5) as Order,
> CAST(0 as Numeric(5,2)) AS Amount,
> IDENTITY(int, 1,1) AS UniqueNum
> INTO TempC
> FROM Customer
> WHERE 0=1
> ENDTEXT
> ?SQLEXEC(1, lcSQL) && This returns -1 instead of 1
>
> Regards,
>
> C Zhong


? SQLEXEC(1, lcSQL) ??????????????????
How can you be sure that the connection handler is 1?????
Also it is always a good idea to test what is the error if SQLEXEC() returns -1

IF SQLEXEC(...., ...., ....) < 0
   AERROR(laError)
   MessageBox(laError[1,2])
ENDIF


Also if you put your code here in forum in < vfp > < /vfp > (w/o spaces) tag it will be more readable.

Borislav Borissov



ENTIRE THREAD

How to create Identity field in SQL Server Posted by Chieh Zhong @ 8/16/2006 7:59:58 AM
RE: How to create Identity field in SQL Server Posted by Borislav Borissov @ 8/16/2006 9:14:30 AM
RE: How to create Identity field in SQL Server Posted by Chieh Zhong @ 8/16/2006 4:46:40 PM
RE: How to create Identity field in SQL Server Posted by Borislav Borissov @ 8/16/2006 5:12:11 PM