dcsimg
Results 1 to 10 of 10

Thread: Help needed about adding multiple parametes to sql command

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Help needed about adding multiple parametes to sql command

    Hello, I'm trying to check for some values whether they exist in an mdb database table using vb.net 2013. My code so far:

    Code:
    Dim Command As New OleDb.OleDbCommand
    If Not Connection.State = ConnectionState.Open Then
    	Connection.Open()
    End If
    
    Dim dbStr As String = "SELECT * FROM products_tbl WHERE productName = ?"
    'set that as the commandtext of your command object.
    Command = New OleDbCommand(dbStr, Connection)
    
    'then use 
    Command.Parameters.AddWithValue("ProdName", s)
    'To add the value to the parameters collection
    
    Dim dr2 As OleDbDataReader
    dr2 = Command.ExecuteReader()
    What I want to do is adding some column strings to the command, for example:
    productName, sk, list, special, monetaryUnits_id, specialTl, vat_id, stock

  2. #2
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,230

    Re: Help needed about adding multiple parametes to sql command

    Hi,

    In principle, all that code looks fine, so what is not happening that is supposed to be happening? In other words, what are you actually trying to achieve?

    If you want to query different fields with different values then it is up to you to code the variations that you need but until you explain that in detail then it is a bit difficult for us to help you further.

    Cheers,

    Ian

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,569

    Re: Help needed about adding multiple parametes to sql command

    You will need to use the And/Or SQL operators. Take a look at this website for more details: http://www.w3schools.com/sql/sql_and_or.asp

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Help needed about adding multiple parametes to sql command

    Thanks for the reply. It was difficult to explain my problem because my English isn't so good and my knowledge about terms of vb.net and sql is limited, this confuses me.

    My code is working fine. I want to add multiple column names to WHERE clause. I have this line:
    Code:
    Dim dbStr As String = "SELECT * FROM products_tbl WHERE productName = ?"
    For example I didn't try but will this work?
    Code:
    Dim dbStr As String = "SELECT * FROM products_tbl WHERE productName = ? productName = ? sk = ? list = ? special = ? monetaryUnits_id = ? specialTl = ? vat_id = ? stock = ?"

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

    Re: Help needed about adding multiple parametes to sql command

    And I quote:
    Quote Originally Posted by dday
    You will need to use the And/Or SQL operators.
    do you want it to search where ANY of those match (use OR) or where the ALL need to match (use AND)?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Help needed about adding multiple parametes to sql command

    I want it to search where ANY of those match.

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

    Re: Help needed about adding multiple parametes to sql command

    Then you need to use OR...

    select from atable where this=that or another=something or anotherfield=anothervalue


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Help needed about adding multiple parametes to sql command

    As for the question asked in the topic's title... I assume since you are using ? in the query as the place-holder for the parameter, that you are working with an Access database. If so, then multiple parameters are set up the same way as the first was. The only thing to be careful about is that the parameters have to be set up in the same order as they appear in the query. Thus, for example:
    Code:
    Dim strQuery As String = "SELECT * FROM Users WHERE UserName = ? AND PassWord = ?"
    
    ' The UserName parameter comes first, so set that up first followed by the PassWord parameter
    Command = New OleDbCommand(strQuery, Connection)
    Command.Parameters.AddWithValue("UserName", txtUserName.Text)
    Command.Parameters.AddWithValue("Password", txtPassword.Text)

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    Re: Help needed about adding multiple parametes to sql command

    Quote Originally Posted by Pyth007 View Post
    As for the question asked in the topic's title... I assume since you are using ? in the query as the place-holder for the parameter, that you are working with an Access database. If so, then multiple parameters are set up the same way as the first was. The only thing to be careful about is that the parameters have to be set up in the same order as they appear in the query. Thus, for example:
    Code:
    Dim strQuery As String = "SELECT * FROM Users WHERE UserName = ? AND PassWord = ?"
    
    ' The UserName parameter comes first, so set that up first followed by the PassWord parameter
    Command = New OleDbCommand(strQuery, Connection)
    Command.Parameters.AddWithValue("UserName", txtUserName.Text)
    Command.Parameters.AddWithValue("Password", txtPassword.Text)
    This will probably work. Thanks for the helps.

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,569

    Re: Help needed about adding multiple parametes to sql command

    This will probably work. Thanks for the helps.
    Out of curiosity, did you read the documentation on the And/Or SQL operators that I linked to earlier?

Tags for this Thread

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