Update DataSource Question

Here is sample from Documentation..

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.UpdateCommand = New SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " & _
                                     "WHERE CategoryID = @CategoryID", nwindConn)

catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

Dim workParm As SqlParameter = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
workParm.SourceColumn = "CategoryID"
workParm.SourceVersion = DataRowVersion.Original

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")  

Dim cRow As DataRow = catDS.Tables("Categories").Rows(0)
cRow("CategoryName") = "New Category"

catDA.Update(catDS)

Is there anyway I can do this without the "Select" ??

I guess I'm looking for a way to do this with having to go though the process of creating a dataset.

Hi,

a basic pattern for doing it the quick'n dirty way :

Dim conn as OleDbConnection = new OleDbConnection("your connection string here")
Dim cmd as OleDbCommand = conn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "update toto set tata = '" & myValue & "'"

cmd.ExecuteNonQuery()

cmd.Dispose()
conn.Close()


-S

I'm going to have to give the points to naveenkohli cause he was first with the correct response.

I'll give you guys 25 more points if you can tell me how to add Parameters, I've got this now:


        l_OLECommand = l_OLEConnection.CreateCommand

        l_OLECommand.CommandText = UpdateString

        l_OLECommand.Parameters.Add("@test2", OleDbType.VarChar, Len("OUT")).Value = "OUT"
        l_OLECommand.Parameters.Add("@test1", OleDbType.VarChar, Len("7")).Value = "7"



        l_OLECommand.ExecuteNonQuery()


This works without Parms

First mark the command type to be of type StoredProcuedure (if you are dealing with SP)

l_OLECommand.CommandType = CommandType.StoredProcedure;

Then call ExecuteQuery.

SP ??

oooooooo SP - Stored Procedure. Nope not dealing with them

Let me ask you this - why wouldn't the above code work ??

I've got this:

UpdateString = "Update CTO_TEST Set STATUS=? Where ID=? "
        l_OLECommand = l_OLEConnection.CreateCommand
        l_OLECommand.CommandText = UpdateString
        l_OLECommand.Parameters.Add("@test2", OleDbType.VarChar, Len("OUT")).Value = "OUT"
        l_OLECommand.Parameters.Add("@test1", OleDbType.VarChar, Len("7")).Value = "7"
        l_OLECommand.CommandType = CommandType.Text

        l_OLECommand.ExecuteNonQuery()

This doesn't error out but it does update the DataSource either.

What is your update string..

Dim UpdateString as string
UpdateString = "Update CTO_TEST Set STATUS=? Where ID=? "

Try this..

l_OLECommand.Parameters.Add("@Status", OleDbType.VarChar, Len("OUT")).Value = "OUT"
       l_OLECommand.Parameters.Add("@ID", OleDbType.VarChar, Len("7")).Value = "7"

I tried - No luck. I think that's just a Parm name.

is it possible that command parameters can only be used with queries and not nonquery items?

HEre is sample from Documentattion. IT does exactly what you are trying to do.

Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)      

catDA.UpdateCommand = New OleDbCommand("UPDATE Categories SET CategoryName = ? " & _
                                       "WHERE CategoryID = ?" , nwindConn)

catDA.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 15, "CategoryName")

The problem is I have 2 SQL's a Select and an Update. I just want an Update.