EXECUTE SCALAR WITH PARAMETER

What happens?

Always return nothing.

Thank you.

I can't see anything obviously wrong... I'd step through it in the debugger and confirm that the right values are being passed.

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.

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.

In a web app I would store it in the connection strings section of web.config.

Yes, and just use it ?

Thank you.

Yip

Would you please provide simple sample of database access library ?

Thank you.

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

Ok. Is there any link that is discuss about it ?

Thank you.

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.

Ok then.

Thank you very much for your help.

This is pretty useful overview of data access - its old now, but still mostly relevant.

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

It is great.

Thank you.