0

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 by papadakiss

3
Contributors
6
Replies
53
Views
1 Year
Discussion Span
Last Post by tinstaafl
0

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")
0

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)

0

Please try it

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

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)

0

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)
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 by tinstaafl

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.