Check inline:

>Hi everyone,
>
>I need to transfer my system from using .dbf file replace to MS SQL,
>
>And i have start to learn coding with MS SQL as below:
>
>CON = SQLSTRINGCONNECT("MYSERVER")
>
>IF CON > 0
> ? "OK"
>ELSE
> ? "ERROR"
>ENDIF
>
>= SQLEXEC( CON, "CREATE DATABASE MYDATABASE;")
>= SQLEXEC( CON, "USE MYDATABASE;")
>= SQLEXEC( CON, "CREATE TABLE CUSTOMER (CID INT(10), CNAME VARCHAR(20);")
>= SQLEXEC( CON, "INSERT INTO CUSTOMER (CID,CNAME) VALUES 123,'ABC';")
>= SQLEXEC( CON, "SELECT * FROM CUSTOMER","TEMP")
>
>The above were what i learn today.
>
>And, now i back to my old system, can you tell me how to change the code to SQL
>
>
>==========
>QUESTION 1
>*****
>USE C:\CUSTOMER ALIAS CUS
>=CURSORSETPROP("BUFFERING",5)
>NEWNO = "IBM"
>
>DO !FLOCK()
>ENDDO
>
>APPE BLANK
>REPL CUS_NO WITH NEWNO
>UNLOCK
>
>SCAT MEMV MEMO
>M.CUS_NAME = "IBM COMPANY"
>GATH MEMV MEMO
>
>DO WHILE !TABLEUPDATE(.T.,.T.)
>ENDDO
>
>* OR
>* =TABLEREVERT(.T.,"CUS")
>USE
>

All these for a simple insert??? Assuming in your SQL server table you defined default values for fields and/or allow nulls:

*Single
newno = "IBM"
cus_name = "IBM COMPANY"
SQLExec(m.handle,"insert into customer (newno,cus_name) values (?m.newno,?m.cus_name)")

*Multiple-not the most effective way
SQLPrepare(m.handle,"insert into customer (newno,cus_name) values (?m.newno,?m.cus_name)")
newno = "IBM"
cus_name = "IBM COMPANY"
SQLExec(m.handle)
newno = "AMD"
cus_name = "Advanced Micro Devices"
SQLExec(m.handle)

*Or
SQLExec(m.handle,'select * from customer where 1=2','myCustomer')
*make cursor updatable - see "Updating Remote Data with SQL Pass-Through" in help
CursorSetProp('Buffering',5,'myCustomer')
* code for inserting new records (but not append blank,scatter ... gather thing)
*ie:
set null off
insert into myCustomer (newno,cus_name) values ("AMD","Advanced Micro Devices")
*...
tableupdate(2,.t.,'myCustomer')

>
>==========
>QUESTION 2
>In my old system i can use INDEX ON SUBS(CUS_NO,1,3) + STR(CUS_YEAR) TAG CUSSEQ
>do i need to add one more field inside the SQL-file as below:
>***
>CUS_NO = "IBM "
>CUS_YEAR = 2004
>CUS_NY = "IBM 2004" < = new field for sort
>***
>Then call it with SELECT * FROM CUSTOMER WHERE CUS_NY = 'IBM 2004';
>Is't correct ?
>

No in SQL server you can create composite indexes w/o concatenating them. But you don't need composite indexes as well (and also you didn't need it in VFP). Create them as separate indexes.

SELECT * FROM CUSTOMER WHERE CUS_NO == "IBM" and Cus_year=2004 order by cus_no, cus_year

does the job both in VFP and MSSQL.

>
>==========
>QUESTION 3
>How can i get the number of record from SQLEXEC command, ( i don't want to give all into a TEMP file then use RECC() )

Give all into a temp file! What do you mean? Hope not something like "select *", "select cnt(*)". It should take considerable time on a huge table!!! That info already is kept in a single table namely - "sysindexes"

text to m.lcSQL noshow && Get reccounts for all tables in db
select so.name, so.id, si.rowcnt 
  from sysobjects so 
  inner join sysindexes si on so.id=si.id
  where si.indid < 2
endtext
SQLExec(m.handle,m.lcSQL,'myResult')

Or for a single table - ie:customers

text to m.lcSQL noshow && Get reccounts for customers table
select rowcnt 
  from sysindexes 
  where Id = Object_Id('customers') and indid < 2 
endtext
SQLExec(m.handle,m.lcSQL,'myResult')

>
>
>==========
>QUESTION 4
>My database as below :
>
>PROD_ID , PROD_NO , PROD_NAME
>---------------------
>11 , I008 , ICE-CREAM LEMON
>43 , I009 , ICE-CREAM APPLE
>22 , H013 , HOT LEMON TEA
>38 , H014 , HOT TEA
>
>Now i want to add a new product with begining number "I", how to do that ?
>** how to know the next number is I010 **

Get max beginning with "I" and add 1, concat. Or keep them as separate from the start.

Sounds like if you use remote views you'd do much better. Start with them and as you get the basics move other IMHO.
Cetin