Re: managed or unmanaged - Database Discussion Boards

A Slightly Different Master Detail Problem

TofuBug2424-Nov-05 20:20

24-Nov-05 20:20

Now before anyone sends me links to any articles here on CP or else where on master/detail solutions i've already looked at them and none of them show me what i want to do.

I have a very simple database with two tables one holds a collection of jobs with basic field info for each plus a ClientID field to link each job to a specific Client.

the client table has all the detailed info about each client in the table it's primary key obviously being ClientID;

now my program consists of:

a DataGrid showing all jobs scheduled for the current day (i always use stored procedures this one pulls everything based on records matching a date i submit)

a bunch of textbox controls one for each column in the Client table.

now all the master detail articles i have read would list all the clients in one table and clicking on one would update the details table with all the jobs for that client.

what I want to do is when a user clicks on a specific job in the datatable the textboxes will be updated to reflect the correct Client's details for that specific job.

my stored procedure to get a specific clients details uses the current rows clientID of the job table.

now the first job i select and the textboxes work. when i select any other jobs the text boxes never change.

what am i doing wrong and could anyone point me in the right direction.

thanks

Ryan

-- modified at 2:20 Friday 25th November, 2005

using the osql command

dundealing24-Nov-05 8:40

dundealing

24-Nov-05 8:40

I have a book which shows an example of using the osql command but when I try it, I get a message that the SQL Server does not exist or access is denied.

The command I am entering is:

osql -E

Can anyone tell me how to grant access or find another way around this?

Re: using the osql command

Rob Graham24-Nov-05 15:16

Rob Graham

24-Nov-05 15:16

Is your server set up for windows authentication or sql server authentication? If the latter, -E will fail. This will also fail if the server is not running...

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Re: using the osql command

Arjan Einbu25-Nov-05 12:57

Arjan Einbu

25-Nov-05 12:57

The options are either both windows auth AND sql server auth at the same time, or only windows auth. So osql -E should be available always. If it doesnt work, it might be that you're logged on to Windows using an account that has not been granted access to MS SQL Server.

Re: using the osql command

dundealing27-Nov-05 6:51

dundealing

27-Nov-05 6:51

When I installed the MSDE version of SQL Server, I did so with Windows authorisation.

If I need to grant my account access to the SQL Server, could you tell me how to do this?

When my computer starts, the Server is showing as running, and so I don't think this is the problem.

Re: using the osql command

Arjan Einbu27-Nov-05 23:03

Arjan Einbu

27-Nov-05 23:03

In Enterprise Manager: Expand the server your trying to access, then expand the Security node and click on the Logins node under Security. Here you can create a new login with Windows Authentication.

-a

managed or unmanaged

John L. DeVito24-Nov-05 6:10

John L. DeVito

24-Nov-05 6:10

happy thanksgiving to all in the US,

So here's the deal. I'm writing a large system with a lot of different components. I've decided to use C++ as the 'mian' programing language so that for critical time dependant operations I can switch to native code easily. My question is, is ADO.NET "fast" enough for critical datasource operations, or would it be wiser to switch to native code for all datasource operations? The system is HIGHLY database intensive and so I would want to gain as much time as possible.

Thanks,
John

Re: managed or unmanaged

Mike Dimmick24-Nov-05 12:54

Mike Dimmick

24-Nov-05 12:54

The only real way for you to answer that is to measure.

However, remember that the round-trip time involved in a database call is non-trivial - much the same as any other cross-process call on the same machine (if your database is on the same machine) and subject to network latency as well (if on another machine). Therefore you should try to do as much in each database call as possible. In the distributed computing arena this is often called making your interface 'chunky' rather than 'chatty'.

Stability. What an interesting concept. -- Chris Maunder

mySQL 4.0 vs mySQL 5.0

JKallen24-Nov-05 6:00

JKallen

24-Nov-05 6:00

I am running mySQL 4x on a FReeBSD dedicated. I am trying to figure out how to install mySQL 5 NOT IN PLACE OF mysql 4. I want to be able to run both versions for testing reasons before I remove the older installation.

The standard pkg_add returns an error regarding a conflict with the older version of mysql.

SQL Convertion of 0

Brendan Vogt23-Nov-05 2:19

Brendan Vogt

23-Nov-05 2:19

Hi,

I am concatenating 2 fields, wine name and wine vintage:

SELECT Wne_Name + ' ' + Wne_Vintage...

Some wines have a vintage of 0, not sure why. How do I do an if statement in the SELECT statement, I want all 0 values to be replaced by "N/A":

Amani Vineyards Merlot 2002
Amani Vineyards Merlot N/A

Wne_Vintage is of type int.

I hope someone can help.

Regards,
ma se

Re: SQL Convertion of 0

