|
If you don't like my previous answer then you might like to elaborate on your problem.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
-- modified at 12:09 Friday 10th March, 2006
|
|
|
|
|
Thank Colin Angus Mackay;P very much, i have gotten desired result according your answer.
|
|
|
|
|
I'm using MySQL on an ASP.net project, using the official .NET data provider to connect to my MySQL database.
What I want to do is get the final SQL command that my MySqlCommand object uses after I've set it's parameters and asked it to execute the query.
MySqlCommand.CommandText returns my original SQL with my parameter place holders, but I want to see the actual SQL used.
For example, I create my SQL queries by the following:
string _SQL = "SELECT name FROM users WHERE department = ?department; "; <br />
<br />
MySqlCommand DbCom = new MySqlCommand(_SQL, DbCon);<br />
DbCom.Parameters.Add("?department", MySqlDbType.VarChar, 25);<br />
DbCom.Parameters["?department"].Value = "engineering";<br />
MySqlDataReader DbRDR = DbCom.ExecuteReader(); <br />
<br />
[SNIP]... CODE HERE ...[/SNIP]<br />
<br />
DbRDR.Close(); <br />
DbRDR = null; <br />
DbCom.Dispose();
If I use "DbCom.CommandText" it will return "SELECT name FROM users WHERE department = ?department; ", but I want it to return the actual query which would be "SELECT name FROM users WHERE department = `engineering`; "
Can anyone please enlighten me on how to do this?
Thanks
|
|
|
|
|
The idea of using parameters in a command is to separate the actual command from the data used to modify the command. That is, it splits the parameters away from the actual command as a way of reducing the possibility of a SQL Injection Attack. It sends the two pieces of information to the database as command + parameters rather than concatenating the parameters into the command before being sent to the database.
Distinctive wrote: it will return "SELECT name FROM users WHERE department = ?department; "
That looks correct to me. That is your command text.
Distinctive wrote: but I want it to return the actual query which would be "SELECT name FROM users WHERE department = `engineering`; "
Look at it this way. If you have a line of C# code like this:
Console.WriteLine("Hello my name is {0}", name);Would you expect the compiler to be able to tell you what the "actual command" is? It can't until runtime, because until runtime name has an unknown value.
[DISCLAIMER: I've not used MySQL and the above is based on how other database data providers work in .NET and any provider produced by a third party may not operate in the same way.]
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Thanks for your help, that makes sense to me, I didn't realise that it worked that way.
|
|
|
|
|
Hello. I am making a stored procedure with the following SQL statement:
Update Table1 set Field1='X'
Where Field2 = 'N'
I want the StoredProcedure to return Field3 from the updated record. Is it possible to do this?
Thanks.
|
|
|
|
|
sure
right after your update, put the select statement
select field3 from table1 where field2 = 'N'
and execute your stored procedure with either
cmd.ExecuteReader() / cmd.ExecuteScalar()
daniero
|
|
|
|
|
Actually that's not exactly what I wanted. The example I gave was not complete. Here's what I want to do:
- Select @Res = max(Y) From table1 where X=0
- Update table1 set X=1 where Y = @Res
I want to get the max record where X=0 and then update that record. The problem is that there may be two processes executing this stored procedure at the same time.
Thus, they may execute the 'select' part at the same time, get the same 'max(Y)' and then update the same record. That's what I wnat to avoid. I need to assure that between the 'select' and the 'update' there is no process accessing the same record in the table. This is a concurrency problem.
|
|
|
|
|
Hi ¡
Im trying to retrieve data from a SQL table in Visual Web Developer 2005. Depending of the value from a column a GridView displays the entire register of the SQL Table Row, but the value of that column should be generated randomly...
Any Idea?
THANX ¡¡
|
|
|
|
|
Hello every body i am sorry for this question but i am beginer in web developing and i don't get it how to edit dada in gridview in net 2.0
please if some body can help me.
good bye
best regars
down i apply the text
DataSet ds = new DataSet();
ds = SqlFunction.EditBook();
if (ds != null)
{
//
DataView dv = new DataView();
dv.Table = ds.Tables["Table"];
// string str = ds.Tables[0].TableName.ToString();
// HttpContext.Current.Trace.Write("Table name=",str);
/* DataGrid datagrid1 = new DataGrid();
datagrid1.DataSource = dv;
datagrid1.DataBind(); */
GridView1.DataSource = dv;
GridView1.DataBind();
}
else
{
HttpContext.Current.Trace.Write("Error in EditBook");
}
|
|
|
|
|
Anyone have quick explanation or link to info about what these directories are all about... what are they for?
I am not having any particular problems, but I am wondering what they are for.
|
|
|
|
|
The '80' directory contains client tools and any components shared between instances of SQL Server 2000 (which is version 8.0). The '90' directory similarly contains client tools and components shared between instances of SQL Server 2005 (version 9.0).
Directories named MSSQL.n and MSSQL$name contain the program files and (by default) data files for a particular instance of SQL Server. Different instances can be at different service pack levels, which is why they've got separate copies of a lot of the program files.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi all,
How to use Linked servers (sp_addlinkedserver)
thanks
|
|
|
|
|
Look at Books online. There is a full explanation there. You can also go to MSDN and search for linked servers
|
|
|
|
|
|
Hi!
I have a SP that looks like the following:
Create Procedure dbo.Name
(
@FirstName varchar(50)
)
As
Declare @FirstNameID int
Insert Into Table1
(
FirstName
)
Values
(
@FirstName
)
Select @FirstNameID = Scope_Identity()
GO
The problem I am having is this procedure also needs to check, if the FirstName already exists then the entry being made should use that ID otherwise it needs to add a new FirstName.
The ID is Auto incrimented.
If you need more detail please email me so that I can give more detail...
Thanks Guys!!!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Something like this should get you started:
CREATE PROCEDURE dbo.Name
(
@FirstName VARCHAR(50)
)
AS
DECLARE @FirstNameID INT
SELECT
@FirstNameID = FirstNameID
FROM
Table1
WHERE
FirstName = @FirstName
IF @FirstNameID IS NULL
BEGIN
INSERT INTO Table1
(
FirstName
)
VALUES
(
@FirstName
)
SELECT @FirstNameID = Scope_Identity()
END
|
|
|
|
|
Ok that looks like it will work but how would I handle an error if the user tries to enter an existing FirstName?
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Like you would handle any other error. Some people like to use the return value. I like to throw an execption back to my app so that I don't have to manage integer to error message conversions. Pick a style that your comfortable with.
CREATE PROCEDURE dbo.Name
(
@FirstName VARCHAR(50)
)
AS
DELCARE @ErrStr VARCHAR(200)
DECLARE @FirstNameID INT
SELECT
@FirstNameID = FirstNameID
FROM
Table1
WHERE
FirstName = @FirstName
IF @FirstNameID IS NULL
BEGIN
INSERT INTO Table1
(
FirstName
)
VALUES
(
@FirstName
)
SELECT @FirstNameID = Scope_Identity()
END
ELSE
BEGIN
SET @ErrStr = 'First name already exists in database.'
RAISERROR(@ErrStr,16,1)
RETURN
END
|
|
|
|
|
I've got two tables in a master-detail relationship, does anyone know how to get the row in the master field which is tha parent to the row in the child table?
Thanks in advance Ed
|
|
|
|
|
simple thing,u can use datarelation class in the dataset object.
/*code*/
ds.relations.add("relationname",ds.tables[0].columns[id],ds.tables[1].columns[id])
/* use foreach statement to retrieve the records.......*/
use getchildrows () inside the loop.....
feel free to ask: arunjothi@gmail.com
|
|
|
|
|
There is a data relation defined in the DataSet already (created using the designer in VC# Express 2005). What I'm trying to achieve is essentially filling in some default values in the child row based on the data stored in the parent row.
At the moment I've got an event handler for the DataGridView.DefaultValuesNeeded event, this is working well so far but that's because the default values (although dynamic) are simple, e.g. one of them is the current date so I've just put it a:
e.Row.Cells["QuotationsDate"].Value = DateTime.Now;
But looking up a value from the parent table is more difficult since e.Row.DataBoundItem is not yet initialized, presumably this is called before the row is initialized.
Is the only option to modify the DataSet.Designer.cs file so that it provides the default values. This would work (I think) but I don't know what VS would do if I happened to load the designer, it'd probably overwrite the changes. Still it may be the only solution.
Any thoughts?
Ed
|
|
|
|
|
Anybody know how to script out the data in SQL 2005? i.e. I want all the data in a table to come out as INSERT statements.
Or do I have to write the code myself?
regards,
Paul Watson
Ireland
Feed Henry!
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
adapted from toxcct:
while (!enough)
sprintf 0 || 1
do
|
|
|
|
|
I havn't come across anything (but havn't used SQL 2005 that much) but my guess is that you will have to write the code yourself.
Ed
|
|
|
|
|
Hi guys and gals!
I am using C# (VS2003.NET) to create my PPC application and SQL CE as my database back end. I have a little app that captures information on my mobile device. How can I sync my PPC data with a SQL Server 2005 Express DB on my server?
Any help will be GREATLY appreciated.
Kind greetings
Willem Le Roux
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
