Help needed - displaying sql COUNT value

[Stuart Marshall]

Hi there

I'm very new to these forums so I hope I'm posting this in the right place, my apologies if I am not.

I've been learning VB.net for about 2 months now and very recently I have been working with VB.net and SQL databases.

I have a data tier that allows me to connect to a SQL database. Below is the code I have written showing my attempt to display a value gained from a count select statement in another form within the same project. But it is reporting a problem with the format of the value I am trying to display.... can anybody help me?

-----------------------------


Public Sub CountValue()
Dim objConnection As DataTier.Connection
Dim objQueryCount As DataTier.Query
Dim countsql As Long
Dim Selected As Integer

Selected = Me.grdData.SelectedRows(0).Cells("eMailGroup_ID").Value

countsql = ("SELECT COUNT(eMailTransaction_ID) AS Exp1 FROM tblEmailTransaction WHERE (eMailGroup_ID = " + Selected + ")")
Try

' Generate a count value

objConnection = New DataTier.Connection("CAPRi_Beta_01", "PC08009")
objQueryCount = objConnection.CreateQuery(countsql)


If objQueryCount.Execute() Then

add.TxtCount.Text = countsql

Else
MessageBox.Show(objConnection.LastError, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

Catch ex As Exception
MessageBox.Show(ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
countsql = Nothing
Selected = Nothing
objConnection = Nothing
objQueryCount = Nothing

End Sub
---------------------------------------------------

I'm fairly sure it's something to do with the .tostring extension, but I'm really confused on this one.

thanks for your help

Stuart Marshall

 

[giadich]

Dim countsql As Long
....
countsql = ("SELECT COUNT(eMailTransaction_ID) AS Exp1 FROM tblEmailTransaction WHERE (eMailGroup_ID = " + Selected + ")")
....
objQueryCount = objConnection.CreateQuery(countsql)

are you trying to pass a long value into .CreateQuery()?

 

[Stuart Marshall]

Yes thats what the error message says, It reports that the value cannot be converted to a LONG value.

Am I doing this right or should I be doing this through a different way?

 

[Arg81]

That's correct, because you are declaring countsql AS LONG.

I'm sure a count should be an Integer....

 

[giadich]

objQueryCount = objConnection.CreateQuery(countsql)

don't you want to pass a sql string in to createquery()???

 

[Stuart Marshall]

That makes alot of sense.... what would be difference though?

Is their a big difference between what I have done and what you suggest...

Apart from less code, good practice and all that

 

[Stuart Marshall]

That's correct, because you are declaring countsql AS LONG.

I'm sure a count should be an Integer....

Hi there.

I tried changin the countsql dim as Integer but it reports the following error message.

Conversion from string "select count(emailTransaction_ID "to type 'Double' is not Valid.......

Any ideas what I am doing wrong?

 

[giadich]

Dim objConnection As DataTier.Connection
Dim objQueryCount As DataTier.Query

i do not recognize this code. is this some custom data access you are using? if so, lookup the syntax for the methods you are using. make sure you passing the datatype which the interface of the calling method is expecting.

 

[Stuart Marshall]

It's a custom made data tier for accessing my sql database.

Are you saying that the code used in the data tier determines exactly what classes and methods can be used to access the information in the database via my code...??

 

[giadich]

yes, if you define your own data access class then it will dictate how your code interact with the db.

if your method requires an integer, you pass in an integer (assuming you use this integer to further define which query to run...)

if your method requires an sql statement, you'll need to pass in an sql string and not in integer... i think this is the case here but without seeing your data access code, i can't be 100% sure.

you need to look at the CreateQuery() method in your data tier and find out what it needs in terms of parameters and pass in the appropriate data.

 

[Stuart Marshall]

Wow... thanks for that.. I will check it out and get back to you all.

Thanks for all your help so far.