-
Aug 14th, 2018, 10:13 AM
#1
Thread Starter
Hyperactive Member
[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
-
Aug 14th, 2018, 10:15 AM
#2
Thread Starter
Hyperactive Member
Re: Databae does not update
Sorry this is an SQL 2017 express DB
-
Aug 14th, 2018, 10:28 AM
#3
Re: Databae does not update
The syntax is:
Set FIELDNAME=@Variable
You have these reversed
-
Aug 14th, 2018, 10:29 AM
#4
Re: Databae does not update
The syntax is:
Set FIELDNAME=@Variable
You have these reversed
-
Aug 14th, 2018, 10:29 AM
#5
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
-
Aug 14th, 2018, 10:30 AM
#6
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
-
Aug 14th, 2018, 11:46 AM
#7
Thread Starter
Hyperactive Member
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
-
Aug 14th, 2018, 11:52 AM
#8
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
-
Aug 14th, 2018, 12:03 PM
#9
Thread Starter
Hyperactive Member
Re: Databae does not update
Originally Posted by Zvoni
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
-
Aug 14th, 2018, 01:08 PM
#10
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
-
Aug 14th, 2018, 01:34 PM
#11
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
-
Aug 14th, 2018, 05:05 PM
#12
Thread Starter
Hyperactive Member
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?
-
Aug 14th, 2018, 06:52 PM
#13
Thread Starter
Hyperactive Member
Re: Databae does not update
SQL was not refreshing Reboot and all is well.
Thanks for the help
-
Aug 14th, 2018, 09:23 PM
#14
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
-
Aug 14th, 2018, 09:44 PM
#15
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:
Using myDb As New SqlClient.SqlConnection("conectionstring here"), 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
Last edited by jmcilhinney; Aug 14th, 2018 at 10:11 PM.
-
Aug 14th, 2018, 10:07 PM
#16
Re: [RESOLVED] Databae does not update
Nice... did not know that.
+1
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|