Visual Basic :: Updating Database Value

Updating Database
I just started with programming a month or so ago, so I'm still very new to all of this. Here is my problem.

I have problems linking my database to my program. The database was created using Acess. I have 4 Ado controls on my form, one for the client table, one for the supplier table, one for the product table and one for the transaction table in my database. I can allready add or edit things in the client, supplier and product table, but I'm having problems with the transaction one. On the form I have a textbox (txtAmount) where I enter the amount of the product I'm buying or selling. Then there's two command buttons (cmdBuy and cmdSell) I can choose from depending if I'm buying or selling something. Just below that I've added another textbox (txtBalance) where it shows my current balance. Then in the click event of the two command buttons I've added something like this:

(I dimmed intBalance as an integer)

intBalance = intBalance + txtAmount.Text
txtBalance = intBalance

Up till here everything works fine, the balance gets updated on my form whenever I buy or sell something, but I want to save the balance that gets displayed in txtBalance to my database. I have a adodc control on my form called dcTransactions which is linked to my Transaction table in my database. One of the fields in the transaction table is balance and that's where I want to save the balance that gets displayed in txtBalance field. I tried using dcTransactions.recordset.update, but it doesn't work.

I want to save other things to my database as well, like the history of the clients and suppliers (what they bought or sold to me and for how much), but I'll probably be able to figure that out when I get the Balance working.

This is just for a project for college so please keep it very simple. Any kind of help will be much appreciated.

Updating Database
Anyone know of any utilities that can insert new data into access fields automatically??

No new tables, just additional entries into the fields

Updating Database
Hello World

Hi, Its me again. I have a local database which serves 4-6 computers. Every time there are changes with the table (e.g. modified by computer 1). How can I update the database automatically without clicking the Refresh button? All we know that every time we view all the list we need to open the connection and then close it again, right? So, how can I handle this problem? Please help. Urgent. Thanks

Updating To A Database.
What are the ado commands to update to a database, rather than adding or deleting a record? For instance, in a program that I'm writing, there is occasionally an error when not all the values have been written to the database, or they have not been written correctly and i'd just like to update the record.

The book that I have says that you can use adodc1.recordset.edit , but this causes an error saying that it's an invalid command. Is there another command that I need to use?

Thanks,
Ryan

Updating My Database
I'm using VB.net to update and pull from an access database. The UI has a combobox whose datasource is bound to the key of the database. I also have textboxes, datetimepickers, and a checkbox that are databound to the database, causing the data inside them to change when you pick a differnt item in the combobox. My problem that I am having is that I cant seem to pull information out of the display objects, and update the database.

here is the code that I am using currently I apologize for the cruddy looking code.

Dim clientrow As DataRow = ClientsDS1.Clients.NewClientsRow()
Dim clientrow3 As DataRow = ClientsDS1.Clients.NewClientsRow()
Dim clientrow2 As DataRow
clientrow3 = ClientsDS1.Clients.FindByName(ComboBox1.GetItemText(ComboBox1.Items.It em(ComboBox1.SelectedIndex)))
clientrow.Item("Name") = ComboBox1.GetItemText(ComboBox1.Items.Item(ComboBox1.SelectedIndex))
Dim some = ComboBox1.GetItemText(ComboBox1.Items.Item(ComboBox1.SelectedIndex))
If TextBox13.Text <> "" Then
clientrow.Item("BuisCom") = TextBox13.Text
End If
If TextBox5.Text <> "" Then
clientrow.Item("City") = TextBox5.Text
End If
If TextBox17.Text <> "" Then
clientrow.Item("Cphone") = TextBox17.Text
End If
If DateTimePicker1.Value.ToShortDateString <> "" Then
clientrow.Item("DOB") = DateTimePicker1.Value.ToShortDateString
End If
If DateTimePicker3.Value.ToShortDateString <> "" Then
clientrow.Item("DoBpm") = DateTimePicker3.Value.ToShortDateString
End If
If DateTimePicker4.Value.ToShortDateString <> "" Then
clientrow.Item("DoFs") = DateTimePicker4.Value.ToShortDateString
End If
If DateTimePicker5.Value.ToShortDateString <> "" Then
clientrow.Item("DoRec") = DateTimePicker5.Value.ToShortDateString
End If
If DateTimePicker6.Value.ToShortDateString <> "" Then
clientrow.Item("DoSale") = DateTimePicker6.Value.ToShortDateString
End If
clientrow.Item("Flowers") = CheckBox1.Checked
If TextBox16.Text <> "" Then
clientrow.Item("Hphone") = TextBox16.Text
End If
If TextBox12.Text <> "" Then
clientrow.Item("Recom") = TextBox12.Text
End If
If DateTimePicker2.Value.ToShortDateString <> "" Then
clientrow.Item("SDob") = DateTimePicker2.Value.ToShortDateString
End If
If TextBox6.Text <> "" Then
clientrow.Item("State") = TextBox6.Text
End If
If TextBox4.Text <> "" Then
clientrow.Item("Street") = TextBox4.Text
End If
If TextBox7.Text <> "" Then
clientrow.Item("Zip") = TextBox7.Text
End If
If TextBox7.Text <> "" Then
clientrow.Item("SName") = TextBox2.Text
End If
Dim nu
ClientsDS1.Clients.BeginLoadData()
ClientsDS1.Clients.RemoveClientsRow(clientrow3)
ClientsDS1.Clients.EndLoadData()
nu = OleDbDataAdapter1.Update(ClientsDS1)
ClientsDS1.Clients.BeginLoadData()
ClientsDS1.Clients.AddClientsRow(clientrow)
ClientsDS1.Clients.EndLoadData()
nu = OleDbDataAdapter1.Update(ClientsDS1)
ClientsDS1.Clear()
OleDbDataAdapter1.Fill(ClientsDS1, "clients")
OleDbDataAdapter2.Fill(ClientsDS1, "Agents")

