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

C

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
 
Sort by date Sort by votes
JuggaloBrotha said:
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
Click to expand...


***Running it NOW. Stand by***
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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: 65
Last edited:
Upvote 0 Downvote
cacaoguy said:
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
Click to expand...
Yep, I forgot to declare YearStr:
Dim YearStr As String = DateTime.Now.Year.ToString()
cacaoguy said:
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.
Click to expand...
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?
 
Upvote 0 Downvote
JuggaloBrotha said:
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?
Click to expand...

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: 66
Upvote 0 Downvote
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
 
Upvote 0 Downvote

Similar threads

manny cash
  • Question
Here is my code database and table I am so confuse
Replies
2
Views
260
manny cash
manny cash
M
  • Question
Question I am using VB2019 I want to create a database, I got problems
Replies
1
Views
1K
jmcilhinney
jmcilhinney
M
  • Question
System.IndexOutOfRangeException: 'indv_no'
Replies
1
Views
317
jmcilhinney
jmcilhinney
S
  • Solved
Resolved element.Add check if it exist before add
Replies
4
Views
315
SigsterSal
S
A
  • Question
Question Is this code thread-safe?
Replies
5
Views
857
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top