Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.net - Command Parameter Values

Posted on 2014-02-04
5
Medium Priority
?
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 25

Accepted Solution

by:
SStory earned 1000 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 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

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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