SQL: writing more concise paramaterized SQL

[darrel]

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

 

[Erik Funkenbusch]

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)"

 

[darrel]

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]

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]

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]

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]

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

 

[Marina Levit [MVP]]

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]

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]

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)

 

[Patrice]

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.

 

[darrel]

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]

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...

 

[darrel]

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