|
Hi All,
Can anyone tell me the order in which sql Query executes
Example:
select C.*,RenewalDate=(select * From CustomerApprovals where CustNo=1 Order By ApprovalDate Desc)
from Customers C where C.CustNo=1
|
|
|
|
|
All at once - SQL is a set based language not a procedural language, although for certain things you can act in a procedural manner (e.g. using cursors) but this is slow because it is not using the strengths of the language.
Basically a SELECT statement is telling SQL Server what you want, and then SQL Server figures out itself how to achive the result you want.
If it makes it any easier for you to think about you can think of the subquery executing first. However, the reality is that many parts of the query will execute in parallel.
Aside from that I cannot see what you are tying to do with your statement because it is invalid.
|
|
|
|
|
Look at the execution plan to see the order in which it will be executed.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi
I want to create a report that lists the customers and all the items that they have not bought from us for a certain period. I have 3 relevant tables, CustomerMaster, InventoryMaster, SalesDetail.
SalesDetail holds all the sale info, for example
CustomerCode : 1AFR204
StockCode : FRA202
QtyInvoiced : 2
NetSalesValue: 1295
Year : 2006
Month : 7
The master tables holds all customers and inventory.
I want the report to basicaly look like this:
Ultra liquors Newton Park
Not listed:
Boland Cabernet
Spier Sauvignon Blanc
To be able to do this I need a recordset that consists of the stockcode and customercode of items not sold.
Any ideas how to do this? I have been playing with not in subqueries. Its easy to display items not sold or customers that did not buy, seperately, but to combine them is a different animal.
Thanks
Izak
|
|
|
|
|
SELECT CM.CustomerCode, IM.StockCode
FROM CustomerMaster CM, InventoryMaster IM
WHERE CM.CustomerCode NOT IN(SELECT SD.CustomerCode
FROM SalesDetail SD
WHERE SD.StockCode = IM.StockCode
AND SD.Year = 2006
AND (SD.Month >= 6 and SD.Month <= 7))
ORDER BY CustomerCode
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks!
If I understand it correctly, the customermaster and the inventorymaster creates all possible combinations except for the combinations in the NOT IN clause?
So...
If customermaster has 200 records and inventorymaster has 100 records and the salesdetal has 750 distinct records. 200 * 100 = 20000 - 750 = 19250, the result will have 19250 possible sales combinations that did not happen.
Am my logic correct?
Thanks again Eric, this has been very helpfull! 
Izak
|
|
|
|
|
Izak - wrote: Am my logic correct?
Seems so to me.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want use the msde bootstrapper with my app to install both on my client machine. Currently I use the .netframework bootstrapper to install check if the framework is on the client machine, if not it must be installed first, now I want to add the msde to the installation, so that it installs the msde from the setup.exe, then checks for the framework and finnaly install my app.
Does anyone know how I can do this??
Thanx in advance
He who laughs last is a bit on the slow side
|
|
|
|
|
Check this useful link
http://codeproject.com/managedcpp/dotnetsetup.asp
|
|
|
|
|
Because I'm an beginner to NHibernate.
So I don't know what i need to do before running an very easy example success .
|
|
|
|
|
Hi all,
I want to create dynamic tree view menu in HTML by javacript.The condition is user don't know how many subfolders or files are in a root folder.
please help me .It's very urgent.
Regards & Thanks
Sunil
|
|
|
|
|
This question doesn't appear to have anything to do with databases. Pehaps you might find an answer if you post in an appropriate forum[^]
|
|
|
|
|
hi all
i want to set a password on sa login at installation time through setup.ini file.
plz help me how it is possible..
ankesh
|
|
|
|
|
SAPWD="password"
He who laughs last is a bit on the slow side
|
|
|
|
|
hi all i have this pro on hand now.. i have downloaded some sample database applications from the internet..however when i want to debug it i gives me the error--An unhandled exception of type "system.Data.OleDb.OledbException" occurred in system.data.dll
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace Database_Example
{
///
/// Summary description for Form1.
///
public class fclsMain : System.Windows.Forms.Form
{
OleDbConnection m_cnADONewConnection = new OleDbConnection();
OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter();
DataTable m_dtContacts = new DataTable();
int m_rowPosition = 0;
private System.Windows.Forms.TextBox txtContactName;
private System.Windows.Forms.TextBox txtState;
private System.Windows.Forms.Button btnMoveFirst;
private System.Windows.Forms.Button btnMovePrevious;
private System.Windows.Forms.Button btnMoveNext;
private System.Windows.Forms.Button btnMoveLast;
private System.Windows.Forms.Button btnSave;
private System.Windows.Forms.GroupBox grpNewRecord;
private System.Windows.Forms.TextBox txtNewContactName;
private System.Windows.Forms.TextBox txtNewState;
private System.Windows.Forms.Button btnAddNew;
private System.Windows.Forms.Button btnDelete;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public fclsMain()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.txtContactName = new System.Windows.Forms.TextBox();
this.txtState = new System.Windows.Forms.TextBox();
this.btnMoveFirst = new System.Windows.Forms.Button();
this.btnMovePrevious = new System.Windows.Forms.Button();
this.btnMoveNext = new System.Windows.Forms.Button();
this.btnMoveLast = new System.Windows.Forms.Button();
this.btnSave = new System.Windows.Forms.Button();
this.grpNewRecord = new System.Windows.Forms.GroupBox();
this.txtNewContactName = new System.Windows.Forms.TextBox();
this.txtNewState = new System.Windows.Forms.TextBox();
this.btnAddNew = new System.Windows.Forms.Button();
this.btnDelete = new System.Windows.Forms.Button();
this.grpNewRecord.SuspendLayout();
this.SuspendLayout();
//
// txtContactName
//
this.txtContactName.Location = new System.Drawing.Point(48, 112);
this.txtContactName.Name = "txtContactName";
this.txtContactName.Size = new System.Drawing.Size(112, 20);
this.txtContactName.TabIndex = 0;
this.txtContactName.Text = "";
//
// txtState
//
this.txtState.Location = new System.Drawing.Point(168, 112);
this.txtState.Name = "txtState";
this.txtState.Size = new System.Drawing.Size(80, 20);
this.txtState.TabIndex = 1;
this.txtState.Text = "";
//
// btnMoveFirst
//
this.btnMoveFirst.Location = new System.Drawing.Point(16, 152);
this.btnMoveFirst.Name = "btnMoveFirst";
this.btnMoveFirst.Size = new System.Drawing.Size(32, 23);
this.btnMoveFirst.TabIndex = 2;
this.btnMoveFirst.Text = "<<";
this.btnMoveFirst.Click += new System.EventHandler(this.btnMoveFirst_Click);
//
// btnMovePrevious
//
this.btnMovePrevious.Location = new System.Drawing.Point(56, 152);
this.btnMovePrevious.Name = "btnMovePrevious";
this.btnMovePrevious.Size = new System.Drawing.Size(32, 23);
this.btnMovePrevious.TabIndex = 3;
this.btnMovePrevious.Text = "<";
this.btnMovePrevious.Click += new System.EventHandler(this.btnMovePrevious_Click);
//
// btnMoveNext
//
this.btnMoveNext.Location = new System.Drawing.Point(96, 152);
this.btnMoveNext.Name = "btnMoveNext";
this.btnMoveNext.Size = new System.Drawing.Size(32, 23);
this.btnMoveNext.TabIndex = 4;
this.btnMoveNext.Text = ">";
this.btnMoveNext.Click += new System.EventHandler(this.btnMoveNext_Click);
//
// btnMoveLast
//
this.btnMoveLast.Location = new System.Drawing.Point(136, 152);
this.btnMoveLast.Name = "btnMoveLast";
this.btnMoveLast.Size = new System.Drawing.Size(32, 23);
this.btnMoveLast.TabIndex = 5;
this.btnMoveLast.Text = ">>";
this.btnMoveLast.Click += new System.EventHandler(this.btnMoveLast_Click);
//
// btnSave
//
this.btnSave.Location = new System.Drawing.Point(176, 152);
this.btnSave.Name = "btnSave";
this.btnSave.Size = new System.Drawing.Size(40, 23);
this.btnSave.TabIndex = 6;
this.btnSave.Text = "Save";
this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
//
// grpNewRecord
//
this.grpNewRecord.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnAddNew,
this.txtNewState,
this.txtNewContactName});
this.grpNewRecord.Location = new System.Drawing.Point(16, 192);
this.grpNewRecord.Name = "grpNewRecord";
this.grpNewRecord.Size = new System.Drawing.Size(264, 64);
this.grpNewRecord.TabIndex = 7;
this.grpNewRecord.TabStop = false;
this.grpNewRecord.Text = "New Contact";
//
// txtNewContactName
//
this.txtNewContactName.Location = new System.Drawing.Point(8, 24);
this.txtNewContactName.Name = "txtNewContactName";
this.txtNewContactName.Size = new System.Drawing.Size(112, 20);
this.txtNewContactName.TabIndex = 0;
this.txtNewContactName.Text = "";
//
// txtNewState
//
this.txtNewState.Location = new System.Drawing.Point(126, 24);
this.txtNewState.Name = "txtNewState";
this.txtNewState.Size = new System.Drawing.Size(80, 20);
this.txtNewState.TabIndex = 1;
this.txtNewState.Text = "";
//
// btnAddNew
//
this.btnAddNew.Location = new System.Drawing.Point(214, 24);
this.btnAddNew.Name = "btnAddNew";
this.btnAddNew.Size = new System.Drawing.Size(40, 23);
this.btnAddNew.TabIndex = 2;
this.btnAddNew.Text = "Add";
this.btnAddNew.Click += new System.EventHandler(this.btnAddNew_Click);
//
// btnDelete
//
this.btnDelete.Location = new System.Drawing.Point(224, 152);
this.btnDelete.Name = "btnDelete";
this.btnDelete.Size = new System.Drawing.Size(56, 23);
this.btnDelete.TabIndex = 8;
this.btnDelete.Text = "Delete";
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// fclsMain
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnDelete,
this.grpNewRecord,
this.btnSave,
this.btnMoveLast,
this.btnMoveNext,
this.btnMovePrevious,
this.btnMoveFirst,
this.txtState,
this.txtContactName});
this.Name = "fclsMain";
this.Text = "Database Example";
this.Load += new System.EventHandler(this.fclsMain_Load);
this.Closed += new System.EventHandler(this.fclsMain_Closed);
this.grpNewRecord.ResumeLayout(false);
this.ResumeLayout(false);
}
#endregion
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new fclsMain());
}
private void fclsMain_Load(object sender, System.EventArgs e)
{
m_cnADONewConnection.ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\contacts.mdb";
m_daDataAdapter =
new OleDbDataAdapter("Select * From Contacts",m_cnADONewConnection);
OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
m_daDataAdapter.Fill(m_dtContacts);
this.ShowCurrentRecord();
}
private void fclsMain_Closed(object sender, System.EventArgs e)
{
m_cnADONewConnection.Close();
}
private void ShowCurrentRecord()
{
if (m_dtContacts.Rows.Count==0)
{
txtContactName.Text = "";
txtState.Text = "";
return;
}
txtContactName.Text =
m_dtContacts.Rows[m_rowPosition]["ContactName"].ToString();
txtState.Text = m_dtContacts.Rows[m_rowPosition]["State"].ToString();
}
private void btnMoveFirst_Click(object sender, System.EventArgs e)
{
m_rowPosition = 0;
this.ShowCurrentRecord();
}
private void btnMovePrevious_Click(object sender, System.EventArgs e)
{
if (!(m_rowPosition == 0))
{
m_rowPosition = m_rowPosition-1;
this.ShowCurrentRecord();
}
}
private void btnMoveNext_Click(object sender, System.EventArgs e)
{
if (!(m_rowPosition == m_dtContacts.Rows.Count-1))
{
m_rowPosition = m_rowPosition + 1;
this.ShowCurrentRecord();
}
}
private void btnMoveLast_Click(object sender, System.EventArgs e)
{
if(!(m_dtContacts.Rows.Count==0))
{
m_rowPosition = m_dtContacts.Rows.Count-1;
this.ShowCurrentRecord();
}
}
private void btnSave_Click(object sender, System.EventArgs e)
{
if (!(m_dtContacts.Rows.Count==0))
{
m_dtContacts.Rows[m_rowPosition]["ContactName"]= txtContactName.Text;
m_dtContacts.Rows[m_rowPosition]["State"] = txtState.Text;
m_daDataAdapter.Update(m_dtContacts);
}
}
private void btnAddNew_Click(object sender, System.EventArgs e)
{
DataRow drNewRow = m_dtContacts.NewRow();
drNewRow["ContactName"] = txtNewContactName.Text;
drNewRow["State"] = txtNewState.Text;
m_dtContacts.Rows.Add(drNewRow);
m_daDataAdapter.Update(m_dtContacts);
m_rowPosition = m_dtContacts.Rows.Count-1;
this.ShowCurrentRecord();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
if (!(m_dtContacts.Rows.Count==0))
{
m_dtContacts.Rows[m_rowPosition].Delete();
m_daDataAdapter.Update(m_dtContacts);
m_rowPosition=0;
this.ShowCurrentRecord();
}
}
}
}
does this errors occur due to where the dB file is located..is it correct? can someone out there help will this pro? and maybe guide me to correcting it ..thanks in advance..
the whole chunk of codes is written below,the pro is highlighted in bold...thanks in advance
one of the ans i got is entering cnADONewConnection.Open();
before m_daDataAdapter.Fill(m_dtContacts);
but i got an error like this C:\Documents and Settings\STUDENT\Desktop\Database Example\Form1.cs(235): The type or namespace name 'cnADONewConnection' could not be found (are you missing a using directive or an assembly reference?)
can anyone please give me more detailed help as im really new to this
|
|
|
|
|
I'd honestly recommend learning core Winforms programming until you understand which bits of the code you pasted above are absolutely useless in trying to diagnose your problem. Then worry about databases.
And in future, whatever bugs you have, try stepping through the code so you know where the actual problem is. That's how programmers solve bugs, we look at the code line by line, until we see where the program isn't doing what we expect or hoped.
You've probably just got a wrong connection string.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
skyeddie wrote: one of the ans i got is entering cnADONewConnection.Open();
before m_daDataAdapter.Fill(m_dtContacts);
but i got an error like this C:\Documents and Settings\STUDENT\Desktop\Database Example\Form1.cs(235): The type or namespace name 'cnADONewConnection' could not be found (are you missing a using directive or an assembly reference?)
Maybe because in yor code the name is actually m_cnADONewConnection. Someting one would expect you to recognise...
The constructor overload you are using for the OleDbDataAdaptor creates one that opens and closes the connection with each fill operation, so you shouldn't need the Open().
Is the database really where the connection string says it is?
Is it writeable?
Is it really an access database?
Put a breakpoint in the beginning of the Load event handler, say the first line, then single step to see which line is causing the exeption.
Add some error handling (try-catch in the load handler would be nice, so the app wouln't need to just evaporate on the current error)
And next time, just post the code causing the problem, not the whole bloody form. Most of us won't waste our time hunting through 500 lines of code to see what you're talking about.
|
|
|
|
|
wht the .. some people say "plz post more details then some say post a section" wht u all programmers r thinking actually.. anyway wht useless ans u all give.. i have found out the ans myself !! all u supposingly smart ass jus didnt provide any help .. im new in c# yet i solve myself..haha!!
|
|
|
|
|
Congratulations. you just made my list of posters to ignore in the future. Glad you're able to solve your own problems, because you are certainly going to get to in the future...
|
|
|
|
|
hey
i am developing a web application using asp.net, but coding in C#.
i need to insert into my database (sql server 2000/access). how shud i do it.
i have two textboxes.. watever the user enters in it, it shud be added in a particular table.
Kunal Piyush
|
|
|
|
|
shud ?
The fact that your ASP.NET codebehind runs on the server means the code is no different to accessing a database using WinForms. Any help you can find on the web for database access will work just fine.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
i coded a bit.. but now sql database i showing an error if i keep the field "Username" as a primary key.
firstly the data is entered in a very ad-hoc fashion.. there is a row empty at the top...data entered in between two rows which are already filled with data..
and if i delete some data,then i get the error as
"Another user has modified the contents of the table,the database row ur modifying no longer exists in the database....... violation of PRIMARY KEY constraint...."
THE CODE:-
try
{
SqlConnection conn = new SqlConnection("server=(local);" +
"database=path;" +
"connection timeout=3;" +
"Trusted_Connection=yes;" +
"Integrated Security=true;" +
"user id=sa;" +
"password=");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "Insert into login(Username,Password) values('"+uname.Text+"','"+pass.Text+"')" ;
SqlDataReader dread = cmd.ExecuteReader();
if (dread.Read())
{
failed.Text = "Could not add the user";
}
else
{
Response.Redirect("success.aspx");
}
conn.Close();
}
catch(Exception kp)
{
failed.Text=kp.Message;
}
}
Kunal Piyush
|
|
|
|
|
the question is simply this boys n girls, ladies n gents,
how do I iterate thru all the elements of a Datagrid in a windows form (.net 1.1). thats when I take input from user. how do i know the number of rows entered in it.
thanks
I'd appreciate if u tel me that in c# but anyways tahts not compulsory
thanks in advance
|
|
|
|
|
Doesn't the DataGrid have a Rows property, which has a Count property ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
OK got ur point, stupid question I know
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
