MS SQL Library

[linum]

How can I use "ReadRow" to populate a table on a form? Can somebody post an example of this?

 

[georgea]

Although this dll seems usefull, it's little hard for a newbie to work with it, without documentation.

My main issue is that i always get a message .."There is already an open DataReader associated with this command which must be closed first." from the LastError unless i open and close connection everytime i want to execute a simply query. I think this is not the right way to retrieve data.

I also don't know how to use any other function instead of Read(fieldname).

It's a pity for such a usefull dll to exist without any help or examples.
If anyone can give any clue i would be thankful.

 

[Cableguy]

Try to examine the SQL Module, wich is a dedicated module to make some usual actions easier...
Also, I am sure, the usage of the DLL, is in acordance of the SQL syntax and procedures...

 

[georgea]

First of all thank you for your answer.

I know how to handle SQL syntax but i have no experience in .Net nor Basic4ppc. With this library for example, i can't do a Commit although i achieve Update.

From my experience using other application development tools, you create a transaction, you provide the connection strings, you connect and then you execute every command sql supports. When you finish, you disconnect and you destroy the transaction.

These things i can't manage with this library and unfortunately i can't get any help from other modules/libraries because i can't find similar methods.

For my company and me is very importand to connect directly to MSSQL and i can predict that things will be very tough if i don't find any way to manipulate easily this library.

Thanks, anyway.

 

[Cableguy]

I have only worked with mySql, so I am not the best person to help you...
Still, I have some spare time, but also I don't have MSSQL installed....
I would be willing to try and help you out, but I would also need some guide-lines from you,

 

[georgea]

Thank you for your interest.

If anytime you would give a try with MSSQL you could install MSDE Version or MSSQL Express Edition 2005 and later . Both are free avalaible for download by Microsoft.

To give you a clue i paste a part of my code:

With this code i connect to the database using global string variables that i have already set from an initialization file.

mssql.New1
mssql.Open("Persist Security Info=false;Integrated Security=false;Server=" & server & ", " & port &"; initial catalog=" & database & "; user id="& login & "; password=" & pass)

With this code i connect to the database using global string variables that i have already set from an initialization file.

After Open() returns me True i do the following, like in publisher's example:

If connected = True Then
  mssql.ExecuteQuery("Select nickname From Employees Where status = 1")
  Do While mssql.Data
    mssql.Read("nickname")
  Loop
End if

Then if i want to execute another query like an other Select i have to execute:

mssql.Close
mssql.Open(..like first part..)
mssql.ExecuteQuery("Select.....")

Maybe i am wrong but i can't find any other solution to work. As you can understand, i can't always connect/disconnect when i want to do a simple Select.

Finaly, i can't execute a Commit too:

Dim execres as Boolean
execres = mssql.ExecuteNonQuery("Update Employees Set status = 0")
If execres = True Then
  execres = mssql.Commit
  if execres = False Then
    Msgbox("Commit Failed.")
  End If
End If

I also can't work with ReadRow, FieldInfo, GetResultSet, BeginTransaction and TransactionDispose.

That's all...

 

[Cableguy]

Have you tried to dispose the query?
from #1 Post:

TransactionDispose - returns True or False

From what I understand, the query is not finalized, as it is in MySQL, and therefore gets locked...

I have not yet tried anything, and I'm running out of time for today

 

[georgea]

It's a good idea, but i can't reach until the TransactionDispose because when i execute BeginTransaction and get True, then i can't execute ExecuteQuery successfuly because it returns False as a result.

Anyway, thanks for your help.

 

[georgea]

Today i am trying to use transactions to see if i am wrong.

So i connect to the database:

Dim connected As Boolean
connected = mssql.Open("Persist Security Info=false;Integrated Security=false;Server=" & server & ", " & port &"; initial catalog=" & database & "; user id="& login & "; password=" & pass)

I Get connected = True.

Then i try to begin a transaction:

Dim result As Boolean
result = mssql.BeginTransaction

I also get result = True.

So, i am connected and i have create a transaction and i execute a query:

