|
Hi,
I have a problem with sql:
ALTER PROCEDURE dbo.GetGuestbookData<br />
( <br />
@SortOrder varchar(64)<br />
)<br />
AS<br />
<br />
SELECT *<br />
FROM Guestbook INNER JOIN Users<br />
ON Guestbook.AuthorID = Users.RecordID<br />
ORDER BY Guestbook.RecordID +'@SortOrder'<br />
RETURN
I am trying how to write a proc. which retrieves two tables sorted in order which user wants (ASC/DESC) but I don't know how to write it in code according to the value of my variable
-
ORDER BY Guestbook.RecordID ASC/DESC
Thanks.
|
|
|
|
|
Oh, it was quite easy answer (and stupid question  )
I made a if clause here:
<code>CREATE PROCEDURE dbo.GetGuestbookData
(
@SortOrder varchar(64)
)
AS
IF @SortOrder='ASC'
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID ASC
END
ELSE
BEGIN
SELECT *
FROM Guestbook INNER JOIN Users
ON Guestbook.AuthorID = Users.RecordID
ORDER BY Guestbook.RecordID DESC
END
RETURN </code>
|
|
|
|
|
Urgent! Please help!
Ok. Here is the situation:
Want to use ADO to work with MS Access, BUT!
I want to make ADO to use MS Access OLE DB provider instead of the ODBC. So I'm trying to use Open method of the conenction object in ADO but:
1. if I try to pass full connection string like so:
"PROVIDER=Microsoft.Jet.OLEDB.4.0;DSN=C:\mydb.mdb; UID=; PWD=";
I get "Could not find installable ISAM" error. Then I found MSDN article that says that connection string parameters weren't allowed at open time it has to be just the provider name. Set everything else after the initial success. Ok so I try :
2. to call Open method of Connection object with just "PROVIDER=Microsoft.Jet.OLEDB.4.0" and empty user name and password. Then I get "Authentication failed." But I do not have any authentication for that db set up. Even more - what Authentication if no db was provided yet?
When I was using OLE DB directly I had "Authentication failed" issue come up one but that was solved by NOT setting properties at all - skipping (I remember empty strings didn't help). But with ADO Open method requires those two parameters for user and password which I have no idea how to skip.
So!
How in the world do you connect to an MDB with ADO via OLEDB provider from C++ in VC 6?
Urgent! Please help!
Thank you very much!
|
|
|
|
|
|
I can't remember on the recordset.first which I had it in ASP.
When comes to ASP.NET, I can't find it.
Is there anyway to declare it?
|
|
|
|
|
I didn't get your doubt. Are u talking about Ado.Net or Ado ?
Sreejith Nair
[ My Articles ]
|
|
|
|
|
I'm talking about ASP.NET.
Do they have this BOF and EOF?
|
|
|
|
|
ASP.NET doesn't handle databases. ASP = Active Server Pages. In other words it is a web application framework.
ADO.NET is the database interface technology in the .NET Framework.
DotNet wrote:
Do they have this BOF and EOF?
No. ADO.NET uses a different model for accessing the database.
You can access the database in one of two ways.
Using a DataAdapter with DataSets or (for forward & read only operations) you can use a DataReader
You might also want to read this article[^] on the differences and migration between classic ADO and ADO.NET
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
how can add a record into db
|
|
|
|
|
Well, this question is totally new topic.
So please post it to a new section.
Thanks.
|
|
|
|
|
Hi,
I have a problem with handling errors from accessing SQL database. When there is an error, SqlException returns a message, such as
"Violation of UNIQUE KEY constraint 'CK_Site_SiteName'. Cannot insert duplicate key in object 'Site'."
However, My application requires to know which type the error belongs to so that it can process properly. Example, if it catches the above error, it will notify user that "Choose another name."
Give me some ideas, thanks in advance.
be kunkun
kunkun
|
|
|
|
|
The SqlException.Number[^] property will give you the error number that SQL uses, and if you create your own error messages, it will give you the number for that too.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Hi, I'm using ADO .NET but sometimes I get an InvalidOperationException:
ExecuteReader requires an open and available Connection. The connection's current state is Open, Executing.
The class where I'm doing the db stuff has the following in it:
<br />
MyDB::MyDB() {<br />
String* connection = String::Concat(S"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=",<br />
getPath(), S"\\MyDB.mdb;");<br />
m_pDB = new OleDbConnection(connection);<br />
m_pDB->Open();<br />
}<br />
<br />
void MyDB::Query(String* query){<br />
<br />
try {<br />
m_pTable = NULL;<br />
m_pDA = new OleDbDataAdapter(query, m_pDB);<br />
m_pDataSet = new DataSet(" Database");<br />
<br />
m_pDA->Fill(m_pDataSet, "Results");<br />
m_pTable = m_pDataSet->get_Tables()->get_Item("Results");<br />
} catch (Exception* e) {<br />
a = e->GetType()->ToString();<br />
b = e->Message;<br />
c= e->StackTrace;<br />
}<br />
}<br />
<br />
}<br />
There more, but I think the problem's in there. I'd guess I'm running into problems with different threads using the database concurrently? What should I be doing differently?
Thank you, let me know if you need more information.
|
|
|
|
|
Hi,
I have a free time after couple of weeks of work, so I decided to finish my project in asp.net and SQL - I have a table Users with user credentials such as name, nickname, email, motto, avatar, ... and now I want to make a guestbook (more comfortable) with all these features like avatar... Although I don't know what to do? Should I create a table "Guestbook" there columns "Nickname" or "RecordID" and "Comment" and then use a repeater, or rather datalist and for each record in Guestbook table find appropriate user details in Users table and add them to datalist? Or it is easier to save these user details along with each record in Guestbook, when user writes it? (I don't think it is a good idea). I would prefer the first way of doing this, but I haven't a clue how to do it - I am a begginer, I have already written a page with repeater, but I used msdn more than anything else, and I think there should be used stored procedures, aren't I? So I ask you, could you help me with this, could you give me an idea (or the sql proc and the code to bind database with datalist  ). Thanks in advance.
|
|
|
|
|
Okay, so you have:
User table
----------
Name
Nickname
Email
Motto
Avatar
Guestbook
---------
Nickname or RecordID
Comment
So, some questions you have to ask yourself are:
How do I link the Guestbook table to the user table? For instance, is the Nickname going to be unique? If so then it is a candidate for being the primary key on the User table, and therefore the join field (aka foreign key field) on the Guestbook table.
Am I going to allow more than one comment per user? If you are, then you need an extra field on your guestbook to become the primary key. So maybe you need the Nickname (to join to the user - if that is the User table's primary key) AND the RecordID (to identify each guestbook entry).
David Fawn wrote:
I think there should be used stored procedures, aren't I?
In my opinion, yes, you should use stored procedures. Others have their own opinion. Click here[^] if you want to know the benefits of stored procedures.
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Colin Angus Mackay wrote:
How do I link the Guestbook table to the user table? For instance, is the Nickname going to be unique? If so then it is a candidate for being the primary key on the User table, and therefore the join field (aka foreign key field) on the Guestbook table.
Both nickname and RecordID(=prim. key) are unique in Users table.
Colin Angus Mackay wrote:
Am I going to allow more than one comment per user? If you are, then you need an extra field on your guestbook to become the primary key. So maybe you need the Nickname (to join to the user - if that is the User table's primary key) AND the RecordID (to identify each guestbook entry).
Yes, there will be many comments by the same user (in fact it's discussion, but we don't want to discuss in thread view, but in "guestbook-like" view) I had an idea to connect these two tables together (based on "RecordID" in the Users and "IDofAuthor" in the Guestbook or on "Nickname" in Users and "Nickname" in Guestbook but I don't know if it is possible and how to do it (in stored proc.) and how to bind it with datalist.
Thanks
|
|
|
|
|
Method 1:<br />
myProc <br />
@outparam INT OUTPUT<br />
AS<br />
[insert sql here]<br />
SELECT @outparam == @@IDENTITY
Method 2:<br />
<br />
myProc<br />
<br />
[insert sql here]<br />
SELECT @@IDENTITY
What would be the prefered method? Is there a best practice? I know method 2 is faster and I'm trying to educate myself if there is a better way.
|
|
|
|
|
I don't think it really matters, depending on what db library your clients are using the second would probably be easier to code on the client side, no parameters to define.
|
|
|
|
|
Those are pretty much your options if you choose to have your Id's autoincremented by the database.
The alternative is to generate ids in your code, and then assign the id before saving. For inserts, this is much faster. It also makes writing code easier, because you can assign foreign keys before ever saving the "parent".
There are a few techniques for doing this, but all of them require using a bigger number than an INT.
my blog
|
|
|
|
|
I normally do this:
CREATE PROCEDURE dbo.data_ins
@Ident bigint OUTPUT = NULL,
@Data1 varchar(10),
@Yadda int
AS
-- Set the OUTPUT parameter
-- (Note that SCOPE_IDENTITY() is trigger-safe,
-- whereas @@identity isn't.)
SET @Ident = SCOPE_IDENTITY()
-- Select the record just inserted
-- can be used to retrieve new identity and check
-- defaults, etc.
SELECT * FROM myTable WHERE ident = @Ident
RETURN @Ident
GO
This gives the developer using the procedure three separate options for retrieving the new identity.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
A bit over kill if you ask me.
RETURN @Ident should fail. RETURN only supports INT not BIGINT.
|
|
|
|
|
It will only fail if the identity ever reaches a point where it's actually too large to be CAST as an int.
If you already know how you want to do it, then why don't you just do it that way instead of asking for advice you aren't going to even consider?
We have 8 developers here and about 30 separate database applications that in some cases share stored procs. What you think of as overkill, some think of as tyrannically restrictive.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Grimolfr wrote:
It will only fail if the identity ever reaches a point where it's actually too large to be CAST as an int.
Since the identity field is defined as BIGINT I think there is a strong possibility that it may reach a point where it is larger than an INT. If you don't expect it to reach this point, then don't make it a BIGINT. Any code that uses this proc will either need to be rewritten when identity does become larger, or written up front to handle the exception. But why design an app with know flaw or code for a preventable error?
Grimolfr wrote:
If you already know how you want to do it, then why don't you just do it that way instead of asking for advice you aren't going to even consider?
I did not ask for opinions or advice. In any case, giving all possible answers is neither an opinion nor advice.
|
|
|
|
|
In the datagrid.aspx, there is a Edit Hyperlink. When you click on the current page, it will go to customer.aspx.
Whatever is in the datagrid - "data binded", will be bind on the (i.e, txtPCID.text, txtName.text, txtPrice.text, txtDescription.text).
Is there anyway to do that?
Can I bind more than 1 field in a HyperlinkColumn?
If so, how?
|
|
|
|
|
Although I'm not exactly sure what you want,
you could always use a template column and put whatever controls you want to appear in the column there.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
