Re: Output vs select - Database Discussion Boards

David Fawn8-Oct-04 4:15

8-Oct-04 4:15

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

Output vs select

Not Active7-Oct-04 7:37

Not Active

7-Oct-04 7:37

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.

Re: Output vs select

Kurt _B7-Oct-04 13:00

Kurt _B

7-Oct-04 13:00

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.

Re: Output vs select

Steven Campbell8-Oct-04 7:11

Steven Campbell

8-Oct-04 7:11

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

Re: Output vs select

Grimolfr12-Oct-04 4:43

Grimolfr

12-Oct-04 4:43

I normally do this:

CREATE PROCEDURE dbo.data_ins
    @Ident bigint OUTPUT = NULL,
    @Data1 varchar(10),
    @Yadda int
AS

/* Insertion Query */

-- 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

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: Output vs select

Not Active12-Oct-04 6:09

Not Active

12-Oct-04 6:09

A bit over kill if you ask me.

RETURN @Ident should fail. RETURN only supports INT not BIGINT.

Re: Output vs select

Grimolfr12-Oct-04 7:59

Grimolfr

12-Oct-04 7:59

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

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: Output vs select

Not Active12-Oct-04 9:54

Not Active

12-Oct-04 9:54

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.

Can I bind more than 1 field in a HyperlinkColumn?

DotNet7-Oct-04 7:14

DotNet

7-Oct-04 7:14

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?

Re: Can I bind more than 1 field in a HyperlinkColumn?

Not Active7-Oct-04 7:32

Not Active

7-Oct-04 7:32

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.

Re: Can I bind more than 1 field in a HyperlinkColumn?

Michael Winburn7-Oct-04 7:40

Michael Winburn

7-Oct-04 7:40

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.

OQL: Where to get started?

sefaen6-Oct-04 19:50

sefaen

6-Oct-04 19:50

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

Re: OQL: Where to get started?

sreejith ss nair7-Oct-04 1:15

sreejith ss nair

7-Oct-04 1:15

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 ]

dataAdapter update problem

PJ-Intellia6-Oct-04 3:32

PJ-Intellia

6-Oct-04 3:32

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

Re: dataAdapter update problem

sreejith ss nair7-Oct-04 1:20

sreejith ss nair

7-Oct-04 1:20

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 ]

Re: dataAdapter update problem

shambho12-Oct-04 2:13

shambho

12-Oct-04 2:13

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)

Anandah

Save text with its format to database?

bekunkun6-Oct-04 0:39

bekunkun

6-Oct-04 0:39

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

Re: Save text with its format to database?

David Salter6-Oct-04 10:27

David Salter

6-Oct-04 10:27

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

Re: Save text with its format to database?

bekunkun6-Oct-04 22:29

bekunkun

6-Oct-04 22:29

Thanks a lot, Davey
I've tried it and I find it good.

kunkun

DataTable.Select()

amatyasik5-Oct-04 5:57

amatyasik

5-Oct-04 5:57

Hi !
(Sorry for my bad english Poke tongue | ;-P

)
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 Big Grin | :-D

Re: DataTable.Select()

Guillermo Rivero5-Oct-04 6:15

Guillermo Rivero

5-Oct-04 6:15

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...

Re: DataTable.Select()

EdbertP5-Oct-04 16:59

EdbertP

5-Oct-04 16:59

Shouldn't it be "COL2 IS NULL" ?

Re: DataTable.Select()

amatyasik5-Oct-04 21:28

amatyasik

5-Oct-04 21:28

thanks to everyone.... Big Grin | :-D

it's so easy .... OMG | :OMG:

error when calling stored procedure in ole db

sudheer_ars4-Oct-04 20:28

sudheer_ars

4-Oct-04 20:28

Hi,
I am new to OLE DB. I created a simple stored procedure that insert
given data to a table. when I tried to call Open function of the wizard
generated ole db class, it fails with a HRESULT value : [-2147217887]. (the
error lookup tool returns 'No such Message' for this value). While debuging
i found, the Execute function is returning this value.

My code is given below. Is it correct ?

CdbospTestTable1 m_db;

m_db.m_test1 = 111;
m_db.m_test2 = 222;

HRESULT hr = m_db.Open();
if(FAILED(hr))
AfxMessageBox("Error : Open");

When I created OLE DB consumer class, selecting table, then the insertion
succeeded.

The creation of the class was :
Insert => New Atl Object .
Data Access : Consumer
OLE DB provider : OLE DB provider for Sql Server
Type property : Command
Support : change, insert and delete

Then selected the Stored Procedure name.

Thanks and Regards
Sudheer

Re: error when calling stored procedure in ole db

sreejith ss nair7-Oct-04 1:11

sreejith ss nair

7-Oct-04 1:11

Suppose if you are discussing about error message then you need to include what that message is. Without that we can't figurout that. what is the reason. Please try to include the error message.

Sreejith Nair
[ My Articles ]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.