|
|
I want to pull data from a table using a regular SQL statement for my adapter (using WHERE and ORDER BY - this I can do fine) but I want to use values from another table as criteria for my WHERE statements... how do I do this?
Basic Sample would be nice.
Thanks
|
|
|
|
|
There are many ways. Can you give an example of what you are trying to do? Do you just want to use the value from one row in the other table as the criteria? (Simply extracting the data into variables then using in a where might be sufficient in this case) Do you want to use data from many rows as the criteria? (In which case some sort of table join would be best)
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Lets say Table1 has:
ID, Num1, Num2, Num3, FullName, ItemName, ItemColor, ItemValue, ItemWeight
Table2 has:
ID, Ber1, Ber2, Ber3
To sort Table1 generically I would use:
SELECT ID, Num1, Num2, Num3, FullName, ItemName, ItemColor, ItemValue, ItemWeight FROM Table1 WHERE ItemValue>100 ORDER BY Num1, Num2, Num3, ItemWeight
I would like to use Table2’s values from columns Ber1, Ber2, and Ber3 as criteria to sort by for Table1’s Num1, Num2, and Num3’s values.
Just the values from a single row for now would be helpful (Columns Ber1, Ber2, and Ber3).
This is used for configuring the Adapter for the connection.
By the way, the two tables aren't related.
The columns (Num1, Num2, Num3 and Ber1, Ber2, Ber3) do have the same Look-Up Tables. I just have Table2 as a criteria to sort Table1 by.
Looking into JOINS.
Thanks
|
|
|
|
|
Thanks Colin,
I ended up using an INNER JOIN similar to what follows:
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Num1 = Table2.Ber1 AND Table1.Num2 = Table2.Ber2 AND Table1.Num3 = Table2.Ber3 WHERE ItemValue > 100 ORDER BY ItemWeight, AndAnythingElse
This was the easiest, just didn't know what was best.
|
|
|
|
|
Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and if
it safely to use this combination of checking error status
Every time after i calling to some store procedure i use this:
------------------------------------------
DECLARE @intError int
EXEC @intError = myProcedure @param1, @param2 .....
SELECT @intError = COALESCE(NULLIF(@intError, 0), @@ERROR)
IF(@intError =0 )
......
......
--------------------------------------------
my question is, can the @@ERROR variable have an error that COALESCE or
NULLIF function were raise?
Thanks.
P.S. if it's not good idea to use it after calling to stored procedures so
what can u advice me
------------------------------------
To study, study and only to study
|
|
|
|
|
1)How to make (.mdf) file in Sql Server (use to deploy database on other computer)
Actually i want to deploy my SQL database along with its data to other computer
EsHbAn BaHaDuR
|
|
|
|
|
|
|
si_69 wrote:
Would be best to attach and detach
May I ask why?
The article you link to does at least tell the user how to get round the main problem of detaching and reattaching (loss of users - which is because the logins are stored in the Master database and referenced in the individual databases and the uids may be different across servers). However, I've never had that problem with restoring a backup.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
May I ask why?
minimal downtime and no data loss,it is best to move it by detaching and then reattaching the database on the new server. The database will be offline during the move but it is better to have it offline than to miss transactions when backing up and then restoring the database on the new server.
|
|
|
|
|
Hi To ALL!
can any body tell me the difference between batches and stored procedure?
|
|
|
|
|
|
In addition to the previous post, stored procedures are compiled.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
I have an MFC project which I want to update to ADO.NET. The user can choose which columns to query on and display, so it looks to me that ExecuteReader is more appropriate than a fixed dataset.
I more or less see how do it directly:
m_OleDbConnection->Open(); // Open up the connection
m_OleDb = new OleDbCommand(S"select * from Persons", m_OleDbConnection);
m_Reader = m_OleDb->ExecuteReader();
where I basically import the string of the OleDbCommand from an outside source.
What I'd like to know is: can this (or should this) be done with a 3 tier setup with a business layer? The examples I've seen basically fill a dataset, using the Merge command on the client side.
Is it possible (or desirable) to return a reader object, which I can then parse on the client side? If so what command is used in place of Merge? Any suggestions on sample code?
Thanks,
Ilan
|
|
|
|
|
OleDbDataReader derives from MarshalByRefObject. You wouldn't get a copy of the data; instead (if it worked at all) you'd get a proxy which would marshal calls back from the client tier to the business object tier. This is likely to be really, really slow as the DataReader interface is quite chatty.
I'd recommend either sticking to DataSet or using custom objects.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks Mike for the advice. I had the feeling I was on the wrong track and you confirmed my feeling.
I still have the feeling that I should be using a business layer because I've got several machines interfacing to the same database. It looks like a good idea to pack away the database connections in a different layer.
My "problem" with the dataset is that each time I ask for a different combination of columns. Is this a real problem, or am I putting up a straw man? In other words, can I make a dataset with all the columns and then just fill some of them, or do I need to create each time a dataset on the fly with just the columns I am actually filling?
If I suppose that I need to create a dataset on the fly, then another question comes up. In the end, I show the data in a list control box. The question is: would it be better to make a temporary dataset and then read the data from it into the list control, or pass a pointer to the list control and fill it directly? What bothers me here is that it seems that I'm passing part of the application logic to the business layer.
So my question returns to: can I use one, predefined dataset, where I only fill in part of the columns, or do I need to define each time a temporary dataset with just the columns I fill?
Thanks,
Ilan
|
|
|
|
|
Hi there,
what is the advantage of using a View instead of directly sending a query to the table? The only thing that comes into my mind is to restrict direct table access and allow the developer only to mess around with the view. Are there performance advantages?
Thanks for your time!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Matthias Steinbart wrote:
The only thing that comes into my mind is to restrict direct table access and allow the developer only to mess around with the view
The conditions that make up the view get precompiled so that the query operates marginally faster than sending the query direct.
You base the view on multiple tables which means you can access normalised data in a denormalised form as if it were one table.
Unioning many physical tables in to one large view. On one project I work on there is a physical table for each year because the quantity of data is so large. It can be accessed through a view which represents the whole set. IIRC, this is called horizontal partioning.
I don't use views much, so there may be other uses. I tend to use stored procedures for everything.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi Colin,
thanks for your reply. I actually didn't know that views get compiled.
Colin Angus Mackay wrote:
IIRC, this is called horizontal partioning.
What does IIRC mean? I made up "If I remember correctly"...  Is this correct?
I've just reviewed a pretty large and well functioning Web-App and I've seen that those people as well used stored procedures for just anything. Made sense to me.
BTW, I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one 
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Matthias Steinbart wrote:
What does IIRC mean? I made up "If I remember correctly"...
Yes, that's right - I shouldn't used these abbreviations, especially as I get on at others for using "U" and "R" and other forms of text/sms-speak.
Matthias Steinbart wrote:
I yesterday read your article about SQL injections. Very interesting topic and well explained. You got my 5 on that one
Thanks.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I've done the following HTA to connect to MyDB.MDB and open all records in MyTable, the connection seems ok, but when I go to display it in a span (DBData), it get the following error:
Object required: 'DBData'
Any help with this would be appreciated.
<html>
<head>
<title>Database Connection</title>
<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>
<style>
BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}
</style>
<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"
set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst
strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"
rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML
</script>
<body>
<span id="DBData"></span>
</body>
</html>
|
|
|
|
|
<pre><html>
<head>
<title>Database Connection</title>
<HTA:APPLICATION ID="objDBCreate" APPLICATIONNAME="DBCreate" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="normal">
</head>
<style>
BODY
{
background-color: buttonface;
font-family: Helvetica;
font-size: 8pt;
margin-top: 2px;
margin-left: 8px;
margin-right: 3px;
margin-bottom: 3px;
}
</style>
<body>
<span id="DBData"></span>
<script language=VBScript>
set conn=CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "MyDB.MDB"
set rs=CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM MyTable", conn
rs.MoveFirst
strHTML = "<table border='1' " & _
"style='border-collapse: collapse' " & _
"bgcolor='white' bordercolor='black' " & _
"width='50%' id='Table1' >"
Do Until rs.EOF
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("Computername") & "</td>"
strHTML = strHTML & "<td width='50%'>" & _
rs.Fields.Item("IPAddress") & "</td>"
strHTML = strHTML & "</tr>"
rs.MoveNext
Loop
strHTML = strHTML & "</table>"
rs.Close
conn.Close
Set rs=Nothing
Set conn=Nothing
DBData.InnerHTML = strHTML
</script>
</body>
</html>
|
|
|
|
|
How i can insert a crypted string into a field of record(its type is Binary)using Query Analyzer?
|
|
|
|
|
Hi everyone,
My problem:
I have a DB where primary/foreign keys are of type Guid.
I was trying to filter in my report all records where foreign key is Guid X.
Somehow, no records are selected!
Observing the sql query reveals:
....WHERE `tbA`.`ParentID`='{guid {B44D446E-F50C-43DD-882C-66A6130D0EEF}}'
What am I doing wrong? 
I'm using CR (Ver. 9) for Visual Stodio .Net 2003
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
