|
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
|
|
|
|
|
You can't use IF or CASE statements in a WHERE clause with SQL Server 2000.
I would follow the advice posted below and store the query contents in a varchar, @SQL_STATEMENT. When you get the the WHERE clause, you can concatenate the onto the WHERE clause your criterion. To get around having to include logic to decide wether to include 'AND', build the query with ... WHERE 1=1. Then concatenate 'AND X = @X' IF @X > 0.
After this, you can do an exec(@SQL_STATEMENT). I don't see anything wrong with this as the query optimizer will still be able to optimize the query. THe only thing you have to be careful of with these is that the calling code doesn't allow any SQL to make it into the dynamic stored proc. Asp.net will do this for you unless you turn it off. It's an @Page directive called ValidateRequest I think...
Good luck
|
|
|
|
|
Hello all,
I'm a newbie in C#. For testing I use access as database.
I have defined a dataset with a table. This table is used as datasource for my datagrid. On the table I have defined two eventhandlers for RowDeleted and RowChanged. Every time a row in the grid is added, deleted or changed the event is triggered.
In the Eventhandlers I create the Insert-, Update- and Delete-Commands for my DataAdapter and then I call the DataAdapter.Update(dataTable)-method.
The deletes and updates work fine. But Inserts have not an effect to the database. When I ask my dataset for Changes after an insert with(dataset.hasChanges()). I don't get any changes.
Can anybody help me with my problem?
Thanks in advance,
Frank
|
|
|
|
|
I have two tables : "tblCustomers" and "tblAddresses". Between these two tables there excists a one to many relation, based on the the field "CustomerId", which is a primary key in the table "tblCustomers" and a foreign key in the table "tblAddresses"
Both of them are loaded in a dataset "ds" when loading my Data Entry Form "Customers". The Entry form has besides the neccessary textboxes to enter the data for the table "tblCustomers" also a datagrid "grdAddresses" . This table must contain all the addresses from the customers. Some customers can have more then one addresses ( e.g. one home-address and two or more delivery-addresses.) There is a button "Addresses" on the form, when clicked, popup another data entry form, into which i must can enter the addresses for the current customer. Once a address is entered, these data must be shown in the datagrid "grdAddresses" on the main form "Customers". However, these datagrid may not display all the fields that excists in the table "tblAddresses".( e.g. "customerID" , because this data is already shown in the main form. I can't get it done to realize this.
It is possible if i make the following settings for the datagrid:
grdAddresses.Datasource = "tblAddresses", and then make the neccesary settings in the property window.
But in order to have a synchronisation between de data in the main form and the data in the datagrid i must have the following settings:
grdAddresses.DataSource : ds
grdAddresses.DataMember = "tblCustomers.tblAddresses"
With such a settings i can't make it done.
Can somehelp me ?,
|
|
|
|
|
I'm not exactly sure on this as I haven't done datagrid binding for some time.
You probably can create a DataView based on your DataSet which contains only specific columns you want, and then bind the grid to the DataView.
As far as I know, this will work for reading purposes only (not if you want to add a new record to the DataSet).
You'll have to edit the DataSet through code (which seems to be what you're doing) and once it's edited the changes will be reflected to the DataView and therefore the grid.
Does it help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Hi All
I am going mental trying to insert some data from a webform thru ado.net by use of a sproc.
Heres my code:
dim sqlConn as new sqlConnection(data......)
dim sqlda as new sqldataadapter("storedprocedure", sqlconn)
dim sqlds as new dataset
sqlda.insertcommand.commandtype=commandtype.storedprocedure
sqlda.insertcommand.parameters.add("@EmpId", control.text)
sqlda.Insertcommand.executenonquery()
sqlconn.close()
---------------->
The code compiles with no errors I run the project seems like it does something but does not write to the database. I have verified the stored procedure works via query analyser so I tried google but amazingly there is not alot..
Please help me im in programmer hell..
Thanks
-- modified at 5:47 Wednesday 26th October, 2005
|
|
|
|
|
You do not need to use SqlDataAdapter if you are not updating your data through a DataSet.
Create an SqlCommand instead, specify the connection, the sql string, and the parameters.
Then execute the SqlCommand and you'll have your data updated.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Thanks Ed. Just did it now... Much simpler and it worked better yet...
Thanks
Dom
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
