Hi,
i’m trying to insert into a mssql database using SQLCommand MBS and get this error:
24000 [FreeTDS][SQL Server]Invalid cursor stateThe thing is that the record is successfully inserted in the database but I get an error.
Also, a simple select in the database works as intended.
I use this method to insert a record:
[code] dim SQL as string = "Insert into “+me.kTableName+”(CT_Num,CT_Intitule,CT_Type,CG_NumPrinc,CT_NumPayeur,N_Risque,N_CatTarif,N_CatCompta,N_Period,N_Expedition,N_Condition,CT_DateCreate,CT_Classement,bb_uuid) “+ _
" values (:ctnum,:ctintitule,:cttype,:cgnumprinc,:ctnumpayeur,:nrisque,:ncattarif,:ncatcompta,:nperiod,:nexpedition,:ncondition,:ctdatecreate,:ctclassement,:bbuuid)”
dim tDate as new Date
dim cmd as new SQLCommandMBS(me.dbSage.db,SQL)
cmd.Param(“ctnum”).setAsString(new SQLStringMBS(me.numero_compte_tier))
cmd.Param(“ctintitule”).setAsString(new SQLStringMBS(me.intitule))
cmd.Param(“cttype”).setAsLong(me.type)
cmd.Param(“cgnumprinc”).setAsString(new SQLStringMBS(me.account_id))
cmd.Param(“ctnumpayeur”).setAsString(new SQLStringMBS(me.num_payeur))
cmd.Param(“nrisque”).setAsLong(me.risque)
cmd.Param(“ncattarif”).setAsLong(me.cat_tarif)
cmd.Param(“ncatcompta”).setAsLong(me.cat_compta)
cmd.Param(“nperiod”).setAsLong(me.period)
cmd.Param(“nexpedition”).setAsLong(me.expedition)
cmd.Param(“ncondition”).setAsLong(me.condition)
cmd.Param(“ctdatecreate”).setAsDateTime(new SQLDateTimeMBS(tDate))
cmd.Param(“bbuuid”).setAsString(new SQLStringMBS(me.classement))
try
cmd.Execute()
me.db.Commit()
catch r as SQLErrorExceptionMBS
// SAConnection::Rollback()
// can also throw an exception
// (if a network error for example),
// we will be ready
try
// on error rollback changes
if me.db<>nil then
me.db.Rollback()
end if
catch x as SQLErrorExceptionMBS
// ignore
end try
// show error message
MsgBox r.message
end try[/code]
I also tried with me.db.InsertRecord(me.kTableName,d) using a dictionnary but I get the same error anyway.
My connect method to the database:
[code]// preload libs, this helps on Linux to find them
if not me.db.isConnected then
dim tItem as XOJO.IO.FolderItem
tItem = Xojo.IO.SpecialFolder.GetResource(“libtdsodbc.dylib”)
dim libtdsodbc as FolderItem = GetFolderItem(tItem.path, FolderItem.PathTypeShell)
dim s as new SoftDeclareMBS
call s.LoadLibrary(libtdsodbc.UnixpathMBS)
// connect
try
// we used Microsoft SQL Server 2008 and run app on Windows to test this
dim cs as string = “DRIVER={FREETDS};Server=”+me.kHost+";UId="+me.kUser+";PWD="+me.kPassword+";Database="+me.kDatabase+";TDS_VERSION=7.2"
me.db.SetFileOption me.db.kOptionLibraryODBC, libtdsodbc
me.db.Option(“UseAPI”) = “ODBC”
me.db.Option(“AutoCache”) = “true”
me.db.Connect(cs,"","",SQLConnectionMBS.kODBCClient)
me.db.Scrollable = false // disabling scrolling cursors is much faster for Microsoft SQL Server…
catch r as RuntimeException
MsgBox r.message
end try
end if
[/code]
Thanks,
Julien