updating individual columns

kscase · May 10th, 2007, 09:38 AM

I'm not sure if I should ask this question here or in one of the other forums, but I'm going to start here.

I want to avoid writing triggers if I can - that's my backup plan.
I want to write a subroutine that I can call from a variety of save routines.

I need to be able to execute the following SQL statement for multiple tables in my application:

UPDATE <tbl_nme>
set user_update = my.user.name, date_update = now()
where current_row_dataset

I don't know if it makes a difference that I am not using a datagrid, but a form with multiple comboboxes to select other new values.

I'm looking for suggestions for the best way to do this using VB 2005 and SQL 2005.

Thanks,
Karen

dparsons · May 10th, 2007, 09:58 AM

I am somewhat lost. Why would you need to write a trigger to achieve this? It seems to me that this is a rudimentary update statement??

I guess my question to you is: what exactly are you asking for? Are you asking what would be the best way to write a rountine that would execute this statement? Also, your where clause doesn't make any sense to me, could you please explain? =]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

kscase · May 10th, 2007, 10:09 AM

Yes, this is a very basic update statement. However, I don't want to code the statement for each table in my application, which is what would happen with a trigger.

The where clause is just a generic way of showing that I only want to update the current row/dataset.

What I don't want to do is two separate update statements, one to update the audit columns, and one to update the other data.

I'm still not sure if I'm explaining this well enough for you.

Thanks,
Karen

BrianWren · May 10th, 2007, 10:15 AM

The SQL to update a column in a table is:

Code:

UPDATE < TableName > ( < Fieldlist here, 1:1 with next part > )
VALUES               ( < Valuelist here, 1:1 with Fieldlist > )
WHERE < Condition(s) here >

You would make this a string, which can be assembled using all the standard VB concatenation techniques. It gets kind of ugly sometimes, as you are jumpling in and out of literal components of the string, and those parts that are supplied by textboxes and objects like My.User.Name.

Having this string, you can use a Connection to execute it.

Does that help?

dparsons · May 10th, 2007, 10:16 AM

Ahh. Well something like this would work:

Private Sub updateTable(ByVal sTableName as string, ByVal sUserName as string, ByVal iRow as Integer)
Dim sSql as string = ""

sSql = "UPDATE " & sTableName & " SET user_update='" & sUserName &"', date_update='" & Now() & "' WHERE row =" & iRow
'Execute SQL Statement
End Sub

Does that work?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

kscase · May 10th, 2007, 11:08 AM

okay, let's back up a step or two ...

Let's say, I want to change multiple values in a row, I am using combo boxes to select the new values. I am not using a datagrid.

Is there a way to give the values to it as I get them, but do ONE update for the table adapter at the end?

dparsons · May 10th, 2007, 11:47 AM

hmmm. So you could potentially update more then 1 column per row and want the ability to update those columns but only issue one UPDATE command, is that correct?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

kscase · May 10th, 2007, 11:53 AM

Yes!

Here's the code I'm working on right now.

Code:

        Dim strConn As New SqlConnection(APC_WirelessConnectionString)
        strConn.Open()

        Dim sqlCmd As New SqlCommand
        sqlCmd.CommandType = CommandType.Text
        sqlCmd.CommandText = _
        "UPDATE Equipment " + _
        "SET " + _
        "   LocationID = " & cmbChgLocation.SelectedIndex & ", " + _
        "   StatusID = " & cmbChgStatus.SelectedIndex & ", " + _
        "   OutColorID = " & cmbChgColor.SelectedIndex & ", " + _
        "   OutCarrierID = " & cmbChgCarrier.SelectedIndex & ", " + _
        "   user_update = " & My.User.Name & ", " + _
        "   date_update = " & Now() & ";"

        sqlCmd.ExecuteNonQuery()

        strConn.Close()

        Me.APC_WirelessDataSet.Equipment.Clear()

It's not working yet, but I'm working on it.
It says my connection isn't properly initialized. I don't understand yet, but I will.

If someone has a better idea, I'm definitely open to help.

Karen

dparsons · May 10th, 2007, 12:07 PM

You havent provided your SQL Command with a connection string to use so it isn't initialized.

Add this:
sqlCmd.Connection = sqlConn

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

kscase · May 10th, 2007, 12:16 PM

Thank you.

Sometimes another pair of eyes see the obvious ...