For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)
Parameters were idenfitied with a colon...
Where ModDate :MyDate
But, now that I am stwitching to .NET, the parameters are identified with @
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?
Thanks,
Jon
PS: Creating stored procedures on my customers' databases is not an option. 4 12441
You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:
private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
--
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries: http://www.miradyne.net
"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)
Parameters were idenfitied with a colon...
Where ModDate :MyDate
But, now that I am stwitching to .NET, the parameters are identified with
@
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?
Thanks,
Jon
PS: Creating stored procedures on my customers' databases is not an
option.
Thank you for your response. I know how to use parameters with SqlCommand,
but I also need to use local variables within the text of the query.
I easily used complex queries such as the following in my old programming
environment in the ADO control. It was simple because the parameter indicator
was a colon, and thus different from the local variable character, the at
sign.
In .NET I need to be able to use BOTH parameters AND local variables inside
the same query text (note, adding stored procedures to the databases of my
customers is not an option)...
Declare @D datetime, @D1 datetime, @D2 datetime
Select @D=Modified From ##Track Where TableName = 'DivisionMaster'
if @D is null
Select @D=Modified From ##Track Where TableName = 'ETPR_LastRun'
Select @D1=Modified From ##Track Where TableName = 'CustomerMaster'
if @D1 is null
Select @D1=Modified From ##Track Where TableName = 'ETPR_LastRun'
Select @D2=Modified From ##Track Where TableName = 'CustomerBillingProfile'
if @D2 is null
Select @D2=Modified From ##Track Where TableName = 'ETPR_LastRun'
Select <lots of fields>
From BigTable bg
<lots of joins>
Where bg.Something=:MyParameter
And (dm.Modified>@D or cm.Modified>@D1 or oa.Modified>@D2)
Hi Jon,
I understand you now. I couldn't find any specific documentation about this,
but I employed a good guess and tested it:
SqlCommand command;
SqlConnection connection;
SqlDataReader reader;
connection = new SqlConnection(Globals.WebContentConnectionString);
connection.Open();
command = connection.CreateCommand();
string s = @"DECLARE @UserName nvarchar(20)
SELECT @UserName = 'JoeBlow'
-- Insert statements for procedure here
SELECT Password, PasswordAnswer, IsLockedOut FROM Members
WHERE MemberName = @UserName";
command.CommandText = s;
reader = command.ExecuteReader();
if (reader.Read()) TextBox1.Text = reader.GetString(0);
reader.Close();
connection.Dispose();
command.Dispose();
Worked perfectly.
--
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries: http://www.miradyne.net
"Kevin Spencer" <un**********@nothinks.comwrote in message
news:ub**************@TK2MSFTNGP04.phx.gbl...
You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:
private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
--
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries: http://www.miradyne.net
"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
>For MS SQL Server... I am used to declaring local variables in my SQL queries... Declare @MyInt int, @MyChar varchar(33)
Parameters were idenfitied with a colon... Where ModDate :MyDate
But, now that I am stwitching to .NET, the parameters are identified with @ in SqlCommand, so how do I declare local variables in my queries (like in Query Analyzer)?
Thanks, Jon
PS: Creating stored procedures on my customers' databases is not an option.
Thank you very much.
Jon
"Kevin Spencer" wrote:
Hi Jon,
I understand you now. I couldn't find any specific documentation about this,
but I employed a good guess and tested it:
SqlCommand command;
SqlConnection connection;
SqlDataReader reader;
connection = new SqlConnection(Globals.WebContentConnectionString);
connection.Open();
command = connection.CreateCommand();
string s = @"DECLARE @UserName nvarchar(20)
SELECT @UserName = 'JoeBlow'
-- Insert statements for procedure here
SELECT Password, PasswordAnswer, IsLockedOut FROM Members
WHERE MemberName = @UserName";
command.CommandText = s;
reader = command.ExecuteReader();
if (reader.Read()) TextBox1.Text = reader.GetString(0);
reader.Close();
connection.Dispose();
command.Dispose();
Worked perfectly.
--
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries: http://www.miradyne.net
"Kevin Spencer" <un**********@nothinks.comwrote in message
news:ub**************@TK2MSFTNGP04.phx.gbl...
You use the SqlCommand.Parameters Collection with a parameterized query.
Here's an example:
private static void UpdateDemographics(Int32 customerID,
string demoXml, string connectionString)
{
// Update the demographics for a store, which is stored
// in an xml column.
string commandText = "UPDATE Sales.Store SET Demographics =
@demographics "
+ "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
--
HTH,
Kevin Spencer
Microsoft MVP
Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries: http://www.miradyne.net
"Jon Jacobs" <Jo*******@discussions.microsoft.comwrote in message
news:19**********************************@microsof t.com...
For MS SQL Server...
I am used to declaring local variables in my SQL queries...
Declare @MyInt int, @MyChar varchar(33)
Parameters were idenfitied with a colon...
Where ModDate :MyDate
But, now that I am stwitching to .NET, the parameters are identified with
@
in SqlCommand, so how do I declare local variables in my queries (like in
Query Analyzer)?
Thanks,
Jon
PS: Creating stored procedures on my customers' databases is not an
option.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft...
|
by: gary b |
last post by:
Hello
When I use a PreparedStatement (in jdbc) with the following query:
SELECT store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL
AND type = ?
ORDER BY group_name
|
by: deko |
last post by:
I'm trying to open a Recordset based on a parameterized query. I'm kind of
new to parameterized queries, so I'm sure I'm missing something simple.
Set qdfs = db.QueryDefs
Set qdf =...
|
by: deko |
last post by:
Is it possible to build a parameterized query from another parameterized
query?
I've tried two variations of this and can't seem to get it to work (using
DAO). Any suggestions welcome!
I...
|
by: Dirk Vervecken |
last post by:
Hi i've got an application in dotnet that uses
queries from an Access database.
Now most of the common select statements return the desired data,
however, one the queries requires a parameter....
|
by: anony |
last post by:
Hello,
I can't figure out why my parameterized query from an ASP.NET page is
dropping "special" characters such as accented quotes & apostrophes, the
registered trademark symbol, etc. These...
|
by: Roy |
last post by:
Hey all,
Here's a small VB codeblock that connects to a database and uses 2 SQL
queries then forms a relation for a master/detail view on the aspx side:
Private Sub Binddata(ByVal name As...
|
by: weird0 |
last post by:
i write query in this manner by contatenting a string:
cmd.CommandText = "SELECT acc_name FROM Account_Information
where user_id='" + User_Id + "'";
Is there any other way to do it....
...
|
by: dipalichavan82 |
last post by:
i came across a article, where it was mentioned if we want a dynamic querry to fire then use parameterized querry
e.g.
string inputcity=textbox.text;
SqlCommand cmd = new SqlCommand("select * from...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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: 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...
| |