Null record problem workaround for MSSQL database

[Ronan]

hi all,

I've traversed through the database forum but couldn't locate the exact solution to that problem.
What I've got is ODBCConnection , SQLTranasaction, SQLQuery DataSource and finally DBGRid, MSSQL database is fairly simple with 2 fields where 1st field is primary. For record saving I use

Code: [Select]

procedure TForm1.Button1Click(Sender: TObject);
begin
SQLQuery1.Edit;
SQLQuery1.Post;
end;

procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
SQLQuery1.ApplyUpdates;
SQLTransaction1.Commit;
end;
DeleteSQL, InsertSQL and UpdateSQL properties are empty of SQLQuery.

It shows the records but doesn't write changes to database when I'm trying to change it manually in DBGRid (tried the DBnavigator as well w/o success), so I think that I'm suffering from that null record problem mentioned in this forum(http://forum.lazarus.freepascal.org/index.php/topic,13011.msg67883.html#msg67883) by Lacak2.

Somewhere in the forum it's been mentioned that setting the proper SQL command in InsertSQL, DeleteSQL and UpdateSQL properties might work, But I really don't get what SQL command to set into those properties, although their name explicitly depict their usage?

Help will be appreciated!!

[Lacak2]

Do you have primary key defined on your table ? (it seems, that yes)
How do you have set these properties on SQLQuery1:
- UpdateMode
- ParseSQL
- UsePrimaryKeyAsKey
Can you insert new records ? (or problem exists only when updating existing record)
Do you insert null values in some columns ?

[Ronan]

Do you have primary key defined on your table ? (it seems, that yes)

YES, totally 2 fields in DB where 1 st is primary key,
I paid much attention when creating that table in MS managment studio, "Allow Nulls" to be unchecked in order to overcome that issue. But stil the same.

Updatemode = UpWhereKeyOnly
ParseSQL = True
UsePrimaryKeyAsKey = true

I simply use  SQLQuery.SQL := ' select * from Table1'

Can you insert new records ? (or problem exists only when updating existing record)

Actually the scenario is a bit different for me here, either inserting  or trying to change the existing records compiler throws exception with "Parapemeter 0 is of type WideString, which not supported yet"

Do you insert null values in some columns ?

NO,
But need to inform you about one weird thing, Even though I was deliberetly careful not use null values in records when I open the table in MS managment studio the last row is always filled with null values.

Thanks in advance,

[Lacak2]

Do you have primary key defined on your table ? (it seems, that yes)

YES, totally 2 fields in DB where 1 st is primary key,
I paid much attention when creating that table in MS managment studio, "Allow Nulls" to be unchecked in order to overcome that issue.

OK

But stil the same.

Updatemode = UpWhereKeyOnly
ParseSQL = True
UsePrimaryKeyAsKey = true

OK

I simply use  SQLQuery.SQL := ' select * from Table1'

OK

Can you insert new records ? (or problem exists only when updating existing record)

Actually the scenario is a bit different for me here, either inserting  or trying to change the existing records compiler throws exception with "Parapemeter 0 is of type WideString, which not supported yet"

Hops this is the problem !
What datatype have your columns (NCHAR, NVARCHAR) ?

Do you insert null values in some columns ?

NO,
But need to inform you about one weird thing, Even though I was deliberetly careful not use null values in records when I open the table in MS managment studio the last row is always filled with null values.

it is OK, it is a "new record" ;-)

[Ronan]

What datatype have your columns (NCHAR, NVARCHAR) ?

Combo_Name =  nchar(30)   (Primary Key)
Multiplier         =  float

That's all
 

[Ronan]

Any comment will be apprecitated.

[BigChimp]

Well, what Lacak2 mentioned: if you get an error that widestrings as parameters aren't supported, you might want to use regular ansistrings...

If you don't have that error, you could write a minimal test program that replicates the fault, create a bug in the bugtracker and attach the program.

[Ronan]

Well, what Lacak2 mentioned: if you get an error that widestrings as parameters aren't supported, you might want to use regular ansistrings...

Where actually I'm passing this 0 as parameter?
Weirdness is that, I couldn't even grasp the idea of from that exception is originating, as a result nowhere in program I'm assigning the zero value, but it complains from that.

IMHO, based on my previous posts regarding the DB components configuration, nothing seems to be wrong except that columns data type definition.

Code: [Select]

Combo_Name =  nchar(30)   (Primary Key)
Multiplier         =  float
could that really be something wrong with it ?

[Lacak2]

Nothing is wrong in your table definition.
There is just missing support for "nchar" datatype.

I reported it as a bug http://bugs.freepascal.org/view.php?id=19773

So until this bug will be fixed you must chage your table definition (use instead of "nchar" simply "char" ... I do not know if it is acceptable, but ATM there is no other way)

[Ronan]

Thank you all in advance,

Ok changing the data type of columns eliminates the problem for newly inserted records, But this time it throws the exception for existing records if I try to update them.

Couldn't execute statement. ODBC error details LastReturnCode :SQL_error; Record1 :SQLState :24000 Native Error Message Invalid Cursor State

To apply the updates I use

Code: [Select]

procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
begin
SQLQuery1.ApplyUpdates;
//SQLTransaction1.Commit;  //
end;

[Lacak2]

About your error:
"In SQL Server Enterprise Manager, you may receive the "Invalid cursor state" error message when Microsoft® SQL Server™ runs out of resources while attempting to save selected tables or a database diagram. This error is returned because of insufficient space in your database or transaction log to complete the save process. To correct this problem, check to see if whether the database or the transaction log is full. If so, increase the size of the database to accommodate the change."

Or if it is reproducible, then please submit test program which can show this error.