|
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! 
|
|
|
|
|
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(?)  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:
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
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:
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
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
|
|
|
|
|
I create a com object using vc++.I want to return two recordsets in one call.The object has a method(Mymethod) like this
#define VS_CONNECT_1 L"Provider=SQLOLEDB;password=Pass;persist Security Info=True;User ID=User;Initial Catalog=pubs;Data Source=sqlserver;"
Mymethod(IDispatch **pRs1,IDispatch **pRs2)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState())
// TODO: Add your implementation code here
_RecordsetPtr pRSMems,pRS1
HRESULT hr;
try {
_bstr_t strSQL;
strSQL = "select * from authors;select * from employee";
hr = pRSMems.CreateInstance (__uuidof(Recordset));
if (FAILED(hr)) _com_raise_error (hr);
pRSMems->CursorLocation = adUseClient;
hr = pRSMems->Open(strSQL, VS_CONNECT_1, adOpenStatic,
adLockBatchOptimistic, adCmdText);
if (FAILED(hr)) _com_raise_error (hr);
pRS1 = pRSMems->Clone(adLockUnspecified);
pRSMems->putref_ActiveConnection(NULL);
*pRs1 = pRSMems.Detach();
long cnt = 0;
pRS1 = pRS1->NextRecordset((VARIANT *)cnt);
pRS1->putref_ActiveConnection(NULL);
*pRs2 = pRS1.Detach();
return S_OK;
} catch (_com_error &e) {
HRESULT hrerr = e.Error();
return hrerr;
}
}
I call it in VB
Dim obj As Object
Dim rst As Recordset
Dim i
Dim rst1 As Recordset
Set obj = CreateObject("mycom.object")
i = obj.mymethod(rst, rst1)
Do While Not rst.EOF
MsgBox rst.Fields(1)
rst.MoveNext
Loop
Do While Not rst1.EOF
MsgBox rst1.Fields(2)
rst1.MoveNext
Loop
I clone a new recordset,then put activeconnection of original recordset to null.But I fails yet.I need help.
thanks.
|
|
|
|
|
hello, it's possible to load data from xml file, and then save it in database table, both data structures are the same, has xsd file definition?
i have an xml file generated by my program ex.:
<records>
<record id="1" name="test" date="2003-11-17" />
<record id="2" name="test2" date="2003-11-16" />
</records>
and Database table Table1 with structure:
id, int
name, varchar(50)
date, datetime
i want to store this data in database, i can do that by reading rows, step by step, but i search for some basic method, by using DataSet and DataAdapter (i think), i'm using DataSet.ReadXml method to read data from xml file but i don't know how to store it in db table, i were try methods DataAdapter like FillSchema ect. but i fill like blindman in this topic.
KrisPL
krzysztofo@poczta.onet.pl
|
|
|
|
|
Help!
I'm moving some code to C++ Builder and a small part of the code
is database stuff and I can't get it done. The code is really
simple but since it is database stuff it is virtually impossible
to find good example code (at least for me. I spent hours looking
thru examples, online, etc.) Everything is forms and controls.
I want to do it without that stuff. Like I said, this is just
a small part of the application and I really can't have any form
or user interface stuff.
Here is the code I want in C# for a Microsoft environment. I'd
like it to be in a Borland C++ Builder environment. Can you help?
Note it does the usual (for me!) stuff. Opens/closes a db, does
a standard SQL query (Select) with parameters and sorting, inserts,
and updates. All under program control, no user interface. I left
out a lot of error checking for clarity.
//Class vars
OleDbConnection m_conn;
int m_IDate[200];
string m_UniName;
float m_avalue[200];
m_uninames[200];
int m_cnt;
int m_dbcnt;
public void OpenIt()
{
string source = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;";
source += @"Data Source=C:\xdata\";
source += "alltables.mdb;";
source += "Mode=ReadWrite;";
OleDbConnection m_conn = new OleDbConnection(source);
m_conn.Open();
}
public void CloseIt()
{
m_conn.Close();
}
public void Updateit(int j)
{
int astatus = 2;
string sSQLCommand = "UPDATE ATable SET Status = ? WHERE ";
sSQLCommand += "IDate = ? AND UniName = ? And AValue = ?";
OleDbCommand cmd;
cmd = new OleDbCommand();
cmd.CommandText = sSQLCommand;
cmd.Connection = m_conn;
cmd.Parameters.Clear();
cmd.Parameters.Add ( "Status", astatus) ;
cmd.Parameters.Add ( "IDate", m_IDate[j]) ; // int
cmd.Parameters.Add ( "UniName", m_UniName) ; // string
cmd.Parameters.Add ( "AValue", m_avalue[j]) ; // float
cmd.ExecuteNonQuery() ;
}
GetIt(int adate)
{
string selectname = "SELECT * FROM ATable Where IDate=?";
selectname += " ORDER BY UniName";
OleDbDataReader aReader;
OleDbCommand cmd;
cmd = new OleDbCommand();
cmd.CommandText = selectname;
cmd.Connection = m_conn;
cmd.Parameters.Clear();
cmd.Parameters.Add ( "IDate", adate) ;
aReader = cmd.ExecuteReader();
int j, k;
while(aReader.Read())
{
j = aReader.GetOrdinal("IDate");
k = aReader.GetInt32(j);
if (k != adate)
{
// this should not happen
continue;
}
j = aReader.GetOrdinal("UniName");
if (!aReader.IsDBNull(j))
m_uninames[m_cnt] = "";
else
m_uninames[m_cnt] = aReader.GetString(j);
j = aReader.GetOrdinal("AValue");
m_avalue[m_cnt] = aReader.GetFloat(j);
m_cnt++;
}
aReader.Close();
}
public void InsertIt(int adate, string uniname, int status, float avalue)
{
string strInsert = "INSERT INTO ATable ";
strInsert += "(Status, IDate, UniName, AValue) ";
strInsert += "VALUES (?, ?, ?, ?)";
OleDbCommand cmd;
cmd = new OleDbCommand(strInsert , m_conn);
cmd.Parameters.Add ("IDate", adate) ;
cmd.Parameters.Add ("UniName", uniname) ;
cmd.Parameters.Add ("Status", status) ;
cmd.Parameters.Add ("AValue", avalue) ;
cmd.ExecuteNonQuery() ;
m_dbcnt++;
}
|
|
|
|
|
_SQLObjectListPtr
what sql namespace should be useed to access this ptr ??
i want to use the ListUserPermissions method ..for htis i need the above mentioned ptr..
ranjani
|
|
|
|
|
_SQLObjectListPtr
what sql namespace should be useed to access this ptr ??
i want to use the ListUserPermissions method ..for htis i need the above mentioned ptr..
ranjani
|
|
|
|
|
in my c++ program to find the user permissions for each user in a sql database...i use ListUserPermissions method after importing sqldmo.rll
on execution i get an error saying...
undeclared identifier..ListUserPermissions !!
shud i use any headre file other thann sqldmo.rll ?
ranjani
|
|
|
|
|
can i use the function
ListDatabasePermissions
for getting the acccess permissiosn for a particular database?
ranjani
|
|
|
|
|
There're plenty of articles that shows you how to load a disk file/binary from ASP frontend and insert it into a binary column in SQL server. But, is there a stored procedure that can load file/binary from disk WITHOUT ASP or relying on external component?
QUESTION 1:
If that's possible, I can create a simple stored procedure to insert binary file into a binary column. Pseudo code:
CREATE PROCEDURE sproc_UpdateMessageAttachment
@msgID uniqueidentifier
@inputfile varchar(100),
@status binary[50000] OUTPUT
AS
BEGIN
DECLARE @binaryattachment binary[50000]
--Of course, I made this up. The question is whether such stored procedure exists.
EXEC sp_loadfile @inputfile, @binary OUTPUT
UPDATE messages
SET Attachment = @binaryattachment
WHERE msgID=@msgID
END
QUESTION 2:
Is there alternative to this? Can I instantiate/call method on a COM component from within body of stored procedure?
And if I can what component would I need in this case?
Thanks.
Reference from www.stardeveloper.com:
1. Classical ASP http://stardeveloper.com/articles/display.html?article=2001033101&page=1
2. ASP.NET
a. http://stardeveloper.com/articles/display.html?article=2003022601&page=1
b. http://stardeveloper.com/articles/display.html?article=2003031201&page=1
|
|
|
|
|
I think that otherwise you have to create an extended stored procedure for that
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Thanks. Creating extended stored procedure... means I need to read up on how to pass arguments and returns to/from SQL server and the dll. I think I'll just keep things simple for now:
a. Create a SQL Server Job that launches an application (say, LoadBinary.exe) using xp_xmdshell
b. LoadBinary.exe will load the binary file (disk file) into SQL server.
|
|
|
|
|
There're plenty of articles that shows you how to load a disk file/binary from ASP frontend and insert it into a binary column in SQL server. But, is there a stored procedure that can load file/binary from disk WITHOUT ASP or relying on external component?
QUESTION 1:
If that's possible, I can create a simple stored procedure to insert binary file into a binary column. Pseudo code:
CREATE PROCEDURE sproc_UpdateMessageAttachment
@msgID uniqueidentifier
@inputfile varchar(100),
@status binary[50000] OUTPUT
AS
BEGIN
DECLARE @binaryattachment binary[50000]
EXEC sp_loadfile @inputfile, @binary OUTPUT --Of course, I made this up. The question is whether such stored
procedure exists.
UPDATE messages
SET Attachment = @binaryattachment
WHERE msgID=@msgID
END
QUESTION 2:
Is there alternative to this? Can I instantiate/call method on a COM component from within body of stored procedure?
And if I can what component would I need in this case?
Thanks.
Reference from www.stardeveloper.com:
1. Classical ASP http://stardeveloper.com/articles/display.html?article=2001033101&page=1
2. ASP.NET
a. http://stardeveloper.com/articles/display.html?article=2003022601&page=1
b. http://stardeveloper.com/articles/display.html?article=2003031201&page=1
|
|
|
|
|
I don't know about your specific situation, but have you looked at using BULK INSERT or bcp (via xp_cmdshell)?
Regards,
Jeff Varszegi
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
