updatecommand

[wandii]

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
[color=blue]
> cmd = New SqlClient.SqlCo mmand("Select * from
> Northwind.dbo.C ustomers ", consql)
> adapter.SelectC ommand = cmd
> ' read customer table
> adapter.Fill(da tatable1)
>
> ' update customer
> cmd = New SqlClient.SqlCo mmand("UPDATE
> Northwind.dbo.C ustomers SET CustomerID =
> @CustomerID, CompanyName =
> @CompanyName "& _
> "WHERE CustomerID =
> @oldCustomerID" , consql)
>
> cmd .Parameters.Add ("@CustomerI D", SqlDbType.NVarC har,[/color]
10,[color=blue]
> "CustomerID ")
> cmd .Parameters.Add ("@CompanyName" , SqlDbType.NVarC har,[/color]
40,[color=blue]
> "CompanyNam e")
>
> ' update database
> adapter.UpdateC ommand = cmd
> adapter.Update( datatable1)
>[/color]

[Cor Ligthert [MVP]]

Re: updatecommand

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.sql client
Imports System.Data
Public Class Form1
Private DBName As String = "TestDataBaseAu toNumber"
Private ConnString As String
Friend da As New SqlDataAdapter
Friend Conn As New SqlConnection
Dim dt As New DataTable

Private Sub Form1_Load(ByVa l sender As System.Object, _
ByVal e As System.EventArg s) Handles MyBase.Load
'To have a test is a very very small database created
CreateNewsqlDat abase(DBName)
'In this parts are the commands
CreateCommands( )
'
'This part is to test the sample
da.FillSchema(d t, SchemaType.Mapp ed)
Dim col As DataColumn = dt.Columns(0)
col.AutoIncreme nt = True
col.AutoIncreme ntSeed = -1
col.AutoIncreme ntStep = -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.D ataSource = dt
End Sub

Private Sub Button1_Click(B yVal sender As System.Object, _
ByVal e As System.EventArg s) Handles Button1.Click
BindingContext( "dt").EndCurren tEdit()
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.DeleteComman d = cmdDelete
da.InsertComman d = cmdInsert
da.SelectComman d = cmdSelect
da.UpdateComman d = cmdUpdate
da.TableMapping s.AddRange(New Common.DataTabl eMapping() _
{New Common.DataTabl eMapping("Table ", _
"Sample", New Common.DataColu mnMapping() {New
Common.DataColu mnMapping("Auto Id", "AutoId"), _
New Common.DataColu mnMapping("What Ever", "WhatEver") })})
'
'cmdSelect
cmdSelect.Comma ndText = "SELECT AutoId, WhatEver FROM Sample"
cmdSelect.Conne ction = Conn
'
'cmdInsert
cmdInsert.Comma ndText = "INSERT INTO Sample(WhatEver ) " & _
"VALUES (@WhatEver); SELECT AutoId, " & _
"WhatEver FROM Sample WHERE (AutoId = Scope_Identity( ))"
cmdInsert.Conne ction = Conn
cmdInsert.Param eters.Add(New SqlParameter("@ AutoId", SqlDbType.Int,
4, "AutoId"))
cmdInsert.Param eters.Add(New SqlParameter("@ WhatEver",
SqlDbType.NVarC har, 50, "WhatEver") )
'
'cmdUpdate
cmdUpdate.Comma ndText = "UPDATE Sample SET WhatEver = @WhatEver
WHERE (AutoId = @Original_AutoI d) " & _
"AND (WhatEver = @Original_WhatE ver OR @Original_WhatE ver IS NULL
AND WhatEver IS NULL); " & _
"SELECT AutoId, WhatEver FROM Sample WHERE (AutoId = @AutoId)"
cmdUpdate.Conne ction = Conn
cmdUpdate.Param eters.Add(New SqlParameter("@ AutoId", SqlDbType.Int,
4, "AutoId"))
cmdUpdate.Param eters.Add(New SqlParameter("@ WhatEver",
SqlDbType.NVarC har, 50, "WhatEver") )
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_AutoId ",
SqlDbType.Int, 4, _
ParameterDirect ion.Input, False, nb, nb, _
"AutoId", DataRowVersion. Original, Nothing))
cmdUpdate.Param eters.Add(New SqlParameter("@ Original_WhatEv er", _
SqlDbType.NVarC har, 50, ParameterDirect ion.Input, False, nb, nb, _
"WhatEver", DataRowVersion. Original, Nothing))
'
'cmdDelete
cmdDelete.Comma ndText = "DELETE FROM Sample WHERE (AutoId =
@Original_AutoI d) AND " & _
"(WhatEver = @Original_WhatE ver OR @Original_WhatE ver IS NULL AND
WhatEver IS NULL)"
cmdDelete.Conne ction = Conn
cmdDelete.Param eters.Add(New
System.Data.Sql Client.SqlParam eter("@Original _AutoId", _
System.Data.Sql DbType.Int, 4, System.Data.Par ameterDirection .Input,
_
False, nb, nb, "AutoId", System.Data.Dat aRowVersion.Ori ginal,
Nothing))
cmdDelete.Param eters.Add(New
System.Data.Sql Client.SqlParam eter("@Original _WhatEver", _
System.Data.Sql DbType.NVarChar , 50,
System.Data.Par ameterDirection .Input, False, _
nb, nb, "WhatEver", System.Data.Dat aRowVersion.Ori ginal, Nothing))
End Sub

