OQL: Where to get started? - Database Discussion Boards

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 ]

Re: error when calling stored procedure in ole db

Colin Angus Mackay7-Oct-04 1:42

Colin Angus Mackay

7-Oct-04 1:42

sreejith ss nair wrote:
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.

He did include the error message: "it fails with a HRESULT value : [-2147217887]. (the error lookup tool returns 'No such Message' for this value)."

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.

Saving files into image field on sql server2000

firad3-Oct-04 20:18

firad

3-Oct-04 20:18

i made uploading tool using freeaspupload component and i customized it to store the uploaded files to a DB using ADO, i had the webserver (IIS) and the sqlserver 2000 on the same machine and it was working fine ,no problems at all , but when i moved the DB to the production server (the webserver and the DB on separate machines) the upload failed , it started to give me a syntax error msg:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'
(file is the field i use to store my files to) i tried to use appendChunk but still the same error

Opinions on loading large file into database?

ThomasH13-Oct-04 12:30

ThomasH1

3-Oct-04 12:30

Hello everyone, I'd like your opinions! I'm rewriting a program that I did back in 1996; it takes a large "external-system" text file, and compares it against our database. If there's a difference between the file entry and database entry, we'll take the entry in the file and put it into the DB, and make note of it on a report. We don't run this program often, and we always take the file as the "correct" data. I'd like to load the file into an ADO.NET dataset, but I'm a little hesitant because of the average file size.

The size of the flat file is usually between 2 and 4 MB large. Is this an okay size for a dataset in memory? I'd want to load up the entire file at one time into a dataset. Then I'd set up a DataAdapter and DataSet and etc for my database, and compare both datasets together. I could just call DataAdapter.Update on the database's dataset to make the changes. So not only would I have the 2-4 MB flatfile in memory, but I'd also potentially have the same size (2-4MB) from the database! The benefit is that I'd really be "using" ADO.NET... comparisions would be easier, and the update would be cleaner.

My alternative is to open the file, and read it one record at a time. I'd then do an executescalar to check our database, and another executescalar if I had to update our database. This method seems memory-friendly, but database-intensive and network-intensive because of all the individual transactional updates... I'd have to update the database one-record-at-a-time!

Do any of you load up huge 2-4 MB text files into a dataset? If so, do you have any problems with this approach? I really want to use datasets- but this program will be running on workstations, on an as-required basis- it won't be running on a server, so I don't want to write a program that takes forever to run. The workstations are semi-powerful; but this "might" have to run on an XP Pro box with only 256MB of memory. (Most of the XP Pros have 512 mb ram.)

Oh, I'll be using C#.NET and ADO.NET under .NET Framework version 1.1. The file isn't in XML, and I think it would be pointless to convert it to XML first, and then process it. Correct me if I'm wrong, though!

Thanks for any ideas!

-Thomas

Datetime problem on sql statement

Gamoss3-Oct-04 3:54

Gamoss

3-Oct-04 3:54

Hi folks,

Pretty newbie on everything and here goes my first question.

I could not find similar question, but if someone has asked this allready before, please give me a link where to find answer to my problem Smile | :)

So I have this problem that I'm trying to get Year from a database into a dataset. So how can I separate year from the datetime value in the sql statement?

I have tried SUBTRING and EXTRACT but those are not working at all.

I'm doing my project with vb.net

Below is the sample I've tried with no luck.

select firstname + ' ' + Lastname As Name, EXTRACT(YEAR FROM Birthday) as Year, Email from CardTable ORDER By Cardnumber Asc

Gamoss, Icq: 32048647

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