Database

13-Jul-04 8:29

Your procedure code should work OK for what you're trying to do. It may have timing issues on a high-volume system if two updates hit at the same time, however. (If another process performs an update on that record between your initial select and your actual update, then your safety check will break.)

Here's an optimized version without the timing problem:

CREATE PROCEDURE updateUsers
	@ID int, 
	@Name varchar(50),
	@DateModified datetime
AS

UPDATE
		[Users]
SET
		Name = @Name,
		DateModified = GETDATE()
WHERE
		ID = @ID
	AND	DateModified = @DateModified


IF @@ROWCOUNT = 0
	RETURN 1
ELSE
	RETURN 0

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Re: Beginner Help with Stored Procedure

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Grimolfr13-Jul-04 8:32

13-Jul-04 8:32

GAH, my extra linefeeds are being stripped! Pfft

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Help with SQL Query Please

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

N G Sanya13-Jul-04 6:04

N G Sanya

13-Jul-04 6:04

Hi All,

I have two tables;

master (p_key, father,...)
detail (f_key,childname,...)

populated thus;
master:
1, John,...
2, Jason,...
3, Williams,...

Detail:
1, Steve,...
2, Greg,...
2, Alice,...
3, Mike,...
3, Irma,...

I need to display them as follows in the browser;

1, John, Steve, ...
2, Jason, Greg;Alice, ...
3, Williams, Mike;Irma, ...

I am using ASP 3.0 & MSSQL 7.0 How do I do that ?

Thanks in Advance,
Gangadhara Sanya

Re: Help with SQL Query Please

Grimolfr13-Jul-04 8:46

13-Jul-04 8:46

I don't want you to think I'm ignoring you, since I've responded to the posts above and below yours.

It's been so long since I've worked with SQL Server 7 that I'm not quite certain any more what limitations it has in certain areas. (I work with SQL Server 2000.)

The problem is, there are two "tricks" I would use for your particular issue. One of them I know for a fact you can't use on SQL Server 7 (User-Defined Functions.) That can be worked around with a stored procedure, though. The other trick is a way to build a string that's a concatenation of columns from multiple records in a query. That one I don't know if it works in SQL 7.

I'll give you a code snippet of the second trick that you can play with:

DECLARE @PKey int
DECLARE @ChildNames nvarchar(1024)

SET @PKey = 2
SET @ChildNames = STRING(0)

SELECT
	@ChildNames = @ChildNames + childname + ";"
FROM
	detail
WHERE
	f_key = @PKey

SELECT @ChildNames AS childname_concat

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Re: Help with SQL Query Please

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Steven Campbell13-Jul-04 8:54

Steven Campbell

13-Jul-04 8:54

SQL is not good at presenting data in the format that you want. The best is to get all the data from the database, like this:

SELECT p_key, father, childname
FROM master 
  INNER JOIN detail ON detail.f_key = master.p_key
ORDER BY master.p_key

The data will look like

1, John, Steve
2, Jason, Greg
2, Jason, Alice
...

You can then process this data in your code, to make it appear the way that you want.

There is a little trick to processing the data -- as you loop through the recordset, keep a local variable that refers to the last p_key value, and when it differs from the current p_key value, then you know that you need to start a new line.

my blog

ODBC_CONFIG_SYS_DSN problem! pls help!!!!!!!!!!!!!

girl_lash12-Jul-04 20:23

girl_lash

12-Jul-04 20:23

Im making a program wherein data is to be retrieved based from the database being selected. My list of database files use the same DataSourceName. Im using SQLConfigDataSource function. And ODBC_CONFIG_SYS_DSN as one of its parameters since only the DBQ attribute or the database path is changing. When I select one database file for the first time then, loads it, the data retrieved is correct. However, when I select another database file for the second time, the data retrieved is the same as the data from the first database file I selected. This means that my recordset was not refreshed. But when I checked the ODBC Administrator, it is updated based on my selection. My problem is I dont know if Im missing other functions or include files in order to refresh the recordset. I tried calling the GetDefaultConnect() function before opening the recordset but the result is still the same... please help me with this problem. Im using the CRecordset....

Does anyone care to help? pls.............

