|
You could show us an example of some of the records that are not getting displayed.
Check to see if nulls are getting in the way.
Another thing you can do is take the order by clause and turn it into part of your select statement output to see what the computer is seeing for those seemingly random records.
Something like this, although you might have to clean up the syntax to get it to run.
$request2 = mysql_query("SELECT tablename.*, FIND_IN_SET('urgent', priority), FIND_IN_SET('high', priority), priority, status FROM tablename WHERE status IN('In Progress','Pending') ") or die(mysql_error());
This way you might have a clearer view of why a given record gets dropped.
_____________________________
Give a man a mug, he drinks for a day
Teach a man to mug... -Scott M.
|
|
|
|
|
Thanks for the reply, I'll try that today if I have time. None of my fields allow nulls so I don't think that is the problem.
Now here's something interesting. I put the same values in my HTML form on the previous page (the one that enters them into the database) and submitted, then it shows up in the list, where the first one with the same values does not. I went into MySQL itself and can't find anything wrong with the data...it seems exactly the same in both places. Now one was made before the sorting query was setup and one was made after...would that somehow make a difference? I wouldn't think so since it's just a query...but might as well cover all possibilities.
|
|
|
|
|
Hi all,
I've created a website the is using SQL 2000/SQL2005 as the back end and I've been tasked to take the catalog I've build for the website and allow it to be ran on a CD. So With the CD version of the website I'm using a Access 2003 database, but for some reason my T-SQL queries wont work with Access. I have a bit field in my SQL database that I use as an Active flag. Access use a "Yes/No" field for this and I'm not sure why the query isn't working. I did notice that Access uses -1/0 and not 1/0, so I changed my query to reflect this:
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE (Active) = -1)
Is there any reason why this wouldn't work?
|
|
|
|
|
I see two closing parenthesis )) and only 1 open parenthesis. (
|
|
|
|
|
*slaps forehead* I should have seen that. But changing it to this doesn't work either. I tried all of the following and none seem to work.
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE ((Active) = -1)
or
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE ((Active) = TRUE)
or
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE (Active = TRUE)
|
|
|
|
|
Hi,
... WHERE NOT (Active = 0) might work for both database systems.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Holy smokes Batman... it worked! Thanks!!!
|
|
|
|
|
You're welcome.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
i develop my application backend on ms sql 2008. but my client is using 2000. what is the best may to go.should i script all the database object( tables, storeprocedures .e.t.c)
|
|
|
|
|
Ebube wrote: i develop my application backend on ms sql 2008. but my client is using 2000
This begs the question why you didn't find this out at the start.
Ebube wrote: should i script all the database object( tables, storeprocedures .e.t.c)
You can get scripts generated automatically, however, you'll still have a lot of editing to do because there are lots of minor differences between the SQL used in SQL Server 2000 and the SQL used in SQL Server 2008.
|
|
|
|
|
Hi,
How many primary key constraints can we have in a table of ms-access? Any other constraints available in ms-access other than primary key?
Please share your ideas...
Thanks,
Sivakumar.M.
|
|
|
|
|
One table, one primary key. You can verify this in the documentation[^];
"You can use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table."
I are troll
|
|
|
|
|
As Eddy said one primary key per table.
But you can create indexes on non-key fields and set them to not allow duplicates. Effectively giving you a constraint that the column must contain unique values.
It's also possible to allow two (or more) columns to have duplicates while the combination of the two columns is unique - again by creating an appropriate index.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
How many primary key constraints can we have in a table of ms-access?
Ans: Only 1. Not only in MSAccess, in any database i.e. Oracle, Sql Server etc. It is a general database rule.
You can have many FK and Composite keys however.
Any other constraints available in ms-access other than primary key?
Ans:
Yes.
Primary key constraints
Foreign key constraints
Check constraints
Check constraint expressions
Unique constraints
Default constraints
A little search in google gave me the second answer
<a href="http://office.microsoft.com/en-us/access/HP030838791033.aspx">http:
Hope it helps
Niladri Biswas
|
|
|
|
|
Hi,
I have developed a code to create a ms-access table through VB.NET as mentioned below.
ADOXtable = New ADOX.Table
ADOXtable.Name = "ACN_CHARX_INSTANCE"
ADOXtable.Columns.Append("LOGICAL_DB_NAME", ADOX.DataTypeEnum.adVarWChar, 32)
ADOXtable.Columns.Append("DIM_NAME", ADOX.DataTypeEnum.adVarWChar, 32)
ADOXtable.Columns.Append("CHARX_NAME", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_TABLE", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_VALUE_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("VALUES_UNIQUE", ADOX.DataTypeEnum.adInteger)
ADOXtable.Columns.Append("CHARX_ORDER_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("CHARX_ORDER", ADOX.DataTypeEnum.adInteger)
ADOXtable.Columns.Append("CHARX_NUMERIC_COL", ADOX.DataTypeEnum.adVarWChar, 128)
ADOXtable.Columns.Append("BITMAP_COL", ADOX.DataTypeEnum.adVarWChar, 128)
cat.Tables.Append(ADOXtable)
Now i want to set primary key constraints for this table through coding. Please share your ideas...
Thanks,
Sivakumar.M.
|
|
|
|
|
Something like this might do it.
pk = New ADOX.Index
pk.Name = "PK"
pk.PrimaryKey = True
pk.Unique = True
pk.Columns.Append("TheKeyColumn")
ADOXtable.Indexes.Append(pk)
You might have to set some other properties for pk (e.g. IndexNulls) - have a look at the help/Intellisense.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car).
Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.
modified on Thursday, June 18, 2009 6:25 AM
|
|
|
|
|
kripa ostwal wrote: I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car).
Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.
Assuming your question is: How do I do that?
You are going to have to show us the data model. We cannot help you construct a query without knowing what the data actually looks like (what the user can do on the front end isn't so useful as it may not match the data model)
|
|
|
|
|
I have database with carMaster,AmenityMST & carAmm.
CarMST
carCode carNumber color
1 MH12-KO1212 blue
2 MH42-jk1234 red
AmenityMST
ammCode ammName
1 mobile charger
2 video coach
3 AC
carAmm
carCode ammCode
1 1
1 3
2 2
2 3
Now I want, when user search car with 'video coach' & 'AC', he should get only 1 car i.e. 2
I used
"select carCode from carAmm where ammCode in (2,3)"
but it returns me both cars, even car have not 'video coach'
|
|
|
|
|
kripa ostwal wrote: where ammCode in (2,3)
That is because you are asking for any cars with ammCode of 2 OR 3.
Try a join instead between two result sets.
First set:
SELECT carCode from carAmm WHERE ammCode = 2
Second Set:
SELECT carCode from carAmm WHERE ammCode = 3
Something like this:
SELECT c1.carCode
FROM carAmm AS c1
INNER JOIN carAmm AS c2 ON c1.carCode = c2.carCode
WHERE
c1.ammCode = 2
AND
c2.ammCode = 3
|
|
|
|
|
s,
bt i wrote store procedure for it coz there will be n number of aminities in future for search criteria.
& I passed a string as '2,3'
Can I split it in store procedure & create joins?
or any other way to do this?
|
|
|
|
|
kripa ostwal wrote: s,
kripa ostwal wrote: I passed a string as '2,3'
If you are using SQL Server 2008 you can pass tables. That way you have no crazy string manipulation to get the data you need.
kripa ostwal wrote: Can I split it in store procedure & create joins?
Yes, but the splitting operation is a PITA.
kripa ostwal wrote: or any other way to do this?
You must already be using dynamic SQL, so just variations on a theme (unless you are using SQL Server 2008)
|
|
|
|
|
i m using sql server 2000 with asp.net
|
|
|
|
|
I'm certain this is about as noob-ish as it gets, but I'd like to know what typical solutions to this issue are.
2 people (on two computers) get passed their own dataset instances, representing 1 table in the database. They both want to add a row to the db table. They both click on the new row in the datagridview, and it happily inserts a new row on their monitors, with a new Primary Key value (identity column). Problem is, the number is the same on both computers. So the first person can save just fine, but the second person naturally receives an PK violation error when he or she saves.
How is this issue supposed to be dealt with (i.e. avoided)? Avoid creating the PK value at all on the client side,allowing the database to create the PK when insert has been performed, and just refresh the datagridview with that value (created by the database)?
Some other approach?
Thanks for ideas,
cdj
|
|
|
|
|
I generally avoid PK creation on the client-side. The database alone should be responsible for that. However, if that isn't possible, then my next preferred solution is to use a GUID. That way the two machines will generate different numbers.
Of course, your DBA may baulk at the use of GUIDs as the data will be inserted randomly (and since the PK is often the clustered index, that can slow the performance of the database if you are doing a lot of inserts or do searches on sequential [or near-sequential] ranges of PK values)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
