|
|
In case you don't know, all capitals is considered to be shouting. Don't be rude like that, and no textspeak.
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
Hi,
I have created a DataSet(dsLocal) containing rows from local database that I wish to update with the rows taken from another dataset (dsWeb).
dsWeb dataset may contain Newly added rows and/or modified rows.
dsweb is populated from the Web server Database and dsLocal is populated from Local database
Both Local and Web server database schema are same.
What dataset methods/properties should i use?
|
|
|
|
|
You will need to loop through the tables in the webservice dataset. For each table you will need to loop through each row. Then you need to check if the row already exists in the correlating local dataset/table. If so, update the row, if not add a new one. I've never found a very nice way to move a row from one table to another. The only way that works for me is to just loop through the columns and move the data column by column. Sort of like this:
For Each col As DataColumn In dtOld.Columns<br />
If dtNew.Columns.Contains(col.ColumnName) Then<br />
rowNew(col.ColumnName) = row(col.ColumnName)<br />
End If<br />
Next
Hope this helps get you started.
|
|
|
|
|
Thanks for your reply.
I will work out the solution provided by u.
And I will let u know if any queries are there.
|
|
|
|
|
Thanks,
Will you please tell me what is this rowNew and row
rowNew(col.ColumnName) = row(col.ColumnName)
Waiting for your reply
|
|
|
|
|
Oops. Sorry. I should have clarified that. The rowNew is a DataRow object. You will want to set it equal to the new datatable's NewRow in order to give it the schema. Like this:
Dim rowNew as DataRow = dtNew.NewRow
The row is also a DataRow object, but it is from the old table. You will want to loop through each row, and then for each row loop through the columns like I showed you before. Like this:
For Each row as DataRow in dtNew.Rows<br />
<br />
'The column loop goes here<br />
<br />
Next
Hope this helps.
|
|
|
|
|
Hi,
I wrote this update query. But it doesn't work.
The error message : An aggregate may not appear in the set list of an UPDATE statement.
Here is my query.
update udtStudent set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
i wish to update the score = sum of studentscore from udtQuestionHistory
the problem is at sum().Can anyone advice me how should i overcome it?
Thanks in advance.
thanks.
|
|
|
|
|
Eunice (VB junior) wrote: set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
Try to bring the sum in a select clause and assign it to an integer variable.
For instance,
Declare @intSum INT
select @intSum = sum(score) from student a inner join history b on a.testid = b.testid
Now, @intSum will hold the sum of studentscore from history table.
Later, proceed with your update statement like
update student set score = @intSum where ##your condition##
Hope that thought might help you.
|
|
|
|
|
Hi CS,
thanks for the guidance. I got what you meant.
Here is what i did according to your advice and it works.
Declare @intSum INT
declare @testID int
declare @empno int
select @intSum = sum(studentscore),@testid=testid,@empno=empno from udtquestionhistory
group by empno,testid,questype,testtype,partnumber,process
update udtstudent set score=@intSum where testid=@testid and empno=@empno
Thanks for the help.
|
|
|
|
|
You've already asked this question once. Please don't double-post.
Paul Marfleet
|
|
|
|
|
Hi pmarfleet,
Actually there are different questions. The 2nd question is about subselect. But i'm using the same table as my query testing. Anyway, thanks for the input.
|
|
|
|
|
i have a datetime column field in my table, after declaring it as datetime , i saw the following options under it
description
default value
precision
scale
identity
identity seed
similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: similarly there is a function
how can i pass a funciton name in order to enter the date in teh sql server in the following format?
12-jan-2000
As Colin explained in your last post, this is presentation issue. Datetime field can't store values like what you expect, but it can be done by converting it to the format what you needed. See the following query to understand how to convert datetime value.
SELECT CONVERT(VARCHAR(20),GETDATE(),106)
Hope this helps
|
|
|
|
|
hi all,..
i want to retrive each row with the help of pointer and also the access row ll go to last one. for example
have one table name like sam. that table contain three rows like
proId proName price
------- ------- ------
P101 BOOK 600
P102 PEN 200
P103 PAPER 100
In this table i retrive first row for first request. in next req i want to access sec row as well as the first row goes to last etc..
the output like,.
in sec req my table like following format,
proId proName price
------- ------- ------
P102 PEN 200
P103 PAPER 100
P101 BOOK 600
third req my table like,.
proId proName price
------- ------- ------
P103 PEN 200
P101 PAPER 100
P102 BOOK 600
if anybody have solution reply me asap,,
regards,
syibu...
|
|
|
|
|
Hi,
I wish to update 3 data to udtstudent. These 3 datas are taken from udtQuestionHistory.
How should i write?? I tried the below query. But it doesn't work. Can anyone help me?
Thanks in advance.
my query:
update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)
where TestID =1 and EmpNo=35
Thanks.
|
|
|
|
|
Eunice (VB junior) wrote: update udtstudent set(score,Testdate,MarkDate)in(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)
where TestID =1 and EmpNo=35
Try this:
update udtstudent set a.score=b.scoreSum,a.TestDate=b.Testdate a.MarkDate = b.MarkDate from udtstudent a,(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid) b where a.TestID =1 and a.EmpNo=35
I didn't test it.
Also check your relationships and make certain that only one row is returned from the sub query!
|
|
|
|
|
Hi EvilNoodle,
Thanks for the answer. Initially it doesn't work. But i altered a bit from ur query and it works already. Thanks.
Here is the query after modified.
update udtstudent set score=b.scoreSum,TestDate=b.Testdate,MarkDate = b.MarkDate
from udtstudent a,(select sum(studentscore)as scoreSum,TestDate,MarkDate from udtQuestionHistory
where TestID =1 and EmpNo=35 group by empno,testid,testdate,markdate) b
where a.TestID =1 and a.EmpNo=35
Thanks for the help.
|
|
|
|
|
Try this:
<br />
update udtstudent <br />
set(score,Testdate,MarkDate)=<br />
(select sum(studentscore),TestDate,MarkDate from udtQuestionHistory where TestID =1 and EmpNo=35 group by empno,testid)<br />
where TestID =1 and EmpNo=35 <br />
You always pass failure on the way to success.
|
|
|
|
|
Hi GuyThieBaut,
Thanks for the suggestion. But it doesn't work. Anyway appreciate that.
-- modified at 22:21 Monday 8th October, 2007
|
|
|
|
|
Okay you're welcome (for the code that did not work ,
Well done on cracking this one
You always pass failure on the way to success.
|
|
|
|
|
In my sql server database , i want the datetime field to store the data in the following manner
12-Jan-2007
how can i do it ?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
That is a presentation issue. Dates cannot be stored in any particular format, just the one the database defines (which I think is a long integer of some kind)
|
|
|
|
|
You can not store datetime in any format but if you want to store datetime in "12-Jan-2007" format you have to take varchar datatype instead of datetie datatype.
Other way is you can get datetime in "12-Jan-2007" this format in asp.net by String.Format Method.
bEST rEGARD
pATHAN
please don't forget to vote on the post that helped you.
|
|
|
|
|
i am using the select query in the database in order to extracts the record from the database, can i use the convert methed there to get the format like
12-jan-2000?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
