[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

EXECUTE SCALAR WITH PARAMETER

Posted on 2011-10-09
16
Medium Priority
?
205 Views
Last Modified: 2012-06-21
Hi All,

I have below code :

 Public Function Get_Member_Name(ByVal strMemberId As String) As String

        Try

            Dim dic As New Dictionary(Of String, String)
            dic.Add("@MemberId", strMemberId)

            sbdSQLCommand.Length = 0

            sbdSQLCommand.AppendLine("SELECT")
            sbdSQLCommand.AppendLine("ISNULL(FullName, '') AS FullName")
            sbdSQLCommand.AppendLine("FROM TMMEMBERDATA ")
            sbdSQLCommand.AppendLine("WHERE MemberId = @MemberId")

            Using conApp As SqlConnection = New SqlConnection(strSQLCompanyConnection)
                conApp.Open()

                Dim cmdSqlCommand As New SqlCommand

                cmdSqlCommand = ERV_Global_Web.Query_Data(dic)

                cmdSqlCommand.Connection = conApp
                cmdSqlCommand.CommandText = sbdSQLCommand.ToString
                cmdSqlCommand.CommandType = CommandType.Text

                Get_Member_Name = cmdSqlCommand.ExecuteScalar

                If Get_Member_Name Is Nothing Then Get_Member_Name = ""

                Return Get_Member_Name
            End Using

        Catch ex As Exception

        End Try

        Return ""

    End Function

   Public Shared Function Query_Data(ByVal dctOfParameterVariables As Dictionary(Of String, String)) As SqlCommand

        Dim cmdSqlCommand As New SqlCommand

        Dim pair As KeyValuePair(Of String, String)
        For Each pair In dctOfParameterVariables
            cmdSqlCommand.Parameters.AddWithValue(pair.Key, _
                                                  pair.Value)
        Next

        Return cmdSqlCommand

    End Function

What's wrong with my code ?

Thank you.
0
Comment
Question by:emi_sastra
  • 8
  • 8
16 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36938069
What happens?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36938250
Always return nothing.

Thank you.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36938267
I can't see anything obviously wrong... I'd step through it in the debugger and confirm that the right values are being passed.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:emi_sastra
ID: 36938346
I am sorry, it works.

I have simplified into :


  Public Function Get_Member_Name(ByVal strMemberId As String, _
                                    Optional ByVal blnMemberName As Boolean = False) As String

        Try

            sbdSQLCommand.Length = 0

            sbdSQLCommand.AppendLine("SELECT")

            If blnMemberName Then
            Else
                sbdSQLCommand.AppendLine("ISNULL(FullName, '') AS FullName")
            End If

            sbdSQLCommand.AppendLine("FROM TMMEMBERDATA ")
            sbdSQLCommand.AppendLine("WHERE MemberId = @MemberId")

            Dim dic As New Dictionary(Of String, Object)
            dic.Add("@MemberId", strMemberId)

            Return ERV_Global_Web.Execute_Scalar(sbdSQLCommand.ToString, dic, strSQLCompanyConnection)

        Catch ex As Exception

        End Try

        Return ""

    End Function

  Public Shared Function Execute_Scalar(ByVal strSQLCommand As String, _
                                          ByVal dctOfParameterVariables As Dictionary(Of String, Object), _
                                          ByVal strConnection As String, _
                                          Optional ByVal strSQLType As String = "S") As Object

        Try

            Using conApp As New SqlConnection(strConnection)

                Dim cmdSqlCommand As New SqlCommand

                cmdSqlCommand = ERV_Global_Web.Query_Data(dctOfParameterVariables)

                cmdSqlCommand.Connection = conApp
                cmdSqlCommand.CommandText = strSQLCommand
                cmdSqlCommand.CommandType = CommandType.Text

                conApp.Open()

                Execute_Scalar = cmdSqlCommand.ExecuteScalar

                If Execute_Scalar Is Nothing Then
                    Return Return_Empty_Value(strSQLType)
                End If

            End Using

        Catch ex As Exception

        End Try

        Return ""

    End Function

 Public Shared Function Return_Empty_Value(ByVal strSQLType) As Object

        Select Case strSQLType
            Case "S"
                Return ""
            Case "D"
                Return System.DBNull.Value
            Case "N"
                Return 0
        End Select

        Return Nothing

    End Function



Could it be made more robust ?

Thank you.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 2000 total points
ID: 36939607
That seems a reasonable way to code it. Personally I have a database access library which handles all the common stuff such as getting a connection, creating the command, and all I do is pass my query to my library with any parameters. That way I know I'm using well testing database access code every time, and I don't have to mess around with all that detail every time.

One thing I would suggest is creating some code to handle the error situation, log it to a file and send yourself an email would be my recommendation there. Else you'll have trouble debugging in future. Again I'd add that to the library and then you will automatically know about any errors.

But as best I can tell there is nothing wrong with your code the way it is.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940104
In web app.
Regarding the connection string, should we always call a function to get it every time a query is going to counduct?

Thank you.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36940118
In a web app I would store it in the connection strings section of web.config.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940126
Yes, and just use it ?

Thank you.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36940128
Yip
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940235
Would you please provide simple sample of database access library ?

Thank you.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36940269
Unfortunately I can't. But roughly just take what you have and make it generic. A query pretty much needs 2 things, the SQL and the parameters. So create a static class library with the methods you need to access the database e.g. GetValue(String Sql, Dictionary Params), ExecuteSql(String Sql, Dictionary Params), GetRecordSet(String Sql, Dictionary Params).

HTH
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940305
Ok. Is there any link that is discuss about it ?

Thank you.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36940324
I don't know of any, but seriously its hardly more than you've done, just generic so you can use the same code from 2 pages.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940358
Ok then.

Thank you very much for your help.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 36940391
This is pretty useful overview of data access - its old now, but still mostly relevant.

http://msdn.microsoft.com/en-us/library/ee817654.aspx
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36940419
It is great.

Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question