|
Assuming your ID represents sequential order, you can use correlated sub queries to solve the problem. Something like this may work for you:
SELECT
id,
ISNULL(
totalMiles - (SELECT totalMiles
FROM Mileage
WHERE id= (SELECT MIN(id)
FROM Mileage
WHERE id > BaseMiles.id)),0) AS diff
FROM Mileage BaseMiles
ORDER BY id ASC
|
|
|
|
|
Hello Dears
>>>insert into tablename select * from DBName..TABLENAMEby using this Query i can insert datas from another database in the same machine. But when i am connect to remote machine using the following Query
insert into LOCAL SYSTEM TABLENAME select * from REMOTE SYSTEM NAME.REMOTE SYSTEN DATABASE..REMOTE SYSTEM TABLE NAME
i am getting error
"Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"DBNAME"."TABLE NAME"'"Please any one give me idea....
N.Rajakumar B.E.,
Application Developer,
|
|
|
|
|
When you are using a remote connection you have to specify the table-owner. Default this is dbo
So, what you can try is:
<br />
insert into tablename select * from [Remote System Name].[Remote Database].dbo.[TableName]<br />
Wout Louwers
|
|
|
|
|
Hi
I have a table which contains member_id as string field. In this field the values will be like
1.1.2
1.1.2
1.1.2.1
1.1.2.2.1
1.1.2.2.2 etc....
When i want to insert a new member i want to isert existig member's id + 1 .
Eg: if i select 1.1.2 as my manager my id should 1.1.2.3. Do you have any idea about this. Expectng your earliest reply
UK_Nair
|
|
|
|
|
You can do this in an preatty way :
In the server side of your application set the ID manually like this :
1) querry the database to return a single ID (TOP 1 if I remeber right) and sort the return descending.
Select member_id Top 1 From myTable Order By memberId DESC
2) do your stuff with the old ID an et the new intém's ID
3) add your new item
I hope you understand...because is a rough world out there...
|
|
|
|
|
This is probably a very simple problem but it's kicking my ass so I'll get right to it
I have a database table running of ms sql server 2000 which contains amoung other things three columns:
a DateTime which should hold only the date
a DateTime which should only hold a time
a DateTime which should also only hold a time
I need the three seperate DateTimes since the first is used in a storedprocedure to extract records based on the current day
the other two to be displayed in a datagrid as a start and stop time for an event or appointment.
now i can open enterprise manager and open the table directly and the start and stop datetime columns show the time just as i imputed it.
however as soon as my data adapter fills the dataset the datagrid displays an arbitray date (30/12/1899) but does not display the times.
is this something i have to fix on the database side with some input formating or can i control how a datagrid displays dates and times
any help would be seriously appreciated
Ryan
"It's nice to know if I ever need attention all I have to do is die."
|
|
|
|
|
Your problem may be because your faulty way of inserting data into the data base.
Perhaps some code example on how you insert and querry the data base wold help.
I hope you understand...because is a rough world out there...
|
|
|
|
|
The data is already in the db i've tried entering the fields that require just the time and not the date with:
1. only the time
2. the same date as the date only field + the time
the data all looks fine in the database itself
the problem is when the dataadapter fills my dataset (which is a auto generated typed dataset) the fields for the time only items still only show the date regardless if i entered it as 1 or 2 above
here's my stored procedure to look at too if that helps
ALTER PROCEDURE dbo.GetJobs
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS SELECT Job.JobID, Job.JobTitle, Job.JobTimeStart, Job.JobTimeStop
From Job
/* SET NOCOUNT ON */
RETURN
now running this stored procedure in vs returns only the time as they were entered into the db
now if i use the preview data option in vs for the data adapter suddenly that arbitrary date shows up but the time is gone.
also when i bind it to a datagrid control the same happens
when i tried binding one of the members that should return a time to a listbox it shows the same arbitray date plus the correct time.
so that tells me my stored procedure is working and even though it isn't showing everything the dataadapter is filling the dataset with the date and time for the db members correctly
so how do i change how the datagrid displays dates and times is it something i have to do at run time and if so what
Ryan
"Smile often!.... it distracts people while you load the gun."
|
|
|
|
|
Im not sure how much this would help, but have you tried the Format command when extracting the data?
Format(date, "hh:mm") as newdate {this is off the top of my head so its probally wrong, but should get you close}
Also does this help at all?
How to Set the Day/Month/Year Date Format in SQL Server[^]
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Hey all,
I have DataRelation object between 2 tables & 2 DataGrids.
for some reasons, I delete the relation, activate an operation on the tables & then create the relation back. here is some code:
//before the operation starts:
Ds.Relations.Clear();
Ds.Tables["Cust2"].Constraints.Clear();
//after the operation
DataColumn dc1 = Ds.Tables["Cust1"].Columns["Id"];
DataColumn dc2 = Ds.Tables["Cust2"].Columns["Id"];
DataRelation Dr = New DataRelation("cust1to2", dc1, dc2);
Ds.Relations.Add(Dr);
everything is ok until now.
but when I tried to connect the tables to the datagrids:
DtGrd1.SetDataBinding(Ds, "Cust1");
DtGrd2.SetDataBinding(Ds, "Cust1.cust1to2");
I get the exception:
"Cannot have a relationship between tables in different DataSets"
does anyone know what is going on??
thanks,
RoyRose
|
|
|
|
|
HI:everyone
like this
declare @myid smallint
select @myid = 55
kill @myid -- it general some errors
How can i do it ?
POWER
|
|
|
|
|
Try:
declare @myid smallint <br />
select @myid = 53 <br />
<br />
declare @sql varchar(30)<br />
<br />
set @sql = 'kill ' + cast(@myid as varchar(20))<br />
<br />
exec (@sql)
Wout Louwers
|
|
|
|
|
Hi Guys..
I want to ask if there is a way to use *.sql files generated by MsSql Server (or written by someone )in the C#.
For e.g. i have a *.sql file and i want to create tables in my program using this script..
Is there any class in C# or u guys can give me a link ?
thx a lot..
good coding..!
|
|
|
|
|
|
hi all,
i have an procedure as follows:
ALTER PROCEDURE dbo.InsertNewSite
(
@param_url nchar(250),
@param_ip nchar(15),
@param_username uniqueidentifier,
@param_time datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO sites
(url, ip_address, username, access_time)
VALUES (@param_url,@param_ip,@param_username,@param_time)
RETURN SELECT id FROM sites WHERE sites.url = @param_url AND sites.access_time = @param_time
and it is called from code like this:
string newSiteId = "";
lock (this.selectAllSitesNotBannedTableAdapter)
{
newSiteId = (string)this.selectAllSitesNotBannedTableAdapter.InsertNewSiteNotBanned(host, requestor, new Guid("bcadf8d7-c96d-4981-bf26-de9915ac6d49"), DateTime.Now);
}
but newSiteId always returns null?
now to explain the problem i am trying to solve with this code:
i get some data i want to store into the db, the db generates the GUID for the new row, and i then need that GUID in my application. so rather than reloading the entire datagrid, i just add the row to the grid that i just added to the db.
is it possible to get the GUID of the row just added, in one call to the db, or do i need to call it again with the select that is the return statement above ?
kind regards,
g00fy
|
|
|
|
|
Hi all !
in asp.net(using c#) i Plan this question ,and I think it related to this section , because i get no reply yet , Hope anyone help me !
consider this :
First Record is --> firstname :Joe , lastname :Meech
(in asp.net)i wrote this code for save and add :
For Add :
txtName.Text=String.Empty;<br />
txtFname.Text=String.Empty;
and For Save :
DataRow dr=dt.NewRow();<br />
dr["firstname"]=txtName.Text;<br />
dr["lastname"]=txtFname.Text;<br />
dt.Rows.Add(dr);<br />
da.Update(dt);<br />
dt.AcceptChanges();<br />
int RowTablePosition=0;<br />
RowTablePosition =dt.Rows.Count-1;
My difficult is :
if Page.DataBind() , and if want to add a new Record , and then Save this Record --> the First Record is added to the end of the table , No this New Record !!
But if Page or Text is not bind , this new Record is Saved !
i want to save , when "Page.DataBind();"
thanks if anyone help me !
mostafa hosseiny b.
|
|
|
|
|
hello to all ..
i am trying to install MS SQL SERVER 2000 on my pc ...
i have the Standard Edition
but when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
|
|
|
|
|
microuser_2000 wrote: when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
The error message is correct. SQL Server 2000 Standard Edition will NOT run on Windows XP.
See the SKU Matrix on the SQL Server 2000 Product Overview[^] page for more details.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hello again, everyone.
I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I've tried the following:
SELECT REPLACE(Name, ''', '%#39;') FROM Table
which it obviously doesn't like because of not having matching sets of single quotes. I've also tried replacing it with the ASCII equivalent to no avail, as well.
The particular error message I'm receiving in my ASP.Net page is:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'BRIEN'. Unclosed quotation mark before the character string ''.
The record in question returns the string O'BRIEN.
It is worth noting that the operation is not aborted when this error happens, and it does in fact return the record in the proper format. It does, however, not return any records after that one, which exist.
Any ideas? Thanks in advance for your help.
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote: I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I'm not sure I understand the logic of your thinking. How would using REPLACE() tolerate single quotes?
It looks like the code is venturing into a high possibiliy of an Injection Attack. See SQL Injection Attacks and Tips on How to Prevent Them[^]
If you are issuing a SQL Statement from a .NET application like this:
SELECT * FROM MyTable WHERE Name = 'O'Brian' then you should use a paramter instead
SELECT * FROM MyTable WHERE Name = @Name
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi
when i m working on reporting services' rdl file, following error is coming -
The table ‘Table1’ has rows that contain a different number of cells than the number of the columns in the table (including cells that span more than one column).
I think i m trying a bit too complex interface with rdl files, lot of merged cells are used, i may rpovide html prototype of the work i m doing but it'd b better for me if u can give me some hint by reading error only.
Thanks in advance
Utsav
|
|
|
|
|
I have a WebForm that will display paging data to customers.My purpose is
to let the customers can select any page number.The WebForm calls a stored
procedure in SQL2000:
ALTER PROCEDURE MyProc<br />
(@RecordPerPage int,<br />
@PageNumber int<br />
)<br />
AS<br />
select top @RecordPerPage *<br />
from MyTable<br />
where KeyField><br />
select Max(KeyField) from <br />
(select top @RecordPerPage*(@PageNumber-1) KeyField<br />
from MyTable<br />
order by KeyField) a<br />
order by KeyField
Unfortunately,this procedure has a syntax error.It seems that the 'TOP'
keyword can not be followed by an Expression.
How could I do?
I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and --
This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging.
Need your help!
Thanks a lot!
|
|
|
|
|
You should build your SQL statement as a nvarchar and then use sp_executesql:
<br />
DECLARE @query NVARCHAR(500)<br />
<br />
SET @query = N'select top ' + CAST(@RecordPerPage AS NVARCHAR(5)) + N'* from MyTable...<br />
<br />
sp_executesql @query <br />
look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
--> Nick
|
|
|
|
|
 hi
I am having problem in procedure i am getting error as 'Scope_Identity' is not a recognized function name.I read an article that @@IDENTITY should be used is it helpful.The procedure is for the inserting the details.
thanks
kal2na2
|
|
|
|
|
Sounds like you're using SQL Server v7 which does not support SCOPE_IDENTITY() - you must use @@identity however be careful, triggers will alter @@identity to reflect the result of the trigger and not your proc.
--> Nick
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