Updating A Database
Hi all,

I am trying to update a database.
Basically I want to remove old entries and replace them.
How do I read entries from the database but also delete the unwanted as I go?
To delete the superseded entries I’m guessing I open two files at once, one for input and one for output, reading from one to the other and only writing if the details aren’t old.
And then copy the new file over the old and delete the temporary one.
And then I need to open the file for append with new entries. Am I right in saying this or is there an easier/better way??

Updating Database
Im using array code to insert 'RESIGN CODE' data from a 'tblempl' into a 'tblexit'

tblempl contains 1 ResignField. Elrsnc(X) -- can be Elrsnc1, Elrsnc2 etc

tblexit contains RSNCODE 1-11, a total of 11 fields

when tblempl Elrsnc field is 'Elrsnc1', tblExit will add '1' to the fields of 'RSNCODE1' and '0' to the rest of the fields RSNCODE(2-11)

Using the above logic, I have no problem inserting my data into database but when I try to do update. I met with several problems with the Update SQL statement. I attach part of my work below can someone enlighten me as to whats is wrong with my Update SQl? Thanks a lot

Updating The Database
Hellos!!

i was just wondering is there a way lets say i have about max 10 pc running in a network enviroment, how do i send data across in a connection string to update the database and at the same time all the 10 pc's get updated too with the same info...should i just have one pc that each client have access to....

Any Idea...
Any suggestions,
Thankx

Updating A Database
I am using a data control to manipulate an Access database. I only want the database to update when the user requests it to.

The problem is that if the user types anything when a record is displayed, the database updates automatically. Do I have to create two sets of display objects to stop this from happening or is there a better way?

Database Updating
ok i am working on a database program i was wondering can you add a new record to a database using textboxes if so how?

Updating One Database From Another
I have two databases, I need to copy from one DB 4 tables to another Db using ADO, but the records could exists already on the DB where I'm copying to.
Thanx for any help

Updating A Database
I have this DBgrid that retrieves records from the database based on a certain criteria.The question is, when a retrieved record is selected either by double clicking on it or clicking a command button(Select), a form that was used to save changes must appear for a user to update.

Can someone please tell me the code that I can use to achieve this.

Thanks.

Updating Database
I have a form that has a datacombo box on it and I have it setup with a list from a different table then the rest of the form I have it working and populating. What I need is when I make my selection I need the column in the main table to be updated with this new selection. Also when I go to the next record I need that field to be blank or contain the selection that has already been made. So what I need is the selection to be put in the correct column and stay there unless it is changed. below is the code used to populate the datacombobox.

Private Sub Populatecombobox1()

'Dim rscb As New Recordset
'On Error GoTo errorHandler
'Set rscb = New Recordset
'rscb.Open "Select TeamMember_ID,FName&' '&LName as UName from Team order by FName", conn, adOpenDynamic, adLockReadOnly

