How to pass a public variable to a sql query

[dougancil]

I have a public variable that I declared in form1 of an application. I am trying to call that variable in form2 and then pass that variable in a sql query.

If I declare

Public Class Form1
Public payPeriodStartD ate, payPeriodEndDat e As Date

How then to I declare that variable in form2 and how to I pass it to my sql query

SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime) as duration INTO ScratchPad2

FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber
where [Exceptions].exceptiondate between [B][payPeriodStartDate][/B] and [B][payPeriodEndDate][/B]

GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime,
[Exceptions].code, [Exceptions].exceptiondate

(The bold sections is where I need to call the public variables in my query)

thank you

Doug

[marcellus7]

Are you using a tableadapter? That makes it a lot easier. From the tableadapter you can add the query, and when creating the query use an @ to declare parameters. So you'd create teh query like this in the tableadapter:

SELECT name, address
FROM Users
WHERE name = @name

And then you'd call the query from the tableadapter in your code like this:

UsersTableAdapter.NameQuery(Form1.name)

Where you call the query with your variable as a parameter.

[dougancil]

Actually now that I think about this ... I currently have a datagrid view in my form and it has just a

Select * from exceptions query. I declare these variables at the start of my form:

Public payPeriodStartD ate As Date
Public payPeriodEndDat e As Date

and what I'd like to do is to modify my query for the datagrid view to be something more like this

select * from exceptions
where exceptiondate between payperiodstartd ate and payperiodenddat e.

What's the best way to do that?

Thank you

[marcellus7]

Ok, what you can do then is there is an option in the query wizard, when you create the query in the tableadapter, to use the query to fill a datatable. So declare the datatable in the Form (not in the OnClick event), and in the onclick event run the query to fill the datatable. Then you can just set the DataGrid source to the newly filled datatable.

[dougancil]

Marcellus,

Does your answer apply to my modified question as well?

thank you,

Doug

[marcellus7]

Sorry I had to re-read your answer to grasp it. The best way to do that is to create a DataView, set the DataView source to the source table that holds the data for your SELECT * FROM Exceptions query, and filter the DataView based on those dates. In this scenario your DataGrid source would be the dataview. Heres an example:

Public Class Form1
Dim dataTable as New DataTable
Dim dataView as New DataView

dataTable.Fill(SELECT * From Exceptions) 'Just an example of wherever you're filling the datatable

DataView.Table = dataTable
DataGrid.Source = DataView
DataView.RowFilter = "ExceptionDate > '" & PayPeriodStartDate & "' AND ExceptionDate < '" & PayPeriodEndDate  & "'"

It would be something along those lines..

[dougancil]

Marcellus,

Wouldnt it also be possible then in the datagridview to alter the query that's in there to reflect

select * from exceptions
where exceptiondate between Payperiodstartd ate and PayperiodEnddat e?

[marcellus7]

How are you storing the data thats being displayed in the DataGridView? DataGridViews are usually used only to display information thats being stored in a table somewhere. Can you post the code where you select the data and display it in the datagrid?

[dougancil]

This is the only code I have for the datagridview, because I have the SQL connection bound into the form.

SELECT Employeenumber, Exceptiondate, Starttime, Endtime, Duration, Code, Approvedby FROM dbo.Exceptions

[dougancil]

Marcellus,

If I just wanted to use the datagridview wizard and create a query to display data between payrollstartdat e and payrollenddate, how would I construct that query in the wizard? Basically, the data isn't being modified in this "initial" datagrid view. This is only for display. There will be a form that will allow users to edit from this form that will store this data back to the database.

this is the query that I'm trying to execute in my datagridview:

SELECT        Employeenumber, Exceptiondate, Starttime, Endtime, Duration, Code, Approvedby
FROM            Exceptions
where [Exceptions].exceptiondate between '" & payperiodStartDate & "' and '" & payPeriodEndDate"

but I'm getting an error that it's unable to parse the query. Can you offer me a suggestion of how to fix this.

Thanks

Doug

[Marcellus Smith]

Is that code being executed in a tableadapter, or do you have a binding source, or where exactly are you inputting that query?

[dougancil]

I do have a binding source. I have a dataset called MDRDataSet and edited the sql query that is in my table adapter configuration wizard to be this:

SELECT Employeenumber, Exceptiondate, Starttime, Endtime,Duration, Code, Submittedby FROM Exceptions where [Exceptions].exceptiondate between '" & payperiodStartDate & "' and '" & payPeriodEndDate & "'

but it's not producing any results.

[marcellus7]

Use an @ before the variable names, so that they will be used as parameters, and when you fill the dataset, pass those variables as parameters

[dougancil]

Marcellus,

Are you talking about in the code for the form itself?

So it would look like this:

Public @payPeriodStart Date As Date
Public @payPeriodEndDa te As Date