Re: ado - Database Discussion Boards

In my c++ code ..i use ADO commands..and to get the permissions..for userss for each and every databse..
i use the following commands..

sprintf(str1,"SELECT PERMISSIONS(OBJECT_ID('TABLE1'))");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);

the abopve function returns an integer value..
how do i read the value??

ranjani

Use Recordset's Field property and use integer based index (0 in this case) to retrieve the value.

do u eman to say..use
pRecordset->Fields()-> ..
and what is integer basd index as u have mentioned???
plz reply as soon as poss
thank u in advance

ranjani

as far as I remember with ADO you would say:

_variant_t vtMyVal;<br />
<br />
vtMyVal = pRecordset->Fields->Item[long(0)]->Value

and this would retrieve the value.
Item[] can take either integer based index (eg. Item[long(0)]->Value) or string based (eg. Item["SomeColumn"]->Value)

I hope this helps.

Best regards,

Husein

The full code is enclosed herewith!!
When I include the aboce said and run it..the control goes to the catch function..as there's some problem in the above mentioned stt!

void main(void)
{
ADODB::_ConnectionPtr m_pConnection = NULL;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr pRecordset = NULL;
VARIANT *vRecordsAffected = NULL;
char ConStr[500];
char str1[50];
_bstr_t bstrProc =( L"sp_StartByteImport" );
BOOL m_bIsConnectionOpen;

// Create an instance of _Connection
HRESULT hr ;
hr = m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
cout << "Connection object created." ;
_variant_t vRecsAffected(0L);

try
{
if (SUCCEEDED(hr))
{
//Open a connection where database is access database : "c:\\emailparsing\\email_details.mdb"
ConStr[0] = '\0';
strcat(ConStr, "Provider=sqloledb;Server=ie10DT2KCO1473;Initial Catalog=Shree;");
strcat(ConStr,"User Id=sa;Password= ;");
/* Provider=Microsoft.Jet.OLEDB.3.51; Data Source="**/
// strcat(ConStr, "C:\\MSSQL7\\Data\\Shree_Data" );

m_pConnection->Open(ConStr, "", "", 0);

//If database opened successfully then set IsConnectionOpen to TRUE

if (SUCCEEDED(hr))
{
m_bIsConnectionOpen = TRUE;
}

}
sprintf(str1,"USE Shree SELECT PERMISSIONS ( OBJECT_ID('TABLE1') )");
pRecordset = m_pConnection->Execute(str1, vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->Fields->Item[long(0)]->Value;
printf("\n%s\n\n",vtMyVal);
ConStr[0] = '\0';
strcat(ConStr, "SELECT * FROM ");
strcat(ConStr,"TABLE1");
pRecordset = m_pConnection->Execute(ConStr, vRecordsAffected, 1);

if (!pRecordset->GetEOFile())
{
int i = 0;
while (!pRecordset->GetEOFile())
{
printf("row %d is '%s %s'\n",i,
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("id")->GetValue()),
(char*) ((_bstr_t) pRecordset->GetFields()->GetItem("product_name")->GetValue()));
i++;
pRecordset->MoveNext();
}
}
pRecordset->Close();
}
catch (_com_error &e)
{
printf("Description = '%s'\n", (char*) e.Description());
printf("%s\n%s\n%s\n",(char *) e.ErrorMessage(), (char *)e.Source(), (char *)e.Description());
}
::CoUninitialize();
}

ranjani

The first call in the main(void) should be ::CoInitialize(NULL)
This should do the trick.

yes
i got the answer
by ur answer...
now..can u help me out with sql databse stuffs??
coz''
the return value..that is the integer value i get indicates some permissions assigned to teh user..
how do i check it?

ranjani

how do i create a stored procedure in C++?
my program should for any of the sql servers..
so..manually creating a stored procedure won't work..
so..
i have to create it programatically...how do i go abt doing it?

ranjani

Add a new _bstr_t variable and set it's value to "CREATE PROCEDURE ... " and then pass this variable as a parameter to connection's execute method. That could be one way of doing things.

Best regards,

Husein

AND AFTER DOING THAT..
GET THE RETURN VALUE..AS SAID ABOVE FOR PERMISSIOSN STT IS IT??

ranjani

iam able to create the stored procedure..
but how do i go about...
rading the return value..
that is my code is now like..

_bstr_t bstrPr = "DROP PROCEDURE bali" ;
pRecordset = m_pConnection->Execute(bstrPr, vRecordsAffected, 1);

