Updating table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating table

Author

Topic

SherryT
Starting Member

6 Posts

Posted - 2006-11-01 : 10:59:07

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

11752 Posts

Posted - 2006-11-01 : 11:01:59

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

SherryT
Starting Member

6 Posts

Posted - 2006-11-01 : 11:08:15

Thanks spirit1 for your reply. I am using timer to do this. But I am getting error while updating the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-01 : 11:21:40

[code]cmd.CommandText = "UPDATE TransferRequest SET Date_Transferred = getdate() WHERE Date_Transferred IS Null"[/code]
isn't this easier ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 11:24:19

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-01 : 11:30:39

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.

SherryT
Starting Member

6 Posts

Posted - 2006-11-01 : 11:31:27

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.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 11:41:46

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

SherryT
Starting Member

6 Posts

Posted - 2006-11-01 : 11:43:49

Can you please show that in code? Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-01 : 11:50:44

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

SherryT
Starting Member

6 Posts