Committing Update to Database

[macker]

I have a fully edit-able datagrid.

When I click the "Commit Changes" button, I want it to save the changes to the record in the database.

I have some of the code, but am confused about how to go from here.

Any help would be great!!

Thank you:::

The code for the method that is called when the button is clicked::::

Protected Sub gridEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridEdit.SelectedIndexChanged
Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter
Dim commitNow As New Data.SqlClient.SqlCommand



commitNow.CommandText = "UPDATE rate_Details SET depositType = depositType2, minToOpen = minToOpen2, maturity = maturity2, minBalanceToEarn = minBalanceToEarn2, interestRate = interestRate2, APY = apy, compFreq = compFreq2 WHERE rateID = rateID2"
myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")
myCommitCommand = New Data.SqlClient.SqlDataAdapter("UPDATE rate_Details SET depositType = depositType2, minToOpen = minToOpen2, maturity = maturity2, minBalanceToEarn = minBalanceToEarn2, interestRate = interestRate2, APY = apy, compFreq = compFreq2 WHERE rateID = rateID2", myCommitConnection)
myCommitConnection.Open()
commitNow.ExecuteNonQuery()
ContextUtil.SetComplete()
myCommitConnection.Close()
End Sub

[Ribeyed]

hi again :)

whats not working or what part are you stuck on?

[macker]

I really don't know what part is not working.....

It is just not saving the changes in the database.

Do I need to add parameters to that code, or something?

Thanks

[Ribeyed]

yes you'll need to pass the information from your gridview to the SQLStatement

Is doesn't look like your passing in anything to this statement:

"UPDATE rate_Details SET depositType = depositType2, minToOpen = minToOpen2, maturity = maturity2, minBalanceToEarn = minBalanceToEarn2, interestRate = interestRate2, APY = apy, compFreq = compFreq2 WHERE rateID = rateID2"

[macker]

What would be the most simple way to do that?

[Ribeyed]

i knew you where going to ask that! Should have gave you some code lol.


UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2"
That will make them parameters.
Then add this line for each paramater:

myCommitCommand.Parameters.Add("@depositType2", System.Data.SqlDbType.Int)
.Parameters("@depositType2").SourceColumn = depositType2

[macker]

Sorry man, one more question.... lol

It is crashing on the line "commitNow.ExecuteNonScalar()"
i have tried ".ExecuteNonQuery()", and ".ExecuteScalar():
I get the same error with all of these......
"connection property has not been initialized"

I thought I did that, see what you think, i guess?????
Protected Sub gridEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridEdit.SelectedIndexChanged
Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter
Dim commitNow As New Data.SqlClient.SqlCommand

commitNow.CommandText = "UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2"
myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")

myCommitConnection.Open()
myCommitCommand = New Data.SqlClient.SqlDataAdapter("UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2", myCommitConnection)

commitNow.Parameters.Add("@depositType2", SqlDbType.VarChar)
commitNow.Parameters("@depositType2").SourceColumn = "depositType2"
commitNow.Parameters.Add("@minToOpen2", SqlDbType.VarChar)
commitNow.Parameters("@minToOpen2").SourceColumn = "minToOpen2"
commitNow.Parameters.Add("@maturity2", SqlDbType.VarChar)
commitNow.Parameters("@maturity2").SourceColumn = "maturity2"
commitNow.Parameters.Add("@minBalanceToEarn2", SqlDbType.VarChar)
commitNow.Parameters("@minBalanceToEarn2").SourceColumn = "minBalanceToEarn2"
commitNow.Parameters.Add("@interestRate2", SqlDbType.Decimal)
commitNow.Parameters("@interestRate2").SourceColumn = "interestRate2"
commitNow.Parameters.Add("@apy", SqlDbType.Decimal)
commitNow.Parameters("@apy").SourceColumn = "apy"
commitNow.Parameters.Add("@compFreq2", SqlDbType.Decimal)
commitNow.Parameters("@compFreq2").SourceColumn = "compFreq2"

commitNow.Parameters.Add("@rateID2", SqlDbType.Decimal)
commitNow.Parameters("@rateID2").SourceColumn = "rateID2"



commitNow.ExecuteNonScalar()
ContextUtil.SetComplete()
myCommitConnection.Close()
End Sub

[Ribeyed]

I normally use stored procedures never did sql statements from within my code however you still have to open the sqlconnection first:


