# Insert in MSSQL with MBS SQL plugin - Invalid cursor state **URL:** \ **Category:** Extras\ **Created:** [April 18, 2018, 12:50pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045 "2018-04-18T12:50:37Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Julien\_Courts](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/ecd19e/32.png) [@Julien\_Courts](https://forum.xojo.com/u/Julien_Courts)\ **Post date:** [April 18, 2018, 12:50pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/1 "2018-04-18T12:50:37Z")
Hi, i’m trying to insert into a mssql database using SQLCommand MBS and get this error: ```auto 24000 [FreeTDS][SQL Server]Invalid cursor state ``` The 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 ---
**Author:** ![Christian\_Schmitz](https://forum.xojo.com/user_avatar/forum.xojo.com/christian_schmitz/32/158_2.png) [@Christian\_Schmitz](https://forum.xojo.com/u/Christian_Schmitz)\ **Post date:** [April 18, 2018, 1:03pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/2 "2018-04-18T13:03:42Z")
is that due to AutoCache being true? ---
**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\ **Post date:** [April 18, 2018, 1:08pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/3 "2018-04-18T13:08:28Z")
And make sure that you do not use the same connection for more then 1 querry at a time. ---
**Author:** ![Julien\_Courts](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/ecd19e/32.png) [@Julien\_Courts](https://forum.xojo.com/u/Julien_Courts)\ **Post date:** [April 18, 2018, 1:17pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/4 "2018-04-18T13:17:11Z")
Autocache set to false solves the problem, thanks. Yes, I will check the use of differents connection if I have many queries ---
**Author:** ![Christian\_Schmitz](https://forum.xojo.com/user_avatar/forum.xojo.com/christian_schmitz/32/158_2.png) [@Christian\_Schmitz](https://forum.xojo.com/u/Christian_Schmitz)\ **Post date:** [April 18, 2018, 3:33pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/5 "2018-04-18T15:33:53Z")
> [@383330:@Sascha S](#): > > And make sure that you do not use the same connection for more then 1 querry at a time. We have three ways to connect to MS SQL and one has that problem only. ---
**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\ **Post date:** [April 18, 2018, 3:41pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/6 "2018-04-18T15:41:42Z")
> [@383364:@Christian Schmitz](#): > > We have three ways to connect to MS SQL and one has that problem only. Just wanted to help. ---
**Author:** ![Christian\_Schmitz](https://forum.xojo.com/user_avatar/forum.xojo.com/christian_schmitz/32/158_2.png) [@Christian\_Schmitz](https://forum.xojo.com/u/Christian_Schmitz)\ **Post date:** [April 18, 2018, 5:13pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/7 "2018-04-18T17:13:08Z")
Appreciated. I’ll change the AutoCache to not try to cache, if there is no result. ---
**Author:** ![Richard\_Duke](https://forum.xojo.com/user_avatar/forum.xojo.com/richard_duke/32/246_2.png) [@Richard\_Duke](https://forum.xojo.com/u/Richard_Duke)\ **Post date:** [July 30, 2020, 10:05pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/8 "2020-07-30T22:05:52Z")
where should i add in the code for AutoCache??? ---
**Author:** ![Christian\_Schmitz](https://forum.xojo.com/user_avatar/forum.xojo.com/christian_schmitz/32/158_2.png) [@Christian\_Schmitz](https://forum.xojo.com/u/Christian_Schmitz)\ **Post date:** [July 30, 2020, 10:17pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/9 "2020-07-30T22:17:16Z")
``` // turn on auto cache c.Option("AutoCache") = "true" ``` can be set for connection to cache all RecordSets automatically locally in memory. ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 7:10pm UTC](https://forum.xojo.com/t/insert-in-mssql-with-mbs-sql-plugin-invalid-cursor-state/43045/10 "2020-10-29T19:10:50Z")