4 Replies - 9313 Views - Last Post: 28 April 2011 - 11:41 AM Rate Topic: -----

#1 didza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 28-February 10

Passing a parameter to a dataset

Posted 26 April 2011 - 07:00 AM

I created a dataset that is getting data from an access database. Now i would like to go further and make the dataset query accept a parameter from a form datepick value. the query in my table adapter I have changed it as follows to accept a parameter on date1

SELECT Status, Date1, Product, Customer, Supplier, Net, Time1 AS [Time In], Date2, Time2, Transporter, VehicleNo, Trailer, Ticket, [Order], Delivery, ID
FROM            Eof
WHERE  (Date1 = FromDate)
ORDER BY Status, Date1 DESC, [Order], Product, Customer, Supplier



The code I am using in my form is as below. I get a null reference exception on the second line(Object reference not set to an instance of an object). it seems as if my selectCommand is bringing nothing. Have I constructed this wrong, if so how can i make it work. thanks

         Dim asas As New EofDataSet1TableAdapters.EofTableAdapter
        asas.Adapter.SelectCommand.Parameters.AddWithValue("FromDate", dtpDate.Value.Date)
        Me.EofTableAdapter.Fill(Me.EofDataSet1.Eof)

        Me.ReportViewer1.RefreshReport()



Is This A Good Question/Topic? 0
  • +

Replies To: Passing a parameter to a dataset

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8915
  • View blog
  • Posts: 33,427
  • Joined: 12-June 08

Re: Passing a parameter to a dataset

Posted 26 April 2011 - 07:38 AM

As an aside parameters should have the @ symbol at their heads. Example:

select column1 from tableA where column2 = @Param
..parameters.addwithvalue("@Param", value)


This helps SQL differentiate column names from variable names.

Object reference error, eh? This can be solved by looking at that line (break point on it) and investigating the variables involved.


asas.Adapter.SelectCommand.Parameters.AddWithValue("FromDate", dtpDate.Value.Date)

What variables are involved here?

I see three off the bat:
asas
asas.Adapter.SelectCommand
dtpDate

As I mentioned.. make sure your project is in 'debug' mode (top of visual studios is a drop down for mode). Put a break point on that line above (right click on the line, break point, insert break pont). Run the code. Get it to hit that line.

Now here's the fun part. Move your mouse over the variables and you can see who is 'nothing'. If that doesn't work highlight the variable name and drag it to the 'watch' window. (while you are on that break point go to menu debug->watch->watch1).

Report back what you find.
Was This Post Helpful? 0
  • +
  • -

#3 didza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 28-February 10

Re: Passing a parameter to a dataset

Posted 27 April 2011 - 02:55 PM

Thanks modi123 for the response. I have put the @ on my sql parameter to make it like
SELECT Status, Date1, Product, Customer, Supplier, Net, Time1 AS [Time In], Date2, Time2, Transporter, VehicleNo, Trailer, Ticket, [Order], Delivery, ID  
FROM Eof  
WHERE  (Date1 = @FromDate)  
ORDER BY Status, Date1 DESC, [Order], Product, Customer, Supplier 



I am doing this by going to my dataset then select 'edit dataset with designer' then right click and configure the tableadapter, then clicking next next and finish
But just before i click finish I get this warning message
"the wizard detected the following problems when configuring the TableAdapter:
"Eof"
Details:
Generated Select statement.
Error in WHERE clause near '@'
Unable to parse query text

To add these components to your dataset click finish.

I then click finish to apply the changes anyway.
I run my code but still get 'Object reference not set to an instance of an object', on the below code.
In break mode I point to SelectCommand in the below statement and it shows that it's = nothing
EofTableAdapter.Adapter.SelectCommand.Parameters.AddWithValue("@FromDate", dtpFromDate.Value)
        Me.EofTableAdapter.Fill(Me.DataSet1.Eof)


dtpDate is a date picker control so am getting the date from the control.
Was This Post Helpful? 0
  • +
  • -

#4 didza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 28-February 10

Re: Passing a parameter to a dataset

Posted 27 April 2011 - 03:04 PM

I am also assuming that selectCommand in this case is supposed to come with the sql statement i have put in my tableadapter. Am i assuming right?
Was This Post Helpful? 0
  • +
  • -

#5 didza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 28-February 10

Re: Passing a parameter to a dataset

Posted 28 April 2011 - 11:41 AM

I managed to get the solution. firstly the sql statement has to have a ? for the parameter
SELECT Status, Date1, Product, Customer, Supplier, Net, Time1 AS [Time In], Date2, Time2, Transporter, VehicleNo, Trailer, Ticket, [Order], Delivery, ID    
FROM Eof    
WHERE  (Date1 = ?)    
ORDER BY Status, Date1 DESC, [Order], Product, Customer, Supplier 


After doing this the parameter appeared on the tableadapter hence in my vb code removed he extra line and remained with

Me.EofTableAdapter.Fill(Me.DataSet1.Eof,dtpFromDate.value.date)

instead

thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1