UPDATING my Database - I will soon jump out of my window!

[skyyakal]

Cannot figure out how to update a row, please help!!

 

Hallo Gentlemen,

I have written the following code and get an error message, which point out the line

SqlUpdateCommand.CommandText = "UPDATE...

 

Error message (translated from German): "Object reference wasn't tied down to the Object instance"

 

 

Here is the complete code:

Dim MyTable As DataTable = CardsValues.Tables("SimVariablesValues")
       Dim MyRow As DataRow
       Console.WriteLine("Numer of rows: " & MyTable.Rows.Count())
       For Each MyRow In MyTable.Rows
           If MyRow.RowState = DataRowState.Modified Then
               Console.WriteLine(MyRow.Item(1))
               Console.WriteLine(MyRow.Item(2))

               Dim SqlUpdateCommand As System.Data.SqlClient.SqlCommand
               CardsValuesSqlDataAdapter.UpdateCommand = SqlUpdateCommand

               SqlUpdateCommand.CommandText = "UPDATE SimVariablesValues SET Value=" & MyRow.Item(1) & " WHERE VariableScenario_ID=" & MyRow.Item(2)

               Dim SimConnect As System.Data.SqlClient.SqlConnection
               SimConnect.ConnectionString = "data source=BLACK;initial catalog=ScenarioSim;integrated security=SSPI;persist se" & _
               "curity info=False;user id=Administrator;workstation id=BLACK;packet size=4096"
               SqlUpdateCommand.Connection = SimConnect

               Dim UpdateCard As Integer = CardsValuesSqlDataAdapter.Update(CardsValues.SimVariablesValues)
           End If
       Next

 

Console Lines are giving the right data and are just for testing there.

 

Would appreciate any help!

[wyrd]

Dim SqlUpdateCommand As System.Data.SqlClient.SqlCommand

 

Should be

 

Dim SqlUpdateCommand As New SqlCommand(connection)

 

or some such like that.

[skyyakal]

Hi wyrd!

 

Thanks for the reply!

 

I get an error on your code on the SqlCommand:

 

"The Type SqlCommand is not defined."

 

:-(

 

Would you have any other suggestions?

[PlausiblyDamp]

Dim SqlUpdateCommand As New System.Data.SqlClient.SqlCommand

or add

 

Imports System.Data.SqlClient to the top of the code module.

[skyyakal]

Hallo PlausiblyDump!

Your code worked, but I get an Systemerror on the line

 

Dim UpdateCard As Integer = CardsValuesSqlDataAdapter.Update(CardsValues.SimVariablesValues)

 

My Database is very simple.

 

Name: ScenarioSim

Table: SimVariablesValues

3 Columns (datatype bigint, length 8, NULL is allowed in the first two columns):

Var_ID

Value

VariableScenario_ID

 

 

Dim MyTable As DataTable = CardsValues.Tables("SimVariablesValues")
       Dim MyRow As DataRow
       Console.WriteLine("Numer of rows: " & MyTable.Rows.Count())
       For Each MyRow In MyTable.Rows
           If MyRow.RowState = DataRowState.Modified Then
               Console.WriteLine(MyRow.Item(1))
               Console.WriteLine(MyRow.Item(2))


               Dim SqlUpdateCommand As New System.Data.SqlClient.SqlCommand()

               CardsValuesSqlDataAdapter.UpdateCommand = SqlUpdateCommand

               SqlUpdateCommand.CommandText = "UPDATE SimVariablesValues SET Value=99 WHERE VariableScenario_ID=3"

               Dim SimConnect As New System.Data.SqlClient.SqlConnection()
               SimConnect.ConnectionString = "data source=BLACK;initial catalog=ScenarioSim;integrated security=SSPI;persist se" & _
               "curity info=False;user id=Administrator;workstation id=BLACK;packet size=4096"
               SqlUpdateCommand.Connection = SimConnect

               Dim UpdateCard As Integer = CardsValuesSqlDataAdapter.Update(CardsValues.SimVariablesValues)
           End If
       Next

 

I simplified the code for better troubleshooting. Have a DataGrid. Alter just one row and try to update the database. When I press the Update Button, Program hangs, then the error message accurs. Database is up and getting the queries (I see the MSSQL Icon blinking, when the queties are passed on)

 

Thanks again for any comments!

[skyyakal]

SORRY! I found the error!

It just the wrong name of the server! (I have a second one)

 

Thanks to everybody. The upper code works just fine now.

[skyyakal]

Hallo guys,

I have problems updating my database (MSSQL) in the right way.

 

I have a DataGrid Grid Control, where I read in the data from my database. After changing the values in several rows of one column (there are 3 columns) in the DataGrid I would like to update my database by pressing the Update button.

 

The button_Click looks like that:

 

Console.WriteLine(CardsValues.GetChanges.ToString)
       Dim MyTable As DataTable = CardsValues.Tables("SimVariablesValues")
       Dim MyRow As DataRow
       Console.WriteLine("Numer of rows: " & MyTable.Rows.Count())

       For Each MyRow In MyTable.Rows
           If MyRow.RowState = DataRowState.Modified Then
               Console.WriteLine("changed values: " & MyRow.Item(1))
               Console.WriteLine("changed ID: " & MyRow.Item(2))

               Dim SqlUpdateCommand As New System.Data.SqlClient.SqlCommand()

               CardsValuesSqlDataAdapter.UpdateCommand = SqlUpdateCommand

               SqlUpdateCommand.CommandText = "UPDATE SimVariablesValues SET Value=" & MyRow.Item(1) & " WHERE VariableScenario_ID=" & MyRow.Item(2)
               
               Dim SimConnect As New System.Data.SqlClient.SqlConnection()
               SimConnect.ConnectionString = "data source=HI;initial catalog=ScenarioSim;integrated security=SSPI;persist se" & _
                 "curity info=False;user id=Administrator;workstation id=HI;packet size=4096"

               SqlUpdateCommand.Connection = SimConnect
               Dim UpdateCard As Integer = CardsValuesSqlDataAdapter.Update(CardsValues.SimVariablesValues)
           End If
       Next

 

My problem is, that only the first modified value is updated, all others are NOT.

 

I figured out, that as soon as the line

Dim UpdateCard As Integer = CardsValuesSqlDataAdapter.Update(CardsValues.SimVariablesValues)

is executed the row states are put back to Unchanged, so all following values are not updated anymore.

 

I tryed to place the Update statement at the very end of the code above. In this way, only the last changed value is updated (well, since the Update statement is placed at the end.)

 

My question now is, if my code construction above won't work in any way. How do I collect the values of the Column2 in the modified rows? I stay open to any other suggestions.

 

Thank you for your statements!

cygent