Solved

VB.net - Command Parameter Values

Posted on 2014-02-04
5
581 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
ID: 39832399
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
ID: 39832551
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
ID: 39832584
>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
ID: 39834922
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
ID: 39835426
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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