SQL Drilldown or Crawling web app? - Database Discussion Boards

Re: sequence number in insert comand please help

N a v a n e e t h19-Jul-07 22:41

N a v a n e e t h

19-Jul-07 22:41

mindgameny wrote:
a sequence number will fill up the id column

Are you looking for identity columns ? That will generate unique values for the column on each inserts

My Website | Ask smart questions

Re: sequence number in insert comand please help

Pete O'Hanlon19-Jul-07 23:17

Pete O'Hanlon

19-Jul-07 23:17

Oracle doesn't use an identity column.

Deja View - the feeling that you've seen this post before.

Re: sequence number in insert comand please help

N a v a n e e t h20-Jul-07 2:22

N a v a n e e t h

20-Jul-07 2:22

Oops

. I didn't notice it was for oracle., Cool | :cool:

My Website | Ask smart questions

Re: sequence number in insert comand please help

Pete O'Hanlon20-Jul-07 2:57

Pete O'Hanlon

20-Jul-07 2:57

No problem. I was halfway through typing a response about using scope_identity when I noticed that. Perhaps he needs to learn that it's Oracle with a capital O. Big Grin | :-D

Much more noticable that way.

Deja View - the feeling that you've seen this post before.

Re: sequence number in insert comand please help

Pete O'Hanlon19-Jul-07 23:25

Pete O'Hanlon

19-Jul-07 23:25

