Dear sir,

I have basic knowledge of VB .net

I want to sum records (integer) of a column in a table if I give a specific value (text) in a field.

      Dim ds As New DataSet
      Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=p:\inquiries.mdb;")
         Dim sql As String = "Select SUM(orders_amount) FROM orders WHERE (inquiries_ref = '" & TBOrdersCustomer.Text & "')"
            Dim AccessCommand As New OleDbCommand(sql, AccessConn)
            Dim da As New OleDbDataAdapter(AccessCommand)
            da.Fill(ds)
            OrdersDataGridView.DataSource = ds.Tables(0)

Edited 1 Year Ago by papadakiss

Just from a quick glance I would suspect that the trailing ; in the connection string is causing OleDbConnection to look for another value which isn't being supplied. I would suggest deleting that:

Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=p:\inquiries.mdb")

Thank you for your post.

I get the error message:
OledbException was unhandled
No value given for one or more required parameters

ON this command -----> da.Fill(ds)

Please try it

Dim sql As String = "Select SUM(orders_amount) As Amount FROM orders WHERE inquiries_ref ='" & TBOrdersCustomer.Text & "'"

Thank you for your help:

I receive the message:
Oledbexception was unhandled
No value given for one or more required parameters.

on command da.Fill(ds)

This is the final code. It works but the result is blank grid

Dim ds As New DataSet
        Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=p:\inquiries.mdb")
        Dim sql As String = "Select SUM(orders_amount) FROM orders WHERE (orders_customer = '" & TBOrdersCustomer.Text & "')"
        Dim AccessCommand As New OleDbCommand(sql, AccessConn)
        Dim da As New OleDbDataAdapter(AccessCommand)
        da.Fill(ds)
        OrdersDataGridView.DataSource = ds.Tables(0)

You're code looks to be OK. I would suggest verifying that the text being passed to the query is 100% accurate. For instance a leading space will invalidate the name and return no data. One way to correct any inaccuracies is bind a drop down list to the orders_customer column. This restricts the choices to only valid data.

Edited 1 Year Ago by tinstaafl

This article has been dead for over six months. Start a new discussion instead.