Ado with datagrid

[melic]

Hello,
I'm using Labwindows/cvi 6.0 and I've a problem when I try to atach datagrid to Ado data control (both activeX) in UI panel editor. Is not possible to attach DataSource field from Datagrid to Ado, I can't access DataSource field. Why? Is not possible to use it at design time?
I've tried to use programmatically but I have no idea how to use the function SetByReDataSource (from DataGrid), the third parameter is the control Ado. How can I pass it from the Ado ActiveX handle?

I can't buy your NI packet for Databases, there's no more money for the project, sorry 🙂

Thaks.

[Sir]

Hi.
I have the same problem.
It looks like NI blocked this fields to enforce LabWindows users to buy DB conn toolset.
Am I right?

[bilalD]

Hello all

The databinding design time capabilities that these controls have have been specially implemented in VB. VB 6.0 does alot of tricks in the background to make the interaction look easier. We did not implement any special design time capabilities with our ActiveX container, which is why you are unable to do the same VB stuff in CVI. VB 6.0 was specifically desgined to make COM look likes childs play and it does this pretty well. CVI cannot customize the ActiveX container to try and support every VB 6.0 feature. You will actaully notice similar because if you drop the datagrid on a MS VC++ form.

The DB toolset makes interacting with ADO alot easier. You won't have to worry about various ActiveX intricacies that you need to keep an eye out for normally. But you can still use ADO if you can't get the toolkit. It's just alot harder and you will need to refer to the documentation provided to you by Microsoft for this toolkit. The ActiveX controller wizard just generates wrapper C code around the ActiveX control for you to be able to access its functions and properties.

Now with that said, you can databinding for this control, only catch is you will need to do this at runtime. I tried this with the ADO control and the datagrid and got it to work fine. I used the ActiveX controller wizard to generate CVI code for the datagrid , the Microsoft Data Sources Interface and the ADO data control. You need the data soureces interface information to get MSDATASRC_IID_DataSource

And then here is what i did

.
.
.
GetObjHandleFromActiveXCtrl (panelHandle, PANEL_DATAGRID, &gridObjHandle);
GetObjHandleFromActiveXCtrl (panelHandle, PANEL_ADODC, &ADOObjHandle);

status = MSAdodcLib_IAdodcSetConnectionString (ADOObjHandle, NULL, "DSN=bilalsql");

status = MSAdodcLib_IAdodcSetRecordSource (ADOObjHandle, NULL, "SELECT * FROM [Table1]");
status = CA_GetInterfaceFromObjHandle (ADOObjHandle, &MSDATASRC_IID_DataSource, 1, &sourceIUnknown, NULL);

status = MSDataGridLib_IDataGridSetByRefDataSource (gridObjHandle, NULL, sourceIUnknown);
.
.
.

Don't forget to clean up objects after you're done with them

I used sql server, but it should not matter

Hope this answers the question.

[Sir]

I try to use your code example.
1. The program return -> non-fatal runtime error - function CA_GetInterfaceFromObjHandle return val ==-2147467262 - unknown error.
2. Where can I get more or less detailed description of Microsoft Data Source Interface ? (with code example in LabWindows or C++)

[bilalD]

You can find out more about ADO on the MSDN. You won't find CVI examples here, but you will find information about the object model and other help. You will probably be able to find C++ examples for ADO on the MSDN as well.

I used ADO 2.7. I have attached my CVI project.

Hope this helps

[nweb]

Hi Bilal,

I found the code in your example very usefull and was able to connect to my database and fill the DataGrid. However, I have problems to extract an item (cell value) from the DataGrid. When coding in MS Visual C++ it can be done as:
CDataGrid dataGrid;
VARIANT item, vResult;
item.vt = VT_I2;
item.intVal = 1;
vResult = dataGrid.GetColumns().GetItem(item).GetValue();

The wrapper functions in CVI is less straightforward to me. For example, what do I do with the returned MSDataGridLibObj_Columns handle from MSDataGridLib_IDataGridGetColumns function. The function MSDataGridLib_ColumnsGetItem does not take that handle as argument. What is it that I'm missing?

Thanks

[bilalD]

What you will need to do is get a handle to the columns collection using MSDataGridLib_IDataGridGetColumns() and pass that handle to MSDataGridLib_ColumnsGetItem().This will allow you to get to the individual column in the columns collection and return a column handle. Then pass that handle to the MSDataGridLib_ColumnGetValue() to get the value.

[nweb]

 Hello,

 

Actually, this question is (maybe) not related to datagrid but still to ADO. Retrieving data works fine (using sql SELECT) but when using the sql statement UPDATE to change a value in the database I get an error. The code is like the following:

GetObjHandleFromActiveXCtrl (dbPanel, DB_PANEL_ADODC,&adoHandle);

// sqlStr = “UPDATE table_name SET column_name = new_val WHERE column_name = some_val”;

status = MSAdodcLib_IAdodcSetRecordSource (adoHandle, NULL, sqlStr);

status = MSAdodcLib_IAdodcRefresh (adoHandle, &error);

This returns an error “Operation is not allowed when the object is closed”, (status=0x800A0E78). But when reading back from the database the value is changed so the UPDATE statement is correct. Obviously I’m using the wrong methods since I got the same error in Visual C++ when using the same ADO component. I've been looking for any other methods in the fp-files that could be used instead but haven't found anything. Any suggestions?

   

[JonathanN]

Howdy nweb,

I did some research on Microsoft's website and Google and found several references to the error message you were receiving. That error indicates that the Connection object or Recordset object has been closed. I don't know if you ever created a Connection object or not and if you didn't perhaps that's why you are getting that error.

The first recommendation I would try is inserting the Set Command Type function before the Set Record Source call and test out some of the constants for how the command argument is executed (Documentation can be found here).  Secondly, if that has no effect, I would not use the Set Record Set with the UPDATE SQL statement but rather the Connection.Execute method. Set the options parameter on this method to 0x80. This setting indicates that the command text is a command or stored procedure that does not return rows. If any rows are retrieved, they are discarded and not returned.  Make sure to open a connection first, then execute, then close. See the remarks section of the MSDN article here.

Hope this helps!