|
 i dont want the maximum value from the ID column
i want to find out the highest value for a perticuler row in different columns i.e. if i have table with two columns Column1 and column2
Column1 has three rows with values 100, 45, 50
Column2 has three rows with values 101, 25, 40
now i want to find the higest value in first row of Column1 and Column2 which is 101 similarly for second row highest value is 45 and theird row highest value is 50.
so how do i go about doing this
Sasmi
|
|
|
|
|
SELECT ID, CASE WHEN Column1 > Column2 THEN Column1 ELSE Column2 END as Highest
FROM MyTable
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
hi
I like to select certain number of records in the range format from 100 to 200 from the table with condition and displayed in the query analyzer .Which is the best way to achieve it .Earlier I was using Top 200 * from tablename1 where condtion but it is not helping me to get the desired result .
Any other format to retrieve records from the table .A single table is having thousand of records.Using select column names with condition also proved fatal numerous of records are coming
any good query for this
sasi
|
|
|
|
|
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2005-32,GGLD:en&q=sql+server+paging
|
|
|
|
|
I am trying to expand my knowledge of SQL and I am using the MSDE. I am trying to add/import the Northwind database to this server, but I am lost on what to do?? Could someone give me some help?
thanx in advance,
Jude
|
|
|
|
|
Any particular reason for MSDE? SQL 2005 Express is good and SQL Server Management Studio Express CTP makes administering it easy.
Paul
|
|
|
|
|
from what I know 20005 isn't comaptible w/ VS.net 2003
|
|
|
|
|
This is what I did with MSDE. I use a program called DbaMgr V0.55.1 (a free wanna-be Enterprise Manager). I don't remember the link to the program, but I am sure it's one of the first hits one can find get on google. You can use DbaMgr to setup the Northwind database.
I hope this helps,
Paul
|
|
|
|
|
Thanx alot! Worked like a charm!
|
|
|
|
|
TheJudeDude wrote: Worked like a charm!
I am glad I was able to help you.
Have fun getting your feet wet with MSDE.
Paul
|
|
|
|
|
ok, so really, Here's the error message:
"Failed to Connect to the Database!Timeout expired. The timeout period elaspsed prior to obtaining a connection from the pool. This may have occurred because all polled connections were in use and max pool size was reached. at
System.DataSqlClient.SqlConnectionPoolManager.GetPolledConnection(SqlConnectionString options, Boolean&isInTransation)
at System.Data.SqlClient.SqlConnection.Open()
at WindowsApplication1.Form1.dbConnect() in c:\documents and settings\user1\desktop\windowsApplication\form1.cs:line2957"
what do i need to do...i'm enumerating thru a treeView and using the node.text as a servername to create a connection to each pc(treenode). It looks like the connections to the pc's are happening much quicker than the db can store...is this possible?
anyhow i could use some help....
thanks
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Are you making sure that you close connections when you are done with them? (By that I mean that the close goes in a finally block so that it happens regardless of any exceptions that might be thrown (caught or not)
wetrivrrat wrote: WindowsApplication1.Form1.dbConnect()
I'm assuming by the naming here that this is a quick throw away application. If not, you should consider layering the application so that add data access happens in a data layer (a set of classes that are decoupled from the user interface). Or, to put it another way: You should not be doing any database interaction from your form class.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
actually, at this point its working...i found a piece of code that i accidently commented out....
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
How to sort a column in data grid:
I want to sort on the column "LoanNumber"
In my ASP code shown below, I added AllowSorting property to True, in the
DataGrid.Which gives me the hyperlink in the header.
In order to have this happen I need to do two things: create a
sort event handler and specify the event handler for the
DataGrid's sort event.
I created the sub SortEventHandler as below but I am lost as to
what is next to do?
Sub SortEventHandler(sender as Object, e as DataGridSortCommandEventArgs)
...
End Sub
<asp:datagrid id="DG1" runat="server" backcolor="Gainsboro" autogeneratecolumns="false"
onitemcommand="DGColumnBtn"
="" width="512px" allowsorting="True">
<edititemstyle backcolor="Red">
<alternatingitemstyle backcolor="#C0FFFF">
<headerstyle backcolor="Silver">
<alternatingitemstyle backcolor="White">
<columns>
<asp:buttoncolumn text="Details" headertext="Credit Life" buttontype="PushButton">
<asp:boundcolumn datafield="clid" visible="False">
<asp:boundcolumn datafield="CLMaturityDate" dataformatstring="{0:MM-dd-yyyy}" headertext="MAT Date">
<asp:boundcolumn datafield="LoanNumber" headertext="LoanNumber" sortexpression="LoanNumber">
<asp:boundcolumn datafield="Loanamount" headertext="Amount">
Private Sub DG1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DG1.SelectedIndexChanged
End Sub
|
|
|
|
|
kenn_rosie wrote: I created the sub SortEventHandler as below but I am lost as to
what is next to do?
You will probally get an answer in the correct form try the Web Devolopment form[^]
Good luck 
BTW, if you wanted to sort the data before presenting it to the user use the Order By [column] in your select statement.
|
|
|
|
|
Hi,
I wonder if there is any limitation concerning the length of a sql statement.
I have the following situation:
I need to update a single column of a lot of records in a table.
Updating those 20K (20000) records one at the time consumes a lot of time.
In order to speed things up I collect those queries to create one big SQL statement.
Now the problem is the following whenever I place more that 455 update queries only the
first 455 are executed. The others are neglected, because no error has happened (neighter
in my try/catch block or in the SQL Server checked with the profiler). The size of the
sql statement (455 queries) is about 20Kb in size.
Any thoughts on this would be very appriciated
BTW:
I'm developing in VC++ 2005
on a SQL Server V2000
codito ergo sum
|
|
|
|
|
BadKarma wrote: Updating those 20K (20000) records one at the time consumes a lot of time.
In order to speed things up I collect those queries to create one big SQL statement.
Now the problem is the following whenever I place more that 455 update queries only the
first 455 are executed. The others are neglected, because no error has happened (neighter
You did what??? 
You have a big problem in your design. Like you've found out, if one statement fails, you have to consider the entire batch a failure. You can't catch an error because nothing throws one.
Best practice says that you call a stored procedure on the SQL Server to do the update for you and just pass the parameters to it, 20,000 times. The stored procedure can check to see if the update works and RAISEERROR if it failed for some reason. You can catch that error in your code. You also get the benefit of knowing exactly which one of these calls failed.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave Kreskowiak wrote: if one statement fails, you have to consider the entire batch a failure
I know for certain that all queries are valid. If I execute 400 of them in batch there is no
problem, even 455 of them. Only when I exceed the 455 queries 'limit' in the batch, then the last are neglected.
Due to what reason can a valid query be neglected ???
codito ergo sum
|
|
|
|
|
Because the query IS invalid, the last lines are being cut off. There is a limit to the size of a query string you can pass. I think it's 8192 bytes, but I can't find solid documentation on it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
First of all thanks for your time too reply.
The queries i execute look like this:
UPDATE MyTable SET MyField = 1 WHERE Data = 1;
UPDATE MyTable SET MyField = 1 WHERE Data = 2;
UPDATE MyTable SET MyField = 1 WHERE Data = 3;
UPDATE MyTable SET MyField = 1 WHERE Data = 4;
this goes on up until Data reaches 1000.
So like you can see the size of the query is relative small not even 50 characters.
The batch statement though, can reach the size of more the 20k of characters but shouldn't be
a problem because the max bacth size is 65536 * network packet size (see response from Sl0n[^])
When I execute this batch to an limit of 455 queries, there is no problem. The size of this batch is over 8192 byte limit (its arround 18K). When I add more queries to the bacth every query past this 455 limit just doesn't get executed and the batch statement is returned as a success.
So even though I stay well under the limits, those queries aren't executed ???
Or do I miss something?
codito ergo sum
|
|
|
|
|
BadKarma wrote: So like you can see the size of the query is relative small not even 50 characters.
The batch statement though, can reach the size of more the 20k of characters but shouldn't be
With emphasis on "shouldn't". It's not so much the size of the query in characters, but I'm thinking the number of statements in the batch might be your problem. It's not listed in the specs anywhere. There are other minor possibilities, but I've got no time to test them.
Maximum statements cachable in a connection, limits on uncompiled vs. compiled code vs. batch code, ...
What you're doing is just never done outside a stored procedure in the database (at least I've never seen it done), so data on your technique is very hard to come by.
You can try the theory out by batching together statements 400 at a time. ORrrrrrr, you could just write the batch query using an SQL loop, since your data doesn't appear to change from iteration to iteration, just the WHERE clause changes.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 11:46 Monday 30th January, 2006
|
|
|
|
|
SQL Server 2000 has a limit on the batch size, which is 65,536 times the network packet size, which is, by default, 4096 bytes, so what we have is somewhere around 256 Mb. So batch size is not an issue in your case. You may try issuing the same batch from Query Analyzer to see the results.
|
|
|
|
|
I am developing an n-tier application. I am using Business Entities to pass data between the tiers. I have few properties that holds data in form of DataSet. Whenever I need a result set (get all employees of XYZ dept.) I fill the dataset property of my Business Entity and pass the business entity to presentation layer. In presentation layer I bind the dataset to my gridview. Even if I have one record set I am using DataSet to pass the data.
My doubt is what difference it makes on performance if I use DataTable instead of DataSet.
Note:
1) I expect 200 simultaneous users accessing my application. On each request I pass 4-5 DataSet from DataAccess to BusinessLayer to Presentation Layer.
2) On an average I pass 25 records in each DataSet.
regards,
Himalay Oza
|
|
|
|
|
Himalay,
IMO, Datasets are better to bind to a gridview. Assuming you are using SQL 2000/2005 (you didn't specify which database you are using), 200 users at the same time and 25 records in each dataset, you should not have much in concern of performance.
I think your question is a matter of preference.
Paul
|
|
|
|
|
I create a form with a dataset (Fill from 3 dataadpater ).
My form work ok , and have a textbox and datagrid with a relation to my records in textbox.
I create a dataview to filter the records.
After that the textbox don´t change.
I have this code, and it don't work
editNome.DataBindings.Add(New Binding("Text", DsFunc, "TFuncionarios.Nome"))
I try this code , and my textbox show de result.
editNome.DataBindings.Clear()
editNome.DataBindings.Add(New Binding("Text", DsFunc.Tables("TFuncionarios"), "Nome"))
-----------------------------------
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSeguinte.Click
If Me.BindingContext(DsFunc.Tables("TFuncionarios")).Position < DsFunc.Tables("TFuncionarios").Rows.Count - 1 Then
Me.BindingContext(DsFunc.Tables("TFuncionarios")).Position += 1
End If
but another thing appens the data grid don't move when i go to next record.
i think is something with the databinding.
What i must do to make textbox related with my datagrid ???
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
