Pass user input from form To Report Viewer using SQL SELECT ... @var

cacaoguy

Member
Joined
Dec 30, 2015
Messages
13
Programming Experience
5-10
/*
SQL 2008
Visual Studio 2013 - Report Viewer

My vb skill level: Poor but I'm excited to learn
smile.gif

Profession: DBA
- Can I set up the sql connection in VB? - YES
- Can I set up the NAME, Data Source, and Table Adapter in VB? - YES

ERROR:
Argument Not Specified For Parameter
*/

Please guys, I know how uncool it is to ask for a extra "hand holding", but I'm really new at this and realize I'm over my head so I would be very greatfull for your extra patience. Very Sincerely....

1. Here is my Form
my-form.jpg

2. Here is my SELECT statement
SELECT * FROM dbo.procurement_goods WHERE MONTH(date) = @month AND YEAR(date) = @year

3.
This is My Table Adapter
Screenshot - 12_27_2015 , 9_25_31 PM.jpg
parameters.jpg

4. My Code in Visual Studio
I'm posting my new code here and my question to you is: what should my code look like to resolve the missing parameter?

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms

Public Class REPORT_monthly_Procurement_goods
    Dim conn As SqlConnection
    Dim comm As SqlCommand

    Private Sub openconnection()
        conn = New SqlConnection
        conn.ConnectionString = "server=myserver;database=mydb;Integrated Security=SSPI;"
        conn.Open()
    End Sub

    Private Sub ComboBox_MONTH_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox_MONTH.SelectedIndexChanged
        'Convert Month Name to INT
        Dim var_month As String
        If ComboBox_MONTH.SelectedItem.ToString = "January" Then var_month = "1"
        If ComboBox_MONTH.SelectedItem.ToString = "February" Then var_month = "2"
        If ComboBox_MONTH.SelectedItem.ToString = "March" Then var_month = "3"
        If ComboBox_MONTH.SelectedItem.ToString = "April" Then var_month = "4"
        If ComboBox_MONTH.SelectedItem.ToString = "May" Then var_month = "5"
        If ComboBox_MONTH.SelectedItem.ToString = "June" Then var_month = "6"
        If ComboBox_MONTH.SelectedItem.ToString = "July" Then var_month = "7"
        If ComboBox_MONTH.SelectedItem.ToString = "August" Then var_month = "8"
        If ComboBox_MONTH.SelectedItem.ToString = "September" Then var_month = "9"
        If ComboBox_MONTH.SelectedItem.ToString = "October" Then var_month = "10"
        If ComboBox_MONTH.SelectedItem.ToString = "November" Then var_month = "11"
        If ComboBox_MONTH.SelectedItem.ToString = "December" Then var_month = "12"
    End Sub

    Private Sub ComboBox_YEAR_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox_YEAR.SelectedIndexChanged

    End Sub

    Private Sub go_button_Click(sender As Object, e As EventArgs) Handles go_button.Click
        comm.Parameters.AddWithValue("@month", CInt(ComboBox_MONTH.Text))
        comm.Parameters.AddWithValue("@year", CInt(ComboBox_YEAR.Text))
    End Sub

    Private Sub REPORT_monthly_Procurement_goods_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Errors here:
        Me.procurement_goodsTableAdapter.Fill(Me.DSOURCE_ProcurementGoodsTABLE.procurement_goods)

        Me.ReportViewer1.RefreshReport()
    End Sub

End Class

Argument-error1.jpg
 
Of course I don't have your project to actually run this, but I believe this is what you're needing:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms
 
Public Class REPORT_monthly_Procurement_goods
 
    Private Sub REPORT_monthly_Procurement_goods_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Select the current year in the ComboBox
        ComboBox_YEAR.SelectedIndex = 0I
        For Counter As Integer = 0I To ComboBox_YEAR.Items.Count - 1I
            If ComboBox_YEAR.Items(Counter).ToString = YearStr Then ComboBox_YEAR.SelectedIndex = Counter
        Next Counter
        
        'Select the current month in the ComboBox
        ComboBox_YEAR.SelectedIndex = DateTime.Now.Month - 1

        Call LoadReport(DateTime.Now.Month, DateTime.Now.Year)
    End Sub
    
    Private Sub go_button_Click(sender As Object, e As EventArgs) Handles go_button.Click
        Dim YearValue As Integer
        If Not Integer.TryParse(ComboBox_YEAR.SelectedItem.ToString, YearValue) Then YearValue = DateTime.Today.Year
        Call LoadReport(GetMonthInteger(), YearValue)
    End Sub
    
    Private Sub LoadReport(MonthValue As Integer, YearValue As Integer)
        Try
            Me.procurement_goodsTableAdapter.Fill(Me.DSOURCE_ProcurementGoodsTABLE.procurement_goods, MonthValue, YearValue)
            Me.ReportViewer1.RefreshReport()
        Catch Ex As Exception
            MessageBox.Show(String.Format("Error getting data for the report:{0}{1}", Environment.NewLine, Ex.ToString()))
        End Try
    End Sub

    Private Function GetMonthInteger() As Integer
        Dim MonthValue As Integer

        Select Case ComboBox_MONTH.SelectedItem.ToString
            Case "January" : MonthValue = 1I
            Case "February" : MonthValue = 2I
            Case "March" : MonthValue = 3I
            Case "April" : MonthValue = 4I
            Case "May" : MonthValue = 5I
            Case "June" : MonthValue = 6I
            Case "July" : MonthValue = 7I
            Case "August" : MonthValue = 8I
            Case "September" : MonthValue = 9I
            Case "October" : MonthValue = 10I
            Case "November" : MonthValue = 11I
            Case "December" : MonthValue = 12I
            Case Else : MonthValue = 0I
        End Select
        
        Return MonthValue
    End Function
    
