|
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.
|
|
|
|
|
Using a template column, as suggested, allows you to control your binding pretty precisely. So you can have as many asp controls inside a single column as you want, with appropriate databinding expressions for each.
Keep in mind, that you can also have a databinding expression that calls a function in your code behind file.
For example, you could have this function in your code:
Public Function GetAutoAssignIndex(ByVal AutoAssign As Boolean) As Integer
Dim index As Integer = 0
If AutoAssign = True Then
index = 1
End If
Return index
End Function
Your databind might look similar to this:
<asp:dropdownlist id="ddlEditAutoAssign" cssclass="Normal" selectedindex="<%# GetAutoAssignIndex(DataBinder.eval(Container.DataItem, "AutoAssign")) %>" width="90%" runat="server">
This example is for a dropdown containing objects with an "AutoAssign" property defined.
|
|
|
|
|
I would like to start working with OODBs and OQL, are there any suggestions on where to get started? Also can you use ADO.NET to connect to an Object Oriented Database, or are there other methods? Also is there any free Object Oriented Databases available like mySQL for relational databases?
Thanks in advance,
Sef
|
|
|
|
|
I can't guide you for OODB And OQL. Cause i am not in a position to say something about that. About Ado.net, In Asp.Net 2.0 microsoft included ObjectSpace (technology to access table in the form of objects) for Object Oriented Databases. Beta version of 2.0 is avilable. I am not sure about full version of Ado.Net 2.0
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Hi,
I have some strange problems with the update of a dataadapter.
First, I read a row in a table, through a dataset :
da = New SqlDataAdapter(sql, conn)<br />
ds = New DataSet<br />
da.Fill(ds, "MyTable")<br />
da.SelectCommand.CommandText = "select * from address"<br />
Dim sqlBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)<br />
da.InsertCommand = sqlBuilder.GetInsertCommand()<br />
da.DeleteCommand = sqlBuilder.GetDeleteCommand()<br />
da.UpdateCommand = sqlBuilder.GetUpdateCommand()<br />
<br />
ta = ds.Tables("MyTable")<br />
<br />
pka(0) = ta.Columns("address_id")<br />
ta.PrimaryKey = pka<br />
<br />
dim dr As DataRow<br />
<br />
dr = ta.Rows.Find(1) 'get the address with id 1
Then I update a field :
dr("country_id") = system.dbnull.value
and now I commit the change :
da.update(ds)
But changes are not reflected in the database....
The update command doesn't work with this update. If country_id value is not null, it works...
I precise that country_id is nullable in the DB.
Thanks for any help.
Best regards.
Arthur
|
|
|
|
|
See some senario we can't predict that user will give correct, valid data. So i recommand you to define an update,delete command by yourself and execute the command. And assign this defined queary to respective delete,update command of your data adapter
Sreejith Nair
[ My Articles ]
|
|
|
|
|
I dont know whether my suggestion is right. first check the entries are null or not. If null update with ''. ie blank. does this answer the question. sreejiths answer is a good way but working with insert,delete and update query in data adapter is not that easy.
(Behind the truth)
|
|
|
|
|
I have a chat application. It requires that all information of users, rooms and messages have to stored in database. However, users edit their messages with richtextbox control, so I want to save messages with their format. How can I do? Somebody said that I should save them using XML format or HTML script, but I never do it. Please help me!!!
Thanks a lot.
kunkun
|
|
|
|
|
Have you thought about storing the RTF from the Rich Text Box in an image field in the database?
Store your favourite bookmarks online: my-faves.co.uk
|
|
|
|
|
Thanks a lot, Davey
I've tried it and I find it good.
kunkun
|
|
|
|
|
Hi !
(Sorry for my bad english  )
I use datatable and i would search if a row exist, i've tried with the Select method but in some rows contains {null} values and i c'ant find how i'll passed this values in the Select string
<br />
#I Work in VB.NET<br />
<br />
dim filter as String = "COL1 = 1 AND COL2 = null"<br />
If ldataset.Tables("Table1").Select(filter).length Then<br />
....:doh:<br />
End If<br />
Thanks in advance 
|
|
|
|
|
Try This
<br />
filter = "COL1 = 1 AND IsNull(COL2,-1)=-1" (if COL2 is numeric)<br />
<br />
filter = "COL1 = 1 AND IsNull(COL2,'')=''" (if COL2 is string)<br />
Free your mind...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
