Why a field was not updated in an SQL Server Table

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

SQL Server 2000 Forums

Transact-SQL (2000)

Why a field was not updated in an SQL Server Table

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-08-27 : 20:55:04

Access 2003 and SQL 2000 Server.

All of the codes below works, and there is no error messages, but the field
"Last_Access_Date" was not updated in table User_Account.

cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' "& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"

////////////////////////////////////////////////////

In SQL Server Enterprise Manager --> Inspection Databae --> User_Account Table -->
All Tasks --> Manage Permissions

public - The check box for SELECT, INSERT, UPDATE, and DELETE are checked

////////////////////////////////////////////////////

In SQL Server Enterprise Manager --> Security --> Logins -->
Win-2000-Server --> Properties

db_owner is checked
public role is checked

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

THe Table User_Account has the following structure

Column Name Data Type Length Allow Nulls
Name nvarchar 50 0
Email_Address nvarchar 50 1
P nvarchar 20 1
Department_Group nvarchar 50 1
Title nvarchar 50 1
Access_Status nvarchar 50 1
Last_Access_Date nvarchar 50 1

The code below does ran without any error but the
field "Last_Access_Date" was not updated in table User_Account.
This field always remined NULL

//////////////////////////////////////////////////////////

Private Sub Update_Record_Click()
Call User_Login_Using_SQL_String1
End Sub

//////////////////////////////////////////////////////////

Private Sub User_Login_Using_SQL_String1()
Dim cnThisConnect As ADODB.Connection
Dim RecordSet_User_Account As New ADODB.Recordset
Set cnThisConnect = CurrentProject.Connection

Dim cmd As ADODB.Command
Dim Str_SQL As String

Str_SQL = "SELECT * FROM User_Account WHERE User_Account.Name = '" & Forms!Main_Login!User_Name & "'; "

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
'cmd.CommandType = adCmdText

RecordSet_User_Account.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

If IsNull(Me!User_Name) Then
MsgBox "Please enter your user name", vbInformation
Exit Sub
End If

If IsNull(Me!Password) Then
MsgBox "Please enter your password", vbInformation
Exit Sub
End If

'''''''''''''''''''''''''''' RECORDSET CODE BEGINS ''''''''''''''''''''''''

If RecordSet_User_Account.RecordCount = 0 Then
MsgBox "The user account does not exist.", vbExclamation

Else
RecordSet_User_Account.MoveFirst

If RecordSet_User_Account!Name = Me!User_Name Then
If RecordSet_User_Account!P = Me!Password Then

Public_Current_User = RecordSet_User_Account!Name
Public_Access_Status = RecordSet_User_Account!Access_Status

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adCmdText

cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' " _
& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cmd.Execute , , adAsyncExecute

DoCmd.Close acForm, "Main_Login"
DoCmd.OpenForm "Main_Switch_Board"
End If
End If
End If

RecordSet_User_Account.Close

End Sub

ijprasad
Starting Member

29 Posts

Posted - 2006-08-28 : 06:52:59

check is any triggers are defined on the table ?

Inderjeet

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:02:56

What date format do Last_Access_Date have?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-28 : 11:28:03

Why did you use nvarchar datatype to store Dates? use proper DateTime datatype

Madhivanan

Failing to plan is Planning to fail

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-08-29 : 21:49:47

No triggers are defined on the table.
Last_Access_Date format is nvarchar 50
On another table I use nvarchar datatype and it did not gives me any problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 02:40:27

Try a debug.print cmd.commandtext to see what is executed.

Then do a select from User_Account table with same WHERE clause.

Peter Larsson
Helsingborg, Sweden

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-30 : 12:57:21

Lin100,

quote:

Last_Access_Date format is nvarchar 50
On another table I use nvarchar datatype and it did not gives me any problem.

Just because you use it on another table doesn't mean it's right to use it.

The DateTime type was specifically designed to hold date/time values. nVarChar and VarChar were designed to hold character data. Use the proper data type for the data you're storing; it will make things much easier for you in the long run.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources