|
hi i want to make an select statment that contains some data, that is
SELECT Name,ID, Sname,Schools from student
imagine that this table have one student have the same ID and same name but i need the last one how can do that while there are no Primary keys .
can i use grouped by Name alone or i should make a grouped by name,ID,..
Amarni
|
|
|
|
|
Amarni wrote: imagine that this table have one student have the same ID and same name but ... there are no Primary keys
So what is ID if not the primary key?
Amarni wrote: i need the last one
What do you mean by "the last one"? Databases contain sets of data there is no implied order of anything in the database. If you want to make something ordered then it has to be ordered on a column or columns. Which column defines the order?
Amarni wrote: can i use grouped by Name alone or i should make a grouped by name,ID,..
GROUP BY will allow you to aggregate the other columns based on the column or columns you are GROUPing BY. That means you will no longer be able to access ID, Sname, Schools other than through an aggregation function such as MIN(), MAX(), SUM(), AVG() etc.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I use a table named FDetails with 4 text fields and a datetime field named date in MSaccess
when insert values into table ising ado recordset obj I receive some syntax error
recordset->Open("INSERT INTO FDetails(name,path,duration,volume,date) VALUES('hello','location','duration','vol','12/12/2000')",connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,ADODB::adLockReadOnly, ADODB::adCmdText);
any one know inserting date into table
|
|
|
|
|
I have a question SQL Server cursor question
I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table?
Thanks in advance,
Frank
www.TheOpenSourceU.com
|
|
|
|
|
Also, it is important to note that the table i'd be using is a constantly used table (it touches almost every thing)
Thanks again,
Frank
www.TheOpenSourceU.com
|
|
|
|
|
Jawz-X wrote: I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table.
It will not obviously lock the clients table.
SQL Server Books Online:
Cursor Transaction Isolation Levels
The transaction locking behavior of a specific cursor is determined by combining the locking behaviors of the cursor concurrency setting, any locking hints specified in the cursor SELECT, and transaction isolation level options.
Microsoft® SQL Server™ 2000 supports these cursor transaction isolation levels:
Read Committed
SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.
Read Uncommitted
SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.
Repeatable Read or Serializable
SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. This has the same effect as specifying HOLDLOCK on a SELECT statement.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thank you for that information. So, the answer to my question is that, in fact, neither table will be locked.
So, I should probably know this, but please enlighten me and save me some research (I don't use cursors very often, I think I did one once). But why then are cursors to be considered a last resort? Is it simply the amount of resources that they use on the DBMS?
Thank you,
Frank
www.TheOpenSourceU.com
P.S. I like your quote.
|
|
|
|
|
Jawz-X wrote: But why then are cursors to be considered a last resort?
Because they are increadibly slow. I once saw a question on this forum complaining that the code was taking too long (it was using cursors). I suggested a better query to use without cursors and the operation took around one second to complete.
Some things cannot be solved without cursors which is why they exist. But oftentimes the code can be re-written without cursors.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
i m not able to execute ado.net application with sql server
database .
i m getting error as login failed.
i want to use sql server with my asp.net application.as i didnt install sql server in my p.c i dont know user name and pwd.
plz help
|
|
|
|
|
lucky1234560 wrote: i want to use sql server with my asp.net application.as i didnt install sql server in my p.c i dont know user name and pwd.
It is recommended to use Windows Authentication to access the SQL Server. It may be possible that the person who set up SQL Server on your machine did that. Why not speak to them about how they set up SQL Server on your machine? It would seem the most sensible first step.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hello,
It's surely a stupid question, but I have a problem:
I have two tables like A with field´s number and amount and a table B with number and name.
Now I will have a join like 'SELECT ... form A, B where A.number = B.number - but I need the summary of 'amount', even if there does not exist an entry in table B. Is this possible with SQL??
Maiy thanks, Gerhard
|
|
|
|
|
It sounds like you are talking about a JOIN. If you want all of the data from the "left" table even if it doesn't match your SQL would look something like this:
"SELECT amount FROM A LEFT JOIN B ON A.number=B.number"
Or maybe this site could explain it better:
http://www.w3schools.com/sql/sql_join.asp[^]
|
|
|
|
|
Very many thanks for your reply. It's a wonderful help for me. But I have a secon qustion . The solution is great and it works, but can I do it with more than one table?
Someting like: A.numnber1, A.number2, A,amount WHERE A.number1 = B.number AND A.number2 = C.number'.
I have a table with accounting-sentences and there are accounting-numbers, cost-center-numbers and so on. And I want to give the numbers a name (which are stored in different tabels) without loosing the amount of 'value' in table 'accounting-sentences'
Many thanks again, your help was wonderful
|
|
|
|
|
Hello Kschuler,
I found how to do it with more tables. I tried a little bit with the enterprise manager. But i would not have been able to find it without your help. So again many thanks ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) , ciao, Gerhard
|
|
|
|
|
We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:
CREATE PROCEDURE as MyProcedure @myData xml<br />
BEGIN<br />
INSERT INTO MyTable (FirstName, LastName, Notes)<br />
SELECT<br />
MyNode.value('FirstName[1]','varchar(50)'),<br />
MyNode.value('LastName[1]','varchar(100)'),<br />
MyNode.value('Notes[1]','text')<br />
FROM @myData.Notes('Person') as R(MyNode)<br />
END
The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.
The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.
Any ideas?
Jim Conigliaro
jconigliaro@ieee.org
|
|
|
|
|
Try varchar(MAX) instead of text.
|
|
|
|
|
Hello all,
I'm continuing to write a fairly complex application. I have a fair amount of data that I would like to store with the application that will include a lot of lookup data that is both fairly relational and somewhat large (several megs in size). The lookup data will be regenerated periodically. If the amount of data were a little smaller (and a little less in need of strict enforcement of relationships), I'd probably just store it all in XML files. Because of this, I've opted to go with a database to store this data. In addition to the main client application, there are a couple of small apps that will be used to retrieve information to stuff into the database. The database would actually be read-only from the client app and would only be writable from the information gathering applications.
What I'd like to do is set up a database project and have it point to a local database file. I want to source control my scripts for the database and be able to quickly create a copy of the database on a different development machine. It would be even better if I could just move the database around as a file as part of my project. I basically want all the benefits of MSDE / SQL Express with the portability (in the sense of being able to move the files around) of Access.
What would you suggest?
|
|
|
|
|
Use MSDE/Sql Express and employ "detatch database" /"attach database" to copy the database around:
I.e. Detach the master copy of the database. copy the .mdf file (attach will create a new .ldf).
Copy the file to the new server, and on that server "attach" the database, editing the locations for the log and data files as needed. Re-attach the master to your development server and keep working.
The same technique can be used to distribute the final version.
|
|
|
|
|
Hi,
I got the SQL server 2005 Express installed but dunno how to import my data from the MDB files.
Wr do I look for this? Do they have it in on EX Ed?
Tnx
|
|
|
|
|
Good day everyone,
I need to do a Login system in ASP.Net using C#.Net as code behind page for my website.
I've got an Access database conntaining username and password.
Is there anyone know where can I find a sample project?
If you're interested in, pls help
Thanks so much.
eric
|
|
|
|
|
|
I'm having a problem getting my application to connect with an instance of MSDE over the network. I installed MSDE using the following parameters.
INSTANCENAME=MyInstance DISABLENETWORKPROTOCOLS=0 SAPWD=password SECURITYMODE=SQL
I have searched everywhere for an answer to my problem and I have followed all the solutions I can find with no luck. I have made sure the port numbers line up, I have enabled networking protocols, and turned off firewalls.
I can connect to SQL Server just fine over the same network but not to an instanse of MSDE.
SOMEONE HELP PLEASE!!!!!!!!!!!!
|
|
|
|
|
what connection string are you using ?
My second computer is your linux box.
|
|
|
|
|
I'm working on a web app written in vb.net. I recently added a column in the table 'clients'. I had to manually update the .xsd schema file. After doing that, modifying the client info via the web interface stopped working. To fix the problem, in the 'clients_modify' sproc I had to remove some of the where clause criteria (the where clause was previously written to match on all fields). The match on all fields is what is currently in production, and it works. Question - why did I have to remove comparison on 8 of the 25 fields in order for the update to work after the dataset modification?
|
|
|
|
|
NameNotTaken wrote: why did I have to remove comparison on 8 of the 25 fields in order for the update to work after the dataset modification?
Without knowing more about the structure of your database that may be difficult to answer.
A more pertinent question might be why are you trying to match on all these columns when matching on the primary key would do the same job?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
