SQL: writing more concise paramaterized SQL

  • Thread starter Thread starter darrel
  • Start date Start date
D

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
 
As you can see, I'm repeating the two values twice. Is there a less verbose
way to pass those parameters?
Click to expand...

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)"
 
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)"
Click to expand...

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
 
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)
 
I'll give it a shot, though ;o)
Click to expand...

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...
 
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?
Click to expand...

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

-Darrel
 
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.
 
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...
 
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)
 
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.
 
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.
Click to expand...

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

Thanks for that info!

-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)
Click to expand...

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Quick Parameterized SQL question (inserting text) 3
ASP and HTML 6
ASP & Html 1
Asp.net Important Topics. 0
Download the JAVA , .NET and SQL Server interview with answers 2

Back
Top