SQL: writing more concise paramaterized SQL

Discussion in 'ASP .Net' started by darrel, Mar 29, 2006.

  1. darrel

    darrel Guest

    I'm trying to get the hang of using parameterized SQL. I've gotten to work,
    but now some of my queries seem unecessarily long. For instance:

    strSQL = "IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts
    WHERE contactID = ? AND pageID = ?) INSERT INTO
    We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES (?, ?)"

    objCommand.Parameters.Add("@contactID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    contactsCount)
    objCommand.Parameters.Add("@pageID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    objCommand.Parameters.Add("@contactID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    contactsCount)
    objCommand.Parameters.Add("@pageID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")

    As you can see, I'm repeating the two values twice. Is there a less verbose
    way to pass those parameters?

    -Darrel
     
    darrel, Mar 29, 2006
    #1
    1. Advertising

  2. On Wed, 29 Mar 2006 13:54:31 -0600, darrel wrote:

    > As you can see, I'm repeating the two values twice. Is there a less verbose
    > way to pass those parameters?


    strSQL = "IF NOT EXISTS(SELECT * FROM e_Link_SiteMenus_To_DirectoryContacts
    WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
    We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    (@contactID, @pageID)"
     
    Erik Funkenbusch, Mar 29, 2006
    #2
    1. Advertising

  3. darrel

    darrel Guest


    > strSQL = "IF NOT EXISTS(SELECT * FROM
    > e_Link_SiteMenus_To_DirectoryContacts
    > WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
    > We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    > (@contactID, @pageID)"


    I've always seen this used in examples, but I've never been able to get the
    '@' syntax to work with MSSql. In fact, folks have said that the '?' is what
    I need to use.

    I'll give it a shot, though ;o)

    -Darrel
     
    darrel, Mar 29, 2006
    #3
  4. darrel

    David Wier Guest

    I've seen '?' used in OleDB, with Access - but I've always known and was
    originally taught, that with SQL Server 2000 and above, to use the '@' sign
    and that, whichever you're using, in the statement and the parameters,
    should definitely match

    David Wier
    http://aspnet101.com/
    http://aspexpress.com/
    MCP-VB6/SQL Server/MVP (ASP.Net)


    "darrel" <> wrote in message
    news:%...
    >
    >> strSQL = "IF NOT EXISTS(SELECT * FROM
    >> e_Link_SiteMenus_To_DirectoryContacts
    >> WHERE contactID = @contactID AND pageID = @pageID) INSERT INTO
    >> We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    >> (@contactID, @pageID)"

    >
    > I've always seen this used in examples, but I've never been able to get
    > the '@' syntax to work with MSSql. In fact, folks have said that the '?'
    > is what I need to use.
    >
    > I'll give it a shot, though ;o)
    >
    > -Darrel
    >
     
    David Wier, Mar 29, 2006
    #4
  5. darrel

    darrel Guest


    > I'll give it a shot, though ;o)


    And, sure enough, it doesn't work for me. I get this error:

    ------------------------------
    Must declare the variable '@contactID'. Must declare the variable
    '@contactID'.
    Microsoft OLE DB Provider for SQL Server
    IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts WHERE
    contactID = @contactID AND pageID = @pageID) INSERT INTO
    We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    (@contactID, @pageID)
    ------------------------------

    Full code:

    dim strSQL as string
    strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    (@contactID, @pageID)"
    Try
    Dim strConnect As String
    strConnect =
    System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    objConnect.Open()
    Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    objCommand.Parameters.Add("@contactID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    contactsCount)
    objCommand.Parameters.Add("@pageID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    objCommand.ExecuteNonQuery()
    objConnect.Close()
    catch...
     
    darrel, Mar 29, 2006
    #5
  6. darrel

    Elmo Watson Guest

    What's your exact error message?
    I'm curious at this point - I missed it before - why are you using OleDB
    instead of the native SQL client?

    David Wier
    http://aspnet101.com/
    http://aspexpress.com/
    MCP-VB6/SQL Server/MVP (ASP.Net)


    "darrel" <> wrote in message
    news:...
    >
    >> I'll give it a shot, though ;o)

    >
    > And, sure enough, it doesn't work for me. I get this error:
    >
    > ------------------------------
    > Must declare the variable '@contactID'. Must declare the variable
    > '@contactID'.
    > Microsoft OLE DB Provider for SQL Server
    > IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts WHERE
    > contactID = @contactID AND pageID = @pageID) INSERT INTO
    > We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    > (@contactID, @pageID)
    > ------------------------------
    >
    > Full code:
    >
    > dim strSQL as string
    > strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    > AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    > (@contactID, @pageID)"
    > Try
    > Dim strConnect As String
    > strConnect =
    > System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    > Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    > objConnect.Open()
    > Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    > Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    > objCommand.Parameters.Add("@contactID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    > contactsCount)
    > objCommand.Parameters.Add("@pageID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    > objCommand.ExecuteNonQuery()
    > objConnect.Close()
    > catch...
    >
     
    Elmo Watson, Mar 29, 2006
    #6
  7. darrel

    darrel Guest

    > What's your exact error message?

    What I wrote is the exact error message that I get in my browser. Is there
    another place to look for the error somewhere?

    > I'm curious at this point - I missed it before - why are you using OleDB
    > instead of the native SQL client?


    Umm...I don't know. It's just what we've always used here. Should I not be
    using it?

    -Darrel
     
    darrel, Mar 29, 2006
    #7
  8. Do you ever actually add that parameter? You need add the parameter, and
    provide a value for it - otherwise how could sql server know what to
    substitute for the variable?

    You should post the relevant code snippet you are using.

    "darrel" <> wrote in message
    news:...
    >
    >> I'll give it a shot, though ;o)

    >
    > And, sure enough, it doesn't work for me. I get this error:
    >
    > ------------------------------
    > Must declare the variable '@contactID'. Must declare the variable
    > '@contactID'.
    > Microsoft OLE DB Provider for SQL Server
    > IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts WHERE
    > contactID = @contactID AND pageID = @pageID) INSERT INTO
    > We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    > (@contactID, @pageID)
    > ------------------------------
    >
    > Full code:
    >
    > dim strSQL as string
    > strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    > AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    > (@contactID, @pageID)"
    > Try
    > Dim strConnect As String
    > strConnect =
    > System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    > Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    > objConnect.Open()
    > Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    > Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    > objCommand.Parameters.Add("@contactID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    > contactsCount)
    > objCommand.Parameters.Add("@pageID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    > objCommand.ExecuteNonQuery()
    > objConnect.Close()
    > catch...
    >
     
    Marina Levit [MVP], Mar 29, 2006
    #8
  9. darrel

    darrel Guest

    > You should post the relevant code snippet you are using.

    Here it is again:

    dim strSQL as string
    strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    (@contactID, @pageID)"
    Try
    Dim strConnect As String
    strConnect =
    System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    objConnect.Open()
    Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    objCommand.Parameters.Add("@contactID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    contactsCount)
    objCommand.Parameters.Add("@pageID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    objCommand.ExecuteNonQuery()
    objConnect.Close()
    catch...
     
    darrel, Mar 29, 2006
    #9
  10. Try removing the '@' from the objCommand.Parameters.Add statements, such as:

    objCommand.Parameters.Add("contactID",
    System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    contactsCount)

    --
    Kees de Winter

    "darrel" <> wrote in message
    news:eGL%...
    > > You should post the relevant code snippet you are using.

    >
    > Here it is again:
    >
    > dim strSQL as string
    > strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    > AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    > (@contactID, @pageID)"
    > Try
    > Dim strConnect As String
    > strConnect =
    > System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    > Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    > objConnect.Open()
    > Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    > Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    > objCommand.Parameters.Add("@contactID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    > contactsCount)
    > objCommand.Parameters.Add("@pageID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    > objCommand.ExecuteNonQuery()
    > objConnect.Close()
    > catch...
    >
    >
    >
     
    Kees de Winter, Mar 30, 2006
    #10
  11. darrel

    Patrice Guest

    This is because you are using the OleDb provider (not specific to SQL
    Server) which uses "?" as a place holder.

    If you don't do this on purpose you could use System.Data.SqlClient instead
    (that is specifically for use with SQL Server) and that does support the
    @Name syntax.

    --
    Patrice

    "darrel" <> a écrit dans le message de news:
    ...
    >
    >> I'll give it a shot, though ;o)

    >
    > And, sure enough, it doesn't work for me. I get this error:
    >
    > ------------------------------
    > Must declare the variable '@contactID'. Must declare the variable
    > '@contactID'.
    > Microsoft OLE DB Provider for SQL Server
    > IF NOT EXISTS(SELECT * FROM We_Link_SiteMenus_To_DirectoryContacts WHERE
    > contactID = @contactID AND pageID = @pageID) INSERT INTO
    > We_Link_SiteMenus_To_DirectoryContacts (contactID, pageID) VALUES
    > (@contactID, @pageID)
    > ------------------------------
    >
    > Full code:
    >
    > dim strSQL as string
    > strSQL = "IF NOT EXISTS(SELECT * FROM mytable WHERE contactID = @contactID
    > AND pageID = @pageID) INSERT INTO mytable (contactID, pageID) VALUES
    > (@contactID, @pageID)"
    > Try
    > Dim strConnect As String
    > strConnect =
    > System.Configuration.ConfigurationSettings.AppSettings("DBConn")
    > Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
    > objConnect.Open()
    > Dim objCommand As New System.Data.OleDb.OleDbCommand(strSQL, objConnect)
    > Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter
    > objCommand.Parameters.Add("@contactID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    > contactsCount)
    > objCommand.Parameters.Add("@pageID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request.QueryString("pageID")
    > objCommand.ExecuteNonQuery()
    > objConnect.Close()
    > catch...
    >
     
    Patrice, Mar 30, 2006
    #11
  12. darrel

    darrel Guest

    > This is because you are using the OleDb provider (not specific to SQL
    > Server) which uses "?" as a place holder.
    >
    > If you don't do this on purpose you could use System.Data.SqlClient
    > instead (that is specifically for use with SQL Server) and that does
    > support the @Name syntax.


    Ah! That explains it! So, is SqlClient for any SQL db or specifically for
    MSSQL?

    Thanks for that info!

    -Darrel
     
    darrel, Mar 30, 2006
    #12
  13. darrel

    Patrice Guest

    System.Data.SqlClient is specific to SQL Server (it uses the low level
    protocol used by SQL Server).

    See
    http://msdn.microsoft.com/library/d...y/en-us/cpguide/html/cpconadonetproviders.asp
    for details...

    --
    Patrice

    "darrel" <> a écrit dans le message de news:
    ...
    >> This is because you are using the OleDb provider (not specific to SQL
    >> Server) which uses "?" as a place holder.
    >>
    >> If you don't do this on purpose you could use System.Data.SqlClient
    >> instead (that is specifically for use with SQL Server) and that does
    >> support the @Name syntax.

    >
    > Ah! That explains it! So, is SqlClient for any SQL db or specifically for
    > MSSQL?
    >
    > Thanks for that info!
    >
    > -Darrel
    >
     
    Patrice, Mar 30, 2006
    #13
  14. darrel

    darrel Guest


    > Try removing the '@' from the objCommand.Parameters.Add statements, such
    > as:
    >
    > objCommand.Parameters.Add("contactID",
    > System.Data.OleDb.OleDbType.Numeric).Value = Request("hid_contactIDadd_" &
    > contactsCount)


    Nope. Still get the same error. I think Patrice nailed it...I'm just using
    the wrong method if I want to use named parameters.

    -Darrel
     
    darrel, Mar 30, 2006
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. darrel
    Replies:
    6
    Views:
    352
    Darren Kopp
    Feb 28, 2006
  2. Charles Krug

    File Paramaterized Abstract Factory

    Charles Krug, Jan 11, 2006, in forum: Python
    Replies:
    2
    Views:
    404
    Charles Krug
    Jan 11, 2006
  3. Balog Pal
    Replies:
    15
    Views:
    527
    Anand Hariharan
    Mar 23, 2009
  4. W. eWatson
    Replies:
    2
    Views:
    951
    W. eWatson
    Nov 23, 2009
  5. barjunk

    More concise code

    barjunk, Apr 26, 2007, in forum: Ruby
    Replies:
    1
    Views:
    124
    Alex Gutteridge
    Apr 26, 2007
Loading...

Share This Page