Solved

VB.net - Command Parameter Values

Posted on 2014-02-04
5
576 Views
Last Modified: 2014-02-05
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
Comment
Question by:spen_lang
5 Comments
 
LVL 25

Accepted Solution

by:
SStory earned 250 total points
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
>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
 

Author Comment

by:spen_lang
Comment Utility
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
 
LVL 25

Expert Comment

by:SStory
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Retain selection in datagridview 2 22
Paging GridView 7 32
Hovering effect 9 28
Code enhancement 5 12
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now