_bstr_t bstrp = "CREATE PROCEDURE bali @ioparm int OUTPUT AS \nIF PERMISSIONS()&2=0x2 RETURN 1 ELSE RETURN 0";
pRecordset = m_pConnection->Execute(bstrp, vRecordsAffected, 1);
_bstr_t exect = "EXECUTE bali 2";
pRecordset = m_pConnection->Execute(exect,vRecordsAffected, 1);
_variant_t vtMyVal;
vtMyVal = pRecordset->GetFields()->Item[long(0)]->Value;
printf("\n%d\n",(char *)(_bstr_t)vtMyVal);
Iam not able tor ead the return value from this but..
what mite be the reason for this?
what else shud i add to get it done??
also..
initially..
how do i cehk if a procedure name with the name specified already exists??

ranjani

ranjjj, how well do you know ADO programming with VC++? In order to get the RETURN_VALUE from the stored procedure, you need to execute it from the recordset and not from the connection. In you recordset object you need to set certain properties, like CommandType and CommandText. If you programmed ADO with VB, it is the same thing with VC++.

regards,

Husein

Hi,

I need to commit the changes to the database of a dataset containing many (MANY) datatables. A few of them have an auto-increment column, but I need to insert a specific value in the database (which is MS-SQL, by the way).

My code is fine and I send a "SET IDENTITY_INSERT ON" to the database when needed.

Since all the operations I need to perform are very basic and that I have many different tables, I am using the CommandBuilder object. The problem is that the Insert command does NOT include the auto-increment field.

Is there a way to make the extra parameter is added to my InsertCommand? MSDN and the books I have were not of any help.

Thank you very much!

Carl

PS: Here's the code...

<br />
    Public Sub CommitTable(ByVal table As DataTable, ByVal transaction As SqlTransaction, Optional ByVal allowIdentityInsert As Boolean = False)<br />
<br />
        Dim da As New SqlDataAdapter("SELECT * FROM " + table.TableName, transaction.Connection)<br />
        da.SelectCommand.Transaction = transaction<br />
<br />
        Dim cmdBuilder As New SqlCommandBuilder(da)<br />
        Dim cmd As SqlCommand<br />
<br />
        cmdBuilder.QuotePrefix = "["<br />
        cmdBuilder.QuoteSuffix = "]"<br />
<br />
        da.InsertCommand = cmdBuilder.GetInsertCommand<br />
        da.UpdateCommand = cmdBuilder.GetUpdateCommand<br />
        da.DeleteCommand = cmdBuilder.GetDeleteCommand<br />
<br />
        cmd = New SqlCommand("DELETE FROM " + table.TableName, transaction.Connection, transaction)<br />
        cmd.ExecuteNonQuery()<br />
<br />
        If allowIdentityInsert Then<br />
            cmd.CommandText = "SET IDENTITY_INSERT " + table.TableName + " ON"<br />
            cmd.ExecuteNonQuery()<br />
        End If<br />
<br />
        da.Update(table)<br />
        table.AcceptChanges()<br />
<br />
        If allowIdentityInsert Then<br />
            cmd.CommandText = "SET IDENTITY_INSERT  " + table.TableName + " OFF"<br />
            cmd.ExecuteNonQuery()<br />
        End If<br />
<br />
<br />
    End Sub <br />

Of course the Identity column is not included in your SQL statement. Identity column is an auto-increment column. This is handled by the SQL Server which keeps track of the next number to be added to the identity column. But if you need the value of the Identity column after you added the row, use SELECT @@IDENTITY. Note that you should do this within the Stored Procedure or a batch query. If you send the data from a select statement and then send another statement, in this case SELECT @@IDENTITY, you might not get the right value as another user could have added a new row and the @@IDENTITY would return this new value or you might just get a NULL value.

Best regards,

Husein

How can I set/change the text in a row header in a data grid.

ListUserPermissions

in the above mentioned sqldmo function..What value shud passed as the arguments for listing the user access permissions of a sql database???

ranjani

I believe that this post has the answer to your question. Happy programming! Big Grin | :-D

Hi,

I have a DataTable that has a primary key. If I want to find a row by primary key I can use DataTable.Rows.Find (new object [] {id}), or I can perform a row filter like Mytable.DefaultView.RowFilter = "ID = " + id.

It looks like RowFilter is MUCH slower than Find. Any idea why? Does RowFilter perform a linear search and Find does some binary search?

Thanks

Best regards,
Alexandru Savescu

P.S. Interested in art? Visit this!

