|
nop, you don't have it.
SELECT komponenter
FROM recept
WHERE komponenter LIKE 'item%'
ORDER BY komponenter ASC;
this will return :
item1<br />
item2<br />
item3<br />
item4<br />
item5
well, tell me, i don't know what database you're using in your SW, but you might have tool to test SQL statements before updating your whole code and test it each time you change your query...
(Entreprise Manager with SQL Server 2000, SQL*Plus with Oracle DB...)
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
You may also want to consider the IN Syntax if you have the full id:
SELECT * FROM MyTable WHERE MyField IN('Value1','Value2','Value3')
|
|
|
|
|
OK i've used just Microsoft Access database for my application made in c#.
Now i want to know how can use mySQL database.
My questions are the following:
1)How can create a new mysql database within my c# application? (using just sintax with .NET objects or using VisualStudio tools)
2)What i need installed on my (for developing) computer?
3)What is needed installed on the final user computer?
4)I know how create and manageing database in mySQL(using the daemon) so i want to know what are the differences with mySQL and SQL.
Thanks.
|
|
|
|
|
Sasuko,
You may want to visit MySQl's site and look at their .net connector in the tools section.
You may just need to distribute the .dll file with your application.
I hope this is a start for you 
Paul
|
|
|
|
|
so the final user doesn't need the mysql server installed on its pc?
And where can i find the database (where is the file?) i've created before and i want to distribuite with my application?
What directory have i to put the database in during the installation?
|
|
|
|
|
The user should not need to have a mySQL server installed on hir or her machine. Your application should point to the server in the connection string. You'll need to dig around with your mySQL server installation for the database file. If there is an existing database server, you should not have to install a database file anywheres.
|
|
|
|
|
Hola!
Is it possible to so something like this with SQL Server 2005 (in a SPROC)?
DECLARE @Variable varchar(1000)
SET @Variable = 'LastName ASC, FirstName ASC'
SELECT * FROM table ORDER BY @Variable
This doesn't work for sure... but is there a known workaround? I don't want to do "EXEC @sql" for performance and good programming practice reasons.
Thanks!
|
|
|
|
|
ITs not clear to me why you don't just write 'LastName ASC, FirstName ASC' into your query in the first place. If you do have a good reason, then you'll have to write a dynamic query and make use of EXEC @sql. If you're worried about performance, you should know that the optimizer WILL be able to optimize dynamic queries too. It remembers the query plan from that last query so as long as the query isn't going to change too much, you shouldn't have to worry. The only other danger with dynamic queries is that someone would try to inject SQL into the query. If you're using ASP.NET, ValidateRequest is turned on by default.
|
|
|
|
|
The reason is pretty simple, I want the user to be able to sort data by whatever they want. The query I posted was just an example. The real query is about 1 screen long and has 37 parameters.
|
|
|
|
|
I like Edbert's solution (CASE statement in the ORDER BY clause) if the user can only order by one field. However, if you want to support sorting by multiple fields, I think that the dynamic query method will be easier to implement.
|
|
|
|
|
You can't use ORDER BY @Variable.
However, you can use CASE statements to do ORDER BY, e.g.:
SELECT * FROM table
ORDER BY
CASE WHEN @Variable = 'LastName' THEN
LastName
CASE WHEN @Variable = 'FirstName' THEN
FirstName
END ASC
Note: this is limited to one field only. But theoritically you can combine it with another case statement below to order by several columns.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
 Hi all. I have a problem to solve with an MS Access database. In a login form the user have to enter username and password. In the back there is a table called users witch have the fallowing fields: userid, username, userpass. I wrote the fallowing code to check if the username and password match with those in the table users:
