locked
is this valid? RRS feed

  • Question

  • User-1792101031 posted

    I dont get any error messages, I get 1 from the status.InnerText = ex.Message which means to me that a row was updated however, it doesnt. When opening the database the row hasnt been updated.

     

    Can someone please help me to see if the code (UPDATE sql statement) looks valid and if they can see why this wouldnt update even though it says it does?

     

    Protected Sub Assign_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Assign.Click
            Dim SqlString As String

            'Ensure all fields are filled in
            If Not SerialNumCell.Value = "" Or Not AssetNumCell.Value = "" Or Not FstNameCell.Value = "" _
               Or Not LstNameCell.Value = "" Or Not AssetNumCell.Value = "" Then

                'Build SQL Update statement
                SqlString = "UPDATE Hostname_Table, Asset_Table, User_Table SET " & _
                            "Asset_Table.Serial_Number=@Serial_Number, Asset_Table.Asset_Barcode=@Asset_Barcode," & _
                            "Asset_Table.Make=@Make, Asset_Table.Model=@Model, User_Table.User_FirstName=@FirstName," & _
                            "User_Table.User_LastName=@LastName " & _
                            "WHERE Hostname_Table.PC_Name=@HostName"

                Dim con As New OleDbConnection(My_DB)
                Dim cmd As New OleDbCommand(SqlString, con)

                cmd.Parameters.AddWithValue("@Serial_Number", SerialNumCell.Value.ToUpper())
                cmd.Parameters.AddWithValue("@Asset_Barcode", AssetNumCell.Value.ToUpper())
                cmd.Parameters.AddWithValue("@Make", MakeList.SelectedItem.ToString().ToUpper())
                cmd.Parameters.AddWithValue("@Model", ModelList.SelectedItem.ToString().ToUpper())
                cmd.Parameters.AddWithValue("@FirstName", FstNameCell.Value.ToUpper())
                cmd.Parameters.AddWithValue("@LastName", LstNameCell.Value.ToUpper())
                cmd.Parameters.AddWithValue("@HostName", HostNameCell.Value.ToUpper())

                Try
                    con.Open()
                    status.InnerText = cmd.ExecuteNonQuery()
                Catch ex As Exception
                    status.InnerText = ex.Message
                Finally
                    con.Close()
                End Try
            Else
                status.InnerText = "Form incomplete"
            End If
        End Sub

    Monday, January 5, 2009 10:39 PM

Answers

  • User-319574463 posted

     You should try running your SQL in <EDIT> as a direct query within Access </EDIT>

    Your updates simplifies down to

    UPDATE Hostname_Table SET ??  WHERE Hostname_Table.PC_Name=@HostName
    UPDATE Asset_Table  SET Serial_Number=@Serial_Number Asset_Barcode=@Asset_Barcode,
                             Asset_Table.Make=@Make, Asset_Table.Model=@Model WHERE ??
    UPDATE User_Table SET User_Table.User_FirstName=@FirstName, User_Table.User_LastName=@LastName WHERE ?? 

    Nothing is updated on Hostname_Table, so it simplifies to

    UPDATE Asset_Table  SET Serial_Number=@Serial_Number Asset_Barcode=@Asset_Barcode,
                             Asset_Table.Make=@Make, Asset_Table.Model=@Model WHERE ??
    UPDATE User_Table SET User_Table.User_FirstName=@FirstName, User_Table.User_LastName=@LastName WHERE ?? 

    Neither of these specify a where clause that references the Id value of the rows to be updated.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 7, 2009 12:26 PM

All replies

  • User-292928630 posted

     I think you need to write muliple update statements one for each table.

    Monday, January 5, 2009 11:26 PM
  • User-319574463 posted

     You should try running your SQL in <EDIT> as a direct query within Access </EDIT>

    Your updates simplifies down to

    UPDATE Hostname_Table SET ??  WHERE Hostname_Table.PC_Name=@HostName
    UPDATE Asset_Table  SET Serial_Number=@Serial_Number Asset_Barcode=@Asset_Barcode,
                             Asset_Table.Make=@Make, Asset_Table.Model=@Model WHERE ??
    UPDATE User_Table SET User_Table.User_FirstName=@FirstName, User_Table.User_LastName=@LastName WHERE ?? 

    Nothing is updated on Hostname_Table, so it simplifies to

    UPDATE Asset_Table  SET Serial_Number=@Serial_Number Asset_Barcode=@Asset_Barcode,
                             Asset_Table.Make=@Make, Asset_Table.Model=@Model WHERE ??
    UPDATE User_Table SET User_Table.User_FirstName=@FirstName, User_Table.User_LastName=@LastName WHERE ?? 

    Neither of these specify a where clause that references the Id value of the rows to be updated.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 7, 2009 12:26 PM
  • User-821857111 posted

     You should try running your SQL in query analyser (SQL2000) or SSMS (2005 and above)
     

    Ermm... He's using Access.

     

    Wednesday, January 7, 2009 2:54 PM
  • User-319574463 posted

    He's using Access.
     

    Good point Sir! I have corrected my response.

    Wednesday, January 7, 2009 3:08 PM