myCommitConnection.Open()


Then execute the nonquery on your adapter:


commitNow.ExecuteNonQuery()

Then close your connection

myCommitConnection.Close()

[macker]

I have that already, and it does not seem to work, still crashing on the same line.

Protected Sub gridEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridEdit.SelectedIndexChanged
Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter
Dim commitNow As New Data.SqlClient.SqlCommand

commitNow.CommandText = "UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2"
myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")


myCommitCommand = New Data.SqlClient.SqlDataAdapter("UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2", myCommitConnection)

commitNow.Parameters.Add("@depositType2", SqlDbType.VarChar)
commitNow.Parameters("@depositType2").SourceColumn = "depositType2"
commitNow.Parameters.Add("@minToOpen2", SqlDbType.VarChar)
commitNow.Parameters("@minToOpen2").SourceColumn = "minToOpen2"
commitNow.Parameters.Add("@maturity2", SqlDbType.VarChar)
commitNow.Parameters("@maturity2").SourceColumn = "maturity2"
commitNow.Parameters.Add("@minBalanceToEarn2", SqlDbType.VarChar)
commitNow.Parameters("@minBalanceToEarn2").SourceColumn = "minBalanceToEarn2"
commitNow.Parameters.Add("@interestRate2", SqlDbType.Decimal)
commitNow.Parameters("@interestRate2").SourceColumn = "interestRate2"
commitNow.Parameters.Add("@apy", SqlDbType.Decimal)
commitNow.Parameters("@apy").SourceColumn = "apy"
commitNow.Parameters.Add("@compFreq2", SqlDbType.varchar)
commitNow.Parameters("@compFreq2").SourceColumn = "compFreq2"

commitNow.Parameters.Add("@rateID2", SqlDbType.Decimal)
commitNow.Parameters("@rateID2").SourceColumn = "rateID2"

myCommitConnection.Open()

commitNow.ExecuteNonQuery()
myCommitConnection.Close()
End Sub

[Ribeyed]

OK try adding these lines:

after this line:
myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")

add this:


commitNow.CommandType = System.Data.CommandType.Text


remove this line:
myCommitCommand = New Data.SqlClient.SqlDataAdapter("UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2", myCommitConnection)

don't think you need it.

and after this line:

commitNow.Parameters("@rateID2").SourceColumn = "rateID2"

Add this line:

commitNow.Connection = myCommitConnection


give that a try see how you get on. Don't have to time to set up this code here so im just trying to find errors by looking through your code.

[macker]

I did that, and it gets past that now.
Now I get an error that says the expected parameter is not supplied, @depositType2

[Ribeyed]

ok so all the code you have works its just what the error is saying you haven't supplied a parameter for depositType2.

You have to supply the values entered by the user all you are doing is passing the string "depositType2" to the paramater not a variable depositType2.

This line here:

commitNow.Parameters("@depositType2").SourceColumn = "depositType2"

needs to be:
commitNow.Parameters("@depositType2").SourceColumn = theTextbox.Text

[macker]

I put my procedure into sql. I use sql Management Studio 2005.

Now it is just not picking up the parameters, it says the expected parameter was not supplied.

Protected Sub gridEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridEdit.SelectedIndexChanged
Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter
Dim commitNow As New Data.SqlClient.SqlCommand

' commitNow.CommandText = "UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2"
myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")

commitNow.CommandType = System.Data.CommandType.StoredProcedure
commitNow.CommandText = "details_update"
'myCommitCommand = New Data.SqlClient.SqlDataAdapter("UPDATE rate_Details SET depositType = @depositType2, minToOpen = @minToOpen2, maturity = @maturity2, minBalanceToEarn = @minBalanceToEarn2, interestRate = @interestRate2, APY = @apy, compFreq = @compFreq2 WHERE rateID = @rateID2", myCommitConnection)