Private sub command2_click()
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Private Sub Command2_Click()
Dim qdef1, qdef2 As QueryDef
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Set qdef1 = CurrentDb.CreateQueryDef("")
With qdef1
.SQL = "SELECT * FROM users " & "WHERE username= '" & [Forms]![form1]![input_user] & "'" & "and userpass= '" & [Forms]![form1]![input_pass] & "'"
Set rst = qdef1.OpenRecordset()
nri = rst.RecordCount
End With
rst1.FindFirst "username= '" & Me.input_user & "'"
If rst1.NoMatch Then
MsgBox "There is not such user!"
Exit Sub
Else
If nri = 0 Then
MsgBox "Password does not match!"
Exit Sub
Else
' Everythiong is ok... go on and do the stuff!!!
End If
End If
End Sub
My question is if there is any possibility to optimize this code, to make it simple.
Thank you very much.
Respect,
Gabriel
|
|
|
|
|
Aside from a note that you should try using parameters instead of concatenating your SQL statements, the only optimization I can see is this:
Set rst = qdef1.OpenRecordset()
If rst.EOF Then
MsgBox "Not Found"
Exit Sub
Else
MsgBox "Found. Do whatever you want"
End If
First, your SQL statement will not work as you're concatenating the apostrophe with the and operand, see: username= '" & [Forms]![form1]![input_user] & "'" & "and userpass=. You should put a space before the and operand.
Second, your SQL statement will only return a record(s) that have the same username AND userpass, therefore it will not be able to tell if there are no records returned whether it is because the username does not exist or the password is wrong. You do not want to tell users whether it is the password or the username that is wrong. Just tell them username or password is invalid.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Hi. you suggest using parameters and by saying that u refer to using querys? because I was thinking is much more easier to user a query in query builder and then just to refer to it in the code... I was telling you I'm a newbie. Now... the question is: how can I do that?
Thank you.
Gabriel - Romania
|
|
|
|
|
Excuse me if this is old news, I had some run time problems using easy_odbc in conjunction with the MSaccess ODBC driver. The problems were to do with ODBC exception handling for the most part. I had a freind look at it and he came up with some suggestions for patches. Following is the output of a WINDIF compare of the original and patched versions of easy_odbc.cpp and test.cpp. The changes are a bit over my head but the original problems are well and truly fixed, thought this might be useful to someone. On a related subject, is there any way to insert the contents of a variable in line with an SQL query? Something like the following:
// fills a ResultSet with some data
easyodbc::ResultSet rslt = db.ExecuteQuery("SELECT * FROM kids" WHERE name =
MYVARIABLE);
Patch for easy_odbc.cpp:
long Database::Execute(const char* sql) {
try {
if(!this->m_bOpened) {
throw EasyODBCException("Call Open() before
Execute()",-1);
}
<! SQLRETURN ret = SQLPrepare(this->m_hStmt,(unsigned
char*)sql,
!> SQLFreeStmt(this->m_hStmt,SQL_CLOSE);
!> SQLRETURN ret = SQLAllocStmt(this->m_hDbc, &this->m_hStmt);
!> if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0)
{
!> HandleError("DBC");
!> }
!>
!> ret = SQLPrepare(this->m_hStmt,(unsigned char*)sql,
Patch for test.cpp:
// closes the database connection
db.Close();
<! }catch(easyodbc::EasyODBCException *ex) {
!> }catch(easyodbc::EasyODBCException ex) {
<! char buff[51];
<! ex->GetMessage(buff);
<! printf(buff);
!> char buff[2000] = {0};
!> ex.GetMessage(buff);
!> printf("%s\n", buff);
}
return 0;
}
--
Regards
Michel de la Villefromoy
|
|
|
|
|
I have a client server application (Powerbuilder) using Oracle database as the backend. My problem is how to connect to the same database from a remote location using the same application. I amusing ODBC for my connection currently. Kindly advice!
Francis
|
|
|
|
|
Hello guys passed exam 70-228 can I now opt for 229?
What is the systematic order for this exams?
Any advice from any one will be appreciated!!
|
|
|
|
|
jaygreen9 wrote: What is the systematic order for this exams?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have defined the following code:
BEGIN TRANSACTION TCQ11997
SELECT * INTO Q1_1997 FROM Orders_1997
WHERE OrderDate > '12/31/1996' AND OrderDate < '1/4/1997'
SAVE TRANSACTION TCQ
BEGIN TRANSACTION TCQ1View
SELECT * FROM Q1_1997
ROLLBACK TRANSACTION TCQ
COMMIT TRANSACTION
First transaction will create a CLONE table (it is guaranteed) to work. The work upto this point is saved in a SAVE POINT
called TCQ.
The second transaction will fail (I explicitly want this to fail - to simulate "real life" Error).
Now : i am curious running this code should Create the CLONE Table (named TCQ1997) because of the save point. But It doesn't. As a matter of fact, after running this code, SQL Enterprise Manager Crashes. What am I doing wrong here?
|
|
|
|
|
Hi, I'm running into a situation:
<br />
trx = conn.BeginTransaction();<br />
acc = AccBean.Load(acc_id, conn);<br />
request = RequestBean.Load(request_id, conn);<br />
<br />
if (acc.Balance >= request.Amount) {<br />
<br />
request.Status = Status.APPROVED;<br />
<br />
acc.Balance = acc.Balance - request.Amount;<br />
}<br />
trx.Commit();<br />
This works fine. But one of our DBA insisted on STEP 3 being done by UPDATE trigger - and the code became:
<br />
trx = conn.BeginTransaction();<br />
acc = AccBean.Load(acc_id, conn);<br />
request = RequestBean.Load(request_id, conn);<br />
<br />
if (acc.Balance >= request.Amount) {<br />
<br />
request.Status = Status.APPROVED;<br />
<br />
}<br />
trx.Commit();<br />
The problem is, the trigger is trying to update "acc", which is locked during the duration of the transaction. Is there anyway to get around this? Or is UPDATE TRIGGER a bad idea (TRIGGER isn't executed in the same transaction context)? What I found is that the UPDATE TRIGGER was just never fired.
I assume it was never fired because it's outside the transaction and was therefore blocked - correct me if I'm wrong. Under what context is TRIGGER being executed? However, I'm seeing conflicting information:
A trigger is part of the transaction started by the statement that fired it. Thus, if the trigger fails the firing statement fails and is rolled back.
If this is true, why the hell my UPDATE TRIGGER never updated "acc"??
Reference: http://www.dotnetjohn.com/articles.aspx?articleid=133
Thanks in advance.
|
|
|
|
|
May be just a hint, try AFTER UPDATE trigger. I dont like to use triggers.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
CREATE TRIGGER trg_mytriggger ON XXX FOR UPDATE
is same as
CREATE TRIGGER trg_mytriggger ON XXX FOR AFTER UPDATE
"AFTER" is DEFAULT.
|
|
|
|
|
hi Everyone,
I am using ADOMD.net and i am getting error Database 'foodmart 2000 does not exist.
whenever i use mdx sample i can connect to other machines.
But when i use dll of .net in a process it is giving error "Database 'foodmart 2000 does not exist." for only when i connect to remote machine. On local machine it works.
Even in an exe it works for remote machine also .But it does not work in Vc++ dll with .net dll(adomd.net functions exports).
i did not find any way to find out the reason.
please guide me .
thanks in advance.
kuldeep kumar 
|
|
|
|
|
Please excuse my ignorance but I'm new to T-SQL and am having a problem. I have a proc that I want to send three parameters too, but only have it search on the paramters if they are valid (above 0).
In the following example I am filtering on County, Town and Type. But only want to filter if the value of the parameter is included. So if they only pass in county, then thats all I want to filter on.
Any ideas?
<br />
CREATE PROCEDURE FeatureBusinessByTown @County int, @Town int, @Type int AS<br />
<br />
Select Top 5 BusinessID<br />
FROM SELECT TOP 5 fbisBusinessLookup.BusinessID<br />
FROM fbisBusinessLookup INNER JOIN<br />
DIRECTORY ON fbisBusinessLookup.BusinessID = directory.[id]<br />
WHERE <br />
<br />
IF (@county > 0) THEN "(fbisBusinessLookup.countyid = @County)"<br />
IF (@Town > 0) THEN " AND fbisBusinessLookup.TownID = @Town"<br />
IF (@Type>0) THEN " AND fbisBusinessLookup.TypeID = @Type"<br />
<br />
ORDER BY NEWID()) DERIVEDTBL<br />
GO<br />
Definitely a PEBCAK! (Problem Exists Between Keyboard And Chair)
www.FruitBatInShades.com
|
|
|
|
|
You can do this with clever use of AND/OR clauses
Select Top 5 BusinessID
FROM SELECT TOP 5 fbisBusinessLookup.BusinessID
FROM fbisBusinessLookup INNER JOIN
DIRECTORY ON fbisBusinessLookup.BusinessID = directory.[id]
WHERE ((@country <= 0) OR (@country > 0 AND fbisBusinessLookup.countyid = @County))
AND ((@Town <= 0) OR (@Town > 0 AND fbisBusinessLookup.TownID = @Town))
AND ((@Type <= 0) OR (@Type > 0 AND fbisBusinessLookup.TypeID = @Type))
ORDER BY NEWID()) DERIVEDTBL
Note, the above doesn account for nulls in @country, @Town or @Type.
You can also declare a large enough varchar and concatenate a SQL command together then use EXEC(@sqlString) However, if you can do without that then I'd recommend it because there is a security risk in building a SQL command and executing it dynamically.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