If rscb.RecordCount > 0 Then

With DataCombo1(0)
.Text = rscb!FName
Set .RowSource = rscb
.DataMember = "Test"
.DataField = "Analysis_Owner"
.ListField = "FName"

End With
rscb.MoveFirst
End If
Set rscb = Nothing

End Sub

Thanks in advance for any help.

Updating Database
hello...im writing a program in VB.. im trying to update a record in one of the tables in my database but instead of updating it is inserting....what can the problem be?? thanks

Updating Database
Hello,
I am doing a project with VB 6.0 as the front end and Microsoft Access as the backend
I have a database table named Table_Of_Weightage which has two fields

Rem_Abbr Rem_Weightage
act-r 0
bor 0
Acal 0

here as you can see the values in the second field are set to 0.
This is at the start of the project.
What i am supposed to do is search for a particular med in this table then set the value for it's Rem_Weightage.
First i am facing a problem in finding the medicine in the database using ADODB
i have used a find method of the recordset.
this is the code:
For counter1 = 0 To totmed Step 1
adoRecWeight.MoveFirst
adoRecWeight.Find "Rem_Abbr='" & Trim(charMed(counter1)) & "'"
adoRecWeight!Rem_Weight = adoRecWeight!Rem_Weight + GradeValue

Call adoRecWeight.Save
Next counter1
Here i am not able to update the Table_Of_Weighatage after finding a medicine.

I tried another code:
For counter1 = 0 To totmed Step 1
adoRecWeight.MoveFirst
adoConnHomeo.Execute "UPDATE Table_Of_Weightage SET Rem_Weight=Rem_Weight+ 'GradeValue' WHERE (Rem_Abbr='charMed(counter1)')"
Call adoRecWeight.Save
Next counter1

Third and last

For counter1 = 0 To totmed Step 1
adoRecWeight.MoveFirst
adoConnHomeo.Execute "UPDATE Table_Of_Weightage SET Rem_Weight=Rem_Weight+ GradeValue WHERE Rem_Abbr='" & "charMed(counter1)" & "'",ad0RecWeight
adoRecWeight.save
next counter1

still my database is not getting updated.
Note GradeValue=1 or 2 or 3 or 4 based on the select case condition

Am i going wrong in the syntax of update statement?
of i am not putting the statements in an appropriate order?
How do i solve this error?
PLease get back as soon as possible.
Niyati

Updating An SQL Database With Vb6
Hi,
Somebody please help. I want to update a table in sql database using vb6. The field is money data type, but its giving me an error "Disallowed implicit conversion from datatype varchar to money". How do I code.

Thanks
Jeff

Updating A Database
i have a recordset which is populated by an sql query similar "select
customer.custnum,customer lname+customer.fname as
name,loans.loannum,loans.writeoffdate from customer,loans where
customer.custnum=loans.custnum". notice the lname+fname field. when i
update writeoffdate with a value, the new value never gets written back to
the database. if i just query "select * from loans", it does. is a
recordset populated by a query from multiple tables, or virtual fields (
lname+fname) not updatable? i have the cursor type set to adopenkeyset, so
that should be fine.

Updating Database
I'm having problems updating my database from information that is changed in the program. I pass aProduct as Product to an update method in my ProductDA class. I Then use this code in the update method:

Public Shared Sub Update(ByVal aProduct As Product)
        strPartNumber = aProduct.GetPartNumber
        strPartType = aProduct.GetPartType
        strProductName = aProduct.GetProductName
        strSupplier = aProduct.GetSupplier
        strDescription = aProduct.GetDescription
        QtyOnHand = aProduct.GetQtyOnHand

        Dim sqlUpdate As String = "UPDATE Products SET PartNumber = '" & strPartNumber & "', " & _
          "PartType = '" & strPartType & "', ProductName = '" & strProductName & "', ProductDescription = '" & strDescription & "', QtyOnHand = '" & QtyOnHand & "', Supplier = '" & strSupplier & "' " & _
            "WHERE ProductName = '" & strProductName & "'"
        Dim adpProduct As New OleDbDataAdapter
        Try
            'aConnection = ProductConnect.Initialize()
            'Product.Initialize(aConnection)
            Dim p As Product = Product.Find(strProductName)
            adpProduct.UpdateCommand = New OleDbCommand(sqlUpdate)
            adpProduct.UpdateCommand.Connection = aConnection
            adpProduct.UpdateCommand.ExecuteNonQuery()
        Catch e As Exception
            Console.WriteLine(e.ToString)
        End Try
    End Sub

