|
Make as stored procedure:
CREATE PROCEDURE GetRandomRow AS
declare @nRand int
declare @cSQL varchar(200)
select @nRand = 1 + (rand() * (select count(*) from MyTestTable))
select @cSQL = 'SELECT TOP 1 * FROM (SELECT TOP ' + cast(@nRand as varchar)
+ ' * FROM MyTestTable ORDER BY IDColumn ASC) as tbl ORDER BY IDColumn DESC'
exec(@cSQL)
You could send in the table name and ORDER BY field as parameters.
--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
|
|
|
|
|
Your website is getting so many hits and visitors are running so many queries Temp DB logs starts growing exponentially thus your sites starts slowing down and even crashing. What would be the most effecient way to quickly clear temp DB without restarting the servers. I wrote this script which worked but it took a while, but it helped. Would you use another method is this script the best its gonna get?
DBCC SHRINKFILE(‘YourDataBase_log’)
BACKUP LOG YourDataBase WITH TRUNCATE_ONLY
After that is done if the log files still have not shrunken then I do this:
SET NOCOUNT ON
CREATE TABLE dummy
(a VARCHAR(8000))
WHILE (0 < 1)
BEGIN
INSERT INTO dummy SELECT REPLICATE(‘a’, 8000)
DELETE FROM dummy
END
-- modified at 0:45 Tuesday 8th August, 2006
Greg
Coding makes the world go round!!!
|
|
|
|
|
Put tempdb on a seperate diskdrive if possible and this will also improve performance.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
I have been reading this article, but can't seem to get my application to do what I need.
Here is the situation :
Data parsed from text file, dumped into table. User selects options for the data he needs. Data dumped into datagrid via dataset. I want the user to be able to edit info in the datagrid and save the changes to the dataset, which will now be dumped into a table to be used with Word.
I can't seem to find a way to save the changes to the dataset.
Any help very well appreciated.
Jude
|
|
|
|
|
|
Thanx for the reply...it was human error..I was calling the function to update the database before the changes were made in the datagrid
Jude
|
|
|
|
|
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...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