ToddHileHoffer23-Nov-05 2:23

ToddHileHoffer

23-Nov-05 2:23

case when Wne_Vintage = '0' then 'N/A'
else Wne_Vintage end

"People who never make mistakes, never do anything."
My Blog

Re: SQL Convertion of 0

Not Active23-Nov-05 10:22

Not Active

23-Nov-05 10:22

This assumes Wne_Vintage is a character field. If it is an interger remember to cast it to a char.

Re: SQL Convertion of 0

Brendan Vogt23-Nov-05 20:24

Brendan Vogt

23-Nov-05 20:24

Hi,

Thanks for the replies!!

This is what I have and I am getting an error. Not sure how to reseolve it.

SELECT Wne_ID, Wnr_Name + ' ' + Wne_Name + ' ' +
CASE
WHEN Wne_Vintage = '0' THEN 'N/A'
ELSE CONVERT(varchar, Wne_Vintage)
END
FROM cyb_tblWineries, cyb_tblWines
WHERE cyb_tblWines.Wne_Wnr_ID = cyb_tblWineries.Wnr_ID
AND Wne_Show = 'y'
ORDER BY Wnr_Name, Wne_Name;

The error is:

<b>Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.</b>

Wne_Vintage is an int.

Regards

Re: SQL Convertion of 0

Brendan Vogt23-Nov-05 20:41

Brendan Vogt

23-Nov-05 20:41

I posted another comment, please check.

Printing "Yes" /"No" from sql 0/1?

majidbhutta23-Nov-05 1:42

majidbhutta

23-Nov-05 1:42

I want to display "Yes" if the field value is 0 and "No"

if the field value is 1 and "None" if field value is

anything else in DataGrid. Where the datatype of field

value is int in sql server database. How to convert it

in the desired way.
Thnx in Advance

Re: Printing &quot;Yes&quot; /&quot;No&quot; from sql 0/1?

ToddHileHoffer23-Nov-05 2:22

ToddHileHoffer

23-Nov-05 2:22

use the bit datatype.
The only values for bit are 1 and 0.
To convert

case when bitfield = 1 then 'yes'<br />
else 'no end

"People who never make mistakes, never do anything."
My Blog

Converting datetime to Desired format?

majidbhutta23-Nov-05 1:32

majidbhutta

23-Nov-05 1:32

I want to display DateTime in the DataGrid in the Format
"Wednesday,November 30,2005" stored in the sql server

datbase as smalldatetime. How to Convert it in the above

format from smaldatetime.Query reqired.
Thnx in Advance

Re: Converting datetime to Desired format?

ToddHileHoffer23-Nov-05 2:21

ToddHileHoffer

23-Nov-05 2:21

<br />
SET QUOTED_IDENTIFIER ON <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
<br />
create  FUNCTION dbo.udf_FormatDate (@date datetime, @format varchar(50))  <br />
RETURNS VARCHAR(50) AS  <br />
BEGIN <br />
-------------------------------------------------------------------------------------<br />
--<br />
-- Name: FormatDate<br />
--<br />
-- Purpose: Mimics the VB Format routine for dates<br />
--<br />
-- Parameters: <br />
--		@date - Date, Date to be formatted<br />
--		@format - String, Template to format the date to<br />
--<br />
-- Returns: String, Date formatted to user requested template<br />
--<br />
-- Notes: <br />
--		1. Time information is not accounted for in this routine<br />
--<br />
--		2. @format accepts the following values for each section of the date.<br />
--			Day<br />
--				dddd 	- Full Day Name<br />
--				ddd 	- Abbreviated Day Name<br />
--				dd 		- Zero Padded Day Number<br />
--				d 		- Day Number<br />
--<br />
--			Month<br />
--				mmmm	- Full Month Name<br />
--				mmm	- Abbreviated Month Name<br />
--				mm		- Zero Padded Month Number<br />
--				m		- Month Number<br />
--<br />
--			Year<br />
--				yyyy	- Full 4 digit year<br />
--				yy		- 2 digit year<br />
--<br />
--		3. Any unexpected characters will be returned in the string<br />
--<br />
--		4. Assumes database was set up with case-insensitive collation<br />
--<br />
-- Example Usage:<br />
--			All examples use the following date 2003-07-13 00:00:00<br />
--<br />
--			'dddd, mmmm dd, yyyy' 	--> Sunday, July 13, 2003<br />
--			'mmddyyyy' 				--> 07132003<br />
--			'm-d-yy'					--> 7-13-03<br />
--			'mm/dd/yyyy'			--> 07/13/2003<br />
--<br />
-------------------------------------------------------------------------------------<br />
DECLARE @pos AS INTEGER<br />
DECLARE @char AS VARCHAR(1)<br />
--<br />
-- Replace Year<br />
--<br />
	SET @pos = CHARINDEX('yyyy', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('yyyy', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('yy', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('yy', @format)<br />
	END<br />
--<br />
-- Replace Month<br />
--<br />
	SET @pos = CHARINDEX('mmmm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mmmm', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('mmm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(month, @date), 3))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mmm', @format)<br />
	END<br />
	SET @pos = CHARINDEX('mm', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @date) AS VARCHAR(2))), 2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('mm', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('m', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		-- account for MArch and deceMBer<br />
		SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
		IF (@char <> 'a') AND (@char <> 'b')<br />
			BEGIN<br />
				SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(month, @date) AS VARCHAR(2)))<br />
				--PRINT @format<br />
				SET @pos = CHARINDEX('m', @format)<br />
			END<br />
		ELSE<br />
			BEGIN<br />
				SET @pos = CHARINDEX('m', @format, @pos + 1)<br />
			END<br />
	END<br />
--<br />
-- Replace Day<br />
--<br />
	SET @pos = CHARINDEX('dddd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 4, DATENAME(weekday, @date))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('dddd', @format)<br />
	END<br />
	<br />
	SET @pos = CHARINDEX('ddd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(weekday, @date), 3))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('ddd', @format)<br />
	END<br />
	SET @pos = CHARINDEX('dd', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + DATENAME(day, @date)), 2))<br />
		--PRINT @format<br />
		SET @pos = CHARINDEX('dd', @format)<br />
	END<br />
	SET @pos = CHARINDEX('d', @format)<br />
	WHILE @pos > 0<br />
	BEGIN<br />
		-- account for DEcember, sunDAy --> saturDAy, weDNesday<br />
		SET @char = SUBSTRING(@format, @pos + 1, 1)<br />
		IF (@char <> 'e') AND (@char <> 'a') AND (@char <> 'n')<br />
			BEGIN<br />
				SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(day, @date) AS VARCHAR(2)))<br />
				--PRINT @format<br />