No update takes place after the update method runs. I think my sql statement is fine because i can do a query in Access with the same SQL and it works fine. I just dont know where the problem might be coming from. All suggestions are welcome

Updating The Database
Hey I am using Jet4.0 and odbc to access a database. I have many of the fields liked to text boxes, check boxes and other related controls. I navigate through the database through a list box control. I need to update the record that is currently shown. But I am not sure how to do that with the new access method. I have a odbcConnection, odbcAdapter, and odbcDataset control in my form.

Updating Old Database (*.mdb) To New One
Hello fellow coders!

I'm having this kind of issue. User has old database at his computer. When he uses live update, he retrieves a new database from my server.

The problem here is that how i can copy old recordsets to new database? New database could contain new fields that old database doesn't have. And some fields could be different format in new one, like name could be memo not text as in old one.

I tryed to use search but didn't find any answers.

If anyone has example or some useful hints, it would be great!

Best regards,
Ilkka T.

Updating Database
On my form I am connected to my database via Adodc. When I edit the data via this form, the change doesnt show on the form till I exit and reopen the form. Please how can I resolve this issue?

Updating A Database Via The Web
Hi, I have an application that uses an access2000 db. I was wondering if it is possible to set up a check for update function in my application that will go to my companys website and check for an updated version of the database (we update it about once a quarter)... if so, how do I go about doing this?

I'm all dressed up with nowhere to go, walking with a deadman over my shoudler

Updating Database
Please help! I am a beginner with VB and need to update a DAO/Access 97 table with the old table data. The problem is that the new table has extra fields that's not in the old table.

How can i check each field from the new table to see if the same field exist in the old table so that i can import the data if it exist or add a default value if the field does not exist.

Any help please, i'm desperate!!!

Wynand

VB - Database Updating
When I set a record in my database equal to some value why does it reflect that value whether or not I "update" the recordset. Can someone please shed some light as to what an update actually does???

Thanks in advance.

Simple Help Updating A Database
Ive got a program dealing with sales. I have a database with quantities in it. A user enters a SKU and Quantity into a textbox, which goes into a listview. I want to update the quantity for that item in my database. In other words, I want to subtract what is bought from the Quantity in the database. Below is what I have, but it doesnt seem to work. Any ideas?

Private Sub Read_Database()
Dim strSKU As String
Dim strDescription As String
Dim strUnitPrice As String
Dim strQtyNStock As String
Dim Qtyrequested As String
Dim updatedQty As String

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM Inventory WHERE SKU ='" & txtSKU.Text & "'"
With rst
.Source = strSQL
.ActiveConnection = conn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
If .EOF = False Then
strSKU = !SKU
strDescription = !Description
strUnitPrice = !UnitPrice
strQtyNStock = !Qty
txtDescription.Text = strDescription
txtUnitPrice.Text = strUnitPrice
Qtyrequested = txtQty.Text
If txtQty.Text > strQtyNStock Then
MsgBox "Qty sold can only equal Qty in stock", vbCritical
Else
updatedQty = strQtyNStock - Qtyrequested
End If
!Qty = updatedQty
.Update
.Close
Else
MsgBox "SKU not found", vbInformation
txtSKU.Text = " "
txtSKU.SetFocus
validSKU = False
End If
End With

End Sub

Updating Online Database
I have a database locally, that is, in my hard disk. A "clone" of that database is uploaded in a web host since one of the requirements is to display the information it contained in the web. I uploaded it there since we dont have a web server here. My problem now is the updating of the online database. We are planning to update that once a week and the updates come from the local database. Is it possible for vb/asp to fetch records from our local database and upload/append it to the online database? Please help...

Updating Database On WebServer
I have database on the webserver (hosted with an ISP) in MS Access. Updation of the master database on the webserver will be done from multiple locations which are all offline. Each connects using dial-up connection and updates information on the webserver. The information that is updated from each location will be unique. To some extent there will be some common entries which need to be simply changed rather than added as new records. Do mail me if u can help.

Updating Database From MSFlexGrid
Hi all,

I want to update a database table with the contents of a MSFlexGrid using an ADO connection.

Can anybody help me please?

Does anybody have a sample code

Updating Sql Database Outside Building
I have written a program in Visual Basic which is an application form.