End Class


***Running it NOW. Stand by***
 
Hi,
I noticed YearStr needs to be declared.

Dim YearStr As String ????
YearStr = 2015 ????

....................................


Private Sub REPORT_monthly_Procurement_goods_Load(sender As Object, e As EventArgs) Handles Me.Load

'Select the current year in the ComboBox
ComboBox_YEAR.SelectedIndex = 0I
For Counter As Integer = 0I To ComboBox_YEAR.Items.Count - 1I
If ComboBox_YEAR.Items(Counter).ToString = YearStr Then ComboBox_YEAR.SelectedIndex = Counter
Next Counter
 
Here's the error.
Also I noticed that during runtime the combobox.Month collection items were not available in the pull down.
Only the Default of January was there. But when I clicked the pull down there were no months to choose from.
vbdotnet-passing-gobutton-1-25-2016.jpg
 

Attachments

  • empty_combo vbdotnet-passing-gobutton-1-25-2016.jpg
    empty_combo vbdotnet-passing-gobutton-1-25-2016.jpg
    34.1 KB · Views: 55
Last edited:
Hi,
I noticed YearStr needs to be declared.

Dim YearStr As String ????
YearStr = 2015 ????

....................................


Private Sub REPORT_monthly_Procurement_goods_Load(sender As Object, e As EventArgs) Handles Me.Load

'Select the current year in the ComboBox
ComboBox_YEAR.SelectedIndex = 0I
For Counter As Integer = 0I To ComboBox_YEAR.Items.Count - 1I
If ComboBox_YEAR.Items(Counter).ToString = YearStr Then ComboBox_YEAR.SelectedIndex = Counter
Next Counter
Yep, I forgot to declare YearStr:
Dim YearStr As String = DateTime.Now.Year.ToString()
Here's the error.
Also I noticed that during runtime the combobox.Month collection items were not available in the pull down.
Only the Default of January was there. But when I clicked the pull down there were no months to choose from.
You'll need to fill the months combobox with all of the months, and the years too, maybe a Stored Procedure that returns all the years that are present in the database?
 
Yep, I forgot to declare YearStr:
Dim YearStr As String = DateTime.Now.Year.ToString()
You'll need to fill the months combobox with all of the months, and the years too, maybe a Stored Procedure that returns all the years that are present in the database?

Omg Juggalo,

THAT's IT !!!!!!

You've done it!!!!!

:highly_amused: :highly_amused: I am so grateful to you for all of your help, and especially your patience. :highly_amused: :highly_amused:
 

Attachments

  • success.jpg
    success.jpg
    66.5 KB · Views: 55
Here is my final code.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms 

Public Class REPORT_monthly_Procurement_goods
    Dim conn As SqlConnection
    Dim comm As SqlCommand
    
Private Sub openconnection()
        conn = New SqlConnection
        conn.ConnectionString = "server=PRIVATE;database=PRIVATE;Integrated Security=SSPI;"
conn.Open()
    End Sub


   
    Private Sub REPORT_monthly_Procurement_goods_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Select the current year in the ComboBox
        Dim YearStr As String = DateTime.Now.Year.ToString()


        ComboBox_YEAR.SelectedIndex = 0I
        For Counter As Integer = 0I To ComboBox_YEAR.Items.Count - 1I
            If ComboBox_YEAR.Items(Counter).ToString = YearStr Then ComboBox_YEAR.SelectedIndex = Counter
        Next Counter


        'Select the current month in the ComboBox
        ComboBox_YEAR.SelectedIndex = DateTime.Now.Month - 1


        Call LoadReport(DateTime.Now.Month, DateTime.Now.Year)
    End Sub



    Private Sub ComboBox_MONTH_SelectedIndexChanged(sender As Object, e As EventArgs)


    End Sub



    Private Sub ComboBox_YEAR_SelectedIndexChanged(sender As Object, e As EventArgs)


    End Sub

 
    Private Sub go_button_Click(sender As Object, e As EventArgs) Handles go_button.Click
        Dim YearValue As Integer
        If Not Integer.TryParse(ComboBox_YEAR.SelectedItem.ToString, YearValue) Then YearValue = DateTime.Today.Year
        Call LoadReport(GetMonthInteger(), YearValue)
    End Sub



    Private Sub LoadReport(MonthValue As Integer, YearValue As Integer)
        Try
            Me.procurement_goodsTableAdapter.Fill(Me.DSOURCE_ProcurementGoodsTABLE.procurement_goods, MonthValue, YearValue)
            Me.ReportViewer1.RefreshReport()
        Catch Ex As Exception
            MessageBox.Show(String.Format("Error getting data for the report:{0}{1}", Environment.NewLine, Ex.ToString()))
        End Try
    End Sub



    Private Function GetMonthInteger() As Integer
        Dim MonthValue As Integer


        Select Case ComboBox_MONTH.SelectedItem.ToString
            Case "January" : MonthValue = 1I
            Case "February" : MonthValue = 2I
            Case "March" : MonthValue = 3I
            Case "April" : MonthValue = 4I
            Case "May" : MonthValue = 5I
            Case "June" : MonthValue = 6I
            Case "July" : MonthValue = 7I
            Case "August" : MonthValue = 8I
            Case "September" : MonthValue = 9I
            Case "October" : MonthValue = 10I
            Case "November" : MonthValue = 11I
            Case "December" : MonthValue = 12I
            Case Else : MonthValue = 0I
        End Select


        Return MonthValue
    End Function



    Private Sub ReportViewer1_Load(sender As Object, e As EventArgs) Handles ReportViewer1.Load


    End Sub




    Private Sub ComboBox_YEAR_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles ComboBox_YEAR.SelectedIndexChanged


    End Sub


End Class
 
Back
Top