|
WDI wrote:
what should i do?
I'd fix the error.
First, the code you've presented here is not the code you've got a problem with because there are more errors in it than you present. For instance, the word "where" is misspelled.
Second, and I'm sure I've told you this before, you should use parameterised queries. Then you don't need to worry about stringing together fragments of SQL like this. And, it also improves the security of your application, reducing the risk that someone will be able to successfully launch an attack on it. For more information you can read SQL Injection Attacks and Tips on How to Prevent Them[^]. This acticle actually shows you how to use parameterised queries. I don't know why you don't use them. It makes everything so much easier.
Just for your information - I fully expect you to change this to use parameterised queries - The reason for the error is that you need to put an AND or OR between the conditions in a WHERE clause.
Another tip, put a breakpoint on the return statement and examine the contents of the sql string - it is much easier to read that than trying to decipher what the string should look like based on code that concatenates lots of little strings together.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I do waht you told but get
Syntax error (missing operator) in query expression 'AND( styleID = @styleID )'.
my method is :
public static IDataReader GetFilteredProduct( FilterEN filterEN )
{
OleDbConnection oleDbConnection;
OleDbCommand oleDbCommand = new OleDbCommand();
OleDbDataReader oleDbDataReader = null;
oleDbCommand.CommandText = "select Product.productID , Product.productName ,
Product.productInfo , Product.productPrice ,
Product.dateOfInsert , Product.status ,
Product.artistID , Artist.artistName from Product
inner join Artist on Product.artistID =
Artist.artistID ";
oleDbConnection = new OleDbConnection (ConfigurationSettings.AppSettings["DbC"]);
try
{
oleDbConnection.Open();
string boolian = ( filterEN.Boolian == 1 ? "OR" : "AND" );
if ( filterEN.StyleID > 0 || filterEN.ArtistID > 0 )
{
oleDbCommand.CommandText += " where ";
}
if ( filterEN.StyleID > 0 )
{
oleDbCommand.CommandText += boolian + "( styleID = @styleID )";
OleDbParameter paramStyleID = new OleDbParameter ( "@styleID" ,
OleDbType.Integer );
paramStyleID.Value = filterEN.StyleID;
oleDbCommand.Parameters.Add ( paramStyleID );
}
if ( filterEN.ArtistID > 0 )
{
oleDbCommand.CommandText += boolian + "artistID = @artistID ";
OleDbParameter paramArtistID = new OleDbParameter ( "@artistID" ,
OleDbType.Integer );
paramArtistID.Value = filterEN.ArtistID;
oleDbCommand.Parameters.Add ( paramArtistID );
}
oleDbCommand.Connection = oleDbConnection;
oleDbDataReader = oleDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch( Exception ex)
{
throw ex;
}
return oleDbDataReader;
}
|
|
|
|
|
You should put a breakpoint at the start of this code and watch the CommandText being built up. You will then see where your logic error is.
You are adding an AND immediately after the WHERE and this is causing a syntax error. WHERE AND( styleID = @styleID ) is not valid. You need to check in your code whether a filter expression has already been placed in the WHERE clause. Obviously, for StyleID you don't need to check as this is the first filter expression, so nothing comes before it. You can remove the boolian+ (by the way that should be spelt "boolean") in the line that adds the StyleID to the SQL Statement.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
thanks i got it , with this change it fixed!
oleDbCommand.CommandText += " where 1=1";
|
|
|
|
|
|
The title says it all really.
I am looking for something that will allow me to put an LDAP frontend onto and MSSQL server. i.e. Application creates and LDAP query that is received by the LDAP frontend, but the return data is in an MSSQL database.
Does anybody have any information/links on how this can be done?
post.mode = postmodes.signature;
SELECT everything FROM everywhere WHERE something = something_else;
> 1 Row Returned
> 42
|
|
|
|
|
i import a huge database from access to sql server 2000, in Access database there were a field which is auto increment and primery key,but when i check SQL SERVER auto increment constraint not there and im not able to change data type to unique identifier field ,how i change it to auto increment?
Rajith Alwis
|
|
|
|
|
if it is an integer field, set the "identity" property to true and the "increment" property to 1.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
thanx Rob,
earlier i didnt see the identity increment field its appear only when we set the identity property true, thanx
Rajith Alwis
|
|
|
|
|
Hello..
I am connected with a database paradox through Jet Driver.
L " Provider=Microsoft. Jet. OLEDB.4.0; Data Source = \\ server\db; Extended Properties=Paradox 5. X; ".
The problem, other program working through DBE does not give me to open the tables.
How to me to force to work two programs in common?
|
|
|
|
|
If the "other program" is holding open exclusive locks on the tables, you may be out of luck.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
I do not think that she(it) absolutely blocks the tables, as other copies of this program started in parallel perfectly work with this base
|
|
|
|
|
The make sure you aren't trying to get an exclusive lock (check for connection string properties, I can't help much here as I have no recent experience with Paradox. Played with it some 10 or 15 years ago, but...).
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
Cant use wildcards with "LIKE" accessing oracle database
objDv = FilterDataSet(objDs, "SUPERVISOR_NAME LIKE 'se_n'")
where objDs is the data set
should return "sean" but is not
not fimilar with wildcards any ideas?
Cheers
|
|
|
|
|
Hai,
objDv = FilterDataSet(objDs, "SUPERVISOR_NAME LIKE '%se_n%'")
use this '%' sign some times it works if the data contained in a word or spaces added to that data.
underscore may not work in certain situations.
asithangae
|
|
|
|
|
I want to write some data in SQL Server 2000 in a language <B>other than English</B>. plz help me, coz there seem to be no way of doing it...
Thanx
|
|
|
|
|
I've put data in SQL Server in languages other than English and had no problems. The main thing to remember is that for string columns, use nchar and nvarchar.
If this does not answer your question then you need to be more specific. What are you trying to do? And what happens that doesn't work.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks Colin
I've tried that using nchar but it shows some symbols of squares and something like that, Am I using a wrong font or what?
Thanx
And waiting for your answer
|
|
|
|
|
|
Thank you, now it is working...
Thnx agan....
|
|
|
|
|
using ASP.NET (C#.NET) ,try to get a result from two table s using below quary,
SELECT Sum(tbl_user_earned.point_awarded) AS SumOfpoint_awarded, Sum(tbl_transaction_history.points_chash_exchange) AS SumOfpoints_chash_exchange
FROM tbl_transaction_history INNER JOIN tbl_user_earned ON tbl_transaction_history.uid = tbl_user_earned.uid
GROUP BY tbl_transaction_history.uid
HAVING (((tbl_transaction_history.uid)=1));
i tryied to execute this using PHPMYADMIN but this not working ,i got below error
#1054 - Unknown column 'tbl_transaction_history.uid' in 'having clause'
-RAJITH ALWIS
RAJITH ALWIS
|
|
|
|
|
Ok guys,
I'm trying to figure out what mathmatical function is necessary to create an average of these numbers, THEY ARE VARIABLE so basically week by week they change, i.e. so this week is 01 and a year from now will be 52. There are mulitiple transactions a week and I need to separate each by the week number. then I need to list this in a dropdownlist in C#, so to recap:
The following is an output from the DB:
Shift_Number
16,16,16,16,1,1,1,16,16,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1
at any given time I need to beable to have only one #16 and one #1, and one #2 (and so on and so on week by week.) show in a dropdown list. it seems it would be simple enough to just have the ddlist show xx number of shifts but that is going to be impossible 5 years from now. so any ideas
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
|
Sorry it didn't make sense, I read back over it and you're right, sorry.
ok, so lets try this again. I am trying to create a dropdownlist that is databound to sql db, the shift_number(which stays the same until closed out) is recorded everytime a user creates a transaction, and so there are always more than one record of the same shift_number in the database.
For example, if a machine is operating on shift 1, every time the transaction occurs, it registers a 1. So, if I have 19 transactions that occurred,then I am shown 19 1's in a row in the dropdown box. (1,1,1,1, etc. 19 times) However, all I need to see shown in the actual dropdown box is 1 instance of number one. All I really need to access is the shift number, and not the number of transactions. The problem is when I attempt to do so, I am given every instance on that shift that a transaction occurred. How do I query the db to do this? what query that would take each instance of the same number and average them so I am only left with that one number in my drop down box and not 19 of the same number?
basicaly i parse the db to find the shift_number i'm looking for then sum the tranaction_amount to create the total shift_value.
so i may have 23 instances(transactions) of shift_number 16, 5 instances of shift 15, 19 instances of shift 1, etc, etc.(depending on the number of transactions that week) which when I pull the data from the db it will return with 23 different options for shift_number 16 and 5 options for 15, etc,etc,
I only need to see the single <option> for shift 16, a single <option> for 15, etc,etc. so my question really boils down to this, (i think) i need a query or a method to take each shift number visible in the db and display only one value of each in the dropdownlist that will then upon selection allow me to sort by machine_id and shift_number...
what i was saying about the varible week number was that these shifts are only good for a week, the shift gets closed out once a week,(not allways on the same day, but at least once a week) and I need to be able to see the total number of shifts that have closed up to today(when ever today may be)and not have to look at multiple instances of the same shift.
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
All I really need to access is the shift number, and not the number of transactions. The problem is when I attempt to do so, I am given every instance on that shift that a transaction occurred. How do I query the db to do this?
use SELECT DISTINCT ShiftNumber ...
wetrivrrat wrote:
what query that would take each instance of the same number and average them so I am only left with that one number in my drop down box and not 19 of the same number?
You don't want to average them. You just want distinct values (see above)
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