commitNow.Parameters.Add("@depositType", SqlDbType.VarChar)
commitNow.Parameters("@depositType").Value = SourceColumn("depositType2")
commitNow.Parameters.Add("@minToOpen", SqlDbType.VarChar)
commitNow.Parameters("@minToOpen").Value = SourceColumn("minToOpen2")
commitNow.Parameters.Add("@maturity", SqlDbType.VarChar)
commitNow.Parameters("@maturity").Value = SourceColumn("maturity2")
commitNow.Parameters.Add("@minBalanceToEarn", SqlDbType.VarChar)
commitNow.Parameters("@minBalanceToEarn").Value = SourceColumn("minBalanceToEarn2")
commitNow.Parameters.Add("@interestRate", SqlDbType.Decimal)
commitNow.Parameters("@interestRate").Value = SourceColumn("interestRate2")
commitNow.Parameters.Add("@apy", SqlDbType.Decimal)
commitNow.Parameters("@apy").Value = SourceColumn("apy")
commitNow.Parameters.Add("@compFreq", SqlDbType.varchar)
commitNow.Parameters("@compFreq").Value = SourceColumn("compFreq2")
commitNow.Parameters.Add("@rateID", SqlDbType.Decimal)
commitNow.Parameters("@rateID").Value = SourceColumn("rateID2")
commitNow.Connection = myCommitConnection
myCommitConnection.Open()

commitNow.ExecuteNonQuery()

myCommitConnection.Close()
End Sub

[macker]

I changed it to this, and it does the same thing.
It is saying that the procedure is not getting any parameters sent to it.
Any ideas on that? I have moved the proc to sql management studio.
This code is different form the first post from today. They both, however, do the same thing, don't work.. lol.
Thanks


Protected Sub gridEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridEdit.SelectedIndexChanged

' Try
Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter
Dim cmd As New Data.SqlClient.SqlCommand

myCommitConnection = New Data.SqlClient.SqlConnection("server=SNSQLDEV01;" & "database=StifelBank;Trusted_Connection=Yes")
myCommitConnection.Open()
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "details_update"

cmd.Parameters.Add("@depositType", SqlDbType.VarChar)
cmd.Parameters("@depositType").SourceColumn = "depositType2"
cmd.Parameters.Add("@minToOpen", SqlDbType.VarChar)
cmd.Parameters("@minToOpen").SourceColumn = "minToOpen2"
cmd.Parameters.Add("@maturity", SqlDbType.VarChar)
cmd.Parameters("@maturity").SourceColumn = "maturity2"
cmd.Parameters.Add("@minBalanceToEarn", SqlDbType.VarChar)
cmd.Parameters("@minBalanceToEarn").SourceColumn = "minBalanceToEarn2"
cmd.Parameters.Add("@interestRate", SqlDbType.Decimal)
cmd.Parameters("@interestRate").SourceColumn = "interestRate2"
cmd.Parameters.Add("@apy", SqlDbType.Decimal)
cmd.Parameters("@apy").SourceColumn = "apy"
cmd.Parameters.Add("@compFreq", SqlDbType.varchar)
cmd.Parameters("@compFreq").SourceColumn = "compFreq2"
cmd.Parameters.Add("@rateID", SqlDbType.Int)
cmd.Parameters("@rateID").SourceColumn = "rateID2"
cmd.Connection = myCommitConnection

cmd.ExecuteNonQuery()

myCommitConnection.Close()

'Catch ex As Exception
' 'lblerror.text = "Error Processing Save: " ex
' Me.lblError.Text = GetExceptionMessage(ex)
' lblError.ForeColor = System.Drawing.Color.Red
'End Try
End Sub

[macker]

I can not use the text property of the textboxes, because they are in a datagrid, therefore, they all have the same name, and it would not be able to pull the text properly. It would not know which box to get, would it?

[Ribeyed]

right your getting there but your still missing a few things here.


Dim myCommitConnection As Data.SqlClient.SqlConnection
Dim myCommitCommand As Data.SqlClient.SqlDataAdapter

Dim sqlProcedure As String
sqlProcedure = "StoredProcedureName"

myCommitConnection = New Data.SqlClient.SqlConnection("server=DBWEBWORKSTATIO;" & "database=StifelBank;Trusted_Connection=Yes")
Dim cmd As New Data.SqlClient.SqlCommand(sqlProcedure, myCommitConnection)
cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Parameters.Add("@depositType2", SqlDbType.VarChar)
cmd.Parameters("@depositType2").SourceColumn = GridViewItem
cmd.Parameters.Add("@minToOpen2", SqlDbType.VarChar)
' etc......
'then open your connection.....
myCommitConnection.Open()
cmd.ExecuteNonQuery()
myCommitConnection.Close()


Now you have to put all this in a loop. You need to loop through your GridView and run the above code for each row in your GridView.
You need to substiute the parameter variables with the items in your gridview.