SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Updating table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  10:59:07  Show Profile  Reply with Quote
Hi ,
I am very new to this.
I need help updating a table. Here is what I am doing.

I have a table("TransferRequest") that has some records and Date_Transferred field.. Every 10 mins I need to check a view("v_UnTransferred") that checks the untransferred requests from the table and transfer them.
After this process, the Date_Transferred field in the table needs to be updated with the time it was transferred.
Here is my code:


Dim rsSched As New ADODB.Recordset
Dim cmd As New ADODB.Command

rsSched.CursorLocation = adUseClient
rsSched.CursorType = adOpenForwardOnly
rsSched.LockType = adLockReadOnly

rsSched.ActiveConnection = Tconn

rsSched.Source = "SELECT * FROM v_UnTransferred"
rsSched.Open

rsSched.ActiveConnection = Nothing

'''''''The transfer code is here.


cmd.CommandText = "UPDATE TransferRequest SET Date_Transferred = CONVERT(datetime, '" & Now & "', 102)WHERE Date_Transferred IS Null"
cmd.Execute

rsSched.Close

Set rsSched = Nothing
What am I doing wrong?

Thanks for your help
Sherry

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/01/2006 :  11:01:59  Show Profile  Visit spirit1's Homepage  Reply with Quote
do this with the scheduled job.

If you want to do this in code you'll have to keep the application running constantly with the timer.




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  11:08:15  Show Profile  Reply with Quote
Thanks spirit1 for your reply. I am using timer to do this. But I am getting error while updating the table.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 11/01/2006 :  11:21:40  Show Profile  Reply with Quote 
cmd.CommandText = "UPDATE TransferRequest SET Date_Transferred = getdate() WHERE Date_Transferred IS Null"

isn't this easier ?

KH
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/01/2006 :  11:24:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
well that depends on which time you want to have.

SQL server's time or web servers time. this can be a difference if they're in differnet timezones.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 11/01/2006 :  11:30:39  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

well that depends on which time you want to have.

SQL server's time or web servers time. this can be a difference if they're in differnet timezones.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp


Oh yeah. Forgot that i am not in a timezone-challenged area.

KH
Go to Top of Page

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  11:31:27  Show Profile  Reply with Quote
This is the error I am getting

Run-time error ‘3709’:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/01/2006 :  11:41:46  Show Profile  Visit spirit1's Homepage  Reply with Quote
you're not setting the connection to your command
so there is no connection to run this query against.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  11:43:49  Show Profile  Reply with Quote
Can you please show that in code? Thanks
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 11/01/2006 :  11:50:44  Show Profile  Visit spirit1's Homepage  Reply with Quote
here's an example of how:
http://www.freevbcode.com/ShowCode.asp?ID=3687

apply that to your case.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  12:25:39  Show Profile  Reply with Quote
Thanks. I'll work on it.
Go to Top of Page

SherryT
Starting Member

6 Posts
Posted - 11/01/2006 :  13:19:56  Show Profile  Reply with Quote
I got it. I forgot to add
cmd.ActiveConnection = Tconn

Thank you spirit1.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000