Dim execresult As Boolean
execresult = mssql.ExecuteQuery("Select nickname from Employees Where status = 1")

I always get execresult = False.

After that i check mssql.LastError and i get:

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

What i do wrong?

I tryied to get a clue at MSDN at System.Data.SqlClient Namespace SqlConnection.BeginTransaction Method (System.Data.SqlClient) but i didn't.

:sign0085:

 

[agraham]

I've poked around inaside the library with Reflector and I'm afraid that it doesn't appear to allow transactions to work properly. In the example code at your link it shows

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

However the library is not assigning a transaction object to the command object this hence your error is correct. It looks like the library was not fully tested

EDIT :- I've just noticed in the first post that it states "In particular the BeginTransaction and related functions are not fully tested."

 

[georgea]

Yes i know that this library is not official and has bugs, but it seems that citywest has not loged in for a year. So probably this dll is restricted to the use of "Open - ExecuteQuery - Close" everytime someone wants to select/update/etc, which makes me consider using Basic4PPC for commercial use.

Anyway, thank you for your time and i hope Basic4PPC will make an official update with direct MSSQL support in the future because it seems a nice and friendly tool for someone to start developing to PPCs.

 

[agraham]

Try this library. I've disassembled the original and recompiled it with some fixes. I've removed BuildResultSet and GetResultSet as I didn't like what they were doing and you shouldn't need them anyway.

I am not a database programmer but this library is a very thin wrapper over System.Data.SqlClient so if you know what you want I can probably add it.

 

[Cableguy]

Andrew to the rescue!!!!:sign0060:

Just one note.
If this is to evolve to be a full grown dll, You should consider dropping the original name, and append your own, as you usually do.
Also, start a new thread for this....
SQLEx has a nice ring to it, do'nt you think?
And this way, it's another adition to the dll list.

 

[georgea]

Well agraham you really made me smile with your reply.

This time, i connect ok, i begin transcation ok, i execute query ok (this was False previously), i read the data ok.

After that, i execute another query without closing connection or transaction and i also get True but Data seem to be False and empty. I don't know if after first query execution should somehow 'empty' the data.

I don't really know how System.Data.SqlClient works but something like Rowcount for the rows returned or Readrow(row) to read the row you want, would be usefull.

However i think that if someone could execute many sql commands (select/update/etc..) with one connection and could execute commit and rollback then the job is almost done.

P.s. BuildResultSet i think that is useful to return the rows retrieved.

Thank you agraham, you are very helpful, not only for me!

 

[agraham]

I noticed that I omitted to add the transaction to ExecuteNonQuery so I now have. Also I've removed the Rows property as it was meaningless without BuildResultSet.

Although I can't see why that DataReader error might occur I have checked and closed it anyway before making a new one in ExecuteQuery.

 

[georgea]

Now when i execute a select i get True but While mssql.Data = true the Read command returns me "#ERROR#" infinitely. I hope i didn't something wrong. I haven't changed the code that i do the testing.

 

[agraham]

There was a typo in Data that might have caused the #ERROR# problem.

I've added a ReadRemainingRows method that returns a two dimensional string array of all the remaining rows in the Reader.

 

[georgea]

I hope i don't get annoying for sending my feedback.

Now both queries return data after the BeginTransaction!

After that i tryied to do a TransactionDispose but i get False.
I also tryied after second Select to do an Update but it failed returning that "There is already an open DataReader associated with this command which must be closed first."
I think that when a transaction begins, it can contain and update/insert/delete functions too.

Maybe if the TransactionDispose would return True then Update would succeed too.

Good job!

 

[agraham]

What do you mean by an "Update". What is the code line that gives the error? Try this, it might have fixed it.

 

[georgea]

Well done my friend!

Now TransactionBegin and TransactionDispose return True. Also ExecuteQuery and ExecuteNonQuery return True and Commit works great!
I think this dll must be pinned because now works fine, regarding the little testing i did.

Good job agraham, you solved a case that seemed to have been closed.

Thank you very much.