Question Display data from SQL query

[Rubberlegs]

Hello,

I am new to VB.net and programming in general, and I have been asked to create a form that will display the data from this SQL query at the click of a command button, and display the resulting Integer in a text box / label:

SELECT COUNT(DISTINCT MessageIdentity)

FROM MessageRecipients

WHERE SenderInternal = 'Y' and RecipientInternal = 'N' and TimeReceived >= (GETDATE() - 30)

I have connected the Db via the Datasources tab but that's about it!

Please help - I have until 5pm tomorrow!! And I'm only on chapter 4 of the book!!

Any help greatly appreciated.

Thanks,
Mark

 

[MattP]

Connecting via DataSources is a step in the right direction. This may seem like a long process but it only takes a couple of seconds once you've done it a couple of times.

1. Double-click on the DataSet.xsd that was created when you added the DataSource.

2. In the TableAdapter section right click on the Fill, GetData() method that's listed and choose configure.

3. Paste your query into the window. (I would recommend naming your result column so it makes sense for you later otherwise you'll end up with the meaningless name Expr1.)

SELECT COUNT(DISTINCT MessageIdentity) AS NumResults
FROM MessageRecipients
WHERE SenderInternal = 'Y' and RecipientInternal = 'N' and TimeReceived >= (GETDATE() - 30)

4. Click on Query Builder and then Execute Query to make sure you're getting the expected results (Optional Step)

5. Click Finish and close the xsd file.

6. Go back to the form designer

7. From the Data Sources tab click the + in front of your DataSet then the + in front of your DataTable and you should see the NumResults column that your query created.

8. If you're wanting this to be a textbox you can leave this as is, if you want to make it a different control click on the column and you'll see the different controls you can change it to in the ComboBox items.

9. I'll assume textbox from here. Click on the column and drag it to your form. You'll notice that a DataSet, BindingSource, BindingNavigator, and TableAdapter are created. You'll also have a label with the text NumResults: and a TextBox.

10. You won't need the BindingNavigator so you can select and delete it.

11. Go to the code behind for your form and you should notice a line similar to this in the Load even.

Me.TableAdapter.Fill(Me.DataSet.DataTable)

12. Move this code to your button's click event.

13. Run your program.

 

[cjard]

I'd have made that a scalar query off the main tableadapter, but either way works!

 

[Rubberlegs]

Hi,

Thanks for your help. However when I enter my SELECT statement into the TableAdapter Configuration Wizard and run the Query Builder I get the following error:

"The query builder failed. Attempted to read or write protected memory. This is often an indication of other memory is corrupt."

Any ideas?! I have checked the db permissions and they are all correct and when I run the statement in Enterprise Manager it does return the correct results.

This error has happened on two different machines now, so I guess it's nothing to do with the machine setup?

Many thanks once again,

Mark

 

[cjard]

Oh dear. I only ever got this error due to a bugged DLL in my Oracle drivers, but you're not using Oracle


What happens if you:

Make your TableAdapter as normal (See DW2 link in my sig, section Creating a Simple Data App)
Right click it
Choose Add Query
Query That Returns A Single Value
Paste your SQL
Call it "GetDistinctMessageIdentityCount"

Use it like:

myTextBox.Text = myTableAdapter.GetDistinctMessageIdentityCount()

 

[Rubberlegs]

Thanks for your reply. I have done as you suggested and on the click event of a button I added the following code:

Label1.Text = Mailmeter_Test_MRDataSetTableAdapters.MessageRecipientsTableAdapter.GetDistinctMessageIdentityCount()

However VB underlines the statement in blue and is telling me that "Reference to non-shared member requires an object reference"

Thanks,
Mark

 

[cjard]

Maybe then you should make an instance of the TableAdapter then.

If youre not sure what I'm on about, you'll need to look up more info on object oriented programming. For now, do this:

Dim ta as New Mailmeter_Test_MRDataSetTableAdapters.MessageRecipientsTableAdapter
Label1.Text = ta.GetDistinctMessageIdentityCount()

..it fixes your problem but if you don't know why you need to read a tutorial

And please rename Label1 to something meaningful

 

[Rubberlegs]

Thank you! I undrastand now and it works fine

Thanks again