|
ok. Thank u.
------------------------------------
To study, study and only to study
|
|
|
|
|
Most of the SQL errors are non-recoverable unless you are writing code to design SQL statements. If you commonly get SQL errors then it is a sign that your application isn't using the SQL server properly.
For example if you are adding a username/password to a registration database and the username must be unique, you should send a statement that returns different results based on a transacted exist/insert statement rather then trap the exception.
Common occurances shouldn't typically appear in error handling or catch blocks.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
I have not big DB (12 tables)
Suppose application is requested for some data about specified clientID
by means of ADO.NET i got all the relevant data about this clientID from several tables to the client, client perform tasks on this data change it maybe add something new or delete somtething (there is posibility that data will not changed at all), after this client click to SAVE button and return data to DB, here start the problem, i can delete all relevant data about specified clientID in all table and after this insert data tables (insert data that recieved from the application) or i can check what changed and to remove or update or insert data.
ANY IDEAS, and maybe how to perform my problem.
I'm using:
Windows Xp, .NET Framework, Sql-Server 2000.
------------------------------------
To study, study and only to study
|
|
|
|
|
To delete all relevant data and insert them again will be taxing to your database server.
For best performance you can balance between tables to be deleted (best for one-to-many relationship such as multiple selection) and tables to be updated (best for one-to-one relationship e.g. a table with many columns).
If you are using datasets, specify all the insert, delete and update commands and let it do the work for you.
Edbert
|
|
|
|
|
However if i don'et use dataset, i'm developing only db i don't care what hapen in the application layer.
What i should do do delete relevant rows and insert after this or to do some manipulation with data to understand what chenged and aafter this to insert o to delete or to update some rows????
------------------------------------
To study, study and only to study
|
|
|
|
|
SHAPE {EXEC sp_S_Events} APPEND ({SELECT * FROM attendees WHERE event_id=?} RELATE event_id TO PARAMETER 0) AS rsAttendees
From a method (namely, GetEvents) of my COM+ business layer component (written in Visual C++ 6.0 SP5), I am issuing the statement given above but it fails with an error saying:
Error 0x80004005: The data provider or other service returned an E_FAIL status.
However, the same statement works perfectly fine when executed from within a program of Visual Basic. A small portion of code from my COM+ component is given below:
STDMETHODIMP CBizLayer::GetEvents( long EventID, VARIANT *pDataset) {
_ConnectionPtr spConn;
_RecordsetPtr spDataset;
try {
.
.
.
spConn->Provider = L"MSDataShape";
spConn->Open(szConnect, _bstr_t(), _bstr_t(), -1);
spDataset->PutRefActiveConnection(spConn);
spDataset->PutCursorLocation(adUseClient);
spRS->Open(szShapeCmd, vtMissing, adOpenForwardOnly, adLockReadOnly, adCmdText);
spRS->PutRefActiveConnection(NULL);
::VariantClear(pDataset);
V_VT(pDataset) = VT_DISPATCH;
V_DISPATCH(pDataset) = (IDispatch*)(spDataset.Detach());
m_spObjectContext->SetComplete();
_hr = S_OK;
}
catch(const _com_error &ex) {
[...]
}
catch(...) {
[...]
}
I've tried to debug my component and I found out that the call to CBizLayer::GetEvents() method completes without any error (i.e. with an HRESULT of S_OK), but in the client application, I still receive this error  . Why? What could be the reason? I am totally confused!
Please help!
Gurmeet
BTW, can Google help me search my lost pajamas?
My Articles: HTML Reader C++ Class Library, Numeric Edit Control
|
|
|
|
|
I have a select statement followed by a from statement that looks like this:
aName aName, anotherName anotherName
where
etc...
What does that mean?? Why did not they put just:
aName, anotherName
where
also, when you are doing your select, if I do this:
ATable.aField,
AnotherTable.aNotherField,
someField,
someOtherField,
etc.
Which table am I pulling from if I do not specify which table it is coming from?
Cheers,
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
The first statement you mention is using aliases for the table. Typically you don't see people use the exact same name as the table, since that is pointless. I use it to abbreviate the table name i.e.
select e.EmpID, e.EmpName, et.Description
from Employee e, EmployeeType et
where...
Basically, I am aliasing Employee to e and EmployeeType to et.
Which answers your second question. The alias.field just specifies which table to pull the field from. It is only necessary when you have the same field name in two tables in your query. If you do not specify a table name, it will not cause a problem. If a field name exists in two tables in the same query and you do not specify a table name, it will result in an error (can't remember what it is right now).
Example:
select EmpID, EmpName, Description, e.EmpTypeID
from Employee e, EmployeeType et
where e.EmpTypeID = et.EmpTypeID
Notice, I do not put the table name in front of EmpID, EmpName or Description because they only exist in one table. However, both tables have a field called EmpTypeID, so I precede it with the table alias (e and et)
Jeff Martin
My Blog
|
|
|
|
|
Thank you, that answered my question completely and exactly!
Jim
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
I'm new to programming and to Visual Studio in general. I have been working on a contact management project and I'm having a problem adding records to my dataset from my form. I designed my tables in Access and used text boxes to view and change my data. I've included some navigation buttons to move from record to record and a counter to track what record I'm on. When I move to the last record, I should get blank fields in my text boxes but it just moves to the last record. I know that the problem lies in what I've done in Access and not my code as I've used this code with another database in Access that I did not create and it worked fine.
Does anyone have any suggesstions for a Newbie. Even a book recommendation would be very helpful.
Thank you,
Jim Cash
|
|
|
|
|
On the last record, it will obviously display the values of the last field.
if you want a new blank field, you should use datarows.
Such as :
on the click event of add_new button
textbox1.text = ""
textbox2.text = ""
textbox3.text = ""
dim dr as new datarow
dr.add...
Thanks
|
|
|
|
|
|
i have a MS-Access database db1.mdb
now i want to get the name of all the tables in my program which are in db1
like in oracle
"select 8 from tab" which returns names of all tables
do we have any such query in Ms-Acess
|
|
|
|
|
It's generally good practice to add tables to your database that provide a definition of the database structure, so if you do that you can of course retrieve table names that way.
The other option is to use ADOX. Do an MSDN search on ADOX - it provides methods for enumerating all objects in a database.
Robert
|
|
|
|
|
I've got old code in MFC which I've got to update. The old code uses DAO and I'm interested to update to ADO.NET. According to what I've read, it is desirable to have DataSet objects where each DataSet object addresses a single table. Then DataRelation is used to make the connection between tables. (Up until now I made SQL queries which did a join between 2 tables.)
My question now is how do I do things efficiently?
Suppose I have 2 tables, patients and studies. Patients has the name, Patient ID, etc. Studies has Patient ID as a foreign key and other information such as date etc.
To do things efficiently I need to limit the number of records so as not to dump the entire contents of the database. Suppose I look for a patient whose name begins with "abc". Then the query of the patients is efficient because I do something like
SELECT * from patients where name like "abc"
This gives me a list of patients with IDs. This can be a long or short list, which I don't know up front.
The question is: how can I make an efficient query on the studies table, i.e. limit the number of rows?
Also I can ask the opposite question. Suppose I want to query on the basis of date. Then I efficiently query the studies table, but how do I efficiently query the patients table?
Thanks for your help,
Ilan
|
|
|
|
|
IlanTal wrote:
According to what I've read, it is desirable to have DataSet objects where each DataSet object addresses a single table.
No, you would only have 1 dataset, but it would have multiple DataTables in it. You fill the tables with data using one or more data adapters. Then you would add a DataRelation to your DataSet, relating the 2 tables.
So, if you get the patient records with:
SELECT Patient.* FROM Patient WHERE name LIKE 'abc'
You can use a join to fill the Study table, but do not retrieve any fields from the Patient table:
SELECT DISTINCT Study.*
FROM Study
INNER JOIN Patient ON Study.PatientID = Patient.PatientID
WHERE Patient.name LIKE 'abc'
Then add a DataRelation to your dataset:
MyDataSet.Relations.Add("PatientStudies",MyDataSet.Tables("Patients").Columns("PatientID"), MyDataSet.Tables("Studies").Columns("PatientID"))
You could fill the dataset with 2 queries, or you could use a stored procedure to run both queries in a single call to the server (return multiple result sets if your database supports it).
IlanTal wrote:
Suppose I want to query on the basis of date. Then I efficiently query the studies table, but how do I efficiently query the patients table?
SELECT DISTINCT Patient.*
FROM Patient
INNER JOIN Study ON Patient.PatientID = Study.PatientID
WHERE Study.Date BETWEEN @FirstDate AND @SecondDate
|
|
|
|
|
Thank you Scott for your input. Now is the time I have to get things straight, BEFORE I write any actual code.
I see where you are going and will use your advice. I still have one question to your answer. You wrote:
Scott Serl wrote:
SELECT DISTINCT Study.*
FROM Study
INNER JOIN Patient ON Study.PatientID = Patient.PatientID
WHERE Patient.name LIKE 'abc'
The question is: what does DISTINCT do for you? What would be different without it? (In the database all the studies are distinct since I have a unique key on one of the fields.)
Thanks,
Ilan
|
|
|
|
|
It depends on the problem domain. Because I am joining to the Patient table, I may have more than one patient in the same study. DISTINCT will eliminate the replicates and give only one entry for each study.
|
|
|
|
|
Thanks for the reply. Now I understand what the problem was which concerned you. In my case it goes the opposite direction. A given patient may well have more than one study (AIDS being the classic case), but a study is done on a specific patient.
In any case I understand what you were driving at.
Thanks,
Ilan
|
|
|
|
|
Suppose i create sp
CREATE PROC myProc @criteria1 int,
@criteria2 nvarchar(50) = ''
AS
SELECT * FROM myTable
WHERE column1 = @criteria1
AND
column2 = @criteria2
GO
---------------------------
my question is in case the parameter @criteria2 is empty (equal '')
i want the select will ignore this criteria
so it perform like:
SELECT * FROM myTable
WHERE column1 = @criteria1
-----
the same with other criterias.
I can have a lot of parameters and and some of them can be empty or not.
Thanks.
------------------------------------
To study, study and only to study
|
|
|
|
|
You can use conditional logic in your procedure, but you may want to consider why you've got empty parameters coming into a stored procedure - there are valid reasons, but it's nice to try to avoid it. You can also set defaults on parameters to sidestep having to use conditional logic in some cases.
Example:
-- These variables would typically be input params<br />
declare @param1 nvarchar(10)<br />
declare @param2 nvarchar(10)<br />
set @param2 = 'Value1'<br />
<br />
-- These variables just hold the length of the params<br />
declare @lenP1 int<br />
declare @lenP2 int<br />
set @lenP1 = len(@param1)<br />
set @lenP2 = len(@param2)<br />
<br />
-- Outputs the state of the params<br />
if @lenP1 > 0 and @lenP2 > 0<br />
select 'Both parameters were provided.'<br />
else if @lenP1 > 0 and (@lenP2 = 0 or @lenP2 is null)<br />
select 'The first parameter was provided but the second was omitted'<br />
else<br />
-- This statement will be executed.<br />
select 'The second parameter was provided but the first was omitted'
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...’
|
|
|
|
|
Instead of defaulting the value to an empty string you can leave it as null
Then you can do a select like this
CREATE PROCEDURE MyProcedure
@criteria1 int,
@criteria2 varchar(50)
AS
SELECT *
FROM MyTable
WHERE column1 = @criteria1
AND (@criteria2 IS NULL OR column2 = @criteria2)
GO
The key here is the AND... line. What it says is that if @criteria2 is null then the clause is true. If @criteria2 is not null then column2 must equal @criteria2 for the clause to be true. In an OR condition one or both sides must be true for the whole to be true.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Yes, thanks it's help. However the statement that u wrote:
AND (@criteria2 IS NULL OR column2 = @criteria2)
possible write as:
WHERE column1 = @criteria1
AND (column2 = ISNULL(@criteria2, column2))
------------------------------------
To study, study and only to study
|
|
|
|
|
If you want you can write it that way too. However, I'd think that using a function like this wouldn't be as efficient (but SQL Server often surprises me with what it can do efficiently and what not) - so, if that is important to you then you should consider writing both versions and running them through the query analyser to see which generates the better execution plan.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
generates the better execution plan, what do u mean by saying it?
how to check execution plan in the query analyser.
Thank u
------------------------------------
To study, study and only to study
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
