vb.net having an issue with a sql update

I have an asp.net project with vb.net code behind.  I am trying to update a sql table with any values that a user might have changed.
I don't get an error when I run the code, but nothing is updated.
any help would be great
thanks

 Dim ProjectInfo As ProjectInfo = New ProjectInfo
        Dim strConnString As String = ConfigurationManager.ConnectionStrings("TimberOpsConnectionString").ConnectionString
        Dim strQuery As String = "UPDATE [tblProject] Set [Status] = @Status, [GL_Code] = @GL_Code, [Project_Code] = @Project_Code, [Notification_Number] = @Notification_Number, [FKEYProjectType] = @FKEYProjectType, [Name] = @Name, [Brand] = @Brand,  [Is_Complete] = @Is_Complete, [SubAccount] = @SubAccount, [Plan_Year] = @Plan_Year, [CrossOver_Year] = @CrossOver_Year, [Is_Road_Complete] = @Is_Road_Complete, [Is_Road_Applicable] = @Is_Road_Applicable, [Reviewed] = @Reviewed, [Road_Build_Year] = @Road_Build_Year, [Road_Rock_Year] = @Road_Rock_Year, [Acres] = @Acres, [Project_Mean_Elevation] = @Project_Mean_Elevation, [Lat_Degrees1] = @Lat_Degrees1, [Lat_Minutes1] = @Lat_Minutes1, [Lat_Seconds1] = @Lat_Seconds1, [Long_Degrees1] = @Long_Degrees1, [Long_Minutes1] = @Long_Minutes1, [Long_Seconds1] = @Long_Seconds1, [LegalLocation] = @LegalLocation, [Primary_Tract] = @Primary_Tract, [FKEYTimberOwner] = @FKEYTimberOwner, [Project_Owner] = @Project_Owner WHERE [KEYField] = @KEYField"
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand(strQuery, con)
        Try
            con.Open()

            If cmbStatus.SelectedValue Is Nothing Then
                cmd.Parameters("@Status").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Status", cmbStatus.SelectedValue)
            End If

            If txtGLCode.Text Is Nothing Then
                cmd.Parameters("@GL_Code").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@GL_Code", txtGLCode.Text)
            End If

            If txtProjCode.Text Is Nothing Then
                cmd.Parameters("@Project_Code").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Project_Code", txtProjCode.Text)
            End If

            If TxtNoteNumber.Text Is Nothing Then
                cmd.Parameters("@Notification_Number").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Notification_Number", TxtNoteNumber.Text)
            End If

            If cmbProjType.SelectedValue Is Nothing Then
                cmd.Parameters("@FKEYProjectType").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@FKEYProjectType", cmbProjType.SelectedValue)
            End If

            If txtProjName.Text Is Nothing Then
                cmd.Parameters("@Name").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Name", txtProjName.Text)
            End If

            If txtProjBrand.Text Is Nothing Then
                cmd.Parameters("@Brand").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Brand", txtProjBrand.Text)
            End If

            'If rdpLoadStartDate.SelectedDate Is Nothing Then
            '    cmd.Parameters("@Load_Start_Date").Value = DBNull.Value
            'Else
            '    cmd.Parameters.AddWithValue("@Load_Start_Date", rdpLoadStartDate.SelectedDate)
            'End If

            'If rdpLoadEndDate.SelectedDate Is Nothing Then
            '    cmd.Parameters("@Load_End_Date").Value = DBNull.Value
            'Else
            '    cmd.Parameters.AddWithValue("@Load_End_Date", rdpLoadEndDate.SelectedDate)
            'End If

            If btnProjectComplete.Checked = True Then
                cmd.Parameters.AddWithValue("@Is_Complete", "True")
            Else
                cmd.Parameters.AddWithValue("@Is_Complete", "False")
            End If

            If txtSubAccount.Text Is Nothing Then
                cmd.Parameters("@SubAccount").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@SubAccount", txtSubAccount.Text)
            End If

            If txtPlantYear.Text Is Nothing Then
                cmd.Parameters("@Plan_Year").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Plan_Year", txtPlantYear.Text)
            End If

            If txtCrossOverYear.Text Is Nothing Then
                cmd.Parameters("@CrossOver_Year").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@CrossOver_Year", txtCrossOverYear.Text)
            End If

            If btnRoadComplete.Checked = True Then
                cmd.Parameters.AddWithValue("@Is_Road_Complete", "True")
            Else
                cmd.Parameters.AddWithValue("@Is_Road_Complete", "False")
            End If
            If btnRoadNotApplicable.Checked = True Then
                cmd.Parameters.AddWithValue("@Is_Road_Applicable", "True")
            Else
                cmd.Parameters.AddWithValue("@Is_Road_Applicable", "False")
            End If


            If cmbReviewed.SelectedValue Is Nothing Then
                cmd.Parameters("@Reviewed").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Reviewed", cmbReviewed.SelectedValue)
            End If

            If txtRockYear.Text Is Nothing Then
                cmd.Parameters("@Road_Rock_Year").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Road_Rock_Year", txtRockYear.Text)
            End If

            If txtBuildYear.Text Is Nothing Then
                cmd.Parameters("@Road_Build_Year").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Road_Build_Year", txtBuildYear.Text)
            End If

            If txtAcres.Text Is Nothing Then
                cmd.Parameters("@Acres").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Acres", txtAcres.Text)
            End If


            If txtProjMeanElevation.Text Is Nothing Then
                cmd.Parameters("@Project_Mean_Elevation").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Project_Mean_Elevation", txtProjMeanElevation.Text)
            End If

            If txtLatDeg.Text Is Nothing Then
                cmd.Parameters("@Lat_Degrees1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Lat_Degrees1", txtLatDeg.Text)
            End If

            If txtLatMin.Text Is Nothing Then
                cmd.Parameters("@Lat_Minutes1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Lat_Minutes1", txtLatMin.Text)
            End If

            If txtLatSec.Text Is Nothing Then
                cmd.Parameters("@Lat_Seconds1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Lat_Seconds1", txtLatSec.Text)
            End If

            If txtLongDeg.Text Is Nothing Then
                cmd.Parameters("@Long_Degrees1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Long_Degrees1", txtLongDeg.Text)
            End If

            If txtLongMin.Text Is Nothing Then
                cmd.Parameters("@Long_Minutes1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Long_Minutes1", txtLongMin.Text)
            End If

            If txtLongSec.Text Is Nothing Then
                cmd.Parameters("@Long_Seconds1").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Long_Seconds1", txtLongSec.Text)
            End If

            If txtLegalLocation.Text Is Nothing Then
                cmd.Parameters("@LegalLocation").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@LegalLocation", txtLegalLocation.Text)
            End If

            If cmbTract.SelectedValue Is Nothing Then
                cmd.Parameters("@Primary_Tract").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Primary_Tract", cmbTract.SelectedValue)
            End If

            If cmbOwner.SelectedValue Is Nothing Then
                cmd.Parameters("@FKEYTimberOwner").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@FKEYTimberOwner", cmbOwner.SelectedValue)
            End If

            If cmbProjectOwner.SelectedValue Is Nothing Then
                cmd.Parameters("@Project_Owner").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Project_Owner", cmbProjectOwner.SelectedValue)
            End If

            cmd.Parameters.AddWithValue("@KEYField", ProjectInfo.ProjID)
            cmd.ExecuteNonQuery()
            con.Close()
            MsgBox("finished")
            FillPage()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try



    End Sub

