Problem Editing Data
Problem Editing Data
Problem Editing Data
Problem Editing Data
Problem Editing Data
Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data
Problem Editing Data Problem Editing Data
Problem Editing Data
Go Back  Xtreme Visual Basic Talk > > > Problem Editing Data


Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2004, 07:46 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Question Problem Editing Data


I am having a problem editing data in side my dataset. I can "Add" New Records, "Delete" Records, But when I edit an existing record it does not save.

While my form is open and I navigate through the records the changes to the records are there, but if I close the form (Project) and reopen it; the records are back to their original state. I do not receive any error messages inside the Try / catch block. When running debug it, seems to go through the update procedure. Below is a copy of my try / catch code, it is very simple:

Private Sub VidexUpdate()
Try
dbScanners.Update(DsScanners1, "tblVidex")
Catch exc As Exception
MessageBox.Show("Error Saving File..." & _
ControlChars.NewLine & ControlChars.NewLine & exc.Message, "File", MessageBoxButtons.OK, MessageBoxIcon.Hand)
End Try
End Sub

The File I am using is an existing Access Table. It does have one Primary key field, which is an integer and is set on a unique value. Any suggestions are welcome, I have been search various sites and have three reference manuals (Microsofts Core Reference Manual, Language Manual, Programming V.net by Paul Vick) Plus a college text book on programming. I have followed examples in them but still can't seem to save or create an error.

Thanks in advance for any help.
Reply With Quote
  #2  
Old 06-16-2004, 08:22 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Are you using the oledbDataAdapter wizard to create your connections and commands. If you want to update the underlying database table, you need the appropriate update command. The dataAdapter will take delete, insert, update and select commands. These are needed if you want to update the datasource.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 06-21-2004, 06:07 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

Yes, I am using the oledbadapter wizard. I am not sure what you mean by the "Underlying Database table".

I am using the line as:

dbScanners.Update(DsScanners1, "tblVidex")

Is this not the Right Command to use. In Debug it hits this command and seems to work but if I close the project an reopen the updates are not there.

I am new to VB and would appreciate any help. Not looking for someone to write it for me but help. I have three reference manuals and my colloge VB book and though I was coding the proper commands.
Reply With Quote
  #4  
Old 06-21-2004, 06:55 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Because a DataAdapther has select, insert, update and delete commands. If you have not assigned an update command to the DataAdapter it will either not update your database (that is your underlying database table, its the source of your data) or it will throw an exception.

If you have created this DataApapter through the wizard, right click the control in your solution file. View its properties. Scroll through all of them. You will see the DataAdapter takes an active connection and a select command, an update command, insert command, and a delete command. If you have no update command selected, you can not update records . These commands can be sent via code, or hard-coded via the properties tab.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 06-24-2004, 09:00 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

MKoslof,

Thanks for your advice, I followed it and the update command is there and has an "SQL" Statement. So I am confused of why it is not updating the records. I have even tried to do the "Save" on the Record immediately after the update instead of during closing of the form. I have even been changing the name of the update command to see if that would work. I have read about SQL and send a new SQL Statement but am unsure of how to do that. Any guidance you can offer would be great.

Thanks,

Daflugster



Quote:
Originally Posted by MKoslof
Because a DataAdapther has select, insert, update and delete commands. If you have not assigned an update command to the DataAdapter it will either not update your database (that is your underlying database table, its the source of your data) or it will throw an exception.

If you have created this DataApapter through the wizard, right click the control in your solution file. View its properties. Scroll through all of them. You will see the DataAdapter takes an active connection and a select command, an update command, insert command, and a delete command. If you have no update command selected, you can not update records . These commands can be sent via code, or hard-coded via the properties tab.
Reply With Quote
  #6  
Old 06-24-2004, 12:21 PM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Please post your updated code, where you attempt to send the update command to the dataAdapter..I will be in and out of the forum today, but I will try to get back to you as soon as possible.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 06-25-2004 at 06:51 AM.
Reply With Quote
  #7  
Old 07-07-2004, 11:40 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

I am not sure if this is what you needed or asked to see.
This is the Code that I call to do the edit when clicking the save button:

Code:
Private Sub VidexUpdate()
        Try
            dbScanners.Update(DsScanners1, "tblVidex")
            'dbScanners.UpdateCommand()
        Catch exc As Exception
            MessageBox.Show("Error Saving File..." & _
                      ControlChars.NewLine & ControlChars.NewLine & exc.Message, "File", MessageBoxButtons.OK, MessageBoxIcon.Hand)
        End Try
    End Sub
