PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2019 cmd.Parameters.AddWithValue Not Working-VBForums
Results 1 to 7 of 7

Thread: cmd.Parameters.AddWithValue Not Working

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    4

    Question cmd.Parameters.AddWithValue Not Working

    Hello... I have VBA experience but I am new to VB.NET. I found code online that I need in order to filter gridview based on a variable. This code was for SQLClient with a stored procedure so I converted to OleDB for an Access MDB. So far so good. But I cannot get cmd.Parameters.AddWithValue to work. I reduced the code to the simplest form:
    Code:
            Dim ConnectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("App_Data\Northwind.mdb")
            Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)
            conn.Open()
            Dim cmd As New OleDbCommand()
            cmd.CommandType = CommandType.Text
            Dim sda As OleDbDataAdapter = New OleDbDataAdapter()
            Dim dt As New DataTable() 
            Dim SQL2 As String = "Select * From Customers WHERE ContactName = @ContactName"
            If ViewState("ContactName") IsNot Nothing AndAlso ViewState("ContactName").ToString() <> "0" Then
                cmd.Parameters.AddWithValue("@ContactName", ViewState("ContactName").ToString())
            End If
            sda.SelectCommand = New OleDbCommand(SQL2, conn)
            cmd.Connection = conn
            sda.Fill(dt)
    I also tried: Dim SQL2 As String = "Select * From Customers WHERE ContactName = ?" and this did not work, It errors out asking for a variable. It is reading this string literally and not inserting the parameter.

    so I tried the VBA way and it did work:

    Dim SQL2 As String = "Select * From Customers WHERE ContactName = '" & ViewState("ContactName").ToString() & "';"

    Of course, this defeats the purpose of cmd.Parameter. I read elsewhere that OleDB can work with parameters so long as the order is correct and either ? or @Variable should work.

    Can you assist? Thx.
    Last edited by Shaggy Hiker; Jun 6th, 2019 at 09:31 AM. Reason: Added CODE tags.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,394

    Re: cmd.Parameters.AddWithValue Not Working

    Please format code snippets appropriately.
    Code:
            Dim ConnectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("App_Data\Northwind.mdb")
            Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)
            conn.Open()
            Dim cmd As New OleDbCommand()
            cmd.CommandType = CommandType.Text
            Dim sda As OleDbDataAdapter = New OleDbDataAdapter()
            Dim dt As New DataTable() 
            Dim SQL2 As String = "Select * From Customers WHERE ContactName = @ContactName"
            If ViewState("ContactName") IsNot Nothing AndAlso ViewState("ContactName").ToString() <> "0" Then
                cmd.Parameters.AddWithValue("@ContactName", ViewState("ContactName").ToString())
            End If
            sda.SelectCommand = New OleDbCommand(SQL2, conn)
            cmd.Connection = conn
            sda.Fill(dt)
    Code:
    Dim SQL2 As String = "Select * From Customers WHERE ContactName = ?"
    Code:
    Dim SQL2 As String = "Select * From Customers WHERE ContactName = '" & ViewState("ContactName").ToString() & "';"
    Last edited by jmcilhinney; Jun 6th, 2019 at 06:58 AM.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,394

    Re: cmd.Parameters.AddWithValue Not Working

    You need to add the parameter to the actual command you execute. Take another look at your code:
    Code:
            Dim ConnectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("App_Data\Northwind.mdb")
            Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)
            conn.Open()
            Dim cmd As New OleDbCommand()
            cmd.CommandType = CommandType.Text
            Dim sda As OleDbDataAdapter = New OleDbDataAdapter()
            Dim dt As New DataTable() 
            Dim SQL2 As String = "Select * From Customers WHERE ContactName = @ContactName"
            If ViewState("ContactName") IsNot Nothing AndAlso ViewState("ContactName").ToString() <> "0" Then
                cmd.Parameters.AddWithValue("@ContactName", ViewState("ContactName").ToString())
            End If
            sda.SelectCommand = New OleDbCommand(SQL2, conn)
            cmd.Connection = conn
            sda.Fill(dt)
    You add the parameter to one command and then go and create a completely separate command and associate that with the data adapter.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    4

    Re: cmd.Parameters.AddWithValue Not Working

    Great, okay thank you. I will revise this.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,394

    Re: cmd.Parameters.AddWithValue Not Working

    In case you're still having issues, you need to either create the command and then create the data adapter using that, e.g.
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Using connection As New OleDbConnection("connection string here"),
    4.       command As New OleDbCommand("SQL code here", connection),
    5.       adapter As New OleDbDataAdapter(command)
    6.     command.Parameters.Add("@Param", OleDbType.VarChar, 50).Value = "value here"
    7.  
    8.     adapter.Fill(table)
    9. End Using
    10.  
    11. 'Use table here.
    or else create the adapter and get the command from that, e.g.
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Using connection As New OleDbConnection("connection string here"),
    4.       adapter As New OleDbDataAdapter("SQL code here", connection)
    5.     adapter.SelectCommand.Parameters.Add("@Param", OleDbType.VarChar, 50).Value = "value here"
    6.  
    7.     adapter.Fill(table)
    8. End Using
    9.  
    10. 'Use table here.
    Notice a few features here, besides adding the parameter to the correct command. This code creates the objects with Using statements. You should always use Using statements with ADO.NET objects and anything else that supports disposal and is used only within a brief scope. For one thing, creating an ADO.NET connection with a Using statement guarantees that the connection will be closed at the end of the block.

    Note that this code also doesn't explicitly open the connection. There's no need to do so if you're just calling Fill or Update on a data adapter. It will open the connection automatically if it's not already open and close it again afterwards. If the connection was already open, it will leave it open. For that reason, you would only open and close it yourself if you need to keep it open to perform multiple operations.

    This code uses Add rather than AddWithValue. AdWithValue is fine in many circumstances but it requires the type of the parameter to be inferred and sometimes the default inferred type is not correct and can cause problems. If you always specify the type and, for variable-width fields, the size as well, you can always be sure that the parameter type will be compatible with your database.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    4

    Re: cmd.Parameters.AddWithValue Not Working

    This is a great help to me... Again I'm new to VB.NET coming from VBA so I need to refine my coding... Thanks again. I will apply these changes shortly.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    4

    Thumbs up Re: cmd.Parameters.AddWithValue Not Working

    Great, it worked.... I had to tweak for null value when the grid opened for the first time but all functions as it should.. Thanks

    Code:
    Dim dt As New DataTable
            Using connection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("App_Data\Northwind.mdb")),
            Command As New OleDbCommand("Select * From Customers WHERE (ContactName = @ContactName or @ContactName IS NULL)", connection),
            Adapter As New OleDbDataAdapter(Command)
                If ViewState("ContactName") IsNot Nothing AndAlso ViewState("ContactName").ToString() <> "0" Then
                    Command.Parameters.Add("@ContactName", OleDbType.VarChar, 50).Value = ViewState("ContactName").ToString()
                Else
                    Command.Parameters.Add("@ContactName", DBNull.Value)
                End If
                Adapter.Fill(dt)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width