Saving Data to Database.. 1

[ilovevbsomuchimmad]

Hi, I have the following coding which shows data from a database. However throughout the form the number in Textbox1 Changes, when I exit the program and re-load the program the quantity number does not change. The coding I have used is:

visual basic codeim connStr As String = _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\Frank\Desktop\Equipment.mdb"
Dim sqlCommand As String = "SELECT * FROM Items"
Dim Equipmentconnector As New OleDb.OleDbConnection(connStr)
Dim Equipmentadapter As New OleDb.OleDbDataAdapter(sqlCommand, Equipmentconnector)
Dim oDatatable As New DataTable

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Equipmentadapter.Fill(oDatatable)
TextBox1.Clear()
TextBox3.DataBindings.Add("Text", oDatatable, "ItemDescription")

ListBox1.DataSource = oDatatable
ListBox1.DisplayMember = "ItemDescription"
TextBox1.DataBindings.Add("Text", oDatatable, "CurrentQuantity")



If somebody could help me on the coding to actually save the "Current Quantity" in the database, I would appreciate it.

Thanks

 

[TipGiver]

Hi Frank ;P

TextBox1.Clear()
TextBox3.DataBindings.Add("Text", oDatatable, "ItemDescription")

Dim you mean TextBox1 ?

 

[TipGiver]

Dim you mean TextBox1 ?
> No. (I answer myself)

 

[TipGiver]

Have a look at Equipmentadapter.Update(...)
With a google search you will find easily the answer.

PS: Do some search on the 'DataSet' (instead of DataTable)


______________
Hope these hints help

 

[ilovevbsomuchimmad]

Hi, Ive tried Google but cant find anything. I know I should be using Dataset but for what I need to do i prefer Datatable. However So far I have the following in a Buttons command, but its still not working:

Equipmentadapter.Update(oDatatable)

Im sure i have to mention textbox1 Somewhere along the lines

 

[Keyth]

You have not defined an update command for your adapter. Try adding an adapter to your form and then within your form's load event, set the oledbDataAdapter's SELECT, UPDATE, DELETE and INSERT commands connection property to your pre-defined connection. This will help you use the adapter at design time as well as use it at runtime.

1 Added the adapter to the form tray using a wizard
2 Within the form load event...

Me.OleDbDataAdapter1.SelectCommand.Connection = myconnection
Me.OleDbDataAdapter1.UpdateCommand.Connection = myconnection
Me.OleDbDataAdapter1.DeleteCommand.Connection = myconnection
Me.OleDbDataAdapter1.InsertCommand.Connection = myconnection


3. Then on closing your form or wherever required add the update command...
...

me.OleDbDataAdapter1.Update(tablename)

 

[ilovevbsomuchimmad]

Sorry, what would I replace Myconnection with? What would the my connection mean? Also upon the following coding which goes with the button, I replaced the (tablename) with (AddRemoveItems) and it says it has not been declared for some reason.

me.OleDbDataAdapter1.Update(tablename)

Thanks for your reply

Btw I did add the adapter and link it to my database. (Its just the coding im stuck on)

 

[Keyth]

> Sorry, what would I replace Myconnection with?

Dim myconnection as new oledbconnection
myconnection.connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\Frank\Desktop\Equipment.mdb"
"

> I replaced the (tablename) with (AddRemoveItems) and it says it has not been declared for some reason.

Sorry, my mistake... tablename should read datasetname meaning the name assigned to the dataset. The adapter will update the table using the dataset, it will fill and update using the same TableName.

Dim ds as new dataset '(ds = datasetname)
adapter.fill(ds, "Items") 'Assigning "Items" as the table name
..
adapter.update(ds, "Items") 'updating the tablename "Items

 

[ilovevbsomuchimmad]

Hey well I have the following:

Dim myconnection As New OleDb.OleDbConnection
myconnection.connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\Frank\Desktop\Equipment.mdb"

But on the second line, it underlines "MyConnection" and says declaration expected, which is weird to me as its been declared above. Any Suggestions please?

 

[Keyth]

Have you pasted the block of code within a sub/function etc?

It sounds like you have pasted the myconnection.connectionstring... bit outside.

for example:

Public Class frmSearch


    Dim myconnection As New OleDb.OleDbConnection

        myconnection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source = C:\Documents and Settings\Frank\Desktop\Equipment.mdb"


End Class

should be

Public Class frmSearch

    Dim myconnection As New OleDb.OleDbConnection

    [b]Private Sub mySubNameHere()[/b]
        myconnection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source = C:\Documents and Settings\Frank\Desktop\Equipment.mdb"
    [b]End Sub[/b]

End Class