|
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
|
|
|
|
|
Hi Geeks,
I am quite new to SQL Server.Well I have a problem..
Could someone help me ??
This is my Query:
sql="SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate='11/15/2003'
ORDER BY IP_Id"
Now in the IP table there are records containing date values
i.e. AdmDate='15/11/2003' ;these date values r in small date format with precision to seconds.
Now I want All the records of a paticular date irrespective of their time Value.
-SimSan
|
|
|
|
|
I typically use
SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate >= '2003-11-15' AND
AdmDate < DATEADD( Day, 1, '2003-11-15' )
ORDER BY IP_Id Of course, typically I use this is in a stored procedure where the literal date is replaced by a parameter, where using DATEADD makes a bit more sense.
|
|
|
|
|
This works fine...Thanks for the tip.
Well...I wanted to clarify if SQL Server treats
Dates in American Date format.If it does,why this date format ('yyyy-mm-dd') is in use.
-SimSan
|
|
|
|
|
Because I'm in the UK 
SQL Server interprets literal dates in queries using the user's default locale - even in stored procedures, IIRC. You could write '10/11/2003' to be interpreted as October 11th, but if your user is using the English (UK) locale, it will be interpreted as 10 November.
"SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting." Using Date and Time Data[^]
The ISO format yyyy-mm-dd is supposed to be supported in all locales with the same meaning.
On re-reading Writing International Transact-SQL Statements[^], it actually looks like you should use unseparated dates (format yyyymmdd) to ensure correct interpretation. So I've been getting it wrong. 
|
|
|
|
|
Thanks again for your info..U've got it right!!!
-SimSan
|
|
|
|
|
You could use
SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate BETWEEN '11/15/2003 00:00' AND '11/15/2003 23:59'
ORDER BY IP_Id"
|
|
|
|
|
Real simple problem but it is driving me nuts.
Create a table in SQL with two columns.
NameId and FullName
Make the NameId a primary Key.
Add a nameid and a fullname to the table.
Save the table as NameId.
Now using only the .net wizards for a windows forms project I do the following:
1. Create new project (windows forms)
2. Drag two textboxes onto the form.
3. Drag a button onto the form.
4. Drag a sqlDataAdapter onto the form which starts the wizard.
5. Connect to the sqldatabase for the table.
6. Allow it to create the sql statements.
7. Create the query using both fields.
8. Do not change the advanced settings.
9. Save the wizard.
10. Create a dataset using the NameId table.
11. Bind the two textboxes to the table fields via their respective properties.
12. On the window form events create an event for load.
13. Enter the fill method of the sqldataadapter:
this.sqlDataAdapter1.Fill(this.dataset1, "NameId");
14. Return to the design screen and create an click event for the button under its respective events.
15. In the code enter the following:
this.sqlDataAdapter1.Update(this.dataset1, "NameId");
now compile
my results are the form will display the one record, however when I change the fullname field to something else it does not update the sql NamId table...
What am I doing wrong...
 Bruce...
|
|
|
|
|
I have a table1 where I have a dateentered and trantype.
I need to find the max(dateentered) and then based on trantype join table2 or table3. All data should be in one result. How do I do this?
|
|
|
|
|
You can't selectively join tables. You should probably use a union. Without knowing your table structure, here is a stab at it...
select max(dateentered), otherfields
from table1
join table2 on (table1.trantype = table2.trantype)
union
select max(dateentered), otherfields
from table1
join table3 on (table1.trantype = table3.trantype)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
