Hi,
   I am trying to update the customer table by using the updatecommd,
please see
below, however, when it runs it does not fire the update statement.  I
ran the sql profiler
and the only statement the profiler shows is the 'Select * from
Customers...' and zero
rows updated.

Does any knwo why the updatecommand does not fire.

Thanks in advance

Regards,


----------------------------------------------------------------------------------------
.... code sample

Show quoteHide quote

Wandii,

http://www.vb-tips.com/default.aspx?ID=3405596d-4556-4aa8-be12-d7c12bbb3726

We have a problem at the moment with showing links on our website.

Therefore is here the text.

This sample is to show the following items for an SQLServer Database
Creating by hand the select, insert, update, delete commands including the
parameters
Filling that table
Showing that table with negative seeded identnumbers
Update that table (you can edit the table)

Not implemented is any error handling beside showing that there is an error
(not at the places where that in fact cannot happen in this sample).

You need for this a new project, drag in a DataGridView and a Button on the
form and paste this code in the class. (Although it is 2005 is it as well to
use with 2003 where you than have to change the DataGridView for a DataGrid
and some of the code.

If you want to use another DataBaseName change that name at DBName, be aware
that it in advance of the sample every time will be Droped (Deleted).
--------------------------------------------------------------------------------

Imports System.Data.sqlclient
Imports System.Data
Public Class Form1
    Private DBName As String = "TestDataBaseAutoNumber"
    Private ConnString As String
    Friend da As New SqlDataAdapter
    Friend Conn As New SqlConnection
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
        'To have a test is a very very small database created
        CreateNewsqlDatabase(DBName)
        'In this parts are the commands
        CreateCommands()
        '
        'This part is to test the sample
        da.FillSchema(dt, SchemaType.Mapped)
        Dim col As DataColumn = dt.Columns(0)
        col.AutoIncrement = True
        col.AutoIncrementSeed = -1
        col.AutoIncrementStep = -1
        For i As Integer = 0 To 3
            dt.Rows.Add(dt.NewRow)
            dt.Rows(i)(1) = ChrW(i + 65)
        Next
        dt.DefaultView.Sort = "WhatEver"
        '
        'the situation is only showed in the Datagrid
        'the click on the button does the update
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
        BindingContext("dt").EndCurrentEdit()
        da.Update(dt)
    End Sub

    Public Sub CreateCommands()
        Dim nb As Byte = 0
        Dim cmdSelect As New SqlCommand
        Dim cmdInsert As New SqlCommand
        Dim cmdUpdate As New SqlCommand
        Dim cmdDelete As New SqlCommand
        da.DeleteCommand = cmdDelete
        da.InsertCommand = cmdInsert
        da.SelectCommand = cmdSelect
        da.UpdateCommand = cmdUpdate
        da.TableMappings.AddRange(New Common.DataTableMapping() _
        {New Common.DataTableMapping("Table", _
        "Sample", New Common.DataColumnMapping() {New
Common.DataColumnMapping("AutoId", "AutoId"), _
        New Common.DataColumnMapping("WhatEver", "WhatEver")})})
        '
        'cmdSelect
        cmdSelect.CommandText = "SELECT AutoId, WhatEver FROM Sample"
        cmdSelect.Connection = Conn
        '
        'cmdInsert
        cmdInsert.CommandText = "INSERT INTO Sample(WhatEver) " & _
        "VALUES (@WhatEver); SELECT AutoId, " & _
        "WhatEver FROM Sample WHERE (AutoId = Scope_Identity())"
        cmdInsert.Connection = Conn
        cmdInsert.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int,
4, "AutoId"))
        cmdInsert.Parameters.Add(New SqlParameter("@WhatEver",
SqlDbType.NVarChar, 50, "WhatEver"))
        '
        'cmdUpdate
        cmdUpdate.CommandText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoId) " & _
        "AND (WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL
AND WhatEver IS NULL); " & _
        "SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
        cmdUpdate.Connection = Conn
        cmdUpdate.Parameters.Add(New SqlParameter("@AutoId", SqlDbType.Int,
4, "AutoId"))
        cmdUpdate.Parameters.Add(New SqlParameter("@WhatEver",
SqlDbType.NVarChar, 50, "WhatEver"))
        cmdUpdate.Parameters.Add(New SqlParameter("@Original_AutoId",
SqlDbType.Int, 4, _
        ParameterDirection.Input, False, nb, nb, _
        "AutoId", DataRowVersion.Original, Nothing))
        cmdUpdate.Parameters.Add(New SqlParameter("@Original_WhatEver", _
        SqlDbType.NVarChar, 50, ParameterDirection.Input, False, nb, nb, _
        "WhatEver", DataRowVersion.Original, Nothing))
        '
        'cmdDelete
        cmdDelete.CommandText = "DELETE FROM Sample WHERE (AutoId =
