|
When I run this query I get 2 row back with different dates:
SELECT Sdate1,EDate1,SDate2,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
I would like to get only one row back with Sdate1,Edate2.
How do I do this?
|
|
|
|
|
Without knowing anything about your data the following is the best I can manage:
SELECT TOP 1 Sdate1,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Hi,
I need your help.....I want to add numbers stored in the DB and display them on the ASP page.
My Access DB looks like this:
WeekNo Course1 Course2 Course3 Course4
1 2 4 5 1
2 4 6 7 9
I want users to get thier totals for differnt weeks and get their results displayed on the screen via an ASP page.
What I have at the moment is: Users are able to Enter their data and View it then are able to update it. I need code that can get me started.
Thanks in advance.
Petunia
|
|
|
|
|
I do not know how many people out there need something like this, but I recently had use for it with my new dedicate server across the country. They have an MSDE installed database on there but it was not configured for remote connection. To make matters worse, they block port the SQL port to protect from viruses.
Well, I had to reconfigure it so that I could connect to it with Enterprise Manager (since I am a spoiled brat). I found this freeware tool that makes it an easy matter to configure your server along with other features. It is not Enterprise Manager, but it is a lifesaver:
http://www.asql.biz/DbaMgr.shtm[^]
It also has the ability to generate an Insert script for your data in a table. That can be handy when you want to move a table of data quickly or genrate a script to prepopulate a new database.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
I am trying to find a method to return two results sets from a single query that can be handing by one call to the SqlDataAdapter. I am using paged data and I need to return a table with the current page of rows along with the total count of rows that would match (not limited to the page being returned).
The stored procedure I am using has selection for the current page of rows and then does another select for the record count. I have not been able to get the data adapter to populate another table in the dataset with the record count.
Any idea?
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
Using multiple SELECTs with a DataAdapter yields multiple DataTables in a DataSet that you pass to DataAdapter.Fill. If you want any of the DataTables named, you must use a DataTableMapping which can be assigned to the DataAdapter.
When you use a SqlCommand, you can get multiple result sets by calling SqlCommand.ExecuteReader with either no parameters or CommandBehavior.Default, so long as CommandBehavior.SingleResult is not passed. Then you loop over SqlDataReader.NextResult() and inside loop over SqlDataReader.Read().
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
I must be doing something wrong. It does not seem to pick up the second result set. The stored procedure returns two result sets as verified in query analyser but when I feed to to the sda which has been generated in the visual designer and supplied with a typed dataset, it only has one table in it.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
If you're using a strongly-typed DataSet, then you have named DataTables and must use a DataTableMapping on your SqlDataAdapter like I mentioned previously. Both tables must be mapped accordingly.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Heath Stewart wrote:
must use a DataTableMapping on your SqlDataAdapter
Actually, after looking at the results, the dataset does not get two tables, only one. Not sure what could be causing that. I can run it in QA and the sp returns two result sets. Will have to dig further.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
It can, actually. The trick is to set up your tables mappings by using a strongly-typed DataSet that you already made and using the SqlDataAdapter's designer. In any case, dragging-n-dropping controls and using designers makes for poor coding - getting your hands dirty in code is what it's all about. Heck, after I designer my initial layout in the forms designer I usually end up breaking the designer support because I completely rearrange the code for faster load times, localization, and many other things.
Besides, table mappings are fairly easy to code. There's not much there, just table-table pairs, and each of those has column-column pairs. Hand-coding a SqlCommand with various parameters (including outputs) is much more tedious.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Sorry for wasting you time. It is due to a very bad programming habbit I have gotten into lately where I wrap base code in a try-catch to nullify the error until a later time when I get the error logging installed at that level. Yep, it was blowing an error in the base classes of my data manager that I did not notice since I cancelled them 
Over all though, it has been a learning experience for multiple result sets. Found tha the visual designer in VS.NET handles them pretty easily while configuring your field mappings as long as it can parse your query properly. I used a temp table inside the stored procedure and the designer errored out and would not let me go further.
Anyway, now that I have had a little pain in this area, I am sure to remember it for the future and will probably work more with passing back statistical information in a second result set. Works well.
Thanks!
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
Hi. I have 2 tables, prod and inv. These two tables are joined by invProductID (the primary is in prod). So my question is how do I make a sp too insert data into both these tables? I have just started working with SQL, so am not sure on this. Because, in order to insert into table inv, I will need the primary key value from prod.
Is there a way to do this, or do you have to take on a transactional approach? Or, if I do it in 2 sp's, how do I return the invProdutID from products, to use in inv.
Hope this makes sense.
|
|
|
|
|
I'm assuming SQL Server 2000, and that you have no usable key data (for example, it's possible for every field in two different records to be identical, apart from a key field).
If you're generating your own primary keys, there shouldn't be an issue. What you'd normally do is keep some form of incrementing identifier in a separate table. You'd read the next value into a variable, update the stored value, then use that key information in both places.
If you're using automatic numbering (using an IDENTITY column), you can retrieve the last inserted identifier using the SCOPE_IDENTITY() function. You need to retrieve this straight after the first INSERT, otherwise it may not be the correct identifier.
Depending on your requirements, you may want to put both INSERTs inside a transaction. Do this if you want to be sure that either both, or neither, INSERT completes. Be aware that if an error occurs, the transaction is not automatically rolled back. See SQL Server Transactions and Error Handling[^] in the Articles section.
|
|
|
|
|
Apologies if this is a blonde question but does anyone know of a tool that specifically monitors bandwidth used by MSSQL server? i.e. We have a SQL database server box and we want to monitor and tally all calls to the SQL port.
Our sys admin tells me that the normal bandwidth monitoring software he is using cannot track SQL usage even though in my mind it is just another port.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
|
|
|
|
|
I guess you could use packet sniffing software such as WinPCap.
Have you tried the SQL Profiler? I know you can set a number of filter options and log them to a file.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
This may be a silly question but I'm a bit stuck and am asking for clever ideas 
When I insert text into the DB i use a function called PrepSQL which just replaces ' with ''. All very well, but I now have a problem when the content is edited. Each individual ' get replaced so I end up with "it'''''''''''''s". Any ideas how to check for this and resolve it 
|
|
|
|
|
Use parameters on your command objects (ADO) or on your SQLExecute (ODBC). If you do that, you shouldn't need any reserved character escaping codes - and your statements may run faster too.
SQL string construction is basically brain-dead, unless you have no alternative, or you want to keep your code database independent. Even then, parameters are usually a better choice.
|
|
|
|
|
If you're going to stick w/ doing the PrepSQL function on the way in, then I'd recommend using an UnprepSQL method on the way back out (i.e., when you pull data out of the database and display it). So, going in ' would become ''. Coming back out, '' would become '. That will resolve the problem.
Good luck!
Sean McCormack
|
|
|
|
|
Respected!
also i hav got mi problem's solution from respected Mr Heisen but i still couldnt solve mi prob...now i m pasting the code of deleting the row from related tables....plz check mi code n identify the mistake if u find...
SqlDataAdapter DeleteDA = createTeacherDA Conn);//function call
SqlDataAdapter DeleteDetail = createTeacherDetailDA(Conn);//function call
DataRowCollection rc = TeacherDetail.Rows;
rc[0].Delete();
//d.Delete();
//Teacher.Rows.Remove(rc.Find(1,);
//DeleteMemberDs.Teacher.Rows[0].Delete();
AddMember am = new AddMember();
DeleteDA.Fill(am.AddTeacherDs,"Teacher");
DeleteDetail.Fill(am.AddTeacherDs,"TeacherDetail");
DeleteDA.Update(am.AddTeacherDs,"Teacher");
DeleteDetail.Update(am.AddTeacherDs,"TeacherDetail");
DeleteMemberDs.AcceptChanges();
Meroo
|
|
|
|
|
Hi everyone,
I am trying to do a select sub-query, but for some reason it is not working out.
I have the following stored procedure:
CREATE MyProcedure
@newID int,
@oldID int
AS
INSERT INTO MyTable(ExperimentID, Threshold, Energy) VALUES(@newID, Select Threshold, Energy from OldTable where ExperimentID = @oldID)
This mixing of a given value with a subquery does not seem to work. However, there must be a mechanism to do this. Does anyone has some experience with something like this and can maybe help me.
Thanks a lot.
Cheers,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
|
 i am using MSDE and i'm trying to create new database but the connection security option is always Integrated Security is that means that anybody can get access to my data and if ues how can i protect my data.
