none
Where to ask question..... RRS feed

  • Question

  • I have a question about a database problem in visual basic. Which forum do I ask the question.

    fenix

    Sunday, June 7, 2015 9:42 AM

Answers

  • The best thing to do in such case is to take the query you're building via code with the same values you're passing, to the Query Designer and run it there to see if it will update successfully or not.

    You have many values in your where condition and some of them contains DateTime, if the format doesn't fit the comparison will fail.

    You can also eliminate the params in where condition one by one to see on which value the comparison is failing.


    Fouad Roumieh



    Sunday, June 7, 2015 5:17 PM

All replies

  • Well, it depends on whether the issue is related to the database itself or a database query or similar. Then you should probably ask in the SQL Server forums if you are using a SQL Server database: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    If you are using some non-Microsoft technology or product (for example MySql or Oracle) you should not ask in these forums at all as they are intended for Microsoft's products and technologies only. Then you should ask in the manufacturer's own forums or to the manufacturer of that software directly.

    If your issue is related to some Visual Basic code this is the right forum to ask such questions.

    If you are unsure in which particular forum your question belongs you could always try to ask it in the forum where you think it belongs. The thread may be moved to another forum by a moderator if it makes sense.

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread in an appropriate forum if you have a new question.


    Sunday, June 7, 2015 12:26 PM
  • >>> database problem in visual basic.

    We don't if your problem is code or DB specific, write down your problem here and if it is not the right forum a moderator will move it for you to the forum related if its on msdn, or you will be advised to an external forum.


    Fouad Roumieh

    Sunday, June 7, 2015 2:59 PM
  • I have a question about a database problem in visual basic. Which forum do I ask the question.

    fenix


    Without knowing what the question is who could know what forum to advise you about?

    La vida loca

    Sunday, June 7, 2015 3:17 PM
  • Hi,

        I think the posting should go here. It can always be moved.

    I am updating a record. The code runs with no errors, but the database record never gets updated. 

    Any help would be appreciated.

    Fenix

       Public Shared Function UpdateLogin(oldLogin As AppDB, newLogin As AppDB, ByRef Login As AppDB, AuditHdr As auditHdr) As List(Of AuditLineItem)
            Dim LineItemList As New List(Of AuditLineItem)
            Dim LineItemSeq As Integer = 0
            Dim connection As SqlConnection = BTDB.GetConnection()
            Dim updateStatement As String =
                "UPDATE AppGlobal SET " &
                "AppID = @NewAppID, " &
                "AppPassword = @NewAppPassword, " &
                "AppNickName = @NewAppNickName, " &
                "AppCreateDate = @NewAppCreateDate, " &
                "AppDelHist = @NewAppDelHist, " &
                "AppDelHistDatTim = @NewAppDelHistDatTim " &
                "WHERE AccountID = @OldAccountID AND " &
                "AppID = @OldAppID AND " &
                "AppPassword = @OldAppPassword AND " &
                "AppNickName = @OldAppNickName AND " &
                "AppCreateDate = @OldAppCreateDate AND " &
                "AppDelHist = @OldAppDelHist AND " &
                "AppDelHistDatTim = @OldAppDelHistDatTim"
    
            Dim UpdateCommand As New SqlCommand(updateStatement, connection)
    
            AuditHdr = New auditHdr
            'AuditHdr.AuditID = 0
            AuditHdr.AccountID = 999999
            'Dim auditHdr As auditHdr
            AuditHdr.CreateDate = Now
            AuditHdr.TableName = "AppGlobal"
            AuditHdr.Action = "D"
            AuditHdr.ScreenID = gm_ScrID
    
            Dim auditLineItem As AuditLineItem
    
            UpdateCommand.Parameters.AddWithValue("@AccountID", Login.AccountID)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "AccountID"
            auditLineItem.BeforeValue = oldLogin.AppPassword
            auditLineItem.AfterValue = newLogin.AppPassword
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppID", Login.AppID)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "E-mail"
            auditLineItem.BeforeValue = oldLogin.AppID
            auditLineItem.AfterValue = newLogin.AppID
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppPassword", Login.AppPassword)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "Password"
            auditLineItem.BeforeValue = oldLogin.AppPassword
            auditLineItem.AfterValue = newLogin.AppPassword
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppNickName", Login.AppNickName)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "NickName"
            auditLineItem.BeforeValue = oldLogin.AppNickName
            auditLineItem.AfterValue = newLogin.AppNickName
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppCreateDate", Login.AppCreateDate)
            auditLineItem = New AuditLineItem
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "Create Date"
            auditLineItem.BeforeValue = CStr(oldLogin.AppCreateDate)
            auditLineItem.AfterValue = CStr(newLogin.AppCreateDate)
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppDelHist", Login.AppDelHist)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "Delete or History"
            auditLineItem.BeforeValue = oldLogin.AppDelHist
            auditLineItem.AfterValue = newLogin.AppDelHist
            gm_AppDelHist = newLogin.AppDelHist
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@AppDelHistDatTim", Login.AppDelHistDateTim)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "Delete or History Date"
            auditLineItem.BeforeValue = CStr(oldLogin.AppDelHistDateTim)
            auditLineItem.AfterValue = CStr(newLogin.AppDelHistDateTim)
            gm_AppDelHistDate = newLogin.AppDelHistDateTim
            LineItemList.Add(auditLineItem)
    
            UpdateCommand.Parameters.AddWithValue("@NewAccountID", newLogin.AccountID)
            UpdateCommand.Parameters.AddWithValue("@NewAppID", newLogin.AppID)
            UpdateCommand.Parameters.AddWithValue("@NewAppPassword", newLogin.AppPassword)
            UpdateCommand.Parameters.AddWithValue("@NewAppNickName", newLogin.AppNickName)
            UpdateCommand.Parameters.AddWithValue("@NewAppCreateDate", newLogin.AppCreateDate)
            UpdateCommand.Parameters.AddWithValue("@NewAppDelHist", newLogin.AppDelHist)
            UpdateCommand.Parameters.AddWithValue("@NewAppDelHistDatTim", newLogin.AppDelHistDateTim)
            UpdateCommand.Parameters.AddWithValue("@OldAccountID", oldLogin.AccountID)
            UpdateCommand.Parameters.AddWithValue("@OldAppID", oldLogin.AppID)
            UpdateCommand.Parameters.AddWithValue("@OldAppPassword", oldLogin.AppPassword)
            UpdateCommand.Parameters.AddWithValue("@OldAppNickName", oldLogin.AppNickName)
            UpdateCommand.Parameters.AddWithValue("@OldAppCreateDate", oldLogin.AppCreateDate)
            UpdateCommand.Parameters.AddWithValue("@OldAppDelHist", oldLogin.AppDelHist)
            UpdateCommand.Parameters.AddWithValue("@OldAppDelHistDatTim", oldLogin.AppDelHistDateTim)
    
            Try
                connection.Open()
                UpdateCommand.ExecuteNonQuery()
                Dim insertCount As Integer = UpdateCommand.ExecuteNonQuery
    
                If insertCount < 1 Then
                    LineItemList = Nothing
                End If
    
            Catch ex As SqlException
                MessageBox.Show("SQL Server error #4  " & ex.Number & ": " & ex.Message, ex.GetType.ToString)
            Finally
                connection.Close()
            End Try
    
            Return LineItemList
    
        End Function

    Sunday, June 7, 2015 4:57 PM
  • The best thing to do in such case is to take the query you're building via code with the same values you're passing, to the Query Designer and run it there to see if it will update successfully or not.

    You have many values in your where condition and some of them contains DateTime, if the format doesn't fit the comparison will fail.

    You can also eliminate the params in where condition one by one to see on which value the comparison is failing.


    Fouad Roumieh



    Sunday, June 7, 2015 5:17 PM
  • Hi, I took all the WHERE lines out except for: 

               "WHERE AccountID = @OldAccountID" and the record got updated in the database. AccountID is the prime key.

    Now I have to find out the rest of the WHERE statements are incorrect and fix them.

    Regards,

    Fenix

    Sunday, June 7, 2015 11:58 PM