Help with dynamic sql - Database Discussion Boards

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

Re: SQL 2005 Express?

Paul Conrad23-Nov-05 7:43

Paul Conrad

23-Nov-05 7:43

It so turns out that I installed a version of SQL 2005 Express from October, and Management Studio Express needed the latest release from Microsoft.

So I've got it working now Smile | :)

AutoIncrement

vuthaianh21-Nov-05 20:58

vuthaianh

21-Nov-05 20:58

I inserted three records into table Products(ProductID, ProductName) with ProductID set to AutoIncrement.
Three records with ProductID were 1, 2, 3.
After that i deleted three recored and insert the fourth record. This record would got the the productID's value is 4
How could i set the ProdudctID of this record to 1, just like the beginning.
Thanks

Re: AutoIncrement

S Douglas22-Nov-05 0:36

S Douglas

22-Nov-05 0:36

vuthaianh wrote:
How could i set the ProdudctID of this record to 1, just like the beginning.

I’m guessing you’re using MS Access; the only way to re-start the counter is to compact / repair the database. It only works when there are no other data connections open and when no records exist in the table.

Hope this helps.

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

How to write the Query

Medikonda21-Nov-05 20:19

Medikonda

21-Nov-05 20:19

Hi All,
Thanku for ur quick n speedy response................
Here I came with a question......

I have table called -- Client
a column is there that is --- clientCode navarchar(15)
Another table user_ref
in this I have UserID as primary key...
Now wht i have to do is I have to get clientCode from Client table to user_ref table and
I have to make (userID,clientCode) as primary key.

hope u all got the query
pls tell me ASAP
thanku
sangeetha

sangeetha

Re: How to write the Query

Het210922-Nov-05 19:02

Het2109

22-Nov-05 19:02

I didn't got ur question.

Het Waghela
Smile | :)

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

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

Re: How to write the Query

Frank Kerrigan24-Nov-05 4:11

Frank Kerrigan

24-Nov-05 4:11

I'm making assumptions here it not very clear what you require

Run an update with two tables :
-------------------------------
update user_ref
SET user_ref.clientCode = Client.clientCode
from user_ref client
WHERE user_ref.USERID = Client.USERID

Count of Rows Returned

kenexcelon21-Nov-05 17:39

kenexcelon

21-Nov-05 17:39

I have this:

With Me.daNumCourses.SelectCommand
.CommandText = "SELECT Section.Section_ID, Section.InstructorID, Instructor.ID FROM Section INNER JOIN Instructor ON Section.InstructorID = Instructor.ID AND Section.InstructorID = '" & Me.instr.ID & "'"
intCount = .ExecuteNonQuery()
End With
Me.dsNumCourses.Clear()
Me.daNumCourses.Fill(Me.dsNumCourses)
Me.txtNumberCourse.Text = intCount
I keep getting -1 so what am I doing wrong?

Thanks

Re: Count of Rows Returned

Colin Angus Mackay21-Nov-05 20:42

Colin Angus Mackay

21-Nov-05 20:42

Why are you doing this? Why not just fill the data set and get a count of the rows in the DataTable? e.g. dsNumCourses.Tables[0].Rows.Count

My: Blog | Photos

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

Re: Count of Rows Returned

kenexcelon21-Nov-05 21:03

kenexcelon

21-Nov-05 21:03

I just want to get the count of the classes that a certain instructor teaches. I tried the .count property and that counts all of the rows in a particular table.

Thanks for your reply. If you have a suggestion on how to do that please post back.

Re: Count of Rows Returned

Colin Angus Mackay21-Nov-05 22:45

Colin Angus Mackay

21-Nov-05 22:45

Why don't you have your SQL as:

SELECT COUNT(*) FROM ....

Also, the way you build your SQL is suscepatable to a SQL Injection Attack and you should try and use parameterised queries where ever possible. You can read more on how to improve the security of your application here: SQL Injection attacks and tips on how to prevent them[^]

My: Blog | Photos

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

Re: Count of Rows Returned

kenexcelon22-Nov-05 6:45

kenexcelon

22-Nov-05 6:45

How do you return the number of rows from the query? In other words if I have a variable intCount how do I set intCount to the "count" of the query?

Thanks for your reply. We've noticed a few security holes in our application in a few places and we are fixing those right now.

Re: Count of Rows Returned

Colin Angus Mackay22-Nov-05 8:13

Colin Angus Mackay

22-Nov-05 8:13

Dim cmd As SqlCommand
cmd = SqlCommand.New()
cmd.CommandText = "SELECT COUNT(*) FROM MyTable WHERE someColumn=@someValue"
cmd.Connection = myConnection
Dim numRows As Int
numRows = cmd.ExecuteScalar()

numRows will contain the number of rows that match the given filter (i.e. what is filtered in the WHERE clause). COUNT() is an aggregate function that returns a count, when COUNT(*) is used it returns the number of rows. If you use COUNT(columnName) then it will count the number of rows where the column value is not null.

Does this help?

My: Blog | Photos

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

Re: Count of Rows Returned

anandss23-Nov-05 8:43

anandss

23-Nov-05 8:43

This is the correct way. ExecuteNonQuery should be used for Insert/Update/Delete statements. If the CommandText is "SELECT" statment, then it will always return -1. When it is DML statement, then it will return the number of rows affected by that DML statement.

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