Database

Re: Create "report query" [modified]

Mycroft Holmes14-May-09 21:56

Mycroft Holmes

14-May-09 21:56

Hmmm I presume you are using SQL Server 2005/8. Try this article [^]

Never underestimate the power of human stupidity
RAH

Jörgen Andersson14-May-09 22:06

Jörgen Andersson

14-May-09 22:06

You can try something similar to this.

select
homeid,
max(case when statusid=1 then datefinished else null end) status1,
max(case when statusid=2 then datefinished else null end) status2,
max(case when statusid=3 then datefinished else null end) status3,
max(case when statusid=4 then datefinished else null end) status4,
from history
group by homeid;

<edit>fixed typo</edit>

modified on Friday, May 15, 2009 4:36 AM

AlexeiXX315-May-09 7:23

AlexeiXX3

15-May-09 7:23

Thanks lot!!!
This reduced my query a lot

I just have a question
What is the max for?? Confused | :confused:

I removed the max as well as the group by and still works

Thanks again

Alexei Rodriguez

Jörgen Andersson15-May-09 13:12

Jörgen Andersson

15-May-09 13:12

AlexeiXX3 wrote:
Thanks lot!!!

You're welcome

AlexeiXX3 wrote:
What is the max for??
I removed the max as well as the group by and still works

In case you have duplicated values in the table, the query will then select the last date (use Min() for the first date).

This is of course not necessary if you have HomeID and StatusID as a composite key in the table.

How to create an exist database [modified]

AlexeiXX315-May-09 21:44

AlexeiXX3

15-May-09 21:44

Hi thanks again Smile | :)

Now i see the need for the max and group by

I had just added homes to the History with status 1 only, thats why it wouldnt make any difference, but when i inserted homes with status 2, 3 and 4, i got duplicated rows

Alexei Rodriguez

yueru14-May-09 16:22

yueru

14-May-09 16:22

If I has database but in sqlite command can't write into exists dataase

sql_con = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

how to command that write whatever database is exist !!!
I 've already use command
but it's not work

if (File.Exists("" + info + ".db"))
           {
               sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=False;Compress=True;");
               sql_con.Open();
               sql_cmd = sql_con.CreateCommand();
               sql_cmd.CommandText = "drop table par ;";
               sql_cmd.ExecuteNonQuery();
           }
           else
           {
               sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=True;Compress=True;");

           }

          //  FileStream fileStream = new FileStream(@" " + info + ".db", FileMode.Create);


           sql_con.Open();
           sql_cmd = sql_con.CreateCommand();
           sql_cmd.CommandText = "create table par (Date nvarchar(20), Time nvarchar(10), Linein NVARCHAR(20), Lineout NVARCHAR(20), Number NVARCHAR(20), Status nvarchar(10),Duration int(20), Cost nvarchar(20), Detail nvarchar(10), Network nvarchar(10));";
           sql_cmd.ExecuteNonQuery();

modified on Thursday, May 14, 2009 11:03 PM

[Message Deleted]

munklefish14-May-09 0:41

munklefish

14-May-09 0:41

[Message Deleted]

Re: STORE PROCEDURE: Problems with update statement

jbracken197314-May-09 1:06

jbracken1973

14-May-09 1:06

I am not sure how you are passing the binary type to the sp, I am guessing it is in error because you are passing a string to a binary field. Either convert it to binary when passing it to the sp or do a CONVERT(binary(16), @password)

I don’t believe the SQL stage is the place where you want to build you cryptology, the application should send you a encrypted string.

PS.. I assume this is MS SQL Server

John

Re: STORE PROCEDURE: Problems with update statement

munklefish14-May-09 2:08

munklefish

14-May-09 2:08

Hi,

Thanks for the response.

I realised i was entering a string rather than a binary. I was simplytesting the procedure in the SQL editor to test it in preparation for use with my website. The coding of the website passes in a hashed string, hence the SQL / procedure set-up.

P.s. I did mention in the orginal post that it was an SQL2005 DB.
I deleted the post once i realised my foolish mistake.

Thanks again.

SQL and Novell [modified]

aastudent13-May-09 20:16

