|
select * from table where TextColumn like ('%search term%')
Andy Gaskell, MCSD
|
|
|
|
|
Thanks Andy ,when I used the statement you said nothing return but there is no error,then I changed '%' to '*' then it find the search term ,but there is still one problem,e.g I want to find 'M' in 'Mazdak',it doesn't find it,but if I change '*M*' to '*M or M*' then it find it(the first letter).Can you tell what's the problem?(I use Access)
Mazy
Don't Marry a Person You Can Live With...
Marry Someone You Can Not Live Without
|
|
|
|
|
I need convert a SqlBinary to an interger. What is the best way to do this?
Thanks.
|
|
|
|
|
We are using SQL Server 8, The column size is 1024 when
I try to insert a text that is longer than 255 it gets truncated.
This happens even when I try to insert directly into the table (using Query analyser), -usually we use ADO with VB 6-
I checked the database option to make sure the compatibility option is not set to 6.5, since that would make SQL 8 act
as SQL 6.5 which has a maximum varchar size of 255, and therefore truncates anything bigger.
Help
|
|
|
|
|
O.k I found the answer to this a while ago.
First it was not truncated. In Query analyzer the default max size of the results returned is 255.
Second our ADO module had a hard coded parameter that limited the use of any strings larger than 255.
|
|
|
|
|
The statement:
DELETE FROM License WHERE LicenseID = 4
Should surely delete all rows from the License table that have a LicenseID field equalling 4, but it is not affecting the table License in any way, despite indicating success.
Any ideas? The Row with LicenseID equal to 4 (there is only one) is still there. It is executed on a Microsoft Access database, and the data type of LicenseID is (auto)number, if any of that helps.
After 3 hours my eyes are starting to bleed.
________________
David Wulff
http://www.davidwulff.co.uk
"My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group, there was less competition there" - Gandhi
|
|
|
|
|
What is the result of doing:
SELECT * FROM License WHERE LicenseID = 4 ?
Are there any triggers on the table?
Do you get the same behaviour on other tables?
Cheers,
Simon
If we fail to anticipate the unforeseen or expect the unexpected in the universe of infinite possibilities, then we may find ourselves at the mercy of anyone or anything that can not be programmed, categorized or easily referenced.
|
|
|
|
|
simons wrote:
SELECT * FROM License WHERE LicenseID = 4
That returns, as expected, the row where the LicenseID is equal to 4. I know I must be doing something wrong, as I have managed to delete rows before. (Interestingly enough, I had this same problem before, and eventualyl found a solution, but i'll be damned if I can find where it is now).
________________
David Wulff
http://www.davidwulff.co.uk
"Love your neighbor, but don't get caught" — Murphy's Laws of Sex
|
|
|
|
|
As is said above, basically, you need to specify the columns to operate on as well (you could just delete data from certain columns, something I've never tried). Just bung a * after DELETE, as if you were doing a SELECT operation.
Also, I don't use spaces around '=' in SQL, I don't know if this is permitted or not, you might wanna check that out (presumably if you done it elsewhere it's fine).
The reason it's returning success is because you're not actually telling it to delete anything at all. Hope that helps.
--
Andrew.
|
|
|
|
|
Andrew Peace wrote:
Just bung a * after DELETE, as if you were doing a SELECT operation
That was the first thing I tried, but alas it makes no difference. The row/s are still not being deleted.
Andrew Peace wrote:
Also, I don't use spaces around '=' in SQL
I do when writing the statements in ASP/JScript, as it makes it easier to read with all the " + strVar + "'s floating around in there. It doesn't make any difference though if I remove the spaces.
Andrew Peace wrote:
The reason it's returning success is because you're not actually telling it to delete anything at all.
That is what I had been suspect all along, though I can't understand why it could not be finding the rows.
Thanks for the suggestions though.
________________
David Wulff
http://www.davidwulff.co.uk
"Love your neighbor, but don't get caught" — Murphy's Laws of Sex
|
|
|
|
|
Did you find this problem? If not, I have an idea....
In Access at least, if you set relationships up between tables and use the Enforce Referential Integrity option, delete operations will not occur unless you deleted all related rows in other tables. You can avoid this problem using the 'Cascade' options which will make the changes for you.
--
Andrew.
|
|
|
|
|
I haven't fixed it, no, but what you've just said could be the problem... I'll check tomorrow as it's far to late/early to do anything about it tonight.
Thanks again.
________________
David Wulff
http://www.davidwulff.co.uk
"DON'T MOVE! It can't see you if you don't move" - Sam Neil talking to me as John Simmons walked into the office.
|
|
|
|
|
Unfortunately this was not the problem.
Any further ideas?
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
Not immediately, the following springs to mind but I presume you've already checked these;
1. Is the connection string to the database correct and allowing read/write access? An example of a connection string from one of my programs is;
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName
That's all you should need, the defaults for the rest of the stuff are ok unless you need to use usernames and passwords.
2. Is the command being built as you expect? E.g. make sure that the string actually does equal what you expect when the command is being executed.
3. The database isn't a read-only file (?!) Seems daft but you wouldn't believe the number of times I forget to reset attributes when copying from backup cd's.
Can't think of anything else for now but will try my best to think of something a bit more solid - I know how annoying this kind of thing can be 'cause I'm currently working on a database app too. It's doing my head in 'cause it's got to be written in VB and it's SO repetetive. Maybe a code snippet might be useful if possible?
--
Andrew.
|
|
|
|
|
Andrew Peace wrote:
1. Is the connection string to the database correct and allowing read/write access? An example of a connection string from one of my programs is;
I use that exact same line.
Andrew Peace wrote:
2. Is the command being built as you expect? E.g. make sure that the string actually does equal what you expect when the command is being executed.
I have got into the habit now of always printing any SQL statement I construct dynamically, onto the page so I can check it. I learnt to do that the hard way.
Yes, the SQL statement is correct. If I execute it from within Access itself it will delete the records!
Andrew Peace wrote:
3. The database isn't a read-only file (?!) Seems daft but you wouldn't believe the number of times I forget to reset attributes when copying from backup cd's.
I hadn't checked this before, but I have now and it is not read only. The one I uploaded to the web server was - thanks for pointing that out. At least that's one headache prevented before it happens!
----
Like I said to simons above, I had this problem before and eventually managed to solve it - but i'll be damned if I can remember what it was I did.
What's even more frustrating is that I know this is going to be some stupidly simple problem when we know what is causing it!
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
Okay, seeing as I can't think of anything else I'll post some of my code, you might notice something perhaps.
This comes with the disclaimer that it's in VB and I don't usually do VB ;
Private ConnectionString As String
' Sets the filename of the database to open
'
' IN
' dbFileName - the filename of the database
Public Sub SetDatabaseFilename(dbFileName As String)
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName
End Sub
' Opens the database
'
' IN
' OUT - a connection to the database - close with Connection.Close
Public Function OpenDatabase() As ADODB.Connection
Dim adoconn As New ADODB.Connection
' DEBUG ONLY
SetDatabaseFilename "C:\Documents and Settings\Administrator\My Documents\Main.mdb"
' END DEBUG ONLY
adoconn.Open ConnectionString
Set OpenDatabase = adoconn
End Function
' Removes a customers's record from the database
'
' IN
' ID - ID of the customer record to be deleted
'
' OUT - success?
Public Function RemoveCustomer(ID As Integer) As Boolean
On Error GoTo Fail
Dim adoconn As New ADODB.Connection
Set adoconn = OpenDatabase
adoconn.Execute "DELETE * FROM tblClients WHERE ID=" & ID
adoconn.Close
RemoveCustomer = True
Exit Function
Fail:
RemoveCustomer = False
If IsObject(adoconn) Then
adoconn.Close
End If
End Function For some reason it's taken out some of my blank lines, but anyway, hope that's useful.
--
Andrew.
|
|
|
|
|
I found the problem. Remember when I told you it would be something so simple and also so stupid that we'd laugh afterwards? Well...
I was tring to delete a non existing record, or to put it another way, I was using the incorrect record ID.
Let me explain how this got past all the checks...
The ASP page receives a record ID in data posted from a another page's form. It extracts this ID and stores it in a local variable:
if (Request.QueryString("TransactionID").Count)
strTransactionID = Request("TransactionID");
else
Then, before I delete the record, I need to extract information about it, specifically if the user has permission to do so, and the ID's or linked records in other tables so I can update a couple of related records. I cosntruct the statement like thus:
SELECT Client.ClientID, Transaction.TransactionType, Transaction.TransactionData0, "+
"Transaction.TransactionData1, Transaction.TransactionData2, Transaction.ProductID, "+
"Product.NumberOfLicenses "+
"FROM (Client INNER JOIN Product ON Client.ClientID = Product.ClientID) " +
"INNER JOIN [Transaction] ON Product.ProductID = Transaction.ProductID";
Now, this was where my bug was being introduced. I had forgotten to add a WHERE clause specifing that I only wanted records with TransactionID's matching the one I retrieved above. After I get the records from this, I just checked to see if there were any, then used the various data items to update the relavent linked records, and finally I constructed the SQL statement to delete the record(s) using the TransactionID returned from the SELECT operation (which of course was always the first record in the table, which had now been deleted about eighty times!). What is very worrying is that none of my other test data fell through with the other tables, so I need to do some serious brainstorming as to why that managed to slip through.
When I output the SQL statement to the page so I could check it was correct, I was using the strTransactionID rather than that I used in the DELETE statement. I figured of course that the transaction ID's where the same...
D'oh.
Now who feels the utter idiot?
Thanks for helping me though my mental breakdown, guys! This wasn't the same cause the last time I had similar difficulties though, but at least I can get this out by Monday morning if I pull off another all-nighter tonight.
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
Is your app multi-threading or on the web?
If so, are you using BeginTrans and CommitTans?
Matt Gullett
|
|
|
|
|
The latter, and no.
________________
David Wulff
http://www.davidwulff.co.uk
"I loathe people who keep dogs. They are cowards who haven't got the guts to bite people themselves" - August Strindberg
|
|
|
|
|
In 'SQL Server' security the group login 'DOMAIN/Domain Users' is permitted to use my target database. On Friday (1/18) I was logged into my SQL NT server as 'DOMAIN/Administrator' and the app under development was testing well. When I returned to work on Monday (1/21) the same app withouting any code changes would not execute any SQL queries.
After testing, I discovered that logged in as '(local)/Administrator' the app would work. Logged in as '(local)/Mike' the app would work or logged in as 'DOMAIN/Mike' the app would work. However, logged in with a domain account name other than 'Administrator' that does not exist on the SQL NT server the app fails. So, I know that 'SQL Server' is doing NT Authentication of a sort. It just appears that between Friday and Monday it stopped using the domain controller for authentication.
I have tested to be sure that the DC still considers the SQL NT server to be a part of the domain. I have shutdown and restarted the SQL NT server twice.
I can run the SQL Server Enterprise Manager from other workstations logged into the domain with domain-only user names.
Any suggestions of what to try or what may have changed between Friday and Monday are appreciated.
>>>-----> MikeO
|
|
|
|
|
Hi,
I have a numbric field !
I wanna set Request Value property to this field !
I mean user must FILL the field !!!
How can i do that by SQL statments ??? (i need SQL functions)
My month article: Game programming by DirectX by Lan Mader.
Please visit in: www.geocities.com/hadi_rezaie/index.html
Hadi Rezaie
|
|
|
|
|
I have a server that runs on a configurable number of threads, that process incoming requests and write them to database. Is it advisable to use one OLE-DB connection using the CDataSource class to send all the concurrent database updates? My question is: does one SQL query on the connection block another, if SQL server itself does not cause locking? or put in different words, do the connection wait for one command to finish before starting another?
Thanks for any help ...
Thomas
modified 29-Aug-18 21:01pm.
|
|
|
|
|
There is a question to me:
- I have all rights to Control the Server (but I have no permission to Setup SQL Server on it)
- I have a database file (Save from other Server had setup SQL Server and copy to my Server)
- I must Access and Update the Database..
--> How to access a SQL Server database while i have no permission to setup SQL Server or DLL on my Server?????????
I have no solution...
If you have any ideas please help me by email to me at: newcinc@hcm.fpt.vn
(Sorry if my English is not good...)
Thanks for your reading
Hai Binh (Vietnam)
http://go.to/JSLib
|
|
|
|
|
You need to Update the SQL Server, or the database, Because, you can import the database from other database.
If you have other sql servr running with the database that you want to get, you can import the database to your SQL Server...
Best Regards....
Is Friday, and Party!!!
My software never has bugs. It just develops random features.
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
Without creating a DSN how do I specify that the connection should use TCP/IP?
I'm using ADO.
<reason behind my question>
I need to connect to a remote SQL Server and the only thing our firewall lets through is TCP/IP. I could just specify the IP address, but that means if we have to change it for some reason or another, the program no longer works; so I point it to a hostname (database.mycompany.com) which will always resolve to the correct IP.
My current workaround uses a DSN with the client configured to TCP/IP, changing it to another protocol breaks the application (thus I know that TCP/IP is what makes it work).
<reason>
TIA,
James
Sonork ID: 100.11138 - Hasaki
"Not be to confused with 'The VD Project'. Which would be a very bad pr0n flick. " - Michael P Butler Jan. 18, 2002
|
|
|
|