EXECUTE SCALAR WITH PARAMETER

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.
LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
What happens?
0
emi_sastraAuthor Commented:
Always return nothing.

Thank you.
0
Dale BurrellDirectorCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

emi_sastraAuthor Commented:
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
Dale BurrellDirectorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
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
Dale BurrellDirectorCommented:
In a web app I would store it in the connection strings section of web.config.
0
emi_sastraAuthor Commented:
Yes, and just use it ?

Thank you.
0
Dale BurrellDirectorCommented:
Yip
0
emi_sastraAuthor Commented:
Would you please provide simple sample of database access library ?

Thank you.
0
Dale BurrellDirectorCommented:
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
emi_sastraAuthor Commented:
Ok. Is there any link that is discuss about it ?

Thank you.
0
Dale BurrellDirectorCommented:
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
emi_sastraAuthor Commented:
Ok then.

Thank you very much for your help.
0
Dale BurrellDirectorCommented:
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
emi_sastraAuthor Commented:
It is great.

Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.