One way of doing this in Oracle (and I believe it's probably the easiest) is to use a trigger. Suppose that you have the following table:

create table myTable
(
  id number(10),
  name varchar2(20);
)
alter table myTable
add (constraint mytable_pk primary key (id));

Create a sequence for this:

create sequence myTable_seq;

Now, you write a pre-insert trigger:

create or replace trigger myTable_Insert
before insert on myTable
for each row
when (new.id is null)
begin
  select myTable_seq.NEXTVAL
  into :new.id
  from dual;
end;

To test this, enter:

INSERT INTO myTable(name) values ('Test');

Deja View - the feeling that you've seen this post before.

Re: sequence number in insert comand please help

mindgameny20-Jul-07 17:23

mindgameny

20-Jul-07 17:23

Thanks a lot for your help
it helps me a lot for my project and i was able to do it in sql
but now i want to write code on vb.net for same purpose.
is that posible?
the data table is already in database (oracle)
and i cant change it.
so where and what i need to do in vb.net. please help
thanks a lot

Re: sequence number in insert comand please help

Pete O'Hanlon22-Jul-07 9:26

Pete O'Hanlon

22-Jul-07 9:26

Just because the table is in place doesn't mean that you can't use this technique. All you need to do is create the sequence table and the pre-insert trigger. They won't affect your table in the slightest.

Deja View - the feeling that you've seen this post before.

SQL Drilldown or Crawling web app?

MattPenner19-Jul-07 7:36

MattPenner

19-Jul-07 7:36

We often need quick access to our sql data. Often we open SQL Analyzer to grab the data when we know exactly what we want. Other non-SQL users wade through the application housing the data.

I just thought of creating a web application where users could "crawl" the data by choosing their database and table and being presented with the data. Then they could drill down in the data by clicking links that are based on the table's foreign keys.

In a perfect world there would be many features such as
* Having custom converters based on the data such as showing text values rather than foreign key ids that link to a lookup table, showing numbers in specified formats, displaying a picture rather than the path, etc.
* Allowing security based on passed credentials or a user entered username/password. Passed credentials would allow access to the data the user has initial rights to. Manual user credentials would be useful for giving access to specific areas not available to the user or testing access to user accounts that applications use.
* Searching
* ???

Before I go and create something like this (which would be fun) does anyone know of something similar? An application with the source code available would be preferable but I'd be willing to take a look at closed commercial apps as well.

Thanks!

Matt Penner

Re: SQL Drilldown or Crawling web app?

Paddy Boyd20-Jul-07 1:09

Paddy Boyd

20-Jul-07 1:09

MS Access.

Re: SQL Drilldown or Crawling web app?

MattPenner20-Jul-07 5:44

MattPenner

20-Jul-07 5:44

Sorry, not quite sure what you mean by that reply. A little more specific?

I'm not much of a fan of Access. I don't see how it would help me accomplish what I'm trying to do.

Thanks.

Re: SQL Drilldown or Crawling web app?

Paddy Boyd22-Jul-07 21:28

Paddy Boyd

22-Jul-07 21:28

MS Access can link to a SQL Server database and then your users can use the query builder to get to the data. Means that there's no direct link into sql server (if you connect with a read only user) and they will be using an interface that many of them are probably already used to.

store pictures in a table using SQL Server 2005

mavii19-Jul-07 3:48

mavii

19-Jul-07 3:48

how pictures are stored in a table column

Re: store pictures in a table using SQL Server 2005

Colin Angus Mackay19-Jul-07 3:59

Colin Angus Mackay

19-Jul-07 3:59

mavii wrote:
how pictures are stored in a table column

put them in a varbinary(max) column.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

DateTime format

Peterson Luiz19-Jul-07 3:24

Peterson Luiz

19-Jul-07 3:24

Is there any way to change the date format in sql server 2005 forever?? I mean, in a way that all connection use this format.

The instruction set dateformat works fine, but I need that all connections start with a certain date format. I can't change the format before the program connects to the database.

Thanks

Peterson

Re: DateTime format

Colin Angus Mackay19-Jul-07 3:31

Colin Angus Mackay

19-Jul-07 3:31

Peterson Luiz wrote:
Is there any way to change the date format in sql server 2005 forever?? I mean, in a way that all connection use this format.

There is no format. It is an illusion. SQL Server does not store dates in any specific format. It may interpret SQL statements as having a certain format, but generally good practice is to use ISO format (yyyy-mm-dd) so there is no ambiguity. Where possible you should be using paramerised queries so that when you pass a date to the database you pass a DateTime object from .NET - which means you no longer have to worry about the date format.

Re: DateTime format

Peterson Luiz19-Jul-07 3:36

Peterson Luiz

19-Jul-07 3:36

But in my case, I have an old C++ program that stores the dates in the format mm/dd/yy hh:mm:ss.
If I do a set dateformat mdy, I can do an insert with the mm/dd/yy format. The default of my sql server for some reason is dmy.
When I try to insert the data whitout setting the dateformat to mdy I get I conversion error.

Thanks

Peterson

Re: DateTime format

Colin Angus Mackay19-Jul-07 4:18

Colin Angus Mackay

19-Jul-07 4:18

Most C++ database frameworks have facilities to pass parameters.

And, just to reiterate, it is generally good practice to use ISO date format rather than a regional variant format. It is the job of the presentation layer of your application to format and interpret dates, not the database.

Re: DateTime format

Mike Dimmick19-Jul-07 5:43

Mike Dimmick

19-Jul-07 5:43

DATEFORMAT inherits its setting from the server's default language. You can set this in various ways, see Books Online for details (look for 'default language Option' in the index).

Your database connection method may have a way to specify a different language. Both the ODBC driver and OLE DB provider accept a Language option which can be used to override the default.

Stability. What an interesting concept. -- Chris Maunder

replication

SVb.net19-Jul-07 3:22

SVb.net

19-Jul-07 3:22

hi i have big problem with my replication
i created replication on maaster server (Sql server enterprise) replication name Client3G (transaction) then i create subscriptions on other sql server instance( developer ) by Client3G
but when run it make error . this error ('the process could not connect to distributo 'data'. the step failed')

123

display last 4 records in a table

mavii19-Jul-07 2:36

mavii

19-Jul-07 2:36

if i want to display only last entered 4 records from a table, then which query will be written

Re: display last 4 records in a table

Colin Angus Mackay19-Jul-07 2:39

Colin Angus Mackay

19-Jul-07 2:39

mavii wrote:
if i want to display only last entered 4 records from a table, then which query will be written

Well, The table needs a date stamp (DateTime column) that records when data was inserted.

SELECT TOP 4 * FROM MyTable<br />
ORDER BY DateStamp DESC

Re: display last 4 records in a table

Sathesh Sakthivel19-Jul-07 2:40

Sathesh Sakthivel

19-Jul-07 2:40

SELECT TOP 4 [fields] FROM [table] ORDER BY DateStamp DESC

SSK.

how to retrieve data from excel sheet

Farrukh Hussein19-Jul-07 2:27

Farrukh Hussein

19-Jul-07 2:27

its very urgent and need a code
i have to traverse a values in a table of database one at a time of almost 19 digits in string format which is need to retrive from a specific column of a sheet of MS-Excel and need to match with all values in a specific field of that table.

Hussein

Re: how to retrieve data from excel sheet

Rob Graham19-Jul-07 3:35

Rob Graham

19-Jul-07 3:35

Connect to Excel using ADO (System.Data.Oledb classes for .NET) with the following connection string:

e.g open an OledbConnection using:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= \myfilepath\myexcelsheet.xls
;Extended Properties=\"text;HDR=Yes;FMT=Delimited;\";Extended Properties=\"Excel 8.0;\""

Note that HDR =Yes implies the first row in the Excel sheet is to be treated as column names.
If you use HDR=No, the first row is data, and the sheet column names (A,B,C,D...) are used.

Open the worksheet (in a DataReader or a DataAdapter) using a query like this one:
"select my19digitfild from $MyWorksheetName where mymatchfield = mymatchvalue"

Note the $ in $MyWorkHeetName. The $ indicates the "table" name to use is the worksheet tab name (e.g $Sheet1). If you omit the $, it will look for a named rage top determine the 'table'.

Good luck.

download mysql (link)

p_19-Jul-07 1:44

19-Jul-07 1:44

hi all
i want to download mysql free version to connect it vc++

please tell me a direct link as i need it
i try but not succeeded
thanks Rose | [Rose]

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