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
13  1288 
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)" 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
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" <no*****@nowhere.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl... 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
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.AppSett ings("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?
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" <no*****@nowhere.com> wrote in message
news:e9**************@TK2MSFTNGP14.phx.gbl... 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.AppSett ings("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?
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.
"darrel" <no*****@nowhere.com> wrote in message
news:e9**************@TK2MSFTNGP14.phx.gbl... 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.AppSett ings("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...
> 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.AppSett ings("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)
--
Kees de Winter
"darrel" <no*****@nowhere.com> wrote in message
news:eG****************@TK2MSFTNGP11.phx.gbl... 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.AppSett ings("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...
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" <no*****@nowhere.com> a écrit dans le message de news: e9**************@TK2MSFTNGP14.phx.gbl... 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.AppSett ings("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...
> 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
System.Data.SqlClient is specific to SQL Server (it uses the low level
protocol used by SQL Server).
See http://msdn.microsoft.com/library/de...tproviders.asp
for details...
--
Patrice
"darrel" <no*****@nowhere.com> a écrit dans le message de news: uD**************@TK2MSFTNGP11.phx.gbl... 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jim R |
last post by:
The following is a create that I was trying to run in SQL/2000 SQL
Analyser:
CREATE TABLE tblxyz as (Select * from tblPlayers);
tblPlayers has 2 rows in it.
I continue to get the error
...
|
by: Ross Morton |
last post by:
I'm writing a shellscript where I'm performing some SQL queries and
assigning the results to variables. The problem is that the query produces
a column heading, some underline characters and '1...
|
by: Yisroel Markov |
last post by:
Greetings,
I have this database I converted from Access 97 to Access 2000. Front
end on the PC, back end on the server. It works fine, except for one
thing: one of the reports is extremely slow...
|
by: amywolfie |
last post by:
Is there a 3rd party utility out there which can convert plain ol' SQL to JetSQL?
Thanks,
amy
===
|
by: VictorReinhart |
last post by:
Hi,
I am intersted in trying to reduce the cost of C# development, by
reducing the number of lines of code. In my opinion, as a business
developer, the biggest opportunity to reduce the number of...
|
by: vishal |
last post by:
I am new to sql and require some help on cursors?
what are they and how and why are they used for???
it will be kind enough if anyone helps me in this regards..
regards
vishal jain.
|
by: Ian Roddis |
last post by:
Hello,
I want to embed SQL type queries within an XML data record. The XML
looks something like this:
<DISPLAYPAGE>
<FIELD NAME="SERVER" TYPE="DROPDOWN">
<OPTION>1<OPTION>
<OPTION>2<OPTION>...
|
by: darrel |
last post by:
I've been struggling with getting paramaterized SQL to work for a few days.
I got a bit of help last week and seemed to heading in the right direction,
bu I keep getting a 'must declare...
|
by: =?Utf-8?B?S3VtYXI=?= |
last post by:
I am using granados telnet client for connecting to the telnet and get the
data from it. Every thing appears to be going smooth. But for some reason
when I try to write the byte data to a string or...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
