Execute SQL from Custom Code

Hi all,
Hopefully, this is a quick and easy one to answer. Is it possible to
execute an sql statement from custom code on the "Code" tab of a
Report's properties menu? I'm pretty sure I could do this by writing a
custom assembly and referencing it from the report, but what I want to
do is write a code snippet on the code tab, which expects three
parameters, and returns a scalar value (similar to this):
Public Shared Function DLookup(ByVal Expression As String, ByVal
Domain As String, Optional ByVal Criteria As String = "") As String
Dim strSQL As String
Dim retVal As String
Dim oConn As System.Data.SqlClient.SqlConnection
Dim oCmd As System.Data.SqlClient.SqlCommand
oConn.ConnectionString = sharedConnStr
oCmd.Connection = oConn
oConn.Open()
strSQL = "SELECT " & Expression & " FROM " & Domain
If Criteria.Trim().Length > 0 Then
strSQL += Criteria
End If
oCmd.CommandText = strSQL
retVal = oCmd.ExecuteScalar()
End Function
This is mainly pseudocode, as I don't have a clue how to do it or if
it's possible. Any enlightenment will be appreciated.I am pretty sure that even if you get this to work from your development
environment it will fail when deployed unless you write a custom assembly.
My advice is to stay away from doing this. Instead, have a stored procedure
that you pass parameters to. Or use subreport (all depends on what you are
trying to accomplish).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdjewell" <fivejewells@.cox.net> wrote in message
news:1169586090.910149.111150@.m58g2000cwm.googlegroups.com...
> Hi all,
> Hopefully, this is a quick and easy one to answer. Is it possible to
> execute an sql statement from custom code on the "Code" tab of a
> Report's properties menu? I'm pretty sure I could do this by writing a
> custom assembly and referencing it from the report, but what I want to
> do is write a code snippet on the code tab, which expects three
> parameters, and returns a scalar value (similar to this):
> Public Shared Function DLookup(ByVal Expression As String, ByVal
> Domain As String, Optional ByVal Criteria As String = "") As String
> Dim strSQL As String
> Dim retVal As String
> Dim oConn As System.Data.SqlClient.SqlConnection
> Dim oCmd As System.Data.SqlClient.SqlCommand
> oConn.ConnectionString = sharedConnStr
> oCmd.Connection = oConn
> oConn.Open()
> strSQL = "SELECT " & Expression & " FROM " & Domain
> If Criteria.Trim().Length > 0 Then
> strSQL += Criteria
> End If
> oCmd.CommandText = strSQL
> retVal = oCmd.ExecuteScalar()
> End Function
> This is mainly pseudocode, as I don't have a clue how to do it or if
> it's possible. Any enlightenment will be appreciated.
>|||Thanks Bruce. I kinda gathered that from what I was reading, but I
hoped there might be a silver bullet. The guy I work with told me it
was possible and that he would send me the code (which he still hasn't
sent me). Anyway, you're right, if we need to do this, we'll go ahead
and go with the custom assembly.
I'm currently achieving what needs to be done with subreports. It's a
conversion project from Access to Reporting Services -- hence I aptly
named the function DLookup :)
John
Bruce L-C [MVP] wrote:
> I am pretty sure that even if you get this to work from your development
> environment it will fail when deployed unless you write a custom assembly.
> My advice is to stay away from doing this. Instead, have a stored procedure
> that you pass parameters to. Or use subreport (all depends on what you are
> trying to accomplish).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jdjewell" <fivejewells@.cox.net> wrote in message
> news:1169586090.910149.111150@.m58g2000cwm.googlegroups.com...
> > Hi all,
> >
> > Hopefully, this is a quick and easy one to answer. Is it possible to
> > execute an sql statement from custom code on the "Code" tab of a
> > Report's properties menu? I'm pretty sure I could do this by writing a
> > custom assembly and referencing it from the report, but what I want to
> > do is write a code snippet on the code tab, which expects three
> > parameters, and returns a scalar value (similar to this):
> >
> > Public Shared Function DLookup(ByVal Expression As String, ByVal
> > Domain As String, Optional ByVal Criteria As String = "") As String
> > Dim strSQL As String
> > Dim retVal As String
> > Dim oConn As System.Data.SqlClient.SqlConnection
> > Dim oCmd As System.Data.SqlClient.SqlCommand
> >
> > oConn.ConnectionString = sharedConnStr
> > oCmd.Connection = oConn
> > oConn.Open()
> >
> > strSQL = "SELECT " & Expression & " FROM " & Domain
> > If Criteria.Trim().Length > 0 Then
> > strSQL += Criteria
> > End If
> >
> > oCmd.CommandText = strSQL
> > retVal = oCmd.ExecuteScalar()
> > End Function
> >
> > This is mainly pseudocode, as I don't have a clue how to do it or if
> > it's possible. Any enlightenment will be appreciated.
> >