<br />
<br />
				SET @pos = CHARINDEX('d', @format)<br />
			END<br />
		ELSE<br />
			BEGIN<br />
				SET @pos = CHARINDEX('d', @format, @pos + 1)<br />
			END<br />
	END<br />
IF @format = '//' BEGIN<br />
	SET @format = ''<br />
END<br />
RETURN @format<br />
END<br />
<br />
<br />
<br />
<br />
<br />
<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />

"People who never make mistakes, never do anything."
My Blog

Re: Converting datetime to Desired format?

Luis Alonso Ramos27-Nov-05 8:33

Luis Alonso Ramos

27-Nov-05 8:33

In the DataGrid? An ASP.NET DataGrid? o a Windows Forms DataGrid? In any case, you can set a Format or FormatExpression property for the column (in your app, not in the database). Set the format to "D" to use the long date pattern. See here[^]

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

save image in MsSql 2000 database

yourgangu22-Nov-05 20:52

yourgangu

22-Nov-05 20:52

help any one help me ,by giving code to store an image in database directly from system.i mean a image that is in our system ,should uploaded into the database.

live and let live

Help with dynamic sql

Het210922-Nov-05 18:06

Het2109

22-Nov-05 18:06

Hi all,
I am using dynamic sql in my stored procedure. There is a problem. There are parameters in the dynamic sql query which are of type int or rather which are not of string type. So it is giving me error saying cannot convert type string to int.
Can anyone help me out of this?

Het Waghela
Smile | :)

Be Humble in Victory and Strong in Defeat. Smile | :)

Het Waghela, Blog|Het Waghela DotNet Questions Link|More Links

Re: Help with dynamic sql

jescgf22-Nov-05 18:38

jescgf

22-Nov-05 18:38

hello

Re: Help with dynamic sql

Het210922-Nov-05 18:58

Het2109

22-Nov-05 18:58

Anything wrong with my question?

Het Waghela
Smile | :)

Be Humble in Victory and Strong in Defeat. Smile | :)

Het Waghela, Blog|Het Waghela DotNet Questions Link|More Links

Re: Help with dynamic sql

Colin Angus Mackay23-Nov-05 3:25

Colin Angus Mackay

23-Nov-05 3:25

Look in the SQL Server documentation for CAST or CONVERT

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

SQL 2005 Express?

Paul Conrad22-Nov-05 10:35

Paul Conrad

22-Nov-05 10:35

Hello All,

I've downloaded and installed SQL 2005 Express a week ago. Now, I've downloaded the SQL 2005 Management Studio Express. It complains that it only works with SQL 2000 and SQL 2005. Am I stuck with sqlcmd.exe for creating databases in SQL 2005 Express?

Any help is appreciated.

Paul

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