|
Thank you very much for your reply...
But still I am not able to find the exact answer.
Regards,
Arun Kumar.A
|
|
|
|
|
What have you tried so far?
try this...SELECT ... FROM...GROUP BY ID HAVING value > 0
<< >>
|
|
|
|
|
At last, I have found it:
SELECT A.* FROM
(
SELECT B.ID, B.Date,
RANK() OVER (PARTITION BY B.ID ORDER BY B.Date DESC) AS RowRank
FROM tblName B WHERE
B.Value>0 AND Date<CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
GROUP BY
B.ID, B.Date
) A WHERE RowRank<=5 ORDER BY ID
Regards,
Arun Kumar.A
|
|
|
|
|
the best soultion is create a cursor and put cursor in stored procedure(SP) , finally call SP
-------------------------------
declare @vdd bigint
declare C1 cursor for Select distinct id from pdata
open C1
drop table #temsales
CREATE TABLE #temsales ( col1 bigint, col2 datetime, col3 bigint )
fetch next from c1 into @vdd
while @@fetch_status=0
begin
insert into #temsales select top 5 * from pdata where sales>0 and id =@vdd
fetch next from c1 into @vdd
end
close c1
deallocate c1
select * from #temsales
|
|
|
|
|
I mean a resource is created in a thread and now I want to access it from another thread.
|
|
|
|
|
Define "resource" in the current context.
|
|
|
|
|
Hai I am working with Sqlserver 2000, I want Syntax or a sample code for how to write user defined functions using Switch case statement in sql server 2000
Presently working in Esoft consulting ltd as .net developer
|
|
|
|
|
here is the syntax copied from SQL book online
Syntax
Simple CASE function:
CASE input_expression <br />
WHEN when_expression THEN result_expression <br />
[ ...n ] <br />
[ <br />
ELSE else_result_expression <br />
] <br />
END <br />
<br />
Searched CASE function:<br />
<br />
CASE<br />
WHEN Boolean_expression THEN result_expression <br />
[ ...n ] <br />
[ <br />
ELSE else_result_expression <br />
] <br />
END
and here is an example
<br />
USE pubs<br />
GO<br />
SELECT Category = <br />
CASE type<br />
WHEN 'popular_comp' THEN 'Popular Computing'<br />
WHEN 'mod_cook' THEN 'Modern Cooking'<br />
WHEN 'business' THEN 'Business'<br />
WHEN 'psychology' THEN 'Psychology'<br />
WHEN 'trad_cook' THEN 'Traditional Cooking'<br />
ELSE 'Not yet categorized'<br />
END,<br />
CAST(title AS varchar(25)) AS 'Shortened Title',<br />
price AS Price<br />
FROM titles<br />
WHERE price IS NOT NULL<br />
ORDER BY type, price<br />
COMPUTE AVG(price) BY type<br />
GO<br />
<br />
<< >><br />
|
|
|
|
|
Hi there,
Can any one direct me to documentation containing all the internal commands of SQL Server, please. I've used commands like Format in my sql statements with an Access DB, but SQL Server does not support Format as a function or command.
Thanks, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
|
|
Thanks for your reply, I appreciate it.
Regards, Werries
A programmer's life is good... or is it?? Ek dink nie so nie!
|
|
|
|
|
hello
i have written a program which connects to an internet database(mysql) using odbc. the program works fine on a system which is directly connected to the internet but fails to connect to the database on other systems in the lan.
Thank you
|
|
|
|
|
One option I can think of would be to place the system with the database in the DMZ host area on the LAN.
David
|
|
|
|
|
Hi There,
Im having problems making a simple datagridview update.
The datagridview is bound to a dataset.
The three posted examples are in C# and are made in VS 2005 Express.
Im not recieving any errors and i can see the changed records being passed in all three examples when debugging.
Everything looks fine, except that the database is not updated?
Furthermore, i have made sure that the update commands ok.
Which means that the commands are present and does not contain joins.
Refresh table has also been cheched on in the table adapter configuration.
This is supposed to be dead easy..b1"#ch wine moan ...
Here is my examples:
Could someone please give me a clue to what im doing wrong?
Thanks.
//Update method1
//
private void btnSave_Click(object sender, EventArgs e)
{
myDataGridView.BindingContext[myDataSet.myDataTable].EndCurrentEdit();
DataSetType.DataTableType myChangedRows = (DataSetType.DataTableType)myDataSet.myDataTable.GetChanges();
if (myChangedRows != null)
{
myTableAdapter.Update(myChangedRows); //myChangedRows as DataSetType.DataTableType
myDataSet.myDataTable.AcceptChanges();
}
}
//Update method2
//
private void btnSave_Click(object sender, EventArgs e)
{
myDataGridView.BindingContext[myDataSet.myDataTable].EndCurrentEdit();
if (myChangedRows != null)
{
for (int i = 0; i < myChangedRows.Rows.Count; i++)
{
//Using custom SQL-Comand UpdateRow
myTableAdapter.UpdateRow((int)myChangedRows.Rows[i].ItemArray.GetValue(0), ((Guid)myChangedRows.Rows[i].ItemArray.GetValue(4)));
}
myDataSet.myDataTable.AcceptChanges();
}
}
//Update metohod3
//
private void btnSave_Click(object sender, EventArgs e)
{
myDataGridView.BindingContext[myDataSet.myDataTable].EndCurrentEdit();
if (myDataSet.HasChanges())
{
DataRow[] myChangedRows = (DataSetType.DataTableType)myDataSet.myDataTable.GetChanges();
myTableAdapter.Update(myChangedRows); //myChangedRows as array of DataRow
myDataSet.myDataTable.AcceptChanges();
}
}
-- modified at 14:37 Friday 18th May, 2007
|
|
|
|
|
After having creating several other test projects in an attempt to update my database, i realized that the sql database file (.mdf) is copyed to the output directory everytime the program is being debugged. Very nice...
When setting the copy option to "copy if never" im able to update the database.
I dont understand why this "copy if never" option is not set by default?
Can anybody explain why i can only select filebased databases when creating new data sources?
Is this a vs2005 Express limitation?
Adding a connection string manually to the app.config works however.
Cheers
|
|
|
|
|
Hi
I trying to populate GridView with a DataSource and I am getting a Null Reference Exception at "dsource.ConnectionString"
My method is something like this.....
SqlDataSource dsource = null;
if (!Page.IsPostBack)
{
dsource = new SqlDataSource();
dsource.ConnectionString = ConfigurationManager.ConnectionStrings ["Provider=SQLOLEDB.1;User ID=sa;Password=sa;Initial Catalog=ESS;Data Source=ESS134"].ConnectionString;
dsource.DataSourceMode = SqlDataSourceMode.DataReader;
dsource.ProviderName = "System.Data.OleDb";
dsource.SelectCommand = "SELECT * FROM StateUSA";
gvState.DataSource = dsource;
gvState.DataBind();
}
Please assist me in this regard.
Thank U
Chandu
|
|
|
|
|
If you step through the above code with the debugger, you will find one of the objects is null. I am guessing that it is the connection string. Normally when you access the connection string. If it exists in your config file under connectionStrings you just need to enter the name of the key so your code would be ConfigurationManager.ConnectionStrings["keynamehere"];
You are actually giving the full connectionstring name. So your code should be:
dsource.ConnectionString = "Provider=SQLOLEDB.1;User ID=saassword=sa;Initial Catalog=ESS;Data Source=ESS134";
By the way it is a really bad idea to have the sa password be sa.
Hope that helps.
Ben
|
|
|
|
|
The Following Procedure is Supposed to create a new Record in the Table, but what it does it alter the First record in the table and change the values i have supplied in the current record. am taking those values from VB6 form.
Create Procedure prcInserting @Num_key varchar(10),@Extension int,@Cell_ID int,
@Actual_Extent float,@Lis_key varchar(50), @Func_key varchar(8)
with recompile
as
insert into Property(Num_key,Extension,Cell_ID,Actual_Extent,Lis_key,Func_key,Active,Add_date,Add_User_ID,Spatial_ADD_Date,Rateable,Non_Discreet_Valid)
values (@Num_key,@Extension,@Cell_ID,@Actual_Extent,@Lis_key, @Func_key,0,getdate(),1,getdate(),0,0)
Please Help
Vuyiswa
|
|
|
|
|
This will create the record in the database as an insert does not do an update of a record (unless you have done something insane like use a trigger to perform an update). Have you actually looked inside the table to see if the record is added or not? Don't rely on the order of the table that is returned by a plain SELECT... to ascertain whether or not the record has been added.
If I were you, I would do a count on the rows already present. Then run the procedure, and finally do a count again. If the row count is different then it's successfully inserted.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hERE ARE THE COUNTS
/*First Count*/
537893
/*Second Count*/
537895
As you can see the Count is increased by two. what can be a Problem Because i dont have anty update trigger in the table
Vuyiswa
|
|
|
|
|
Well - it's obviously inserting. It sounds like you may have fired the SP twice here.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
i used Vb6 front end to fire the Proc, when save button is click the it become disabled and the recordset move to the First record, so there is no way to be fired twice.
what do you think is Wrong? Do you want me to send you the vb6 code?
Vuyiswa
|
|
|
|
|
Vuyiswamb wrote: what do you think is Wrong? Do you want me to send you the vb6 code?
No.
If I were you, I would take a look at using the SQL Profiler to see what is actually coming across. For an example of how to do this, read this article Clickety[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
AM NOT SURE, I THINK THE pROBLEM IS IN MY BUTTON, THIS IS THE CODE IN MY BUTTON TO SAVE .
Private Sub cmdAdd_Click()
rrs("Num_key") = txtnumkey
rrs("EXTENSION") = txtextension
rrs("Actual_Extent") = txtactualextent
rrs("Lis_key") = txtliskey
rrs("Func_Key") = txtfunckey
rrs("Prop_Category_ID") = txtcategory
rrs("GEOCODE") = txtgeocode
rrs("Add_Date") = txtadddate
rrs("Add_user_ID") = txtadduserid
rrs.Update
cmdNew.Visible = True
cmdUpdate.Visible = True
cmdRefresh.Visible = True
cmdDelete.Visible = True
End Sub
IS IT CORRECT?
Vuyiswa
Vuyiswa
|
|
|
|