Once the user has filled out the form I would like the information, instead of it being written to a text file as I now have it doing. But to
update our company database over the internet.

Does anyone know how I would go about doing this.

Thank-you in advance, any tips would be much appreciated.

Jason.

MSHFlexGrid && Updating Database
Hi ALL

I wonder if someone can help me?

I have created a MSHflexgrid which I feed a database into which is bound by OLEDB.

I was wanting to have checkboxes in col (0) and I used the sample code from the code section. The problem is that the checkboxes appear to be constantly refreshing so the cross never appears in the box, does anyone know how I get arround this?

Thanks for any help

Scouseman

Updating Treeview/database
Hi,

I am using a Treeview to view categories and items within that category.
Also it's possible to add items to the categories by using a standard command object.

After inserting a new category to the corresponding database table, the treeview has to be reloaded. This is done by using an API because it's not neccesairy to paint the treeview on the form again. After inserting the category to the database table I call the public sub fillTreeview BUT it gives an error on the .Key of the category node....which seems an unsolvable error to me at this point.

The error message is "Key is not unique in collection" but it is (in my opinion) because when I stop the programm and reload it, it shows the added category also in the treeview.....

Anybody has any ideas what I am doing wrong?

HERE IT GIVES THE ERROR:
mNode.Key = objRsParent!woordCategoryID & " ID" 'unieke ID voor de ouder

Updating ADO Database Without Duplicates?
OK.

Here is the deal. I have a program that views two list from a database. We'll call them list1 and list2. Ok..lets say that my database already has items in it. Ok...I launch my program...it pulls the data into my list fine. Ok so now I want to add more items to my lists. Ok...now that I have added items to my lists I then update. Everything seems to be fine until I start my program the next time. I now have duplicate items. Pretty much everytime I hit update it saves the whole list back to the database again. And this is a big problem....because I don't want or need duplicate items.

Here is my update code:

Dim strConnect As String
Dim cmd As Command

Set Cnn = New ADODB.Connection

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:datList.mdb;"

With Cnn
.CursorLocation = adUseClient
.Open strConnect
End With

Set RST = New ADODB.Recordset
RST.ActiveConnection = Cnn
RST.Open "Select * from tommy where cprep", Cnn, adOpenDynamic, adLockOptimistic

For p = 0 To lstTprep.ListCount - 1
lstTprep.ListIndex = p
Cnn.Execute "INSERT INTO tommy(cprep) VALUES ('" & lstTprep.Text & "')"
Next

For t = 0 To lstTtaught.ListCount - 1
lstTtaught.ListIndex = t
Cnn.Execute "INSERT INTO tommy(ctaught) VALUES ('" & lstTtaught.Text & "')"
Next

RST.Close
Set RST = Nothing

So I guess what I'm asking is, is there a way to only update new items?
If you need more info just send me a shout!
????????

Updating Entry In A Database...
VB6, Acess, ADO

i have a form that would let the user browse the entries of a database of students' records. i displayed them through textboxes... i have already perfected the add and save buttons... but, i haven't yet made the edit button.. for example, an entry is being misspelled, and i want to change it... so what's the code for this?...

below is my code for the add and save buttons...

visual basic code:--------------------------------------------------------------------------------

Private Sub cmdAddRecord_Click()

enable

txtId.Text = ""
txtFname.Text = ""
txtLname.Text = ""
txtMI.Text = ""

End Sub

Private Sub cmdSaveRecord_Click()

sexYearVerification

With rsRecord
.AddNew
!IDNo = txtId.Text
!Firstname = txtFname.Text
!Lastname = txtLname.Text
!MI = txtMI.Text
!Sex = optSex
!Year = optYear
.Update
End With

End Sub

Private Sub Form_Load()

disable
Set rsRecord = New ADODB.Recordset
rsRecord.Open "select * from Stude_Rec", dbConnect, adOpenKeyset, adLockOptimistic
fillFields

End Sub

--------------------------------------------------------------------------------

Updating Database From Datagrid Help
Hi All,
Im currently learning vb.net but am having trouble updating records from a datagrid into a dataset and then into the database. I've studdied many books with this problem and i think im getting into a muddle between all the possible solutions. when daStockControl.Update(dsStockControl, "Stock") is called the following error appears "the UpdateCommand affected 0 records."