Re: ODBC_CONFIG_SYS_DSN problem! pls help!!!!!!!!!!!!!

Grimolfr13-Jul-04 8:39

13-Jul-04 8:39

It's a problem with ODBC that I've seen before.

ODBC configurations are often cached in memory. They're read once and the settings are held in memory until the application exits. This is to improve performance (marginally) be not re-reading the same data from the same text file over and over and over again.

The down side is the problem you've run into. One solution would be to create a new DSN on-the-fly for each connection, with unique names, and use the newly created DSN each time. The only time you'd have caching headaches this way is if you reuse the same DSN name for two different connections.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

ODBC_CONFIG_SYS_DSN problem! pls help!!!!!!!!!!!!!

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

girl_lash12-Jul-04 20:22

girl_lash

12-Jul-04 20:22

Im making a program wherein data is to be retrieved based from the database being selected. My list of database files use the same DataSourceName. Im using SQLConfigDataSource function. And ODBC_CONFIG_SYS_DSN as one of its parameters since only the DBQ attribute or the database path is changing. When I select one database file then, loads it, the data retrieved is correct. However, when I select another database file, the data retrieved is the same as the data from the first database file I selected. This means that my recordset was not refreshed. But when I checked the ODBC Administrator, it is updated based on my selection. My problem is I dont know if Im missing other functions or include files in order to refresh the recordset. I tried calling the GetDefaultConnect() function before opening the recordset but the result is still the same... please help me with this problem. Im using the CRecordset....

Does anyone care to help? pls.............

Managing BLOBs

La hadita tripiadora12-Jul-04 14:15

La hadita tripiadora

12-Jul-04 14:15

Hi :

I need to store and retreive a BLOB from and into an Oracle 9i DataBase.
I'm developing an ASP.NET application an i need to display in a web form an image stote as a BLOB.

Does any one have an idea of HOW-TO do that
if you have C# code example i'll really apreciate it!!

thanks

Combo Box Problem

Steve4512-Jul-04 13:59

Steve45

12-Jul-04 13:59

Using VS 2003

When I select the proper DataSource and DisplayMember in the property window of VS for a combo box does anyone know why when I run the local application clicking on the combo box doesn't provide the actual drop down box NOTE: if I select a different table in that same DS it works I have previewed data, schema, keys, etc

************ Details Follow *********************

1. I Connect to a MS-Access dB, create a DataAdapter (DA1) for Table1 and a DateSet (DS) with Table1
2. I populate a Datagrid and associated Textboxes (TB -Text) w/ the DS.DA1 - all O’K
3. I then add some Combo boxes (CB) to form
4. I drag and drop a Table2 (the dropdown choices for the CB) to create DA2
5. I Configure DA2, "Generate DS" for DA2 using the “Existing DS” and Preview data. The DS Schema shows DA1 and DA2 – all appears O.k.
6. For the CB, I "select" from dropdown DS.DA2 (ie Table2) as the DataSource and a given Table2 column for the DisplayMember

When I run the program no CB drop down available from DS.DA2 (table2). Of course, if I select DS.DA1 for the DataSource it works perfectly. Any Ideas?

Steve

SQL permissions (uugghh!!)

Colin Angus Mackay12-Jul-04 5:36

Colin Angus Mackay

12-Jul-04 5:36

I have a database under development. For testing I created a couple of tables with the user account 'test_schema' which is their owner.

If I use the SQL below to query the structure of the table as 'sa' then some of the fields come back as null whereas if I perform the same query as 'test_schema' I get all the fields correctly.

DECLARE @table_name varchar(128);
SET @table_name = 'single_pkey';

SELECT	cols.COLUMN_NAME as column_name, 
	cols.DATA_TYPE as type, 
	cols.CHARACTER_MAXIMUM_LENGTH AS length, 
	CASE WHEN cols.IS_NULLABLE = 'No' THEN 0 ELSE 1 END as is_nullable,
	CASE WHEN pks.column_name IS NULL THEN NULL ELSE cols.ORDINAL_POSITION END AS primary_key,
	cols.ORDINAL_POSITION as position,
	pks.column_name as pks_column_name -- For testing
