• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

VB.net - Command Parameter Values

Hi,

Please could someone help?

I have created a class to store the connection to the database:

Imports System.Data.SqlClient

Public Class data_Users
    Private cn As SqlConnection
    Private sLangdonsPrinters As String
    Private daUsers As SqlDataAdapter

    Private cmdUsers_Select As SqlCommand
    Public cmdUsers_Update As SqlCommand
    Public cmdUsers_Insert As SqlCommand
    Private cmdUsers_Delete As SqlCommand

    Private dsUsers As DataSet

    Public Sub New()
        sCon = My.Settings.sCon
    End Sub

    Public Sub Users()
        Try
            cn = New SqlConnection(sCon)

            'select command
            cmdUsers_Select = New SqlCommand
            cmdUsers_Select.Connection = cn
            cmdUsers_Select.CommandType = CommandType.StoredProcedure
            cmdUsers_Select.CommandTimeout = 30
            cmdUsers_Select.CommandText = "proc_Users_Select_All"

            'update command
            cmdUsers_Update = New SqlCommand
            cmdUsers_Update.Connection = cn
            cmdUsers_Update.CommandType = CommandType.StoredProcedure
            cmdUsers_Update.CommandTimeout = 30
            cmdUsers_Update.CommandText = "proc_Users_Update"

            cmdUsers_Update.Parameters.Add("@Users_ID", SqlDbType.Int, 1, "Users_ID")
            cmdUsers_Update.Parameters.Add("@Users_Name", SqlDbType.VarChar, 50, "Users_Name")
            cmdUsers_Update.Parameters.Add("@Users_Username", SqlDbType.VarChar, 50, "Users_Username")
            cmdUsers_Update.Parameters.Add("@ModifiedDate", SqlDbType.DateTime)
            cmdUsers_Update.Parameters("@ModifiedDate").Value = DateTime.Now()
            cmdUsers_Update.Parameters.Add("@Users_Active", SqlDbType.Bit, 1, "Users_Active")
            cmdUsers_Update.Parameters.Add("@Users_RememberMe", SqlDbType.Bit, 1, "Users_RememberMe")

            'insert command
            cmdUsers_Insert = New SqlCommand
            cmdUsers_Insert.Connection = cn
            cmdUsers_Insert.CommandType = CommandType.StoredProcedure
            cmdUsers_Insert.CommandTimeout = 30
            cmdUsers_Insert.CommandText = "proc_Users_Insert"

            With cmdUsers_Insert
                .Parameters.Add("@Users_Name", SqlDbType.VarChar, 50, "Users_Name")
                .Parameters.Add("@Users_Username", SqlDbType.VarChar, 20, "Users_Username")
                .Parameters.Add("@ModifiedDate", SqlDbType.VarChar, 255)
                .Parameters.Add(New SqlClient.SqlParameter("@NewUsers_ID", SqlDbType.Int))
                .Parameters("@NewUsers_ID").Direction = ParameterDirection.Output
            End With

            'Delete command
            cmdUsers_Delete = New SqlCommand
            cmdUsers_Delete.Connection = cn
            cmdUsers_Delete.CommandType = CommandType.StoredProcedure
            cmdUsers_Delete.CommandText = "proc_Users_Delete"
            cmdUsers_Delete.Parameters.Add("@Users_ID", SqlDbType.Int, 1, "Users_ID")

            'configure the DataAdapter
            daUsers = New SqlDataAdapter
            daUsers.SelectCommand = cmdUsers_Select
            daUsers.UpdateCommand = cmdUsers_Update
            daUsers.InsertCommand = cmdUsers_Insert
            daUsers.DeleteCommand = cmdUsers_Delete

            'Create the dataset
            dsUsers = New DataSet

            cmdUsers_Update.Dispose()

        Catch ex As Exception
            MessageBox.Show("Error while accessing database" & vbCrLf & vbCrLf & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Function GetUsers() As DataSet
        Users()
        daUsers.Fill(dsUsers)
        Return (dsUsers)
    End Function

    Public Sub UpdateUsers(ByVal dsChanges As DataSet)
        daUsers.Update(dsChanges)
    End Sub

End Class

Open in new window


I fill the forms dataset with the database values when the form opens and bind the BindingSource, BindingNavigator and Textboxes.

Public Class frm_UsersAccess

Private objUsers As data_Users
Private dsUsers As DataSet

	Private Sub frm_UsersAccess_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'set object binding
        objUsers = Nothing
        objUsers = New data_Users

	dsUsers = objUsers.GetUsers()
        bsUsersAccess.DataSource = dsUsers.Tables(0)
        bnUsersAccess.BindingSource = bsUsersAccess
        bindObjects_Clear()
        bindObjects()
    End Sub
	
End Class

Open in new window




I have added a parameter named @ModifiedDate that should have a value of DateTime.Now() but the DateTime.Now seems to be set to the time when the sub was first called (when the form opens) and not to the time when the update button was pressed.

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click		
	If dsUsers.HasChanges Then
		objUsers.UpdateUsers(dsUsers.GetChanges)
		dsUsers = objUsers.GetUsers
		bindObjects()
	End If
End Sub

Open in new window


When should I set the parameter values if they are not going to be the bound value of the dataset?

Should I add the following code to the Save button?
cmdUsers_Update.Parameters("@ModifiedDate").Value = DateTime.Now()

Open in new window


Sorry if this makes little sense...

Thanks
0
spen_lang
Asked:
spen_lang
2 Solutions
 
SStoryCommented:
Yes if you want it to mark modified when you do it. What you are doing now is creating the object and the Now() get's called on Form_Load...when the form loads instead of when the Save button is pushed.
0
 
CodeCruiserCommented:
You can also use GetDate() function in SQL itself instead of passing it as parameter unless the server is sitting in a different timezone.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Should I add the following code to the Save button?
yes, just before objUsers.UpdateUsers(dsUsers.GetChanges)
but I agree that using getdate() would be better.
considering the timezone part, you have other functions, see here:
http://technet.microsoft.com/en-us/library/ms188383.aspx
ELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, SYSDATETIMEOFFSET())
    ,CONVERT (time, SYSUTCDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE())
    ,CONVERT (time, GETUTCDATE());

Here is the result set.

SYSDATETIME() 13:18:45.3490361

SYSDATETIMEOFFSET()13:18:45.3490361

SYSUTCDATETIME() 20:18:45.3490361

CURRENT_TIMESTAMP 13:18:45.3470000

GETDATE() 13:18:45.3470000

GETUTCDATE() 20:18:45.3470000

Open in new window

0
 
spen_langAuthor Commented:
Thanks for all your help, I would usually set the modified date on the SQL server but I wanted to use this example to clear how I would set the parameter. So basically the Update command in my Class will need to be set as a public variable.
0
 
SStoryCommented:
Well, Public means any app can use it. Friend means any module within that app can use it, so depending upon how you want to do it...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now