|
Hello everyone,
I am trying to build a stored procedure for a search page that will send many input parameters of different demographic data. The problem is, not all the search fields have to be used so some of the input parameters will be null when they are sent. I'm trying to construct a where clause for this but don't know how to do it without including a whole bunch of "if" statements to build a where clause based on which parameters have data. Does anyone have any suggestions? Thanks for your time!
mav0707
|
|
|
|
|
SELECT *
FROM MyTable
WHERE
(@param1 IS NULL OR (@param1=Column1)) AND
(@param2 IS NULL OR (@param2=Column2))....
If the parameter is null then the left side of the OR clause is true and the right side does not need to be evaluated, if the parameter is not null then the left side of the OR clause is false and so the right side must be evaluated.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
That works great!! Thank you so much for your help!
mav0707
|
|
|
|
|
In ADO.NET
I am able to create an array of DataRows that match my criteria, but only when cboSponsor.Text contains one word. If there is more than one word I get an error message when I am debugging.
-------------------------------------------------------------
Code:
Dim drFound() As System.Data.DataRow
Dim dr As System.Data.DataRow
drFound = Me.DsEvent1.EventTbl.Select("OrgName = " & cboSponsor.Text)
-------------------------------------------------------------
Error message:
An unhandled exception of type 'System.Data.SyntaxErrorException' occurred in system.data.dll
Additional information: Syntax error: Missing operand after 'Helpers' operator.
-------------------------------------------------------------
In this case, 'Helpers' is the second of two words.
How can I code it so it recognizes a phrase?
Thanks
|
|
|
|
|
try
drFound = Me.DsEvent1.EventTbl.Select("OrgName = '" & cboSponsor.Text&"'")
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Thank you.
Now on to solve the next problem that comes up. 
|
|
|
|
|
I'm not sure where this posts belongs because it involves coding in Visual C++. I was asked to create an application that uses a MSDE database for storage. When all the data has been parsed and dumped into the database, I would like the user to view the tables within Access project. My application does not require a custom form to display the data but random queries and reports need to be generated. This was why I chose to Access to display the data. Now my question is:
1. If my application does not need custom forms to display the data and a front-end database client like Access is sufficient in providing all the tools and functionality needed to manipulate the data should I even bother using MSDE? Should I just dump the data into an Access database (.mdb)? I want to make sure I'm designing this effectively.
2. If I were to continue using MSDE, how do I make the call in my code to open the newly created database within Access project? When you create a new access project that connects to an existing database there are certain settings that the wizard asks of you before connection to the database can be made. How do I pass those parameters from my application to access project? Once the project has been created after the connections settings have been configured, the user will have full access to the tables within the database. The user can then create queries and reports as needed. How can I manage this within Visual C++. Am I headed in the wrong direction?
Any help would be much appreciated.
|
|
|
|
|
Hi, imagine the following example:
select [o].[ID] , [r].[quantity],[r].[price]<br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID)
if i would like to apply a filter on the sum of quantity*price for all rows ontop of that and still return each orderrow just like before. how can this be done?
currently i have :
select [o].[ID] , [r].[quantity],[r].[price]<br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID) and<br />
o.id in (<br />
select [o].[ID] <br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID)<br />
group by [o].[id]<br />
having <br />
sum (r.quantity * r.price) >= 100<br />
)
so this query will still give me each orderrow , but filter out orders where the order sum is less than 100
is it really not possible to accomplish this w/o a subquery?
//Roger
|
|
|
|
|
hello forum,
Not sure if this is the most suitable place in codeproject to put a thread related to Active Directory... sorry, if does not.
I have got a problem while searching directory through ADSI.
I want to search those organizational Units which contains a specific substring in its distinguishedName attribute. The filter expression is as follow:
(&(objectClass=organizationalUnit)(distinguishedName=*mysubstring*))
where mysubstring is the specific substring.
The problem is I get always nothing.
Could u give me any idea what is the problem and how to solve it?
thanks in advance!
|
|
|
|
|
Can anybody explain the reason for getting this error.
Snapshot of sql server error log:
2005-02-07 05:38:58.48 spid63 Error: 17805, Severity: 20, State: 3
2005-02-07 05:38:58.48 spid63 Invalid buffer received from client..
2005-02-07 05:54:49.29 spid75 Error: 17805, Severity: 20, State: 3
2005-02-07 05:54:49.29 spid75 Invalid buffer received from client..
The application fails when it tries to update a row in table. The table has a column of type text.
Sabya
|
|
|
|
|
Im making an access db, that is accessed through my VB 6.0 software.
How would a clever way be to store this array in my database without making 500 rows in the table for it.
Its 500 double values from a testsystem, that is used to draw a curve.
|
|
|
|
|
Save the array seperated by ",", like "array1,array2,array3,..... "
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
|
Hi all,
I intend to use the Firebird database engine as the embedded
database engine of our desktop window application written in C#.
Have anyone used it before and is it good enough ? How is it compared
to the Microsoft MSDE data engine in performance, in ease of installation, maintainability....etc.. ?
Thanks
|
|
|
|
|
pyhtang1 wrote:
I intend to use the Firebird database engine
Why would you ask if you have already made a decision?
How did you come to that decision if you have not already made this comparison?
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
hi all,
Jsut a quick question, I have a product database with a productcost field, when i enter a cost into the field i use for exemple £100 or £0.50. When I run my website in asp.net it shows a dollar sign $ instead of a pound sign.
Any ideas on how to correct this?
I have checked the help files in SQL server 2000 and also in visual studio.net...cant find anything related....its gotta be something easy??
Thanks in advance!
|
|
|
|
|
Is your website hosted in the same machne where you edit the database?
If not, your machine probably is configured (in Control Panel, Regional settings) to format the currency values like British (pounds). And probably the ASP.NET server is configured for an english-US culture.
Do something like this in your ASP.NET code:
CultureInfo ci = new CultureInfo("en-br");
decimal dMyMoney = 12.99m;
string strMoneyInPounds = dMyMoney.ToString("c", ci);I'm not exactly sure of the syntax, but that should format your decimal value as currency (the "c" parameter) using the English (UK variant) culture.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Thanks for your help....I've sorted it out now..Knew it would be something simple!
Cheers
Jetset
|
|
|
|
|
Could someone please be so kind to tell my: How can I debug the SQLCE Database form a Pocket PC. There must be a possibility to see what's in the tables of the database???
Thank's very much
Stefan
(Austria, sorry for my English)
|
|
|
|
|
I've saved an image (converting in byte[]) inside a field of my database, now i would read the database getting these bytes[] and convertering into the original image...
I use this code:
OleDbDataReader myReader;
....
.. instance myReader here...
....
myCommand.ExecuteReader();
MemoryStream myStream;
int buffersize = 300000; // doubt n°2 = but if my image is bigger of 300000 ???
Byte[] outbyte = new Byte[299999];
myStream = new MemoryStream(outbyte, 0, 300000, true);
myReader.GetBytes(5, 0, outbyte, 0, buffersize);
myStream.Flush(); // am i just deleting all bytes unused?
pictureBox1.Image = Image.FromStream(myStream);
myStream.Close();
but this code doesn't work, where do i wrong?
|
|
|
|
|
I've tried to change the code in this way:
MemoryStream myStream;
Byte[] outbyte = (Byte[])myReader["Image"];
myStream = new MemoryStream(outbyte);
pictureBox1.Image = Image.FromStream(myStream);
myStream.Close();
but this doesn't work too.
|
|
|
|
|
|
Yes i did, but when i use (Byte[])cmd.ExexuteScalar() i get only 18 byte at the place of 17322 that is my image, why?
|
|
|
|
|
I'm having much problems working with my .mdb database.
Yes there are objects in VISUAL STUDIO (c#) .NET that make easyer the work but i would to write all it needs by code.
What i've to do for the moment is add a row to my database with 4 columns of which one is byte conteiner (image bitmap).
Ok i've tryied much code from google c-charpcorner codeproject msdn end so on... but all that give me some error, the forums user told me to set insertcomand updatecomand fill fillschema set a primarykey but all half informations i can't manage...
Please someone can show me a full code to connect to a simple database .mdb with a Table and insert just a row inside?
This is the schema:
Table 'myTable'
Name: text 20 (primary key)
Surname: text 20
Image: OLEOB byte[]
REALLY THANKS.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