FROM INFORMATION_SCHEMA.COLUMNS cols
LEFT OUTER JOIN (SELECT kcu.COLUMN_NAME as column_name
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
		ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	WHERE tc.TABLE_NAME = @table_name
	AND CONSTRAINT_TYPE='PRIMARY KEY') as pks 
	ON pks.column_name = cols.COLUMN_NAME
WHERE TABLE_NAME = @table_name
ORDER BY cols.ORDINAL_POSITION

As the 'sa' user the [primary_key] column in the result set is always NULL when the owner if 'test_schema'.

Does anyone have any ideas that might help me out?
[If I've missed off some important piece of information then let me know - I'm sure I am just missing something obvious, but I just can't see it right now]

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

Re: SQL permissions (uugghh!!)

Steven Campbell12-Jul-04 7:54

Steven Campbell

12-Jul-04 7:54

In SQL 2000 it is possible that there are multiple tables called the same thing, owned by different users. Is that the case in your database?

my blog

Re: SQL permissions (uugghh!!)

Colin Angus Mackay12-Jul-04 8:02

Colin Angus Mackay

12-Jul-04 8:02

Not in this particular database. Although for other parts of the system (there will be lots of databases) that may be the case - So, I'll have to take that into account elsewhere.

ListView wiht Dataset

orpus11-Jul-04 19:37

orpus

11-Jul-04 19:37

Hi..
In my form, I have three combo boxes, one Add button one Save button
and a listview.

Step 1:The comboboxes is being populated by a dataset.
Step 2: On clicking the add button, the listview should get populated with the contents of the combobox.text.
I can repeat Step2 a number of times, each time selecting a different value in the comboBox and adding in the ListView
Step 3: on clicking the Save button, all the data in the listview should be saved in a database.

How will I accomplish this the task? How do i save the Columns and fields of the ListView into a dataset

plz help with code or explanation

Thanks

Breath dot net

Re: ListView wiht Dataset

brdavid13-Jul-04 15:17

brdavid

13-Jul-04 15:17

First off, you need to decide which database utilities you are planning to code with, that is, are you going to use ADO, DAO, OLEDB, etc. ADO is usually meant for VB users, but I find myself using it without too much hassel in VC++. DAO is meant more for Access users and OLEDB I believe is Active Xish. There is also the cut and dry ODBC which has been around longer. Anyways, tons of material here on CodeProject to help you figure out what is the best for you. After deciding that then move onto your application. There is a wonderful ADO wrapper class here on CodeProject I use. I think there are few others for DAO and ODBC. Get familiar with those.

To save data into a database the following psuedocode can be followed.

OpenDatabaseConnection(...)
Recordset = OpenRecordset(...)
Recordset.AddNew(...)
Recordset.SetFieldValue(Field /*database field*/, Value/*the value to go into that field*/);
Recordset.Update(...)

I would check out the examples here on CodeProject going under the link http://www.codeproject.com/database/

TONS of examples with code that should answer most of your questions.

ms access on web

Asif Rehman11-Jul-04 17:43

Asif Rehman

11-Jul-04 17:43

hi code guys!
I've a web database LOGINS(ms access).
DSN is DSN_pkbiz

I want to access db from within c# app through ODBC.NET.
pls tell me about the exact connection string.
the rest I'll do.

Time is a good teacher but unfortunately it kills all his students.

Re: ms access on web

Mike Ellison11-Jul-04 17:49

Mike Ellison

11-Jul-04 17:49

Hi there. Try looking up http://www.connectionstrings.com[^].

Mysql Free download

JawedVikia8010-Jul-04 19:16

JawedVikia80

10-Jul-04 19:16

Hi there....
Friends is this possible to get Mysql free of charge .
And from where i get the software.
Help will be appriciated .
Thanks

Arjan Einbu11-Jul-04 3:10

Arjan Einbu

11-Jul-04 3:10

This can be easily be found using Google...

JawedVikia8013-Jul-04 21:49

JawedVikia80

13-Jul-04 21:49

I am just new in the field .So please tell me that whic version is better for me and which mirror is better i am living in Pakistan .
Thanks

Mike Ellison11-Jul-04 3:12

Mike Ellison

11-Jul-04 3:12

Hi. Check out http://dev.mysql.com/downloads/[^].