@Original_AutoId) AND " & _
        "(WhatEver = @Original_WhatEver OR @Original_WhatEver IS NULL AND
WhatEver IS NULL)"
        cmdDelete.Connection = Conn
        cmdDelete.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_AutoId", _
        System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input,
_
        False, nb, nb, "AutoId", System.Data.DataRowVersion.Original,
Nothing))
        cmdDelete.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_WhatEver", _
        System.Data.SqlDbType.NVarChar, 50,
System.Data.ParameterDirection.Input, False, _
        nb, nb, "WhatEver", System.Data.DataRowVersion.Original, Nothing))
    End Sub

    Private Sub CreateNewsqlDatabase(ByVal DbName As String)
        Dim ConnString As String = "Server = .\SQLExpress; Database = ;
Integrated Security = SSPI"
        Conn = New SqlConnection(ConnString)
        Dim strSQL As String = "if Exists (Select * From
master..sysdatabases Where Name = '" & DbName & "')"
        strSQL += " DROP DATABASE " & DbName & ";" & vbCrLf
        strSQL += " CREATE DATABASE " & DbName
        Dim cmd As New SqlCommand(strSQL, Conn)
        executecmd(cmd)
        Conn.ConnectionString = "Server = .\SQLExpress; Database =" & DbName
& " ; Integrated Security = SSPI"
        cmd.Connection = Conn
        cmd.CommandText = "CREATE TABLE Sample ( " & _
               "AutoId int identity NOT NULL," & _
               "WhatEver NVarchar(50)," & _
               "CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) "
        executecmd(cmd)
    End Sub

    Private Sub executecmd(ByVal cmd As SqlCommand)
        Try
            Conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "sqlException")
            Exit Sub
        Catch ex As Exception
            MessageBox.Show(ex.Message, "GeneralException")
            Exit Sub
        Finally
            Conn.Close()
        End Try
    End Sub
End Class


Cor

Show quoteHide quote

Thanks Cor for the quick respone.  Actually I was hoping if someone
could pinpoint the
problem with my update codes.  Why updatecommand would not fire?  If I
use the
ExecuteNonQuery() instead of update(datatable1) then update works fine,
but why?

Regards

Wandii,

In my idea is your update command not equal to the one in the sample. I miss
at least the reading of the current item from the database to check to the
old one

Cor

Show quoteHide quote

Cor,
   I tried another simple block of codes, but still not updating the
database and doesn't throw an exception.  Again if I
use the ExecuteNonQuery() it updates the database.  I can see reading
the table in the Sql Profiler, however, no update. Any idea?

Thanks in advace.

--------------------------------------------------------------
       '  select statement
        cmd = New SqlClient.SqlCommand("Select CaseID, UserID  from
dbo.CaseToClose", consql)
        adapter.SelectCommand = cmd
        adapter.Fill(datatable)

        ' update statment
        cmd = New SqlClient.SqlCommand(" UPDATE dbo.CaseToClose set
DateCompleted = Null, StatusID = 3 " +_
                                       "  WHERE CaseID =  @CaseID" + _
                                       "    AND ProblemTypeID =
@ProblemType ", consql)

        cmd.Parameters.Add("@CaseID", SqlDbType.VarChar, 10).Value =
sCaseNum
        cmd.Parameters.Add("@ProblemType", SqlDbType.VarChar, 10).Value
= ProblemType

        adapter.UpdateCommand = cmd
        adapter.Update(datatable)

Wanddi,

A dataadapter does check if the original datarow is not changed.
(Concurrency checking) Therefore he needs a select as in the sample I gave
you.

A profiler or an executenonquery don't need that select, they just update
it, even if somebody else has changed the data. (Real nice if it is a for a
bank where the money was just taken and now put back or even raised. Don't
try it, this is the most know sample and protected).

I hope I make it clear with this. Have a look at the sample I gave you there
the commands, they are NOT equal to your code.

Cor

Show quoteHide quote

int64 or double
serializing data to a file
Design Question for Factory Pattern and Casting Types
Floating Menu Bar
Adding modules from another project
Advice on Training
Custom List casting
Getting the Parent firectory of a file
remove xml node in xml document
Console Application - Exit Code