Private Sub CreateNewsqlDat abase(ByVal DbName As String)
Dim ConnString As String = "Server = .\SQLExpress; Database = ;
Integrated Security = SSPI"
Conn = New SqlConnection(C onnString)
Dim strSQL As String = "if Exists (Select * From
master..sysdata bases Where Name = '" & DbName & "')"
strSQL += " DROP DATABASE " & DbName & ";" & vbCrLf
strSQL += " CREATE DATABASE " & DbName
Dim cmd As New SqlCommand(strS QL, Conn)
executecmd(cmd)
Conn.Connection String = "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(ByVa l cmd As SqlCommand)
Try
Conn.Open()
cmd.ExecuteNonQ uery()
Catch ex As SqlException
MessageBox.Show (ex.Message, "sqlExcepti on")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
Finally
Conn.Close()
End Try
End Sub
End Class


Cor

"wandii" <wandii@yahoo.c om> schreef in bericht
news:1147505804 .769838.206590@ y43g2000cwc.goo glegroups.com.. .[color=blue]
> 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
>[color=green]
>> cmd = New SqlClient.SqlCo mmand("Select * from
>> Northwind.dbo.C ustomers ",
>> consql)
>> adapter.SelectC ommand = cmd
>> ' read customer table
>> adapter.Fill(da tatable1)
>>
>> ' update customer
>> cmd = New SqlClient.SqlCo mmand("UPDATE
>> Northwind.dbo.C ustomers SET CustomerID =
>> @CustomerID, CompanyName =
>> @CompanyName "& _
>> "WHERE CustomerID =
>> @oldCustomerID" , consql)
>>
>> cmd .Parameters.Add ("@CustomerI D", SqlDbType.NVarC har,[/color]
> 10,[color=green]
>> "CustomerID ")
>> cmd .Parameters.Add ("@CompanyName" , SqlDbType.NVarC har,[/color]
> 40,[color=green]
>> "CompanyNam e")
>>
>> ' update database
>> adapter.UpdateC ommand = cmd
>> adapter.Update( datatable1)
>>[/color]
>[/color]

[wandii]

Re: updatecommand

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(datatabl e1) then update works fine,
but why?

Regards

[Cor Ligthert [MVP]]

Re: updatecommand

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

"wandii" <wandii@yahoo.c om> schreef in bericht
news:1147584951 .352174.69350@j 33g2000cwa.goog legroups.com...[color=blue]
> 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(datatabl e1) then update works fine,
> but why?
>
> Regards
>[/color]

[wandii]

Re: updatecommand

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.SqlCo mmand("Select CaseID, UserID from
dbo.CaseToClose ", consql)
adapter.SelectC ommand = cmd
adapter.Fill(da tatable)

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

cmd.Parameters. Add("@CaseID", SqlDbType.VarCh ar, 10).Value =
sCaseNum
cmd.Parameters. Add("@ProblemTy pe", SqlDbType.VarCh ar, 10).Value
= ProblemType

adapter.UpdateC ommand = cmd
adapter.Update( datatable)

[Cor Ligthert [MVP]]

Re: updatecommand

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

"wandii" <wandii@yahoo.c om> schreef in bericht
news:1147713232 .472741.97550@y 43g2000cwc.goog legroups.com...[color=blue]
> 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.SqlCo mmand("Select CaseID, UserID from
> dbo.CaseToClose ", consql)
> adapter.SelectC ommand = cmd
> adapter.Fill(da tatable)
>
> ' update statment
> cmd = New SqlClient.SqlCo mmand(" UPDATE dbo.CaseToClose set
> DateCompleted = Null, StatusID = 3 " +_
> " WHERE CaseID = @CaseID" + _
> " AND ProblemTypeID =
> @ProblemType ", consql)
>
> cmd.Parameters. Add("@CaseID", SqlDbType.VarCh ar, 10).Value =
> sCaseNum
> cmd.Parameters. Add("@ProblemTy pe", SqlDbType.VarCh ar, 10).Value
> = ProblemType
>
> adapter.UpdateC ommand = cmd
> adapter.Update( datatable)
>[/color]