|
If you are already ordering the fields and know you need records from end, why not store them the opposite way as current. I mean order records from current to older dates. In that case all you will need is to get top xx records from the table.
This should have been thought while designing database IMHO.
"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
|
|
|
|
|
I am using VB.net and I have a SQL database table GuestInfo with 1 column Col1 defined as Text with allow NULL & another column Col2 defined as Int with allow NULL
If I use command:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col2 = 12345"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is no error happen
But if I use:
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = 'ABC'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is an error pop-up said that text and varchar are incompatible operator
Then I tried the following:
Dim str As String
str = "ABC"
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = '" & str & "'"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
- There is a same error display
What can I do to search a Text value in column 1?
|
|
|
|
|
I rarely use TEXT data type so have not run across this problem but you might try
cmd.CommandText = "SELECT * FROM GuestInfo WHERE Col1 = Convert(text,'ABC')"
It is probable that the command object passes the string data type as varchar, as it is the default I think.
Do some research on stored procedures and parameters, that will solve all these problems and move your sql development and testing to Management Studio which will speed up your delivery time.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I did try as you suggested but now it is weird, the error now shows: Connect to SQL Server, data types text and text are incompatible in the equal to operator
- Might be in SQL database, I shouldn't defined Col1 as Text? If YES, what I should define to with Dim str as String in VB.net (str = "ABC"):
. varchar(50)
. varchar(MAX)
. char(10)
. nchar(10)
. ntext
Any suggestion? Meanwhile, I will also search for more solutions 
|
|
|
|
|
The default of nvarchar is for unicode and should not be used unless you intend to store non english characters. I would recommend using varchar(##), the length should be slightly larger than the max characters you expect. varchar can be used for up to 8000 characters, this generally meets most requirements. varchar(max) I would only use if you need to store a serious amount of text in a field, ie. a book or at least a story.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I re-define Col1 to varchar(50) then use:
cmd3.CommandText = "SELECT * FROM GuestInfo WHERE Trial = 'ABC' "
Dim lrd As SqlDataReader = cmd3.ExecuteReader()
Working as a charm!
Thanks 
|
|
|
|
|
Dear Friends,
i have one issue in using nested procedure, both procedure having hash table with same name because of which there is an issue for executing same procedure through ASP.net (it not showing issue in sql server)
so i need how to find nested procedure from huge database
Sasmi
|
|
|
|
|
The text of a procedure is stored in a system table in SQL server, from this you can do a string match to find the offending text and identify the procedures using it.
This search[^] throws up a number of possibilities.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everybody
i have two tables that have Many-to-many relationship (personality-to-(Personality_PersonalitySubtypes)-PersonalitySubtypes)
in normal scenarioes , u would select all personalitySubtypes by knowing only the personalityID by inner join the three tables
but suppose that i need the reverse case , i need to select the personalityID by knowing the four combinations of personalitysubtypes knowing that i need it to be completely accurate means if one of the four combination changes the whole personalityID may be changed . how could i do that in sql ?
i tried data structures in C# to iterate and blah blah blah till my brain is down
i don't know what to do
Human knowledge belongs to the world.
|
|
|
|
|
You can use the same data/query structure but instead of filtering on where personalityid = 2 you can use where personalitysubtype in (14,1,78).
Have a look in WHERE IN in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi i am working on vb.net 2008 with mysql.my application will run on lan i want to build a form where user can view all available servers on lan and connect to one he finds suitable.
earlier in sql server i used sqldmo for the same but in mysql i found no such alternatives.???
can smone help???
one more thing i would like to add that is my mysql is version 4.x ) 
|
|
|
|
|
i tried same question on main mysql forum and here also.but got no answer so i take it that it is not possible 
i shldnt hv changed from sql express edition to this  (
|
|
|
|
|
Vishal Saxena dev wrote: so i take it that it is not possible
A reasonable assumption. Why did you change from SQL Server?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
my boss told me to now i hv to hard code it
|
|
|
|
|
Hi,
I am using Microsoft Access 2002 database.
I want to update one field of Table. I have repetitive values in different fields like. (Book Table field & values below)
bookId 22
pageId 1
PKValue 134-100
Position
The PKValue is unique identifier. One Page can contain number of unique PKValues. That means we can have repetitive bookId and pageId values.
Now if I want to update Position field based on bookId, pageId and PKValue my query needs long loop for updating many Positions of single page.
Can somebody guide me what is solution to get fast update of Position field?
I hope I conveyed my question right.
Thank you very much.
|
|
|
|
|
Hi,
if you want to do anything with databases and are new to it, the only advice I have for you is to buy and study a book on the subject; you just can't improvise a DB app and expect it to work reliably without a solid foundation.
The particular answer would be along these lines:
UPDATE tablename SET fieldname=expression_using_field_values_and_or_constants WHERE field1=value1 AND field2=value2
Googling for SQL update would yield this[^] amongst others.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
Thank you for reply. I will act upon your advice. Sure I will grab a book.
As you mentioned the query. I am already using it. Actual problem is performance. You know this update uses composite key (bookId, pageId, PKValue) to update one single field.
UPDATE [Books] SET [Position] = '1234' WHERE bookId =22 AND pageId =1 AND PKVALUE = '134-100';
Now for example I have to updates 200 position of this pageId = 1 and bookId = 22
I have to call 200 times this Query.
What I can do to just fill once for all for a specific page and then next page and go on.
Thank you.
|
|
|
|
|
vhassan wrote: You know this update uses composite key (bookId, pageId, PKValue) to update one single field.
Let me append: for all the selected records.
vhassan wrote: Now for example I have to updates 200 position of this pageId = 1 and bookId = 22
I have to call 200 times this Query.
If you want the same update to apply to all 200 records, then write your query to update all 200 records and just call it once. You need to write your query to do what you want. Perhaps a book, like Luc Pattyn suggested would be helpful to you.
|
|
|
|
|
Thank you for your replies.
|
|
|
|
|
How about using an update query?
Something like:
UPDATE Table SET Position = Position + 1 WHERE PKValues = '134-100' And pageId = 1 And bookId = 22
|
|
|
|
|
Thank you Avi Berger.
Yes this update works.
Position is not incremental instead it is calculated value.
Please read my reply to Luc Pattyn.
Have a good day.
|
|
|
|
|
That was a sample, not something to be used exactly as written.
vhassan wrote: Position is not incremental instead it is calculated value.
Incrementing is one kind of calculation. I have no way of knowing what calculation you need. You would need write whatever calculations and record selection criteria you actually need.
|
|
|
|
|
I have a stored procedure that creates Pivot/cross tab resultset. for example my data is as follows :
Id, Date, Value
1, 01/12/2009, 100
1, 02/12/2009, 110
2, 01/12/2009, 50
2, 02/12/2009, 55
2, 03/12/2009, 75
3, 02/12/2009, 100
The Stored Procedure would result a resultset as follows :-
Id, 01/12/2009, 02/12,2009, 03/12/2009
1, 100, 110, 0
2, 50, 55, 75
3, 0, 100, o
So far so good.
Now I need to join the results of the stored procedure to a another table joined by Id, either in a View or another Stored Procedure. Can I do this, keeping in mind that the number of columns and the names of the colums are variable. The only constant is the Id column name.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
You have a number of choice on how to transport a resultset from a proc
Turn the proc into a view - always the first choice but rarely possible with a pivot
Use a UDF - also dies on the requirement of dynamic SQL to define the columns
Shove the resultset into an XML variable and pass it back as an out parameter - there are a number of articles on CP on the subject. I have used this once and while it worked I have a strong dislike for XML.
Use a global temp table. This is my default option but it has some drawbacks like using the tempdb, house keeping the tables and race issues if it is a popular proc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. I think I can discount the first three options.
I will investigate the other two options.
Thanks again
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
