Results 1 to 16 of 16

Thread: [RESOLVED] Databae does not update

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Resolved [RESOLVED] Databae does not update

    I have no errors showing up when running this I get the Message that the record has been updated but when I look it is not Updated
    any help would be great I am pulling out Hair been looking at this for 3 hours I'm sure I'll be ashamed when I find the answer


    Thanks
    Code:
    Private Sub UpdateWO()
            SQL.AddParam("@WOID", WOID) 'Variable holding the chosen WorkOrderID  Auto-number
            SQL.AddParam("@Completed", txtCompleted.Text)
            SQL.AddParam("@Notes", txtNotes.Text)
            SQL.AddParam("@CompletedBy", cbxCompletedBy.Text)
            SQL.AddParam("@Sanitized", cbxSanitizedBy.Text)
            SQL.AddParam("@CompletedOn", dtpCompletedOn.Text)
            SQL.AddParam("@Mistake", ckbMistake.Checked)
            SQL.AddParam("@Parts", ckbNewParts.Checked)
            SQL.AddParam("@ClosedWO", True)         'Only seen when closing a Work Order
    
            SQL.ExecQuery("UPDATE WorkOrders " &
                          "SET  @completed=WorkPerformed,@notes= Notes,@CompletedBy=CompletedBy,@Sanitized=SanitizedBy," &
                         " @CompletedOn=WorkorderCompleteDate,@Mistake=DeletedWO,@parts=NewParts,@ClosedWO =WOClosed " &
                          "WHERE WorkOrderID =@WOID; ")
            'REPORT & ABORT ON ERRORS
            If SQL.hasexception(True) Then Exit Sub
            MsgBox(" Work Order CLOSED Successfully")
    
        End Sub

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Databae does not update

    Sorry this is an SQL 2017 express DB

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Databae does not update

    The syntax is:

    Set FIELDNAME=@Variable

    You have these reversed

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Databae does not update

    The syntax is:

    Set FIELDNAME=@Variable

    You have these reversed

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Databae does not update

    ?!?!??!
    Did you get your FieldNames and Parameters turned around in your SET-Clause?
    I would have expected ".... SET WorkPerformed=@completed...."
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Databae does not update

    szlamany,
    2 minds, 1 thought..... :-)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Databae does not update

    If this was your thought it didn't work either No errors Said it was completed
    Code:
     SQL.ExecQuery("UPDATE WorkOrders " &
                          "SET  WorkPerformed=@completed,Notes=@notes,CompletedBy=@CompletedBy,SanitizedBy=@Sanitized," &
                         " WorkorderCompleteDate=@CompletedOn,DeletedWO=@Mistake,NewParts=@parts,WOClosed=@ClosedWO " &
                          "WHERE WorkOrderID =@WOID; ")



    Did I read this wrong?
    Thanks

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Databae does not update

    What Library are you using? ADO?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Databae does not update

    Quote Originally Posted by Zvoni View Post
    What Library are you using? ADO?
    this is my SQLControl class I am using

    Imports System.Data.SqlClient


    Public Class SQLControl
    'INFORMATION FOR THE CONNECTION TO THE SQL DATABASE
    'FIRST IS THE SERVERS NAME; DATABASE NAME;USER; PASSWORD;
    'DO NOT FORGET THE SEMICOLINS AS SEPARTOR

    Private DBCon As New SqlConnection("Server=BILLS_LAPTOP\SQLEXPRESS01;Database=HiTech;User=Maint1;Pwd=Password1;")

    'THIS WILL BE RECREATED EVERY TIME WE RUN A QUERY

    Private DBcmd As SqlCommand

    'BILLS_LAPTOP\SQLEXPRESS01
    ' DB Data
    Public DBDA As SqlDataAdapter
    Public DBDT As DataTable ' THIS A STORAE CONTAINER FOR THE DATA


    ' Qury Parameters
    ' THESE WILL BE USED WHEN WE RUN QUERY'S
    Public Params As New List(Of SqlParameter)

    'Query Statistics
    Public RecordCount As Integer
    Public Exception As String 'USED TO STOR ANY ERROR'S ALONG THE WAY


    Public Sub New()

    End Sub
    'Allow Connection String Override
    Public Sub New(ConnectionString As String)
    DBCon = New SqlConnection(ConnectionString)
    End Sub
    'Execute Query Sub
    Public Sub ExecQuery(Query As String)
    'Reset Query Stats
    RecordCount = 0
    Exception = ""
    Try
    DBCon.Open()

    'Creat DB Command
    DBcmd = New SqlCommand(Query, DBCon)

    'Load Parans into DB Command
    Params.ForEach(Sub(p) DBcmd.Parameters.Add(p))

    'Clear Param List
    Params.Clear()

    'Execute Command & Fill Dataset
    DBDT = New DataTable
    DBDA = New SqlDataAdapter(DBcmd)
    RecordCount = DBDA.Fill(DBDT)

    Catch ex As Exception
    'Capture Error
    Exception = "ExceQuery Error: " & vbNewLine & ex.Message


    Finally
    'Close Connection
    If DBCon.State = ConnectionState.Open Then DBCon.Close()


    End Try
    End Sub
    'Add Params
    Public Sub AddParam(name As String, value As Object)
    Dim NewParam As New SqlParameter(name, value)
    Params.Add(NewParam)
    End Sub

    'Error Checking
    Public Function hasexception(Optional Report As Boolean = False) As Boolean
    If String.IsNullOrEmpty(Exception) Then Return False
    If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
    Return True

    End Function


    End Class

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Databae does not update

    hmmmm.... I'm not a .NET-Geek *g*, but one thing that looks fishy to me is your Sub AddParam, and there the value-argument.
    As Object?
    I would have expected "As Variant", but as i said: I'm no .NET-Geek
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Databae does not update

    Ugnh. Why are you using that class? It's not adding any value and probably causing the problems you're experiencing. The ExecQuery method there fills a datatable, what you should be using is an ExecuteNonQuery not a Fill operation.... this is why I don't like generic classes like this, they have the opportuinity to hide details that are important and run the risk of being incorrect on the inside, which is what I'd say is the case here. It also, in my opinion, makes tracking down errors harder.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Databae does not update

    This how I was self taught. I have not had problems before until today I just do not know where the breakdown is.
    I do not know how to do what you ask. How do I search for what your saying and how to learn it?

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Databae does not update

    SQL was not refreshing Reboot and all is well.
    Thanks for the help

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] Databae does not update

    Go through our own Database Tutorial and FAQ thread in the Database section... there's a ton of info in there written by various members. There's also https://homeandlearn.co.uk ... it's one of the more regularly recommended places for learning VB.Net
    Being self taught is fine (I'm largely self-taught myself) but at the same time recognize that there's a lot of bad carp out there too, so be careful of the quality. I saw this because virtually no one uses a database connection class like that any more. It's often much easier and faster to just write it directly against the db client class (SQLClient). Otherwise all you end up creating is a pass-through class, which isn't really necessary.


    Here's an example of how to setup and call an update query without using a wrapper class:
    Code:
            Using myDb As New SqlClient.SqlConnection("conectionstring here")
                Using myCmd As New SqlClient.SqlCommand()
                    myCmd.CommandText = "UPDATE myTable set Field1 = @Fld1, Field2 = @Fld2, Field3 = @Fld3 where ID = @ID"
                    myCmd.CommandType = CommandType.Text
                    myCmd.Connection = myDb
                    myCmd.Parameters.AddWithValue("Fld1", 123)
                    myCmd.Parameters.AddWithValue("Fld2", "test")
                    myCmd.Parameters.AddWithValue("Fl3", "Oooh la la")
                    myCmd.Parameters.AddWithValue("ID", 4452)
                    myCmd.ExecuteNonQuery()
    
                End Using
            End Using
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] Databae does not update

    @tg, just a small point for the record: a single Using statement can create multiple objects. That means that there's no need to nest multiple Using blocks unless you want to execute some additional code between them. As such, your code can be simplified slightly like so:
    vb.net Code:
    1. Using myDb As New SqlClient.SqlConnection("conectionstring here"),
    2.       myCmd As New SqlClient.SqlCommand()
    3.     myCmd.CommandText = "UPDATE myTable set Field1 = @Fld1, Field2 = @Fld2, Field3 = @Fld3 where ID = @ID"
    4.     myCmd.CommandType = CommandType.Text
    5.     myCmd.Connection = myDb
    6.     myCmd.Parameters.AddWithValue("Fld1", 123)
    7.     myCmd.Parameters.AddWithValue("Fld2", "test")
    8.     myCmd.Parameters.AddWithValue("Fl3", "Oooh la la")
    9.     myCmd.Parameters.AddWithValue("ID", 4452)
    10.     myCmd.ExecuteNonQuery()
    11. End Using

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] Databae does not update

    Nice... did not know that.

    +1

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width