Open in new window

David ModugnoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ModugnoAuthor Commented:
just an added note.. I added all the if statements in case I had an empty value..
could that be the issue
0
David ModugnoAuthor Commented:
Any thoughts experts
0
David ModugnoAuthor Commented:
any help would be great... thanks in advance
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

AndyAinscowFreelance programmer / ConsultantCommented:
Your code is as follows:
 Dim ProjectInfo As ProjectInfo = New ProjectInfo
....
            cmd.Parameters.AddWithValue("@KEYField", ProjectInfo.ProjID)
            cmd.ExecuteNonQuery()

Our put another way.  Nothing is updated because you do not supply any key value to identify the row to update.
1
AndyAinscowFreelance programmer / ConsultantCommented:
ps.  You could help yourself by learning about breakpoints and stepping through code.  I guess you would have spotted this in less time than it took to write the question.
0
David ModugnoAuthor Commented:
That is a property in a class
I have verified that it has a value
0
David ModugnoAuthor Commented:
just to be safe I checked again... projectinfo.projid - has the correct value
any other ideas
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>just to be safe I checked again... projectinfo.projid - has the correct value

In the code you supplied that is apparently not the case.  There is one way I can think of that it could be but that would be a nonsensical way to code things.  Please supply the code you use without editing anything out.  (If it is not edited then the code for the ProjectInfo class as well).

