MS Access And VB.net Query Error

Question

I Have A Table with the following Columns

ID(integer)
mat_id(integer)
move_date(Date/Time)
rec_num (Short text)
Qty (number(double))

I have made a connection programmatically and tried a query the code is as below

Public Class ViewMatInfo
    Dim ConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\RMM\RMMDB.mdb;Persist Security Info=True"
    Dim Conn As New OleDb.OleDbConnection(ConStr)
    Dim ShowTable As New OleDb.OleDbCommand
    Dim RAD As OleDb.OleDbDataReader
 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            ShowTable.Connection = Conn
            ShowTable.CommandType = CommandType.Text
            ShowTable.CommandText = "SELECT Sum(qty) AS [totqty] FROM moves WHERE [mat_id] = " & MT_TEXT.SelectedValue.ToString & ""
            Conn.Open()
            RAD = ShowTable.ExecuteReader()
            While RAD.Read
                SUM_TEXT.Text = RAD.GetDouble("totqty")
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Try
            Me.MovesTableAdapter.FillByMat(Me.RMMDS.moves, MT_TEXT.SelectedValue)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
End Class

When I run the application and select the material from "MT_TEXT" combobox and then click on the button the data grid view is filled greatly with all material moves. but the SUM_TEXT text box is still empty. and get the error message "Conversion From string "totqty" to integer is not valid"

Please is there any solution for this problem

The documentation for SELECT does not show delimiters of any sort around the alias - does it work if you use SELECT Sum(qty) AS totqty FROM ...? Also, I recommend using SQL parameters instead of concatentating the query value into the SQL string. — Andrew Morton
– Andrew Morton, Commented Aug 8, 2015 at 11:53
Which line is raising the error? Is it this one? SUM_TEXT.Text = RAD.GetDouble("totqty") — HarveyFrench
– HarveyFrench, Commented Aug 8, 2015 at 13:09

HarveyFrench · Accepted Answer · 2015-08-08 13:07:32Z

1

  = " & MT_TEXT.SelectedValue.ToString & ""

This looks iffy. Why are there two double quotes at the end and only a single one before.

Should the line read:

ShowTable.CommandText 
= "SELECT Sum(qty) AS [totqty] 
FROM moves WHERE [mat_id] = """ & MT_TEXT.SelectedValue.ToString & """"

answered Aug 8, 2015 at 13:07

HarveyFrench

4,6184 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access And VB.net Query Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related