It seems that cmdUpdate.CommandText = "UPDATE Optim SET Serial_No = @Serial_No WHERE Eq_Number = @Eq_Number" is the problem line as the problem as @Serial_No doesnt map to the datagrid and no data is collected.

Code:
Imports System.Data.OleDb

Module modConnection

Public dtStockControl As New DataTable()
Public daStockControl As New OleDb.OleDbDataAdapter()
Public dsStockControl As New DataSet()
Public connStockControl As New OleDb.OleDbConnection()

Public Sub DatabaseConnection()

Dim strConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Windows.Forms.Application.StartupPath & "stock control.mdb;Persist Security Info=False"

Dim strStockControl As String = "SELECT * FROM Optim"

connStockControl.ConnectionString = strConnectString

daStockControl.SelectCommand = New OleDb.OleDbCommand(strStockControl, connStockControl)

daStockControl.Fill(dsStockControl, "Stock")

dtStockControl = dsStockControl.Tables("Stock")

End Sub

End Module

Code:
Private Sub frmStockControl_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Load datbase connection details.
Call DatabaseConnection()

grdStockControl.DataSource = dsStockControl

'Bind data to data grid
grdStockControl.SetDataBinding(dsStockControl, "Stock")

'dsStockControl.Relations.Add("Stock", dsStockControl.Tables("Optim"))

Dim i As Object

End Sub

Private Sub ErrorHandler() 'Displays catched error within program

MsgBox("ERROR DETECTED WITHIN PROGRAM. PLEASE NOTE DOWN DETAILS." & vbCrLf & vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description & vbCrLf & "Source: " & Err.Source, MsgBoxStyle.Critical)

End Sub

Private Sub grdStockControl_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles grdStockControl.CurrentCellChanged
'If the datagrid has been updated then update the recordset
If dsStockControl.HasChanges = True Then

MsgBox("Database Updated = " & dsStockControl.HasChanges, MsgBoxStyle.Information, "Done")

Try

daStockControl.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim cmdUpdate As New OleDb.OleDbCommand()
Dim prm As OleDb.OleDbParameter

cmdUpdate.Connection = connStockControl

cmdUpdate = connStockControl.CreateCommand
cmdUpdate.CommandText = "UPDATE Optim SET Serial_No = @Serial_No WHERE Eq_Number = @Eq_Number"

'MsgBox(dsStockControl.GetUpdateCommand.CommandText.ToString)

prm = cmdUpdate.Parameters.Add("@Eq_Number", OleDb.OleDbType.Integer, 255, "Eq_Number")
prm = cmdUpdate.Parameters.Add("@Serial_No", OleDb.OleDbType.VarChar, 255, "Serial_No")

daStockControl.UpdateCommand = cmdUpdate

daStockControl.Update(dsStockControl, "Stock")

Catch ex As Exception 'Catch and display any errors
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Database Error")
Console.Write(ex.Message)
End Try

End If
End Sub
End Class

Thanks for any help in advance.

Database Updating Problem?!!
The way this program works is you type info into text boxes and when you press the button "picSearch" an sql statment is generated and the dsCustomers data set is filled with the search criteria and 4 list boxes are updated with the customers that was just searched for. When you click on any of the 4 list boxes it takes you to that specific customers records where you should be able to change any of the info (ie change the last name spelling) and have it update to the database. Whats happening is that its updating to the data set but not keeping the changes made to the actual database. I think the problem lies in the fact that i used the wizard to create the connection, data adapter and the dataset but in the SQL() subroutine i again create a new data adapter. The wizard created connCustomers (connection), daCustomers (Data Adapter), dsCustomers (Data Set). Im new at database programming and not even sure if this is the best way to go about it if you have any other sugestions please feel free to let me know.
Note: Connecting to an Access database with a primary key "ID". For testing purposes i was only trying to update the first row of the database.

'***** THIS FILLS THE LIST BOXES *****'
Private Sub picSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox3.Click
dsCustomers.Clear()

Call SQL(txtLast.Text)

'link the list box to the table in the data set
lstSDisplay1.DataSource = dsCustomers.Tables("Customers")
lstSDisplay2.DataSource = dsCustomers.Tables("Customers")
lstSDisplay3.DataSource = dsCustomers.Tables("Customers")
lstSDisplay4.DataSource = dsCustomers.Tables("Customers")

'state the name of the field to be displayed
lstSDisplay1.DisplayMember = "FirstName"
lstSDisplay2.DisplayMember = "LastName"
lstSDisplay3.DisplayMember = "HomePhone"
lstSDisplay4.DisplayMember = "OtherPhone"