I have discovered the same thing. In a test application that I created the results were REALLY unbelievable.
The thing that I did was:
1. Connect to SQL Server
2. Use Northwind as Initial Catalog
3. Filled the dataset with Customers table records

After using DataTable.Rows.Find("MORGK"), the time taken was 40 milliseconds
After using DataTable.DefaultView.Find("MORGK"), the time taken was 0(?) WTF | :WTF:

milliseconds but the record index was correctly found and the return value was 51.
After using DataView.RowFilter the time taken was 70 milliseconds.

And then, going through the documentation, DataTable.Rows.Find() uses PrimaryKey to locate the record. There was nothing like this for RowFilter so my assumption is that DataView uses an approach like Table Scan which means that PrimaryKey is completely ignored. Also, DataView constantly monitors for new rows and if it matches the criteria it is automatically available in the DataView.

I guess this could be a sort of explanation.

Husein

I have an xml file having 600'000 records. I need to fetch only 100 records.

I used ado.net dataset.ReadXml(FilePath) method to read the xml file. But it read the whole file and used lot of time.

Does anybody know how to read the xml file after applying filter as we do in SQL statements.

Email: imran_shahid@msn.com

Dear All!
I have fill the ADO.NET dataset with two tables and joined them as a master/detail.

I want to filter this dataset by applying filter on master table records.

Does anybody know, how to apply filter on dataset have two tables as master/detail.

Email: imran_shahid@msn.com

If you are using more than one table in a dataset you should already have defined the relationships between them. If you did not, look to the msdn for Relations in DataSets. After you filter the master table you can get the the other table's related rows by using the GetChildRows method of each row on the master table. I hope it helps.

When I run cursor I am getting results in a separate results rows.
I need to get all results row as one big results. What should I do?
DECLARE @DateEntered as smalldatetime
Declare @SID as varchar(10)
Declare @TranID as numeric
Declare @TType as varchar(1)
DECLARE MaxDate CURSOR FOR
select a.SID,a.TranID,a.DateEntered,TType
from table1 a join
(SELECT sid,MAX(DateEntered) DateEntered FROM table1
group by sid) b
on a.sid = b.sid
and a.DateEntered=b.DateEntered
OPEN MaxDate
FETCH NEXT FROM MaxDate
INTO @sid,@TranID, @DateEntered, @TType
WHILE @@FETCH_STATUS = 0
BEGIN
if @TType='E'
Begin
select *
from table2
where sid= @sid
and TranID=@TranID
End
Else
Begin
select *
from table3
where sid= @sid
and TranID=@TranID
End
FETCH NEXT FROM MaxDate
INTO @sid,@TranID, @DateEntered, @TType
END
CLOSE MaxDate
DEALLOCATE MaxDate

Try something like this instead of a cursor:

SQL

SELECT
    CASE a.TType
        WHEN 'E' THEN b.field1
        ELSE c.field1
    END as field1,
    CASE a.TType 
        WHEN 'E' THEN b.field2
        ELSE c.field2
    END as field2,
    CASE a.TType
        WHEN 'E' THEN b.field3
        ELSE c.field3
    END as field3 -- etc. etc.
FROM table1 a
LEFT JOIN table2 b ON a.SID=b.SID AND a.TranID=b.TranID
LEFT JOIN table3 c ON a.SID=c.SID AND a.TranID=c.TranID
WHERE
    a.DateEntered = (
        SELECT MAX(a1.DateEntered) FROM table1 a1 WHERE a.sid=a1.sid
    )

Here's another way, making use of a UNION:

SQL

SELECT * FROM (
    SELECT b.*
    FROM table1 a
    LEFT JOIN table2 b ON a.SID=b.SID AND a.TranID=b.TranID
    WHERE
        a.TType='E'
        AND
        a.DateEntered = (
            SELECT MAX(a1.DateEntered) FROM table1 a1 WHERE a.sid=a1.sid
        )

    UNION

    SELECT b.*
    FROM table1 a
    LEFT JOIN table3 b ON a.SID=b.SID AND a.TranID=b.TranID
    WHERE
        a.TType <> 'E'
        AND
        a.DateEntered = (
            SELECT MAX(a1.DateEntered) FROM table1 a1 WHERE a.sid=a1.sid
        )
) targetTable
ORDER BY 
    targetTable.field1 ASC -- or whatever

In general, I use cursors only as a very last resort.

I broke my rule this one time; I usually don't answer anonymous questions. Why don't you get an account? That way, you can receive an email when someone answers a question.

Regards,

Jeff Varszegi