|
|
|
|
|
If Integrated Security is set to SSPI, MSDE uses Windows Authentication - it takes your system logon credentials as being the credentials to log on to the database with. If you don't set Integrated Security, you must provide a username and password.
In the Integrated Security mode, MSDE uses the local system's Windows password database if the connecting process is using a local account, or contacts the domain controller if using a domain account. In the Mixed Mode ('Use a specific user name and password'), the user names and passwords are stored in MSDE's msdb database. The MSDE install defaults to Windows Authentication mode - an attempt to connect without using Integrated Security fails.
Integrated Security is generally considered to be more secure than specifying a username and password in a connection string.
Even this isn't the whole story, because logins (how you connect to the server) are mapped to server roles on a per-server basis (which control things like being able to BULK INSERT, adding other users, shut down the server), and to users at a database level. Users can be granted permissions to create tables, select data, insert data, execute stored procedures, etc. SELECT, INSERT, UPDATE and DELETE permission is granted on a per-table basis; EXECUTE permission is on a per-procedure basis.
To simplify managing users, databases also have roles, which are the equivalent of Windows groups. Roles and users can be either be Granted permission, explicitly Denied permission, or neither. If a user or one or more of their roles has Grant permission, and neither the user nor any roles have Deny permission, permission is granted. This allows you to say 'Grant all members of the public role permission, except Bill'.
The system comes initially configured with BUILTIN\Administrators (the Administrators group on the local machine) and sa logins, which are both made members of the Server Administrators server role. They are both mapped to the 'dbo' user in the master and model databases; dbo is a member of the public and db_owner roles in these databases. By default public has no permissions granted or denied; db_owner has all permissions granted (this cannot be altered).
If you have the full SQL Server, I recommend using Enterprise Manager to edit configurations. Otherwise, you need to look at the sp_addlogin and sp_grantlogin system stored procedures, for starters.
|
|
|
|
|
i m a beginner in .net, especially in ado.net..i want to del a row from a table having one parent table and one child table...i m trying through delete method of datarow class but couldnt do...
one exception occurs wen i use delete method that the table has no row at given index...plz tell me wat to do??????
meroo rana
|
|
|
|
|
It means that you specified an index which is bigger than DataTable.Rows.Count. If you want to delete the last row in the collection you have to use the index of DataTable.Rows.Count - 1. Remember that the Count tells you how many rows you have, but the collection starts from 0 and not 1.
The other thing, when perfoming a certain operation on a table which is in a relationship with another table, you have to follow a little guidline:
1. When inserting, insert first to the parent table and then the child table.
2. When updating, update the parent table first and then the child table.
3. When deleting, delete from the child table first and then from the parent table.
Regards,
Husein
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