'state the name of the field that contains the unique identifier
lstSDisplay1.ValueMember = "ID"
lstSDisplay2.ValueMember = "ID"
lstSDisplay3.ValueMember = "ID"
lstSDisplay4.ValueMember = "ID"
end sub

'***** THIS CREATES A DATA ADAPTER AND FILLS THE DATASET *****
Private Sub SQL(ByVal query As String)
Dim SQL As String = "SQL Statment"
Dim objAdapter As New OleDb.OleDbDataAdapter(SQL, conCustomers)

'fill the dataset using the sql in the data adapter
objAdapter.Fill(dsCustomers, "Customers")

objAdapter.Dispose()
End Sub

'***** THIS IS TO UPDATE THE DATABASE *****'
Private Sub picUpdateDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles picUCSave.Click
Try
iCustomers.Tables(0).Rows(0).Item("FirstName") = txtUFirst.Text
iCustomers.Tables(0).AcceptChanges()
globalVar.frmCustomers.daCustomers.AcceptChangesDuringFill = True
daCustomers.Update(iCustomers)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Updating Database Fields
Hi

I have a program that plays music files. However, i use it on a different computer where the paths to the music files are different. On my computer the paths are C:Songs

On the other computer the paths are C:WorkSongs

I want it so that I can click a button on the form and it will update the database fields by replacing C:Songs with C:WorkSongs

Before you say just change the directory of the files i cannot as i dont have the access writes

THanks

Updating SQL Server Database
I have a problem on updating my SQL server database from what user input on texBox and maskBox at the GUI. Below is my source code regarding this matter and it produce an error. Can anyone help me...

statement = "INSERT INTO tblMEmployee " & _
"(EmpNo, FullName, Designation, Dept, ConM, ConH) " & _
" VALUES (" & _
"'" & txbEmployeeNo.Text & "', " & _
"'" & txbFuName.Text & "', " & _
"'" & txbDesign.Text & "', " & _
"'" & txbDept.Text & "', " & _
"'" & txbContactM.Text & "'" & _
"'" & txbContactH.Text & "'" & _
")"

statement = "INSERT INTO tblLeaveApp " & _
"(EmpNo, Lcode, Date_app, Fdate, Ldate, Lday, ReaApp) " & _
" VALUES (" & _
"'" & txbEmployeeNo.Text & "', " & _
"'" & cmbReaApp.Text & "', " & _
"'" & txbPer.Text & "', " & _
"'" & txbFdate.Text & "', " & _
"'" & txbLdate.Text & "'" & _
"'" & txbReaApp.Text & "'" & _
")"

' Execute the statement.
MyConnObj.Execute statement, , adCmdText

Updating An Access Database In Vb6
I have a database named acesar.mdb with many tables one of which is called "userfile". Within that table is a field called "expDate" which is a date field in date/time format. I want to have a routine which adds one year to each record (member) of that table for that field. Can someone help me with this? The event will be triggered by a "special" password which I have already done. Thanks.

Updating Database And Saving
Hi guys,

at the moment my code allows me to view specific orders from my 'order' table and lets me update to say an order has been completed when i tick the 'completed' checkbox. I also have code to subtract the 'received' number from the 'quantity' number and show result in outstanding, but cant get this to save in the selected record and update the 'quantity' in my 'currentstock' table. Example: if there are 2 laptops ordered and only 1 comes in, when updated it needs to add 1 to 'quantity' in 'currentstock' table in the database. Here is my code at the moment:

Code:
Option Explicit
'declaring the number of records for the reposition'
Dim IngNosRecords As Long
Dim OrderMsgBox As Integer
Dim Sum As Long

Private Sub cmdBack_Click()
Unload Me
selection.Show
End Sub

Private Sub Form_Activate()
'record view'
With datOrder.Recordset
.MoveLast
IngNosRecords = .RecordCount
.MoveFirst
End With

End Sub

Private Sub datOrder_Reposition()
'Shows in the lable what record is being viewed out of how many records are there'
With datOrder.Recordset
lblCurrentRecord.Caption = "Order: " & ((.AbsolutePosition) + 1) & "of" & IngNosRecords
End With

End Sub

Private Sub Form_Load()
'Opens the DB'
datOrder.DatabaseName = App.Path & IIf(Right$(App.Path, 1) <> "", "", "") & "Audit1.mdb"

