312:9 Error when saving record

[paul mancuso]

We're using MS-SQL Server 2000 with Tornado. When saving a record the system generates the following error message:

System.Data.OleDb.OleDbException: Command text was not set for the command object. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orMultpleResults(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at tornado.rs.0(vn p, String p, String p, Int32 p)

I have never seen this error so, do you have any ideas as to what could be causing this error?

[Frank]

You say error "saving" a record, I guess it means modifying a record. You are using Tornado and MS-SQL and you are using "OLEDB" - what is the reason for that? Try the following code with C# DLL and update the pubs records. You can use either SQL or OLEBD as provider. (C# DLL use a generic Ado factory and provides full control of provider selection.


<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
Dim DSN as string = "i22;pubs;sa;sa"
Dim Pub As New tornado.z
With Pub
.dbQP = "U=1|Ni=b5,update|S=7|Q=Sales|BM=sales;0"
.dbDSN = DSN
.dbProvider = "SQL" 'Use OLEDB or SQL. For MS-SQL, Use SQLClient
.dbType = "SQL"
.dbTextHolder = "Title=Update Pubs"
.dbEditUpdateFlds = "0,1,2,3"
.ASPdbNet()
End With
End Sub
</script>


FK

[paul mancuso]

Problem Solved:

The .dbProvider had little to do with solving this error. I'm posting the solution so if others venture down this path they will have another possible solution to try. The following is the actual code I'm using, it appends records to 2 MS-SQL data tables.

Dim addSql as string = ""
addSql = "INSERT INTO STIPProject (mpoPin,mpoName,WorkType,IsActive,Safety,FunctionC lass,"
addSql &= "Region,LetDate,ProjManager,mpoAgency,CreatedBy,Cr eatedDate,ObligateDate,mpoNameCode,"
addSql &= "Sponsor,AirQuality,Remarks,LgDescription,SmDescri ption,TipAmendSw,Program)"
addSql &= "VALUES ('[[mpoPin]]','[[mpoName]]','[[WorkType]]','[[IsActive]]','[[Safety]]','[[FunctionClass]]',"
addSql &= "'[[Region]]',[[LetDate]],'[[ProjManager]]','[[mpoAgency]]','[[CreatedBy]]',"
addSql &= "[[CreatedDate]],[[ObligateDate]],'[[mpoNameCode]]','[[Sponsor]]','[[AirQuality]]',"
addSql &= "'[[Remarks]]','[[LgDescription]]','[[SmDescription]]','[[TipAmendSw]]','[[Program]]');"

addSql &= "INSERT INTO STIPLocation (LocationPin,CountyName,Corridor,ProjectBegin,"
addSql &= "ProjectEnd,MilePostBegin,MilePostEnd,Distance,Pol DistCode,"
addSql &= "Map_Number,GISData) "
addSql &= "VALUES ('[[mpoPin]]','[[CountyName]]','[[Corridor]]','[[ProjectBegin]]','[[ProjectEnd]]',"
addSql &= "'[[MilePostBegin]]','[[MilePostEnd]]',"
addSql &= "'[[Distance]]','[[PoldistCode]]','[[Map_Number]]','[[GISData]]')"

The solution was simple but finding it took hours. You must cap off the first Command Text with a semi-colon but, do not place a semi_colon at the end of the second statement (Command Text). Using a semi_colon will cause the record to be updated or appended correctly but will generate the Command Text not set to command Object error.

[Frank]

any chance do a NWIND (Access or SQL) equivalent. I know it takes tme but if this is important enough and if I can do somtheing to eliminate the syntax error and benefit all users, plese do one.


Frank

[paul mancuso]

Frank,

Happy New Year to you and your staff at ASPdb. Here's to a new year of success's!

I'll make the attempt to develop a NWind sample showing this issue. Give me a few days. Paul

[Frank]

Got your sample. The ";" is a default to delimit the grid edit SQLs. A casual ";" at the end of SQL results a blank SQL (last one) and hence blow up the query. I added a detect blank SQL / exit to fix that. Will send you the patch DLL.

Alos to fix it on your side, you can just change the delimiter to something else rather the ";" like a"~" ->

Dim addSql As String = ""
addSql &= "(|~)INSERT INTO Employees (Lastname,Firstname) "
addSql &= "VALUES ('[[lastname]]','[[firstname]]');"


That'll work too.

Frank