UPDATE SQLCommand

[SjR]

Hi there.
I'm new to ADO.NET, and I'm trying to learn this without having to bother you lot on simple questions

But..
I'm having just a tiny problem with updating the SQL database. I'm trying to update 2 fields on a table "MatterDesc" and ModifiedDate". "MatterDesc" updates fine, "ModifiedDate" won't update. I think the problem is with the SQLParameter (highlighted in red in the code below).

Imports System.Data.SqlClient
 
Public Class frmPIMatter
    Inherits System.Windows.Forms.Form
 
    Dim connCaseMan As SqlConnection = New SqlConnection("Data Source=BIRMDATA;Initial Catalog=CaseMan;Integrated Security=SSPI")
    Dim cmdSelect As New SqlCommand()
    Dim cmdUpdateMatter As New SqlCommand()
    Dim cmdDelete As New SqlCommand()
    Dim prmMatter As SqlParameter
    Dim daMatter As New SqlDataAdapter()
    Dim dsMatter As New DataSet()
 
#Region " Windows Form Designer generated code "
...
#End Region
 
    Private Sub frmPIMatter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim sSQL As String
 
        'Set up SQL parameters
        'UPDATE Matter
        sSQL = "UPDATE Matter SET "
        sSQL = sSQL & "MatterDesc = @MatterDesc, "
        sSQL = sSQL & "ModifiedDate = @ModifiedDate "
        sSQL = sSQL & "WHERE MatterID = " & Me.Tag
        cmdUpdateMatter = connCaseMan.CreateCommand
        cmdUpdateMatter.CommandText() = sSQL
        prmMatter = cmdUpdateMatter.Parameters.Add("@MatterDesc", SqlDbType.VarChar, 50, "MatterDesc")
        [color=red]prmMatter = cmdUpdateMatter.Parameters.Add("@ModifiedDate", SqlDbType.DateTime, 8, "ModifiedDate")[/color]
        prmMatter.SourceVersion = DataRowVersion.Original
        daMatter.UpdateCommand = cmdUpdateMatter
 
        'SELECT
        sSQL = "SELECT Matter.MatterDesc, "
        sSQL = sSQL & "Matter.OpenDate, "
        sSQL = sSQL & "Matter.ModifiedDate "
        sSQL = sSQL & "FROM Matter "
        sSQL = sSQL & "WHERE Matter.MatterID = " & Me.Tag
 
        cmdSelect = connCaseMan.CreateCommand
        cmdSelect.CommandText = sSQL
        daMatter.SelectCommand = cmdSelect
        daMatter.Fill(dsMatter, "Matter")
 
        With Me
            .Text = dsMatter.Tables(0).Rows(0).Item(0) & " [" & Format(.Tag, "000000") & "]"
            .lblOpenDate.Text() = Format(dsMatter.Tables(0).Rows(0).Item(1), "ddd dd/MM/yyyy")
            .txtMatterDesc.DataBindings.Add("Text", dsMatter.Tables(0), "MatterDesc")
            .lblModifiedDate.DataBindings.Add("Text", dsMatter.Tables(0), "ModifiedDate") 'Displays OK when form has loaded - so is reading it OK from the database.
        End With
 
    End Sub
 
    Private Sub frmPIMatter_Closed(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Closed
 
        connCaseMan.Close()
        cmdSelect.Dispose()
        daMatter.Dispose()
        dsMatter.Dispose()
 
    End Sub
 
    Private Sub tbPIMatter_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles tbPIMatter.ButtonClick
 
        Me.lblModifiedDate.Text = Format(Now, "dd/MM/yyyy HH:mm:ss")
 
        Me.BindingContext(dsMatter.Tables(0)).EndCurrentEdit()
        daMatter.Update(dsMatter, "Matter")
 
        RefreshMatters(ctrlActiveScreen)
 
        Me.Close()
 
    End Sub
 
End Class

Thanks for any help

[HumanCompiler]

Since you're using VS.NET, I would suggest you use its power. It can setup all of that for you.

Simply go to the Data tab on your toolbar and drag out a SqlConnection, a SqlDatAdapter & two SqlCommands

Give the SqlConnection it's ConnectionString (make sure it works). Do so by selecting it and changing it in the Properties window, then tell both of your SqlCommands to use the SqlConnection that you created as their connections, then click the elipses (...) on the CommandText property and build your sql statement or instead of using the Command Builder, just type in the Sql Statement you want. VS.NET will then go to your SqlServer and setup the parameters and all that jazz for you.

Then tell the SqlDataAdapter which SqlCommands it should be using for it's SelectCommand and UpdateCommand.

Then all your code will consist of is filling your DataSet and you're done...should work just fine and be less work for you

[SjR]

Cheers
Thats made things a lot easier - and I thought creating the whole lot in code would be easier to understand whats going on..... The modified date updates OK now!

[HumanCompiler]

great, glad to hear it