none
How to call function with many parameter using datatable? RRS feed

  • Question

  • Hello everybody !

    I want to try call function Getdata with many parameter in asp.net but it errors :

    I have file CONNECT.VB

     Public Shared Function GETDATA(ByVal PROC As String, ByVal PARAMETER As SqlParameter()) As DataTable
    
            Dim DT As New DataTable
            Try
                If OPENCONNECT() Then
                    Dim CMD As SqlCommand=CNN.CreateCommand()
                    CMD.CommandType = CommandType.StoredProcedure
                    If PARAMETER IsNot Nothing Then
                        CMD.Parameters.AddRange(PARAMETER)
                    End If
                    Dim DA As New SqlDataAdapter(CMD)
                    DA.Fill(DT)
                End If
                Return DT
            Catch ex As Exception
                Return DT
            Finally
                CLOSECONNECT()
            End Try
    
        End Function

    and procedure with two parameter : @username + @userpass

    I try to call Function GEATDATA in file Login.aspx.vb

    Imports System.Data.SqlClient
    Imports System.Configuration.ConfigurationManager
    Imports CONNECT
    Imports System.Data
    Partial Class LoginQL
        Inherits System.Web.UI.Page
    
      Protected Sub cmdLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
    
      Dim tablelogin As New DataTable = GETDATA("sp_QL_UserCheckPassword", PARAMETER:=("@username",txtusername.Text),PARAMETER:=("@userpass",txtuserpass.Text))
    
    if tablelogin.row(0).items("errcode")=0 then
    
    respose.write("Login success")
    
    else
    
    respose.write("Login failed")
      End Sub
    End Class

    It`s not work . Can you tell me why not ?

    Thank you viewing .

    Saturday, March 24, 2012 11:06 AM

Answers

  • Example : Procedure sp_login with two argument : @user + @pass

    Procedure sp_add with three argument : @user + @pass + @confim

    I'm not sure I understand what you're saying, but I don't think you want to go that route. You need to pass one object, but not a concatenated string of your parameters. Not a good idea. Use a List(Of T), an ArrayList or a Dictionary ... but don't concatenate everything into one string. Not a good idea!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by headshot9x9 Monday, March 26, 2012 3:58 PM
    Monday, March 26, 2012 2:26 PM

All replies

  • What exactly is not working?

    Miha Markic [MVP C#] http://blog.rthand.com

    Saturday, March 24, 2012 4:47 PM
  • Try using a SqlParameterCollection instead of SqlParameter. Create your collection in the calling program and pass the entire collection to your function.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, March 24, 2012 4:57 PM
  • Try using a SqlParameterCollection instead of SqlParameter. Create your collection in the calling program and pass the entire collection to your function.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

        Hi BonnieB . It`s same :

      Public Shared Function GETDATA(ByVal PROC As String, Optional ByVal PARAMETER As Object = Nothing, Optional ByVal value As Object = Nothing) As DataTable
    
            Dim DT As New DataTable
            Dim index As Integer
            Try
                If OpenConnect() Then
                    Dim CMD As SqlCommand = cnn.CreateCommand()
                    CMD.CommandType = CommandType.StoredProcedure
    
                    If Not (PARAMETER Is Nothing) Then
                        For index = 0 To (PARAMETER.Length - 1)
                            If value(index) = Nothing Then
                                value(index) = DBNull.Value
                            End If
                            CMD.Parameters.AddWithValue(PARAMETER(index), value(index))
                        Next
                    End If
    
                    Dim DA As New SqlDataAdapter(CMD)
                    DA.Fill(DT)
                End If
                Return DT
            Catch ex As Exception
                Return DT
            Finally
                CloseConnect()
            End Try
    
        End Function

    I`ll call function : GETDATA("SP_checkpass","@username","@userpass",txtusername.text,txtuserpass.text) => But it`s errors

    I think I try write function GETDATA with parameter is value

    Public Shared Function GETDATA(ByVal PROC As String, Optional ByVal value As Object = Nothing) As DataTable

    I must to check any parameter in Procedure , and call it , I `ll use

    GETDATA("SP_checkpass",txtusername.text,txtuserpass.text)  (It`s easy :D :D)

    and use itforother case : Example GETDATA("name_procedure",parameter 1,parameter 2, etc...)









    • Edited by headshot9x9 Sunday, March 25, 2012 10:29 AM
    Sunday, March 25, 2012 10:27 AM
  • You've totally missed my point. However, on second thought, I think passing a List(Of SqlParameters) would be better than passing the SqlParameterCollection. Notice that I've changed your original function to pass this List(Of SqlParameters) to it:

    Public Shared Function GETDATA(ByVal PROC As String, Optional ByVal PARAMETER As List(Of SqlParameter) = Nothing) As DataTable Dim DT As New DataTable Dim index As Integer Try If OpenConnect() Then Dim CMD As SqlCommand = cnn.CreateCommand() CMD.CommandType = CommandType.StoredProcedure If Not (PARAMETER Is Nothing) Then CMD.Parameters.AddRange(PARAMETER.ToArray()) End If Dim DA As New SqlDataAdapter(CMD) DA.Fill(DT) End If Return DT Catch ex As Exception Return DT Finally CloseConnect() End Try

     

     

     

    Then your Login form would contain this code:

    Protected Sub cmdLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click Dim tablelogin As New DataTable Dim Parms As List(Of SqlParameter) = new List(Of SqlParameter) Parms.Add(new SqlParameter("@username",txtusername.Text)) Parms.Add(new SqlParameter("@userpass",txtuserpass.Text)) tablelogin = GETDATA("sp_QL_UserCheckPassword", Parms)



    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, March 25, 2012 3:25 PM
  • it is agood idea
    I also tried a new way:It will auto-check function argument of Procedure,at the function call we simply pass in the variables 
    ( in the testing phase):D :D . I am researching

    Example : Procedure sp_login with two argument : @user + @pass

    Procedure sp_add with three argument : @user + @pass + @confim

      Public Shared Function GETDATA(ByVal PROC As String, Optional ByVal value As Object = Nothing) As DataTable
     'code in here
    End Function
    
    
    'And call it
    
    GETDATA("sp_login",txtuser.Text,txtpass.Text)
    GETDATA("sp_add",txtuser.Text,txtnewpass.Text,txtconfirmpass.Text)

    Very fun if your partnership . 

    Dear BonnieB




    Monday, March 26, 2012 7:12 AM
  • Hello everybody !

    I want to try call function Getdata with many parameter in asp.net but it errors :


    What errors, there can be 1000000 reasons, however not to see direct from your code.

    Most obvious your connection is wrong because that is the only statement which is always done even if there is an error.


    Success
    Cor

    Monday, March 26, 2012 8:50 AM
  • Example : Procedure sp_login with two argument : @user + @pass

    Procedure sp_add with three argument : @user + @pass + @confim

    I'm not sure I understand what you're saying, but I don't think you want to go that route. You need to pass one object, but not a concatenated string of your parameters. Not a good idea. Use a List(Of T), an ArrayList or a Dictionary ... but don't concatenate everything into one string. Not a good idea!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by headshot9x9 Monday, March 26, 2012 3:58 PM
    Monday, March 26, 2012 2:26 PM
  • Dear BonnieB

    I would consider it .

    Thank for your answer .

    Monday, March 26, 2012 3:57 PM