|
Hello,
I am storing in an SQL table data including email addresses. When trying to retrieve via the Select function rows including an email address the program traps because the @ character witin the select statement is a reserved symbol for parameters.
How can i select rows in a table based on email addresses, such that the @ symbols is not interpreted as a special symbol but part of a string parameter.
I tried to use "\@" instead of @ but it doesent work.
Any suggestions are most appreciated,
Daniel
|
|
|
|
|
Can you show us your code?? or give us more information?
Where do you execute the SELECT command? how do you build your query?
Off the top of my mind, if you're using .NET, using parametized queries (see SqlCommand class and its Parameters property) should solve your problem.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Dear Luis,
Thank you for your reply.
I have been using visual studio.net form designers to construct a data set object, which included a "SubscribersEmails" table. This table stores one (or more) email addresses a subscriber can use to register.
Following is a code snipplet, which attempts to retrieve one row within the SubscribersEmails table which belong to a specific subscriber (identified by a SubscriberID) and is the row associated with one email address (identified by the anEmailAddress string). Note the SubscribersEmail table includes the columns: SubscriberID and emailAddress.
Dim theEmailRows As DataRow()
theEmailRows = subscriberTableDS.Tables("SubscribersEmails").Select("SubscriberID = " & SubscriberID & "AND emailAddress = " & anEmailAddress)
Since during execution the string anEmailAddress includes the @ sign, the above command fails, complaining about the @ sign.
thanks,
Daniel
|
|
|
|
|
grossd wrote:
theEmailRows = subscriberTableDS.Tables("SubscribersEmails").Select("SubscriberID = " & SubscriberID & "AND emailAddress = " & anEmailAddress)
Since anEmailAddress is a string, you have to use delimiters, in this case single quotes.
....Select("SubscriberID = " & SubscriberID & " AND emailAddress = <big>'</big>" & anEmailAddress & "<big>'</big>")Hope this helps,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hello Luis,
Thank you!,
It works very well now. That was exactly the problem.
Daniel
|
|
|
|
|
You're very welcome! I'm glad I could help!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Can anyone help ...
i'm developing an ADO.NET prog., here is a brief description :
--dataSet X contains all tables.
--dataSet Y : temp dataSet for filtering some table views (every search session Y is created and shipped with the chosen table(only one table) from X).
--after the user build the search criteria (by entering some values for some columns or choosing predefined values for lookup columns)he clicks Accept.
-- the Accept button deletes the dirty copy of the table (if exists),which may exists from a previous search session, then Copies another copy(clean one) of the table (original table copied at the start of search session) existing in (Y) and apply the filteration criteria by deleting those rows (from the second table) which does not match the search criteria.
****Important:When the user clicks Accept AGAIN the filtered table (in Y) is deleted and then copy a new one from the already existing one.***********
--after all filteration is done, a dataGrid (M) is bound to (Y) and the filtered table(second table).
**********
PROBLEM
**********
--when the user CHANGES the search criteria (by changing any value or adding new condition), he then presses Accept but (M)refuses to view the updated table.
--note that the first time of search goes ok, but no further results are dispalyed correctly(the first search result lasts forever on M) if the search criteria changes even though the filtered table in (Y) has changed.
****
Code
****
AcceptButton()
{
layer1.ReloadTempTable();
for(int i=0;i
|
|
|
|
|
hi,
i got this table -
Employees:
Id | Name
------------------
1 Avi
2 jack
3 NULL
4 NULL
5 betty
when i query this sql - "select id, name from employees"
i get back a data set with 5 results.
the problem is that its seems like when the query executed , when the query bump into a null value in the Name column, from now on all the results will be null.
it means that the dataset result will be -
Id | Name
------------------
1 Avi
2 jack
3 NULL
4 NULL
5 NULL // SUPPOSE TO BE WITH "betty"
why is that?
(i accord to the problem when i tried to do ds.tables[0].rows[1][4] and it returned dbnull)
|
|
|
|
|
Avi Laviad wrote:
(i accord to the problem when i tried to do ds.tables[0].rows[1][4] and it returned dbnull)
Given the SQL you gave, then this should fail.
What you are saying in this piece of code is that you want the value from table[0] (the first table returned), Rows[1] (the second row, which should contain 2, Jack) and then column[4] (the fifth column is way outside the range - You should get an IndexOutOfRange exception if you run it)
Perhaps, for brevity, you cut too much out of the code and we cannot see the problem anymore.
Do you want to know more?
WDevs.com - The worlds first Developers Services Provider
|
|
|
|
|
i ment ds.tables[0].rows[4][1] - thats return me dbnull.
my code is -
<br />
DataSet ds = DBHelper.Retrieve("select TreeId, owner, Root, BranchTitle, BranchText, LeafText, ScreenShot, Leaf_ScreenShot from Trees");<br />
ArrayList list = new ArrayList();<br />
if (ds.Tables[0].Rows.Count > 0)<br />
{<br />
for (int i=0; i < ds.Tables[0].Rows.Count; i++)<br />
{ <br />
Tree t = new Tree();<br />
if (ds.Tables[0].Rows[i][1] == DBNull.Value)<br />
{<br />
t.Owner = 0;<br />
}<br />
else<br />
{<br />
t.Owner = (double)ds.Tables[0].Rows[i][1];<br />
}<br />
list.Add(t);<br />
}<br />
}<br />
else<br />
{<br />
throw new Exception("No rows found");<br />
}<br />
return list;<br />
and DBHelpr.Retrieve code is
<br />
public static DataSet Retrieve(string Sql)<br />
{<br />
DBFactory db = DBFactory.Instance;
IDbConnection conn = db.connect(Constants.ConnStr);
IDbDataAdapter adapter = db.getAdapter(Sql, conn); <br />
DataSet ds = new DataSet();<br />
<br />
adapter.Fill(ds);
db.disconnect(ref conn);
<br />
return ds;<br />
}<br />
any hint?
|
|
|
|
|
Hello,
I'm having problems retrieving the values of a stored procedure's output parameters. I don't know if the problem is in the stored procedure or in the code.
When the procedure is executed, the records in the three tables are inserted correctly, and the identity from the first table is stored correctly in the other two tables.
When I try to get the value of the parameter after the ExecuteNonQuery call, an InvalidCastException exception is thrown. This is my code:
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;
cmd.CommandText = "ReprDist_InsertDistributor";
cmd.Parameters.Add("@ID_representante", data.RepID);
cmd.Parameters.Add("@NombreComercial", (string) row["NombreComercial"]);
cmd.Parameters.Add("@Password", (string) row["Password"]);
SqlParameter paramDistID = cmd.Parameters.Add("@ID_distribuidor", SqlDbType.Int);
paramDistID.Direction = ParameterDirection.Output;
SqlParameter paramCompID = cmd.Parameters.Add("@ID_empresa", SqlDbType.Int);
paramCompID.Direction = ParameterDirection.Output;
SqlParameter paramUserID = cmd.Parameters.Add("@ID_usuario", SqlDbType.Int);
paramUserID.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
data.DistID = (int) paramDistID.Value;
data.CompanyID = (int) paramCompID.Value;
data.UserID = (int) paramUserID.Value;and this is my stored procedure:
ALTER PROCEDURE ReprDist_InsertDistributor
(
@ID_representante int,
@NombreComercial varchar(70),
@Password varchar(20),
@ID_distribuidor int OUTPUT,
@ID_empresa int OUTPUT,
@ID_usuario int OUTPUT
)
AS
INSERT INTO Empresas (NombreComercial, Direccion, Ciudad, Estado, Telefono, DireccionWeb, EMail, RazonSocial, DireccionFiscal, RFC)
VALUES (@NombreComercial, @Direccion, @Ciudad, @Estado, @Telefono, @DireccionWeb, @EMail, @RazonSocial, @DireccionFiscal, @RFC);
SELECT @ID_empresa = @@IDENTITY;
INSERT INTO Distribuidores (ID_empresa, ID_representante) VALUES (@ID_empresa, @ID_representante);
SELECT @ID_distribuidor = @@IDENTITY;
INSERT INTO Usuarios (Nombre, Apellido, Telefono, EMail, NombreUsuario, Password, ID_empresa)
VALUES (@Nombre, @Apellido, @TelefonoContacto, @EMailContacto, @NombreUsuario, @Password, @ID_empresa);
SELECT @ID_usuario = @@IDENTITY;
RETURN;If anyone can help me out, I'd appreciate it very much!
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
It suddenly started working, I don't know why!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
I'm getting the following error "could not use '|';file already in use". From MSDN i got to know that this occurs if a db is tried to open when its already opened in exclusive mode. But it is not the case here. DB used in MS Access 97 and Dev Env is VC++ 6. Any help would be greatly appreciated.
Thanks
Vikram Attiganal
|
|
|
|
|
I'm currently using a StringBuilder in .NET to build a SQL Query and then using the SqlCommand object to append parameters. I'd like to move this into a stored procedure but am unsure how to do this as I have multiple WHERE clauses that depend on the condition of the form when the user submits it. I've been using sprocs for years now so 'how to create one' is not the issue. Whether and how to implement is where I'm trying to go with this question. The box is running SQL2K and I've read the performance gains that used to be realized for SQL7 have really gone away with optimizations and caching in 2K.
IE:
code looks something like below
VB Code in all caps
select ... from table where
IF FORM_CONDITION_1 THEN
append something to where clause
END IF
IF FORM_CONDITION_2 THEN
append something to where clause
END IF
IF FORM_CONDITION_3 THEN
append something to where clause
END IF
...
EXECUTE SQL COMMAND
I have 5 or 6 conditions that I'm checking for so writing a procedure for each possible set of circumstances seems somewhat silly. My recordsets coming back aren't really that large (50-120K of data on average) so I'm wondering the following.
1. Is there an easy way to perform conditional WHERE clauses in a T-SQL stored procedure without actually building the query in the procedure like I'm doing with the StringBuilder?
2. Should I just scrap the WHERE clause, return all the records and then use the RowFilter property of the datatable to filter my results before rendering them to the page?
3. Should I just continue using the StringBuilder method?
Any thoughts on how other people have handled a situations like this are appreciated.
Thanks,
-Brian
|
|
|
|
|
AnotherUser wrote:
Is there an easy way to perform conditional WHERE clauses in a T-SQL stored procedure without actually building the query in the procedure like I'm doing with the StringBuilder?
Yes.
AnotherUser wrote:
2. Should I just scrap the WHERE clause, return all the records and then use the RowFilter property of the datatable to filter my results before rendering them to the page?
No.
AnotherUser wrote:
3. Should I just continue using the StringBuilder method?
No way!
SELECT *
FROM SomeTable
WHERE (ConditionOne AND (OptionalSqlForConditionOne))
AND (ConditionTwo AND (OptionalSqlForConditionTwo))
AND....
Replace the bold-italic text with the necessary code. e.g. If, say, you are optionally testing whether the filter should be on a start date, you could supply a NULL value or the start date, so the condition would be @StartDate IS NOT NULL (Remember the condition must evaluate to true in order to process your Optional SQL.
For a fuller example, let's say you have two conditions, a start and end date.
SELECT * FROM MyTable
WHERE (@StartDate IS NOT NULL AND (StartDateColumn >= @StartDate))
AND (@EndDate IS NOT NULL AND (EndDateColumn <= @EndDate))
The additional brackets are not required round the Optional SQL IF the optional SQL is just one clause, or if all the clauses are joined together with an AND operator. You do need the surrounding brackets if you have OR clauses, or if your optional SQL needs brackets for other reasons.
Does this help?
Do you want to know more?
|
|
|
|
|
Colin,
Much appreciated. Yes, the example is perfect. I have a bunch of clauses but it's very workable.
Thanks again,
-Brian
|
|
|
|
|
Hey Colin,
I worked up the example and it doesn't really work as expected. Don't know if this is a SQL2K thing or just the way SQL works. The sample code below uses the pubs database.
declare @state char(2)
select @state = 'CA'
declare @au_lname varchar(40)
select @au_lname = 'G'
select * from dbo.authors
where (@state IS NOT NULL AND (state = @state))
and (@au_lname IS NOT NULL AND (au_lname LIKE @au_lname + '%'))
Running the statement as is returns some records. However, if either @state or @au_lname or both are null then no records are returned.
What I'm looking for is the opposite of this. if @state or @au_lname are null then the clause is ignored. If both are null than all records are returned.
-brian
|
|
|
|
|
 I am really sorry - This is what I get for posting stuff staight out of my head without testing it first. I should really know better. 
Anyway, the correct form using your example (which I have just tested) is:
select * from dbo.authors
where (@state IS NULL OR (state = @state))
and (@au_lname IS NULL OR (au_lname LIKE @au_lname + '%'))
Note that the IS NOT NULL is now IS NULL and the AND between the condition and the filter is now an OR
I hope this works out better for you.
Do you want to know more?
WDevs.com - The worlds first Developers Services Provider
|
|
|
|
|
Much better. Works as advertised.
Thanks,
-Brian
|
|
|
|
|
Made a few changes working on what you originally supplied and have something that works. If the parameter is null then I have '1=1' added to it evaluates true for each row checked. If it isn't null then it performs the requested check.
Thanks again for the idea.
-Brian
declare @state char(2)
select @state = 'TN'
declare @au_lname varchar(40)
select @au_lname = 'G'
select * from dbo.authors
where ((@state IS NULL AND 1 = 1) OR (state = @state))
and ((@au_lname IS NULL AND 1 = 1) OR (au_lname LIKE @au_lname + '%'))
order by au_lname
|
|
|
|
|
I have a DTS that takes Visual FoxPro files (.dbf) and reads them to then transform the data and load it into SQL Server.
it runs quite well when executed manually. Fails every time when trying to
run it as a scheduled job via asp.net.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
i have done a test as below:
If I add other tasks such as ActiveX tasks to delete some specific data from the tables in SQL Server,the "delete" task canbe executed quite well,but still failed when transforming the data.i think this test means the problem maybe from foxpro.
Is there any one who can help me? Thank you in advance!![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
I have a problem here while excecute a oledb datareader...
<br />
<br />
...<br />
<br />
rdr.close();
<br />
cmd.CommandText = "SELECT MID FROM MandantMC";<br />
<br />
rdr = cmd.ExecuteReader();<br />
<br />
if(rdr.HasRows)<br />
{<br />
strDir = rdr.GetString(0);
...
rdr has rows but responsed with this message:
System.InvalidOperationException: No data for row/column
I don't understand this. The SQL Query analyzer give me correct results, but not this app. Before I started the Excecution of this datareader, I have closed one before ( you can see the first code line ). First request works. After closing it, the second ( the new ExcecuteReader ) fails. Maybe "cmd" make problems ?
|
|
|
|
|
After the ExecuteReader() do a rdr.Read().. it will fix your problem. First record does not get read automatically.
|
|
|
|
|
Hi, I have a Decimal(9,2) type, and I want to evaluate the fractional component of this. What I actually want to know is if there is a sql function to perform this. If I had 13.45, is there a function to return .45?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
