Help with saving data

[Jacksparrow87]

Hi people, I was hoping someone could offer me some help please, I have my application all working apart from this one problem im facing with.

What the program does is simple, my combobox consists of company names, and when a company name is selected their calls logged are shown in a label. What I need help with is the ‘Save’ button, what I want to do is when the save button is selected I want to +1 to the value in the label and save the new number in the database.

So far I have the following coding which shows the data:

Dim constr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb"
    Dim tblComp As New DataTable
    Dim connection As New OleDbConnection(constr)

    Private Sub Loadcus()
        Dim adapter As New OleDbDataAdapter("SELECT * FROM [Company] ORDER BY CompanyName", connection)
        tblComp.Clear()
        adapter.Fill(tblComp)
        Me.cmbcomp.DisplayMember = "CompanyName"
        Me.cmbcomp.ValueMember = "CompID"
        Me.cmbcomp.DataSource = tblComp
        Me.lblclog.DataBindings.Add("Text", tblComp, "CallsLogged")
    End Sub

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Loadcus()
    End Sub

Any help would be appreciated.
Thanks

 

[RiverGuy]

Are you just working with this one table? Or do you have a table for calls? I would definitely have a table for CallLog and include the date/time the call was made, relating it to Company and just update the Company table based on the count of records in CallLog...or even better--keep it fully normalized and make your "CallsLogged" column be a count of related calls in the other table.

If you are unable to do this, then just use a simple .ExecuteNonQuery() on a Command object with syntax like "UPDATE Company SET CallsLogged = CallsLogged + 1 WHERE CompID = ?", adding a parameter for your selected CompID. Then, just refresh your display.

 

[Jacksparrow87]

Hi Riverguy,

Well I have various tables, for example Logs, Company, Callers, Staff, Product etc (the information is going to get saved into the company table as in that table it shows how many calls that company has logged.)

So far I have the following:

    Private Sub Savecomp()
        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter("Insert into Company(CompID,CompName,CallsLogged) Values('" & lblcompID.Text & "','" & cmbcomp.Text & "','" & lblclog.Text & "')" Update Company Set CompID=’” & lblcompid.text & ‘”,CompName=’” & cmbname.text & ‘”,CallsLogged=’” & lblclog.Text & "' WHERE CompID=" & lblcompid”)
            End If
            conn.Open()

            Dim command As New OleDbCommand(sql, conn)
            command.ExecuteNonQuery()
            conn.Close()

End sub

  Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        lblclog.Text = Convert.ToString(Convert.ToInt32(lblclog.Text) + 1)
Savecomp()    
End Sub

However I know im miles off

 

[RiverGuy]

I don't see where you are setting your CommandText of your Command object. Take the following code:

Dim cmd As New OleDBCommand
With cmd
  .CommandType = CommandType.Text
  .Connection = conn
  .CommandText = "UPDATE [Company] SET CallsLogged = CallsLogged + 1 WHERE CompID = ?"
  .Parameters.AddWithValue("@p1", me.cmbComp.SelectedValue)
End With
conn.Open()
cmd.ExecuteNonQuery()

Now what this code does is what your original post more or less specifies (from what I understand)--it increments the CallsLogged column for a specific CompID by 1. You don't want to base the value off of the label in case you have two users using the system at the same time.

But like I was saying before--the proper design would be to base the CallsLogged off a table that records each call. I'm not sure if Access allows triggers which could update this for you or not.

 

[Jacksparrow87]

Thanks again for your help Riverguy, I tried your coding but first it says I havent declared 'conn' and secondly it says Addwithvalue is not part of the parameters value.

 

[RiverGuy]

You need to either create a new connection or reuse an existing connection available to your routine. AddWithValue might not be part of OLEDB parameters. Try looking at Add instead.

 

[Jacksparrow87]

Thanks you soo much solved, thanks again