'SQL command to view only orders that are not completed'
datOrder.RecordSource = "SELECT * FROM [Order] WHERE Order.Completed = 0 ORDER BY Order.ReqNo"

End Sub
'Buttons to move through the records'
Private Sub cmdNext_Click()

datOrder.Recordset.MoveNext
If datOrder.Recordset.EOF Then
datOrder.Recordset.MoveLast
End If

End Sub
'Buttons to move through the records'
Private Sub cmdPrevious_Click()

datOrder.Recordset.MovePrevious
If datOrder.Recordset.BOF Then
datOrder.Recordset.MoveFirst
End If

End Sub
'Buttons to move through the records'
Private Sub cmdFirst_Click()

datOrder.Recordset.MoveFirst
If datOrder.Recordset.EOF Then
datOrder.Recordset.MoveFirst
End If

End Sub
'Buttons to move through the records'
Private Sub cmdLast_Click()

datOrder.Recordset.MoveLast
If datOrder.Recordset.BOF Then
datOrder.Recordset.MoveLast
End If

End Sub

Private Sub cmdUpdate_Click()
'When button is clicked, it will check input from check box and msgbox then update'
OrderMsgBox = MsgBox("Please confirm that this order has been completed", vbExclamation + vbYesNo)
If Check1.Value = 1 And OrderMsgBox = 6 Then
UpdateCompleted
Unload Me
UpdateStock1.Show
Else
Sum = CLng(txtQuantity.Text) - CLng(txtReceived.Text)
txtOutstanding.Text = CStr(Sum)
SumSave
End If
End Sub

Private Function UpdateCompleted()
'With condition to edit and update the completed field in the order table'
With datOrder.Recordset
.Edit
.Fields("Completed") = Check1.Value
.Update
End With
End Function

Private Function SumSave()
With datSum.Recordset
.Edit
.Fields("Outstanding") = txtOutstanding
.Update
End With
End Function

Database Linking And Updating
Hallo,

I'm new to VB6 and please forgive me for asking this question. I have this data entry process with the following info:

1. I have 2 tables GroupDetail and GroupMaster with field GrpNo as the link to GroupMaster;

2. During a customize data entry screen, i have to update GroupDetail field "GrpName" from GroupMaster table field "grp_name", which I have no idea yet on how to fetch that field value during Addnew.

Part of the code I'm thinking is as follows:

...
With Data1.Recordset
.AddNew
!GrpNo = Left(cbo_grpno, 2)
!GrpName = value should be from GroupMaster table field "grp_name"
.Update
End With
...

Would appreciate if you can share some info on this.

Thanks,

Sending Or Updating Database
hi all,
Am a beginner in Vb. In my application i had come across a problem.
My Application must run in three different branches in 3 cities. Every day the transaction of that day must reach the head office by the evening.
What should I do? Can I use Access as Database? Will Access Support this. If not please give me suggestions about the alternative database.
Looking forward for ur help.
Yours
Stanly Mathew

Updating A Database From Excel
My project of the day is a Sales Budget database in Access 2000 and I need to update the information in the Budget table with info from Excel. I have most of it figured out but how to check that the to key match and update that record for the three columns.

Here is what I have so far:

VB Code:
Do Until Trim(Sheet.Cells(Xo, constKEY)) = "" lblNote.Caption = "Uploading data " & Xo - 1 & " of " & RowCount - 2 & "..." Me.Refresh DoEvents 'Move the row data to the TYPE Var to reduce the number of time that this aplication accesses MS Excel. .Key = Sheet.Cells(Xo, constKEY) .Revised = Sheet.Cells(Xo, constREVISED) .Budget = Sheet.Cells(Xo, constBUDGET) 'Load data to db and do validation for empty strings Set rsAccess = New ADODB.Recordset sSQL = "SELECT * FROM tblBudget" rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic If .Key <> "" Then With rsAccess .AddNew .Fields("Revised03").Value = Trim(RowData.Revised) .Fields("2004Budget").Value = Trim(RowData.Budget) .Fields("Changes").Value = Trim(RowData.Budget) .Update End With End If Xo = Xo + 1 Progbar.Value = Xo DoEvents Loop

after the validation for blank records I need to match tblBudget.key with rowdata.key and update the fields revised03, 2004budget and changes. right now it will add them as new, this I think just needs to be .update. Am I right there.