How do you check if things are updated in the database?
0
David ModugnoAuthor Commented:
I added one of the project numbers to the project ID manually and it still did not work
I have database management open so I can easily see if it updates
so all of the values now come from controls on the page and the keyField is as below
thanks again for the help
 Dim iProjID As Integer = 1435
 cmd.Parameters.AddWithValue("@KEYField", iProjID)

Open in new window

0
David ModugnoAuthor Commented:
I made a change to make sure that any control that is empty is being handled correctly and found that they are not
I changed is nothing to = ""
I found the is nothing was being skipped - but = "" gives an error
an SqlParameter with ParameterName '@SubAccount' is not contained by SqlParameterCollection.

here is the change - before the change the msgbox did not fire - thanks again for the help
If txtSubAccount.Text = "" Then
                MsgBox("is nothing")
                cmd.Parameters("@SubAccount").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@SubAccount", txtSubAccount.Text)
            End If

Open in new window

0
David ModugnoAuthor Commented:
this is how it did look

 If txtSubAccount.Text Is Nothing Then
                cmd.Parameters("@SubAccount").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@SubAccount", txtSubAccount.Text)
            End If

Open in new window

0
David ModugnoAuthor Commented:
I made another change.. now there are no errors now but it still does not update - I'm really stuck here
If TxtNoteNumber.Text = "" Then
                cmd.Parameters.AddWithValue("@Notification_Number", DBNull.Value)
                'cmd.Parameters("@Notification_Number").Value = ""
            Else
                cmd.Parameters.AddWithValue("@Notification_Number", TxtNoteNumber.Text)
            End If

Open in new window

0
AndyAinscowFreelance programmer / ConsultantCommented:
From your original question
I don't get an error when I run the code,
How could the query have an incorrect parameter and not error when you ran it?

Have you singlestepped through the code and checked just what is being put into each parameter?  This will allow you to create the SQL as a line of text (eg. in notepad - copy/paste) which you can then run directly against the database to see if it works and does what you expect.
0
David ModugnoAuthor Commented:
prior to the change it was skipping that code block...
If cmbStatus.SelectedValue Is Nothing Then

was being skipped and moved on to the else part even though there was no value in the text box

after changing to if txtbox.text = "" it now recognized that was true and moved on to adding the parameter with a null value

so yes code continues without error but it never updates
0
AndyAinscowFreelance programmer / ConsultantCommented:
Have you single stepped to see just what lines are being called AND checked the SQL command being generated as I suggested earlier?

        Try
            con.Open()

            If cmbStatus.SelectedValue Is Nothing Then
                cmd.Parameters("@Status").Value = DBNull.Value
            Else
                cmd.Parameters.AddWithValue("@Status", cmbStatus.SelectedValue)
            End If
....and all the others

Open in new window


Where do you add the parameters when you have a 'nothing'.  I think you should have an AddWithValue for each even if the value is DBNull.Value
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ModugnoAuthor Commented:
I have stepped through them all and it all followed the correct path..
could it be a datatype thing

just grasping at anything at this point
0
AndyAinscowFreelance programmer / ConsultantCommented:
>>I have stepped through them all and it all followed the correct path..

And ??
0
David ModugnoAuthor Commented:
it recognizes when there is no value and moves to adding a null value to the property add
0
David ModugnoAuthor Commented:
slowing down and really looking at what was going on was the answer
thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.