The following is the SQL built from the Wizard:
Code:
UPDATE tblVidex
SET       PKSerialNumber = ?, FKBaseNumber = ?, PurchaseDate = ?, Repair = ?, ScannerID = ?, Supervisor = ?, Supplier = ?, Type = ?, UserName = ?
WHERE (PKSerialNumber = ?) AND (FKBaseNumber = ? OR
               ? IS NULL AND FKBaseNumber IS NULL) AND (PurchaseDate = ? OR
               ? IS NULL AND PurchaseDate IS NULL) AND (Repair = ?) AND (ScannerID = ? OR
               ? IS NULL AND ScannerID IS NULL) AND (Supervisor = ? OR
               ? IS NULL AND Supervisor IS NULL) AND (Supplier = ? OR
               ? IS NULL AND Supplier IS NULL) AND (Type = ? OR
               ? IS NULL AND Type IS NULL) AND (UserName = ? OR
               ? IS NULL AND UserName IS NULL)
Reply With Quote
  #8  
Old 07-07-2004, 12:48 PM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

So what is the error you receive? And, is this the update command you are actually trying to conduct? What changes are actually being made to the database..what is filling the parameters?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #9  
Old 07-08-2004, 05:24 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

While my form is open and I navigate through the records the changes to the records are there, but if I close the form (Project) and reopen it; the records are back to their original state. I do not receive any error messages. When I run it through debug it hits the update statement like it is a good update with know problem.

The fields that I am mostly Changing are:

FKBaseNumber
UserName
ScannerID
Supervisor
Reply With Quote
  #10  
Old 07-08-2004, 06:21 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Right. And your default update statement is attempting to update every record in the table. The ? represents parameters. These needs to be passed something. Whether it be values from the user forms, your own hard coded strings, values from a data table, whatever.

I highly suggest creating your own dynamic sql strings. You can create your own Update, delete, select and insert commands. Review this past thread where I show an example of creating your own query string with parameters at run-time..then you update the database....

Need help with inserting into a SQL db
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 07-09-2004, 04:40 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

Thanks for your, Patience, help, and advice. I reviewed the link that you suggested this seems to be a link to add new records... Correct? I can use this logic to edit or update existing records. I do have a question on this line of code:

PHP Code:
Dim myconnection As New OleDbConnnection(conn_String
What is conn_String? Where and how is it defined or instantiated. I think I understand the rest of the code and will definitely try and make it work. However, remember that I am new to VB, came from the world of Clarion, and this is my first project.

Can you tell me why the SQL Statement for the update that the wizard built does not work. I thought that was what it was supposed to do? I am confused on that issue, if it doesn't work on updates why is it generating an update command?
Reply With Quote
  #12  
Old 07-09-2004, 06:25 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Conn_String is a public const. It is just a string declaration. You could use a local string variable, it doesn't matter. This is just a string declaration represening your connection string.

SQL statements should be dynamic(as your application changes, you need to do different functions). The SQL statement produced by the wizard is fine..but it is generic. If the current updates you are sending to the DataAdapter aren't compliant, than this sql statement won't work. That's why it is much easier to just code these commands yourself.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #13  
Old 07-15-2004, 06:38 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

I have tried to get my SQL Statement to work and have made many changes to it and have researched to find answers before I post. I am new at working with SQL and am trying to simply update the records in the file.

I am getting this Error from the try/catch:

Quote:
No value given to one or more reqiuired Parameters
My Code looks Like:

PHP Code:
 strUpdate "UPDATE tblVidex SET PKSerialNumber = ?, FKBaseNumber = ?, PurchaseDate = ?, Repair = ?, ScannerID = ?, Supervisor = ?, Supplier = ?, Type = ?, UserName = ? WHERE (PKSerialNumber = ?)AND(FKBaseNumber = @txt1) AND (UserName = @txt2) AND (ScannerID = @txt3) AND (Supervisor = @txt4)"
sSelect "SELECT * FROM tblVidex"
        
Try
            
MyConnection.Open()
            
MyUpdate = New OleDb.OleDbCommand
            MyUpdate
.Parameters.Add(New OleDb.OleDbParameter("@txt1"OleDb.OleDbType.VarWChar100))
            
MyUpdate.Parameters("@txt1").Value txtBaseNumber.Text
            MyUpdate
.Parameters.Add(New OleDb.OleDbParameter("@txt2"OleDb.OleDbType.VarWChar100))
            
MyUpdate.Parameters("@txt2").Value txtEmployeeName.Text
            MyUpdate
.Parameters.Add(New OleDb.OleDbParameter("@txt3"OleDb.OleDbType.VarWChar100))
            
MyUpdate.Parameters("@txt3").Value txtEmployeeId.Text
            MyUpdate
.Parameters.Add(New OleDb.OleDbParameter("@txt4"OleDb.OleDbType.VarWChar100))
            
MyUpdate.Parameters("@txt4").Value txtSupervisor.Text
            MyUpdate
.Connection MyConnection
            MyUpdate
.CommandText strUpdate
            MyUpdate
.ExecuteNonQuery()

            
'dbScanners.Update(DsScanners1, "tblVidex")
            '
dbScanners.UpdateCommand()
        Catch 
exc As Exception
            MessageBox
.Show("Error Saving File..." _
                      ControlChars
.NewLine ControlChars.NewLine exc.Message"File"MessageBoxButtons.OKMessageBoxIcon.Hand)
        
End Try 
Reply With Quote
  #14  
Old 07-15-2004, 06:57 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

You have 14 parameters and you are only passing 4 of them. And be consistent, I would recommend always using the "@val" method of passing your parameters. Do not mix "?" and "@". And remember you are reassigning the updateCommand at run-time. So you need to make a consistent, proper update statement. Use the same parameter name type and pass all neccessary values.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #15  
Old 07-15-2004, 01:44 PM
Tractor Tractor is offline
Newcomer
 
Join Date: Jul 2004
Location: CT/NH
Posts: 23
Default

I had a similar problem to yours, where my records would insert and delete with the data adapter, but wouldn't update. The solution I found was to add this line of code before I declared my command:

Code:
Me.BindingContext(DataSet, TableName).EndCurrentEdit()
Hope it helps.

-Tractor
__________________
9 x 6 = 42
Reply With Quote
  #16  
Old 07-16-2004, 05:53 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

I have changed my SQL and it eliminated the error... However I am now receiving a SYNTAX Error on the Sql Statement. I searched all day and through the night try to find a solution. Here is the SQL Update:

HTML Code:
strUpdate = "UPDATE tblVidex SET (PKSErialNumber = [@txtSerial]) WHERE (FKBaseNumber = [@txt1]) AND (PurchaseDate = [@purchdte]) AND (Repair = [@txtRepair]) AND (ScannerID = [@txt3])" _
                    & "AND (Supervisor = [@txt4]) AND (Supplier = [@txtSuply]) AND (Type = [@txtType]) AND (UserName = [@txt2]) "
It's probably a simple error, but any suggestion would be helpfull.
Reply With Quote
  #17  
Old 07-16-2004, 08:46 AM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What is the exact error you receive? Make sure the parameter values are sending proper values back to the database. As in, it appears you may have some numerical or date data types. If so, are you passing valid date and numerical parameters to the fields? And are the values of these parameters in the correct format?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #18  
Old 07-16-2004, 01:52 PM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

I have Attached a screen shot of the actual Error I receive in Run time.

Below is the Code for the Update. Can't figure out were the syntax Error is inside the SQL.


HTML Code:
"UPDATE tblVidex SET (PKSErialNumber = [@txtSerial]) WHERE (FKBaseNumber = [@txt1]) AND (PurchaseDate = [@purchdte]) AND (Repair = [@txtRepair]) AND (ScannerID = [@txt3])" _
                    & "AND (Supervisor = [@txt4]) AND (Supplier = [@txtSuply]) AND (Type = [@txtType]) AND (UserName = [@txt2]) "
        sSelect = "SELECT * FROM tblVidex"
        Try
            MyConnection.Open()
            MyUpdate = New OleDb.OleDbCommand
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtSerial", OleDb.OleDbType.Numeric, 6))
            MyUpdate.Parameters("@txtSerial").Value = cboSerialNumber.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt1", OleDb.OleDbType.Numeric, 3))
            MyUpdate.Parameters("@txt1").Value = txtBaseNumber.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt2", OleDb.OleDbType.VarWChar, 30))
            MyUpdate.Parameters("@txt2").Value = txtEmployeeName.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt3", OleDb.OleDbType.Numeric, 4))
            MyUpdate.Parameters("@txt3").Value = txtEmployeeId.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt4", OleDb.OleDbType.VarWChar, 25))
            MyUpdate.Parameters("@txt4").Value = txtSupervisor.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@purchdte", OleDb.OleDbType.DBDate, 10))
            MyUpdate.Parameters("@purchdte").Value = txtPurchaseDate.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtRepair", OleDb.OleDbType.Boolean, 1))
            MyUpdate.Parameters("@txtRepair").Value = chkRepair.Checked
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtType", OleDb.OleDbType.VarWChar, 13))
            MyUpdate.Parameters("@txtType").Value = txtType.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtSuply", OleDb.OleDbType.VarWChar, 20))
            MyUpdate.Parameters("@txtSuply").Value = txtSupplier.Text
            MyUpdate.Connection = MyConnection
            MyUpdate.CommandText = strUpdate
            MyUpdate.ExecuteNonQuery()

            'dbScanners.Update(DsScanners1, "tblVidex")
            'dbScanners.UpdateCommand()
        Catch exc As Exception
            MessageBox.Show("Error Saving File..." & _
                      ControlChars.NewLine & ControlChars.NewLine & exc.Message, "File", MessageBoxButtons.OK, MessageBoxIcon.Hand)
        End Try
The file definition for the Access table is as follows:

PHP Code:
PKSerialNumber Number
Type 
Text
Supplier 
Text
PurchaseDate 
Date/Time
FKBaseNumber 
Number
UserName 
Text
ScannerID 
Number
Supervisor 
text
Repair 
Yes/No 
Reply With Quote
  #19  
Old 07-16-2004, 03:41 PM
MKoslof's Avatar
MKoslofProblem Editing Data MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

If you run your code in debug mode, what is the value being passed here:

MyUpdate.Parameters("@purchdte").Value = txtPurchaseDate.Text

and here:

MyUpdate.Parameters("@txt3").Value = txtEmployeeId.Text

These are defined as Date and numerical values in your table. So you can't send strings as parameters, if the values of the text boxes have not been casted into the proper data type, this update will fail.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #20  
Old 07-28-2004, 06:39 AM
DaFlugster DaFlugster is offline
Freshman
 
Join Date: Jun 2004
Location: Parkersburg, WV
Posts: 34
Default

I can't believe that I am having such trouble with a simple SQL Statement. I haven't posted lately because I have been trying to resolve this on my own, but I have zero luck.

I have changed all strings, Dates, numeric values to match, but I am still receiving the generic "Syntax error in UPDATE Command." error message from my try/catch block. Below is the lates code:

HTML Code:
strUpdate = "UPDATE tblVidex SET (PKSerialNumber = @SN) WHERE (Type = @txtType) AND (PurchaseDate = @purchdte)" _
             & "AND (Supplier = @txtSuply) And (Condition = @txtCondition) AND (Repair = @Repair)" _
             & "AND (FKBaseNumber = @BN) AND (UserName = @txtUserName) AND (ScannerID = @ID) AND (Supervisor = @txtSuper) AND (Category = @Cat)"

        sSelect = "SELECT * FROM tblVidex"
        Try
            MyConnection.Open()
            MyUpdate = New OleDb.OleDbCommand
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@SN", OleDb.OleDbType.Numeric, 6))
            MyUpdate.Parameters("@SN").Value = intSN
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@BN", OleDb.OleDbType.Numeric, 3))
            MyUpdate.Parameters("@BN").Value = intBN
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt2", OleDb.OleDbType.VarWChar, 30))
            MyUpdate.Parameters("@txt2").Value = txtEmp.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@ID", OleDb.OleDbType.Numeric, 4))
            MyUpdate.Parameters("@ID").Value = intID
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txt4", OleDb.OleDbType.VarWChar, 25))
            MyUpdate.Parameters("@txt4").Value = txtSupervisor.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@purchdte", OleDb.OleDbType.Date, 10))
            MyUpdate.Parameters("@purchdte").Value = mdatPurchdate
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@Repair", OleDb.OleDbType.Boolean, 1))
            MyUpdate.Parameters("@Repair").Value = intrepair
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtType", OleDb.OleDbType.VarWChar, 13))
            MyUpdate.Parameters("@txtType").Value = cboType.Text
            MyUpdate.Parameters.Add(New OleDb.OleDbParameter("@txtSuply", OleDb.OleDbType.VarWChar, 20))
            MyUpdate.Parameters("@txtSuply").Value = txtSupplier.Text
            MyUpdate.Connection = MyConnection
            MyUpdate.CommandText = strUpdate
            MyUpdate.ExecuteNonQuery()
        Catch exc As Exception
            MessageBox.Show("Error Saving File..." & _
                      ControlChars.NewLine & ControlChars.NewLine & exc.Message, "File", MessageBoxButtons.OK, MessageBoxIcon.Hand)
        End Try
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Problem Editing Data
Problem Editing Data
Problem Editing Data Problem Editing Data
Problem Editing Data
Problem Editing Data
Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data Problem Editing Data
Problem Editing Data
Problem Editing Data
 
Problem Editing Data
Problem Editing Data
 
-->