Re: Filling a table with select statement - Database Discussion Boards

Re: error?

Blue_Boy5-Mar-09 15:46

Blue_Boy

5-Mar-09 15:46

You are welcome.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

cyannaz5-Mar-09 6:42

cyannaz

5-Mar-09 6:42

helo..i'm new in programming...wanna ask for a favor..
did anyone here know how to do coding for register new password in visual studio..
i need some advice...please help...

for your information..i use microsoft access to store the new password.

Re: register password

cyannaz5-Mar-09 16:11

cyannaz

5-Mar-09 16:11

below is my coding for login button and it's work!
hope to get the information on register button..please help... Cry | :((

Dim sql = "SELECT password FROM [tblpass] WHERE (Password) = '" & txtPassword.Text & "'"
passConnection.Open()
cmd = New OleDbCommand(sql, passConnection)
Dim dr As OleDbDataReader = cmd.ExecuteReader

Try
passConnection.Open()

Catch ex As InvalidOperationException
MsgBox(ex.Message)

End Try
Try
If dr.Read = False Then
MessageBox.Show("Authentication Failed...")
Else
MessageBox.Show("Login Successful...")
Me.Hide()
frmData.Show()
End If
Catch ex As Exception
MsgBox(ex.Message)

End Try

If passConnection.State <> ConnectionState.Closed Then
passConnection.Close()

End If

sp execute problem

Dilum5-Mar-09 6:20

Dilum

5-Mar-09 6:20

i'm using vb.net (VS2005) and Ms sql 2005
when i'm executing sp using my code(executenonquery) it takes more than 10minutes
but when i'm run it normaly(like a sql query in Ms sql2005)it takes only 3seconds.

i created same sp with different name and executed using code.that time it takes only 3seconds(same as normal execute).
in first time, whats da reason for takes long time?anyon can help me?
Thnks you

Re: sp execute problem

Rami Said Abd Alhalim9-Mar-09 22:03

Rami Said Abd Alhalim

9-Mar-09 22:03

can you write how you call sp from VB

Regards,
Rami Sa'id Abdalhalim
Technical Team Leader
Amman Commercial Complex
Ommayah Str. Al-Abdali
Tel: +962 6 5696500 Ext.235
Mobile:+962 79 5366016
Email:r.abdalhalim@alghanem.com.jo
www.alghanemgroup.com

Filling a table with select statement

Muammar©5-Mar-09 1:59

Muammar©

5-Mar-09 1:59

Hi everyone,
I have a simple table called test with two columns, ID and Name.. I'm trying the following but it doesnt work.

INSERT INTO TEST VALUES(SELECT SEQ_INDI.NEXTVAL FROM DUAL,SELECT NAME FROM TEST);

How would you fill its values again (duplicate them) without violating the primary key constraint?? I tried to use a sequence but how would you do it?? and if you would use a sequence, what your sql statement would look like??

Thank you guys!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Re: Filling a table with select statement

Blue_Boy5-Mar-09 2:27

Blue_Boy

5-Mar-09 2:27

INSERT INTO TEST <br />
SELECT SEQ_INDI.NEXTVAL FROM DUAL,SELECT n.NAME FROM TEST AS n

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Filling a table with select statement

Muammar©6-Mar-09 4:37

Muammar©

6-Mar-09 4:37

Thanks mate, but it still not working, I get this:

  1  INSERT INTO TEST
  2* SELECT SEQ_INDI.NEXTVAL FROM DUAL,SELECT n.NAME FROM TEST AS n
SQL> /
SELECT SEQ_INDI.NEXTVAL FROM DUAL,SELECT n.NAME FROM TEST AS n
                                  *
ERROR at line 2:
ORA-00903: invalid table name

I like the way you're dealing with it but it seems like it's missing something..
More help.. please..!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Multipart Problem on the Following Query

Vimalsoft(Pty) Ltd4-Mar-09 23:25

Vimalsoft(Pty) Ltd

4-Mar-09 23:25

Good Day All

I have the Following code

IF MTM_ACTV_SETS.SETS LIKE '%_R%'  OR MTM_ACTV_SETS.SETS LIKE '%_L%'
BEGIN 
DELETE TBL_CLASH_ACTV
FROM MTM_ACTV_SETS AS1
INNER JOIN MTM_ACTV_SETS AS2 ON AS2.SETS=AS1.SETS
INNER JOIN TBL_CLASH_ACTV CA ON CA.ACTV1=AS1.ACTV AND CA.ACTV2=AS2.ACTV
WHERE AS1.ACTV <> AS2.ACTV
END

And i get the following Error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "MTM_ACTV_SETS.SETS" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "MTM_ACTV_SETS.SETS" could not be bound.

What is the Problem ?

Thank you

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za

Re: Multipart Problem on the Following Query

Vimalsoft(Pty) Ltd4-Mar-09 23:56

Vimalsoft(Pty) Ltd

4-Mar-09 23:56

sORRY I WAS JUST A LITTLE BIT TIRED. I GOT THE ANSWER

SELECT  * 
FROM MTM_ACTV_SETS AS1
INNER JOIN MTM_ACTV_SETS AS2 ON AS2.SETS=AS1.SETS
INNER JOIN TBL_CLASH_ACTV CA ON CA.ACTV1=AS1.ACTV AND CA.ACTV2=AS2.ACTV
WHERE AS1.ACTV <> AS2.ACTV
AND AS1.SETS NOT IN  (SELECT SETS FROM MTM_ACTV_SETS
WHERE MTM_ACTV_SETS.SETS LIKE '%_R%'  OR MTM_ACTV_SETS.SETS LIKE '%_L%')

Thank you

Privileges in Oracle

Muammar©4-Mar-09 21:29

Muammar©

4-Mar-09 21:29

Hi,
The only privilege I assigned to a newly created user "U" was CREATE SESSION, then I had to assign DBA to it and create a test table then I revoked the DBA Role from the SYS user, now it shows me that the only privilege and role I have for this user is CREATE SESSION

SQL> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'U';

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'U';

no rows selected

SQL>

why can I still select, update and delete that table though I didnt grant this privileges to this table and how can I revoke something I didnt even granted??

Please help guys

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

2 Oracle Question

E_Gold4-Mar-09 19:19

E_Gold

4-Mar-09 19:19

Hi Evryone
I have 2 Oracle Question
1. Is somone know good manual for install Oracle on my Computer ?
2. Is somone know How to transfer my SQL server database to Oracle ?

thank's for any help

MS-ACCESS Query Problem

M Riaz Bashir4-Mar-09 19:14

M Riaz Bashir

4-Mar-09 19:14

Hi friends.

I am using Ms-Access and following query does not work:

"CREATE TABLE MyTable as Select * from MasterTable"

or give me another solution if it is not possible.

Thank you in Advance
(Riaz)

Re: MS-ACCESS Query Problem

Rupesh Kumar Swami4-Mar-09 20:32

Rupesh Kumar Swami

4-Mar-09 20:32

try

SELECT * INTO myTable<br />
FROM MasterTable

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: MS-ACCESS Query Problem

ChandraRam4-Mar-09 20:57

ChandraRam

4-Mar-09 20:57

Yes, this will create the table, but will also copy over the records from MasterTable into myTable.

Instead, try

SELECT * INTO myTable FROM MasterTable where false<br />

The above will only copy the structure of the table

HTH

Globalization

icanmakeiteasy4-Mar-09 18:42

icanmakeiteasy

4-Mar-09 18:42

Hi,

in my database table having different type of languages like

id     uname
1       Évian-les-Bains
2       Évreux
3       Eysines
4       Île d?Aix
5       L?Isle-d?Abeau

can i get uname values in to english language using select query

thanks in advance

icanmakeiteasy

Re: Globalization

Rupesh Kumar Swami4-Mar-09 20:23

Rupesh Kumar Swami

4-Mar-09 20:23

whether you try following query?
select id, uname as [english language] from tableName

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: Globalization

ChandraRam4-Mar-09 20:58

ChandraRam

4-Mar-09 20:58

icanmakeiteasy wrote:
can i get uname values in to english language using select query

Do you mean translate to English? If so, then I don't think that is possible.

Change DataType

mehrdadc484-Mar-09 18:16

mehrdadc48

4-Mar-09 18:16

Hey,
How can I change dataType of a coloum with quert and witout loosing its records?

Best wishes

Re: Change DataType

Rupesh Kumar Swami4-Mar-09 20:38

Rupesh Kumar Swami

4-Mar-09 20:38

which database type is used by you?

whether you try Google search for change datatype of column[^]

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Help with the SQL Query

Vimalsoft(Pty) Ltd4-Mar-09 3:49

Vimalsoft(Pty) Ltd

4-Mar-09 3:49

Good Day All

I have the Following SP

SQL

ALTER PROC GET_BASED_PATTERN
(
@SUBJECT VARCHAR(30),
@Pattern  nvarchar(128) output 
)
AS
SET @Pattern = (SELECT TOP 1 TERM.[CYCLETEMPLATES]FROM tbl_term TERM 
INNER JOIN dbo.TBL_ACTV v
on TERM.ID = V.TERM
INNER JOIN  DBO._Subjects S
ON V.TERM = S.[LEVEL]
WHERE CODE = @SUBJECT)
SELECT 
CASE 
WHEN LEN(@Pattern) > 1 THEN @Pattern
ELSE '00000000'
END

It returns

00000000

and NUll at the Bottom if the len is not greater than 1. i want it to return 00000000 if its like that.

Thank you

Re: Help with the SQL Query

Syed Mehroz Alam4-Mar-09 8:07

Syed Mehroz Alam

4-Mar-09 8:07

Not sure what you need but if you want to get the same value in both the places, here's a way:

SET @Pattern = (SELECT TOP 1 TERM.[CYCLETEMPLATES]FROM tbl_term TERM 
  INNER JOIN dbo.TBL_ACTV v
  on TERM.ID = V.TERM
  INNER JOIN  DBO._Subjects S
  ON V.TERM = S.[LEVEL]
  WHERE CODE = @SUBJECT)

IF @Pattern is null Set @Pattern = '00000000'
Select @Pattern

Hope that helps.

Regards,
Syed Mehroz Alam

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Append to WHERE clause on condition

Phillip Donegan4-Mar-09 1:22

Phillip Donegan

4-Mar-09 1:22

Hi,

Is it possible to append more conditions to a WHERE clause dependant on a condition? This is what i'm trying to do at the moment with no joy.

E.G.

...

WHERE
(@extOrderID IS NULL OR Orders.ExtOrderID = @extOrderID) AND
(@orderDateAfter IS NULL AND @orderDateBefore IS NULL OR Orders.TimeOrdered >= @orderDateAfter AND Orders.TimeOrdered <= @orderDateBefore) AND
(@requiredDispatchDateAfter IS NULL AND @requiredDispatchDateBefore IS NULL OR (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) >= @requiredDispatchDateAfter AND (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) <= @requiredDispatchDateBefore) AND
(@customerForename IS NULL OR Customer.Forename LIKE @customerForename) AND
(@customerSurname IS NULL OR Customer.Surname LIKE @customerSurname) AND
(@customerPostCode IS NULL OR Address.PostCode LIKE @customerPostCode) AND
CASE WHEN @orderStatus IS NOT NULL THEN
CASE WHEN @statusChecked = 'False' AND Status.StatusName != @orderStatus THEN 1
WHEN @statusChecked = 'True' AND Status.StatusName = @orderStatus THEN 1
ELSE 0
END
END <> 0

AND

CASE WHEN @isSearch = 1 THEN
Orders.IsActive = 1
END

It is the last CASE which is not working, any suggestions would be appreciated!

Phil

Re: Append to WHERE clause on condition

Rob Philpott4-Mar-09 5:24

Rob Philpott

4-Mar-09 5:24

Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row?

If so, no you can't do that, but you could do this

AND (@isSearch = 0 or Orders.IsActive = 1)

Regards,
Rob Philpott.

Re: Append to WHERE clause on condition

Phillip Donegan4-Mar-09 5:43

Phillip Donegan

4-Mar-09 5:43

Rob Philpott wrote:
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row?

Yes that is what I meant, can you think of possibly another way then of how I could dynamically create my where clause based on the parameters passed. I have found away to do this but it involves using an IF to see if @isSearch = 0 and then having the same query but with Order.IsActive = 1 on the end of one of them. Obviously I'd prefer to not do this as I'm just repeating code.

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