update db ?

[Guest]

I am having an issue with getting my ds to update my db. I am pulling info
from the NW db on my Default.aspx page. I then click the edit btn and pass
the data to my Details.aspx page. That works fine. The issue is getting the
ds to actually update the db. I know this is simple, but I can't seem to
figure out what I am doing wrong.
Here is the code... please don't laugh to hard, as this is the first time
that I have tried to do this with code-behind, I normally just use a SqlDS on
the page, but want to make the page look cleaner...
Protected Sub dvDetails_ItemUpdating(ByVal sender As Object, ByVal e As
DetailsViewUpdateEventArgs) Handles dvDetails.ItemUpdating
Dim connStr As String =
ConfigurationManager.ConnectionStrings("NWConnectionString").ToString()
Dim conn As New SqlConnection(connStr)
'Dim strSql As String = "SELECT * FROM Employees WHERE
EmployeeID=@EmployeeID"
Dim upSql As String = "UPDATE Employees SET LastName=@LastName,
FirstName=@FirstName, Title=@Title, Address=@Address," & _
"City=@City, Region=@Region, PostalCode=@PostalCode,
HireDate=@HireDate WHERE EmployeeID=@EmployeeID"
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim ds1 As New DataSet
Dim upCmd As New SqlCommand(upSql, conn)
Dim idParam As SqlParameter = upCmd.Parameters.Add("@EmployeeID",
SqlDbType.Int, 4, "EmployeeID")


Try
' da.SelectCommand = New SqlCommand(strSql, conn)
'da.Fill(ds, "Employees")

upCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20,
"LastName")
upCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10,
"FirstName")
upCmd.Parameters.Add("@Title", SqlDbType.VarChar, 30, "Title")
upCmd.Parameters.Add("@Address", SqlDbType.VarChar, 60, "Address")
upCmd.Parameters.Add("@City", SqlDbType.VarChar, 15, "City")
upCmd.Parameters.Add("@Region", SqlDbType.VarChar, 15, "Region")
upCmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10,
"PostalCode")
upCmd.Parameters.Add("@HireDate", SqlDbType.DateTime, 8,
"HireDate")

idParam.SourceVersion = DataRowVersion.Original

conn.Open()
'Update Employees Table

ds1 = ds.GetChanges(DataRowState.Modified)
da.Update(ds1)
ds.Merge(ds1, False, MissingSchemaAction.Add)
ds.AcceptChanges()
dvDetails.DataBind()

Catch ex As Exception
'Display Error
Console.WriteLine("Error: " & ex.ToString())

Finally
'Close Connection
conn.Close()
'Redirect to Home
Response.Redirect("~/Default.aspx")

End Try
End Sub

As this is for learning purposes only, if you could please let me know what
needs to be changed and the format of the actual update that would be great.
I want to start using this on a normal basis, but really want to understand
it before putting it into a live app.

Thanks

 

[Scott M.]

It doesn't look like you are instantiating your parameter objects, try this:
upCmd.Parameters.Add(New Parameter("@LastName", SqlDbType.VarChar, 20,
"LastName"))

 

[Guest]

Thank you Scott. I will give that a try in just a minute... other than that
does the code look correct?

 

[Guest]

Tried just like what you typed and I get:
Overload resolution failed because no accessible "New" accepts this number
of arguments

 

[Scott M.]

You'll need to change the arguments you are passing in to the parameter to
follow the acceptable signature. Look at the intellisense of VS while
typing out the line and it will tell you what the correct possibilites are.

 

[Guest]

changed the argument to : upCmd.Parameters.Add(New Parameter("@LastName",
TypeCode.Char, "LastName"))
I no longer get any errors, but I am still not updating the db.

 

[Scott M.]

Because it doesn't look like you are assigning a value to the parameter.
You need to look at how to create parameter objects. Try this:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqlparameter.aspx

The simplest form would be:
Dim lName As String = "Smith"
upCmd.Parameters.Add(New Parameter("@LastName", lName))

 

[Guest]

along with you on this joy ride
I did this for each textbox, based off an example found at:
http://msdn2.microsoft.com/en-us/li...rols.templatefield.edititemtemplate.aspxGoing

Dim lname As TextBox = CType(dvDetails.FindControl("LName"), TextBox)
e.NewValues("lname1") = lname.Text
upCmd.Parameters.Add(New Parameter("@LastName", TypeCode.Char, "lname1"))

Now if I understand properly this should be converting the LName control in
the dvDetails container to a textbox, then assigning the text property of the
control to the e.NewValues of the LastName field of the Row in the DB.
e.NewValues is what is used to update the db. Am I correct in my thinking?

I still do not get the data saved to the db, but I wanted to show that I am
learning, I am just lost as to what I keep doing wrong.

Thanks,
Stupid1

 

[Guest]

Also I was having a brain fart and had my error handling setup wrong... now I
get the following displayed when I update:
System.InvalidCastException: The SqlParameterCollection only accepts
non-null SqlParameter type objects, not Parameter objects.

I am trying to find what this means and that might just resolve my issues.

 

[Guest]

I must have something seriously messed up. I am trying to delare the
parameters, upCmd.Parameters.Add(*****), but the ".Add" is not available?????
What in the world am I doing wrong?

upCmd is declared as: Dim upCmd As New SqlCommand(upSql, conn)
and upSql is my String that contains the update command, conn is the
conenction to the db.

now I am really lost