|
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
|
|
|
|
|
hi there i am working whit SQL DB
i have a data grid whit lines of kategorys
i want to stand on the line in the data grid
an then i want to prees the delBtn
an i want the line that i am standing on going to be dlete
how can i do it ?
icq 47305886
|
|
|
|
|
I am using MSDE.
I don't have TextCopy utility when I am using MSDE. What I should do to insert and retrieve image data/binary data from the database ?
|
|
|
|
|
Microsoft SQL server version 7.0
---------------------------------
I have to find out a way..where in the login id and passwords can be stored
in more secured way..
I browsed the netb and found out the following information..does this apply to sql server 7.0 ?
"
Passwords are stored in the sysxlogins table in encrypted form.SQL Server uses an undocumented function, pwdencrypt() to produce a hash of the user's password, which is stored in the sysxlogins table of the master database.
When a user attempts to authenticate to SQL Server several things happen to do this. Firstly SQL Server examines the password entry for this user in the database and extracts the "salt" - 84449305 - in the example. This is then appended to the password the user supplies when attempting to log in and a SHA hash is produced. This hash is compared with the hash in the database and if they match the user is authenticated - and of course if the compare fails then the login attempt fails.
The user's password is converted to it's UNICODE version if not already in this form.The salt is then appended to the end. This is then passed to the crypt functions in advapi32.dll to produce a hash using the secure hashing algorithm or SHA. The password is then converted to its upper case form, the salt tacked onto the end and another SHA hash is produced."
This methodos storing is adopted by SQL server..
in this method...hacking the passwords sounds so easy!
so..
Can someone suggest a better way for storing the passwords and the login information..
storing it in teh registry is also not safe .,I guess!
thank u..
ranjani
|
|
|
|
|
|
Unless someone has found a way of generating passwords from their salted hash value, I don't understand what the problem is....
Steve S
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
