Update Statements won't take variables or form control data

[annbransom]

HELP!!! I am building a website which has a lot of database functionality on it. All of my select statements work and my insert statements. But for some reason I am having trouble getting my update statements to work. I've tried them on several different pages and so the common denominator is me, I just don't know what I am doing wrong. Take the sub below for example. Some of the information being passed in the Update statement comes from variables I've declared. Some of it comes straight from the text boxes on my form. If you look at "City" however, I give it an explicit value in the statement. When I click the update button, the only thing that updates is the city!!! Help! Let me know if seeing any other parts of the code would be helpful.

Thanks,

Ann




Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


Dim sqlConn As SqlConnection = New SqlConnection(dbConn)


Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand()


Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter


Dim dt As New DataTable


Dim FileNamePartOne As String


FileNamePartOne = LastNameTextBox.Text & "_"


Dim FilePathConstant As String = Server.MapPath("Pics\")


Dim file As Array = Split(FileUpload1.FileName, "\")


Dim fi As New FileInfo("\Pics\" & FileNamePartOne & file(file.Length - 1))


Dim FileN As String


'fi.ToString = the path for db


If fi.Exists Then


fi.Delete()


End If


If FileUpload1.FileName <> "" Then


FileUpload1.PostedFile.SaveAs(FilePathConstant &amp; FileNamePartOne &amp; file(file.Length - 1))


FileN = "Pics\" &amp; FileNamePartOne &amp; file(file.Length - 1)


Else


FileN = ""


End If


Dim FName As String


FName = FirstNameTextBox.Text


FName = Replace(FName, "'", "''")


Dim LName As String


LName = LastNameTextBox.Text


LName = Replace(LName, "'", "''")


Dim Add1 As String


Add1 = AddressLine1TextBox.Text


Add1 = Replace(Add1, "'", "''")


Dim Add2 As String


Add2 = AddressLine2TextBox.Text


Add2 = Replace(Add2, "'", "''")


Dim City As String


City = CityTextBox.Text


City = Replace(City, "'", "''")


Dim Occ As String


Occ = OccupationTextBox.Text


Occ = Replace(Occ, "'", "''")


Dim SFName As String


SFName = SpouseFirstNameTextBox.Text


SFName = Replace(SFName, "'", "''")


Dim SLName As String


SLName = SpouseLastNameTextBox.Text


SLName = Replace(SLName, "'", "''")


With sqlCmd


.CommandType = CommandType.Text


.Connection = sqlConn


.CommandText = "UPDATE KidsKlub_Members SET FirstName= '" &amp; FName &amp; "', LastName = '" &amp; LName &amp; "', Email = '" &amp; EmailTextBox.Text &amp; "', AddressLine1 = '" &amp; Add1 &amp; "', AddressLine2 = '" &amp; Add2 &amp; "', City = 'Louisville', State = '" &amp; StateTextBox.Text &amp; "', ZipCode = '" &amp; ZipCodeTextBox.Text &amp; "', PhoneNum1 = '" &amp; PhoneNum1TextBox.Text &amp; "', PhoneNum2 = '" &amp; PhoneNum2TextBox.Text &amp; "', PhoneNum3 = '" &amp; PhoneNum3TextBox.Text &amp; "', Occupation = '" &amp; Occ &amp; "', Birthday = '" &amp; BirthdayTextBox.Text &amp; "', SpouseFirstName = '" &amp; SFName &amp; "', SpouseLastName = '" &amp; SLName &amp; "', SpouseBirthday = '" &amp; SpouseBirthdayTextBox.Text &amp; "', FamilyPicURL = '" &amp; FileN &amp; "' WHERE MemberID = " &amp; CInt(Session("PassID"))


.Connection.Open()


.ExecuteNonQuery()


.Connection.Close()


End With


loaddata()


End Sub

[PharmaPAC]

Ann,
I'm not a VB expert, since I use C++ for desktop appsand C# for web apps, so I may be barking up the wrong tree here. But, I'll give it a shot...

Are you using VB6 or .NET? Assuming you are using .NET, your Replace function calls looked fishy to me, so I googled "VB.Net replace" and came up with this article: http://www.knowdotnet.com/articles/vb6replace.html

After reading it, I think that your replace calls are returning empty strings. To verify this, put a breakpoint (F9) in after one of the calls and see what is happening to your strings. Replace functionality has changed from VB6 to.NET, so I would try using the more recent .NET function call methodology, like this:




Dim FName As String
FName = FirstNameTextBox.Text
FName = FName.Replace( "'", "''")


Or something like that. Look up the Replace method of the .NET String class in MSDN to see more.


Hope this helps...
PharmaPAC</CODE>

[annbransom]

I tried commenting out my replace statements and that didn't fix the problem. Those replace statements work in my insert statements anyway. I am at a total loss. Thanks for the suggestions, though!

If the variables were kicking back empty strings, wouldn't the database entry be updated with Null values?

[PharmaPAC]

What do the strings look like after the replace statement when you debug? normal? any extra or missing characters or spaces? single quotes in the right places?

[vvsharma]

I think Replace should not cause the empty string,whether the paramater to be found exists or not,your orignal string should remain as it is in the later case.As I see in your update statement ,you have already hardcoded the ' ' for each of the fields.Try not using the Replace and see if you still get an empty string?if yes,then Replace function is not the cause.









Vikram

DiscountASP.NET
www.DiscountASP.NET (http://www.discountasp.net/)

[annbransom]

I am not getting empty strings, the update is just not being made unless the value is explicitly coded. It won't take variables or information from text boxes. I tried commenting out the replace statements and it didn't help. Any other thoughts?

[joelnet]

I've created a Database Utility Library that helps simplify queries like yours. You might want to check it out... You can do away with those Replace statments and probably reduce your code by about 4-5x.

http://joel.net/software/databaseutility.aspx

binaries, source code, full documentation.






Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET