By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

How to pass a public variable to a sql query

100+
P: 347
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 payPeriodStartDate, payPeriodEndDate As Date

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

Expand|Select|Wrap|Line Numbers
  1. SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime) as duration INTO ScratchPad2
  2.  
  3. FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber
  4. where [Exceptions].exceptiondate between [payPeriodStartDate] and [payPeriodEndDate]
  5.  
  6. GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime,
  7. [Exceptions].code, [Exceptions].exceptiondate
  8.  
(The bold sections is where I need to call the public variables in my query)

thank you

Doug
Nov 4 '10 #1
Share this Question
Share on Google+
13 Replies


P: 33
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT name, address
  2. FROM Users
  3. WHERE name = @name
  4.  
And then you'd call the query from the tableadapter in your code like this:

Expand|Select|Wrap|Line Numbers
  1. UsersTableAdapter.NameQuery(Form1.name)
  2.  
Where you call the query with your variable as a parameter.
Nov 5 '10 #2

100+
P: 347
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 payPeriodStartDate As Date
Public payPeriodEndDate 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 payperiodstartdate and payperiodenddate.

What's the best way to do that?

Thank you
Nov 5 '10 #3

P: 33
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.
Nov 5 '10 #4

100+
P: 347
Marcellus,

Does your answer apply to my modified question as well?

thank you,

Doug
Nov 5 '10 #5

P: 33
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:

Expand|Select|Wrap|Line Numbers
  1. Public Class Form1
  2. Dim dataTable as New DataTable
  3. Dim dataView as New DataView
  4.  
  5. dataTable.Fill(SELECT * From Exceptions) 'Just an example of wherever you're filling the datatable
  6.  
  7. DataView.Table = dataTable
  8. DataGrid.Source = DataView
  9. DataView.RowFilter = "ExceptionDate > '" & PayPeriodStartDate & "' AND ExceptionDate < '" & PayPeriodEndDate  & "'"
  10.  
It would be something along those lines..
Nov 5 '10 #6

100+
P: 347
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 Payperiodstartdate and PayperiodEnddate?
Nov 5 '10 #7

P: 33
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?
Nov 5 '10 #8

100+
P: 347
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
Nov 5 '10 #9

100+
P: 347
Marcellus,

If I just wanted to use the datagridview wizard and create a query to display data between payrollstartdate 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT        Employeenumber, Exceptiondate, Starttime, Endtime, Duration, Code, Approvedby
  2. FROM            Exceptions
  3. where [Exceptions].exceptiondate between '" & payperiodStartDate & "' and '" & payPeriodEndDate"
  4.  
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
Nov 9 '10 #10

P: n/a
Is that code being executed in a tableadapter, or do you have a binding source, or where exactly are you inputting that query?
Nov 9 '10 #11

100+
P: 347
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Employeenumber, Exceptiondate, Starttime, Endtime,Duration, Code, Submittedby FROM Exceptions where [Exceptions].exceptiondate between '" & payperiodStartDate & "' and '" & payPeriodEndDate & "'
  2.  
but it's not producing any results.
Nov 12 '10 #12

P: 33
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
Nov 12 '10 #13

100+
P: 347
Marcellus,

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

So it would look like this:

Public @payPeriodStartDate As Date
Public @payPeriodEndDate As Date
Nov 12 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.