aastudent

13-May-09 20:16

Hi
I am having trouble setting up SQL 2008 Express on a Novell network. I want to host the database on one of the client PCs and then connect to it from both that PC and the rest of the client PCs. However, I am unable to log in from the other client PCs. This is the first time I am setting up an SQL server, so I really have no idea where I am going wrong.

Thanks
Karl

modified on Thursday, May 14, 2009 4:56 AM

Re: SQL and Novell

scottgp14-May-09 0:19

14-May-09 0:19

Do you mean Netware? If so, I haven't used that in about 15 years. Does SQL Server run on Novell? http://stackoverflow.com/questions/32144/why-cant-sql-server-run-on-a-novell-server[^]
Scott

Re: SQL and Novell

aastudent14-May-09 3:32

aastudent

14-May-09 3:32

Yes, it should work. The host machine is running Windows XP but is a client on a Novell network. I'm trying to connect over TCP/IP. I am using a c# app that need to log in to the db - maybe I am setting the connection string incorrectly?

Data Source = x.x.x.x,1433\SQLEXPRESS or x.x.x.x\SQLEXPRESS,1433? ; Network Library=DBMSSOCN

Do I have to connect with a user ID and password over TCP/IP?

With the above I am not even able to connect from the host machine (it works fine when I substitute with NovellClientName\SQLEXPRESS though)

Re: SQL and Novell

scottgp14-May-09 4:32

Stored Procedure problem >>

14-May-09 4:32

Like I said, I haven't used Novell in a long time, so I won't be able to duplicate anything. However, I would think you'd need to specify a username/password, unless you're specifying a Trusted_Connection. Have you checked out the connection strings bible - http://www.connectionstrings.com/sql-server-2005[^]?
Scott

kindman_nb13-May-09 15:29

kindman_nb

13-May-09 15:29

Hay ,
I edit a stored procedure to select a Name column from various tables as the next :

Create PROCEDURE [dbo].[SelectName]<br />
	@table_name sysname,<br />
	@sName nvarchar(50)<br />
AS<br />
<br />
exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =' +  @sName  )

then when i execute it and pass the next valriables

<br />
@table_name = 'emp'<br />
@sName = 'NameTest'<br />

the next error message appear

<br />
Invalid column name  'NameTest'<br />

what the error here ,,
thanks

jooooo

scottgp13-May-09 16:19

13-May-09 16:19

If you add a print statement to your stored procedure, such as:

print ' SELECT * FROM ' + @table_name + ' WHERE Name =' + @sName

and execute it, you'll see this as output:

SELECT * FROM emp WHERE Name =NameTest
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameTest'.

From there you can see that it's trying to compare the Name column to what it interprets as another column name - not a text literal. You need to put the literal in quotes, so your procedure would need to look like:

exec ( ' SELECT * FROM ' + @table_name + ' WHERE Name =''' + @sName + '''')

That being said, that code is dangerous, and you should read up on SQL injection, e.g. SQL Injection Attacks and Some Tips on How to Prevent Them[^]

Scott

kindman_nb14-May-09 2:07

kindman_nb

14-May-09 2:07

Thanks for reply

I use print as you said
<code>print ' SELECT * FROM ' + @table_name + ' WHERE Name ="' + @sName + '"'

it's ok no errors ,, but no rows are returned ,, for eg

@sName = "Jooo"

this name is already exit but no rows return ,,

I test the same statement but with another column type

<code>print ' SELECT * FROM ' + @table_name + ' WHERE ID =' + @iID

@iID = 1

it's ok and return the the required row,

what the diff between nvarchar and int column to get the int and nvarchar no

jooooo

scottgp14-May-09 4:14

14-May-09 4:14

The print statement that I mentioned was just to help you debug what was happening. Did you also change your exec statement as I recommended?

The difference between the nvarchar and int columns is that to find something in the nvarchar column you need to specify a text literal, which needs to be in quotes. If it's not in quotes it's going to interpret it as another column name. To find something in the int column you specify a numeric literal, which doesn't need to be in quotes, and the numerical literal won't be interpreted as a column name.
Scott