Database Discussion Boards

Michael Potter21-Nov-05 4:09

21-Nov-05 4:09

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

Query to insert datas from Remote System's Sql Server

Rajkamal_dfine20-Nov-05 18:26

Rajkamal_dfine

20-Nov-05 18:26

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,

Re: Query to insert datas from Remote System's Sql Server

WoutL21-Nov-05 5:39

WoutL

21-Nov-05 5:39

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

sql - Dynamic ID creations

UK_NAIR20-Nov-05 17:56

UK_NAIR

20-Nov-05 17:56

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

Re: sql - Dynamic ID creations

Stanciu Vlad20-Nov-05 22:47

Stanciu Vlad

20-Nov-05 22:47

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...

Problems Formating Datagrid Output

TofuBug2420-Nov-05 14:21

TofuBug24

20-Nov-05 14:21

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."

Re: Problems Formating Datagrid Output

Stanciu Vlad20-Nov-05 22:41

Stanciu Vlad

20-Nov-05 22:41

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...

Re: Problems Formating Datagrid Output

TofuBug2421-Nov-05 1:33

TofuBug24

21-Nov-05 1:33

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."

Re: Problems Formating Datagrid Output

S Douglas22-Nov-05 1:18

S Douglas

22-Nov-05 1:18

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[^]

DataGrid & DataRelation

RoyRose7820-Nov-05 5:03

RoyRose78

20-Nov-05 5:03

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

How to Kill a process in procedure?

GflPower19-Nov-05 20:32

GflPower

19-Nov-05 20:32

HI:everyone
like this

declare @myid smallint
select @myid = 55
kill @myid -- it general some errors

How can i do it ?

POWER

Re: How to Kill a process in procedure?

WoutL21-Nov-05 9:17

WoutL

21-Nov-05 9:17

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

Sql Scripts in C#

da vinci coder19-Nov-05 17:56

da vinci coder

19-Nov-05 17:56

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..!

Re: Sql Scripts in C#

Colin Angus Mackay20-Nov-05 8:13

Colin Angus Mackay

20-Nov-05 8:13

teomanersan wrote:
Is there any class in C# or u guys can give me a link ?

Yes, here is a link to an entry on my blog on the subject: http://blogs.wdevs.com/ColinAngusMackay/archive/2004/10/15/857.aspx[^]

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

returning guid from row jsut added

g00fyman19-Nov-05 15:53

g00fyman

19-Nov-05 15:53

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 = "";
      // add the new site to the database
      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

Save difficult ...!!!

mostafa_h19-Nov-05 4:55

mostafa_h

19-Nov-05 4:55

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.

trying to install SQL SERVER 2000

microuser_200019-Nov-05 3:51

microuser_2000

19-Nov-05 3:51

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 .

Re: trying to install SQL SERVER 2000

Colin Angus Mackay19-Nov-05 10:08

Colin Angus Mackay

19-Nov-05 10:08

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

replace single quotes in SELECT

jszpila18-Nov-05 6:43

jszpila

18-Nov-05 6:43

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?

Re: replace single quotes in SELECT

Colin Angus Mackay18-Nov-05 13:44

Colin Angus Mackay

18-Nov-05 13:44

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

SQL Reporting Services Error

utsav_verma17-Nov-05 19:07

utsav_verma

17-Nov-05 19:07

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

A Transact-SQL Question

Xiaoming Qian16-Nov-05 20:37

Xiaoming Qian

16-Nov-05 20:37

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!

Re: A Transact-SQL Question

Grav-Vt17-Nov-05 12:56

Grav-Vt

17-Nov-05 12:56

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

scope identity not a function name

kal2na216-Nov-05 19:37

kal2na2

16-Nov-05 19:37

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

Re: scope identity not a function name

Grav-Vt17-Nov-05 12:57

Grav-Vt

17-Nov-05 12:57

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.