Yes he needs something like this and I said it could be done w/o creating something like this too.
"And YOU NEED date and datetime conversion for empty values if the backend doesn't support empty date/datetime."
Having 'you need' in capitals sounds you're confident I need it, are you really sure ?
What I'm trying to tell you is that I don't need it. AFAIK SQL server doesn't support empty datetime, am I missing something here. An empty date and null is not the same thing and you really should be using nulls for empty dates. 1900/1/1 is a perfectly valid date. If your backend doesn't support it and you didn't allow it to have nulls then implicitly conversion is done for you to 1900/1/1. ie:

Local lnHandle, lcDatabaseName
lcDatabaseName = "myTestData1"
lnHandle=SQLStringConnect('DRIVER=SQL Server;SERVER=cetin\cetin;Trusted_connection=Yes')
If SQLExec(lnHandle, "create database "+lcDatabaseName) < 0
  Do errHand
  Return
Endif
mySQLExec(lnHandle, "use "+lcDatabaseName)
mySQLExec(lnHandle, ;
  "create table myTable"+;
  " (myID int not null, myDate datetime NOT NULL)")

For ix=1 to 10
  m.myDate = {}
  mySQLExec(lnHandle, ;
   "insert into myTable (myID, myDate) values (?m.ix,?m.myDate)")
Endfor
mySQLExec(lnHandle, "select * from myTable","myCheck")
SQLDisconnect(lnHandle)
Browse

Function mySQLExec
Lparameters tnHandle, tcSQL, tcCursorName
tcCursorName = iif(empty(tcCursorName),'',tcCursorName)
If SQLExec(tnHandle,tcSQL,tcCursorName) < 0
  Do errHand with tcSQL
Endif

Function errHand
Lparameters tcSQL
lcError=tcSQL+chr(13)
Aerror(arrCheck)
For ix=1 to 7
  lcError = lcError+trans( arrCheck [ix])+ chr(13)
Endfor
Messagebox(lcError,0,'Error def.')

Cetin


>Hi
>There is code that translates field list to something like:
>INSERT INTO {table} (field1, field2,...) VALUES (?m.field1, ?m.field2, ...)
>then SQLPREPARE, SCAN, SCATTER MEMVAR and SQLEXEC() no matter how many fields the table has. I thought that you need something like this when I've read message.
>
>And YOU NEED date and datetime conversion for empty values if the backend doesn't support empty date/datetime. Or set null for those columns. I've converted empty dates to '1900-01-01' because I don't like null values for columns.
>
>Good luck
>
>
>
>>Dorin,
>>Thanks for the code but it's not me who needs it :)
>>PS: Using ? you don't need type conversions for date/datetime and memo. See my first reply.
>>Cetin
>>
>
>SNIP
>>>>>I'm trying to insert a bunch of records using
>>>>>
>>>>>for n=1 to reccount()
>>>>>scatter memvar
>>>>>result=SQLEXEC(gnhandle,'insert into mytable (field1,field2,field3 etc) values (m.field1,m.field2,m.field3 etc),'')
>>>>>endfor
>>>>>
>>>>>the "result" is coming back as minus 1 so obviously I've got something wrong.
>>>>>
>>>>>In any case, is there a more efficient way? Particularly for tables with large numbers of columns that looks horrendous!
>>>>>
>>>>>Thanks
>>>>>
>>>>>Harry
>>>>
>>>>Harry,
>>>>Prefixing each column insert value with ? would do but a hard and long way :)
>SNIP