none
Getting Sum values of MS Access Field in VB.NET RRS feed

  • Question

  • Hi There,

     I am trying to get the Sum values of a Column and want to check if entered value in Textbox is valid.

    Here is my code below

    mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
                  "Data Source=K:\Amrut Diary\Amrut_Diary\ADDB.mdb")
            con = New OleDb.OleDbConnection(mystr)
            con.Open()

    strsql = "SELECT SUM(Available_Stock) As Avstk FROM STOCKDB Where Material_Name= '" & TMtNm.Text & "'"
            Dim cmd As New OleDbCommand(strsql, con)
            Dim reader As OleDbDataReader = cmd.ExecuteReader
            cmd.ExecuteReader()
                    If TQty.Text > reader("Avstk") Then
                MsgBox("Quantity Overflow")
                TQty.Text = ""
                TQty.Focus()
            End If

    When I execute, I am Getting "No value given for one or more required parameters." Error

    • Moved by VMazurModerator Wednesday, August 17, 2011 10:00 PM (From:ADO.NET DataSet)
    Wednesday, August 17, 2011 6:36 PM

Answers

  • Here's my test code. I made a little change of yours. It works OK. If I entered a value in the TextBox2.Text, which was greater than the column value, it prompts the alert window. 
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strConn As String = "Data Source=VICKY03-MSFT;Initial Catalog=Xueyun;Integrated Security=True"
    
        'Named a alias Larcolais for price column
        Dim strsql As String = "SELECT price AS Larcolais FROM parent WHERE startDate = '" & TextBox1.Text & "'"
        Dim conn As New SqlConnection(strConn)
        conn.Open()
        Dim cmd As New SqlCommand(strsql, conn)
        Dim reader As SqlDataReader = cmd.ExecuteReader
        'begin reader
        reader.Read()
        'if textbox2's text was greater than the current value of Larcolais column, the prompt window was occurred
        If TextBox2.Text > reader("Larcolais") Then
          MsgBox("alert")
        End If
    
      End Sub
    
    Thanks,

    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Thursday, August 25, 2011 9:47 AM
    Monday, August 22, 2011 4:09 AM

All replies

  • Is it the full query? Usually this error happens in a case if some of the column names or table name is a reserved word. You could avoid it by putting square brackets around all object names, like

    strsql = "SELECT SUM([Available_Stock]) As Avstk FROM [STOCKDB] Where [Material_Name=] '" & TMtNm.Text & "'"

    Also I would recommend to pass value from the text box into query using OledbParameter. In this case you can avoid SQL injection vulnerability.

     


    Val Mazur (MVP) http://www.xporttools.net
    • Proposed as answer by Larcolais Gong Friday, August 19, 2011 2:39 PM
    Wednesday, August 17, 2011 9:59 PM
    Moderator
  • I don't see a problem with the syntax of the SQL statement. Did you misspell a column name?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, August 18, 2011 12:34 PM
  • Hello,

    Like Paul's said, your SQL statement looks good. But I also suggest you can put your SQL statement into SSMS and double check if the result set was correct. Then, I suggest you check VMazur's suggestion, try to use parameter to avoid SQL injection.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 19, 2011 2:42 PM
  • Thanks Paul,

     

    I did verifying the Column name, but its correct.

    any other clue ?


    naveen.sd
    Friday, August 19, 2011 6:07 PM
  • On my side, I reproduced your issue when I entered "?" symbol in the TextBox control. I think this question was related to SQL injection. OleDb provider think this was a parameter like "SELECT * FROM table WHERE id=?". To conquer SQL injection attack, I suggest you try the following suggestion.

    1. Constrain input (you can consider regular expression)

    2. Use parameters with SP (strongly recommandation)

    3. Use parameters with dynamical SQL statement (strongly recommandation)

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, August 20, 2011 3:05 PM
  • No luck ,

     

    Is there any alternate way for a querying same task. ? Like using record set...

     

    Thanks in Advance.


    naveen.sd
    Saturday, August 20, 2011 6:21 PM
  • Here's my test code. I made a little change of yours. It works OK. If I entered a value in the TextBox2.Text, which was greater than the column value, it prompts the alert window. 
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strConn As String = "Data Source=VICKY03-MSFT;Initial Catalog=Xueyun;Integrated Security=True"
    
        'Named a alias Larcolais for price column
        Dim strsql As String = "SELECT price AS Larcolais FROM parent WHERE startDate = '" & TextBox1.Text & "'"
        Dim conn As New SqlConnection(strConn)
        conn.Open()
        Dim cmd As New SqlCommand(strsql, conn)
        Dim reader As SqlDataReader = cmd.ExecuteReader
        'begin reader
        reader.Read()
        'if textbox2's text was greater than the current value of Larcolais column, the prompt window was occurred
        If TextBox2.Text > reader("Larcolais") Then
          MsgBox("alert")
        End If
    
      End Sub
    
    Thanks,

    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Larcolais Gong Thursday, August 25, 2011 9:47 AM
    Monday, August 22, 2011 4:09 AM
  • No luck ,

     

    Is there any alternate way for a querying same task. ? Like using record set...

     

    Thanks in Advance.


    naveen.sd

    If you remove the Sum function from the SQL statement will it execute without an error?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, August 22, 2011 11:58 AM
  • Wow.. Its solved..

    Thank You very Much Larcolais Gong, it was ruining my time....

    and Thanks for all...


    naveen.sd


    Saturday, August 27, 2011 3:03 PM
  • Glad to hear I can help. Welcome to back if you have any question later.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, August 28, 2011 11:38 AM