Re: STORE PROCEDURE: Problems with update statement - Database Discussion Boards

Jörgen Andersson14-May-09 22:06

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

Re: Create "report query"

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

Re: Create "report query"

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.

Re: Create "report query"

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

How to create an exist database [modified]

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

scottgp

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

scottgp

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

Stored Procedure problem >>

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

Re: Stored Procedure problem >>

scottgp13-May-09 16:19

scottgp

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

Re: Stored Procedure problem >>

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

Re: Stored Procedure problem >>

scottgp14-May-09 4:14

scottgp

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

Re: Stored Procedure problem >>

Rob Philpott14-May-09 1:16

Rob Philpott

14-May-09 1:16

Err, the table 'emp' doesn't have a column 'NameTest'?

You want to try and avoid that dynamic sql stuff. No query plans and not safe.

Regards,
Rob Philpott.

Filter where sum is not Zero [modified]

Lash2013-May-09 10:45

Lash20

13-May-09 10:45

I have a table where I need to show data where the total amount is not zero e.g

Select iName, sum(Amount) as Total
From TableA
group by iName
having Sum(Amount) <> 0

How do I filter only the following (the sum for Joe is 0)?

iName imonth Amount
JOE 1 0
JOE 2 0
JOE 3 0

and NOT the following (the sum for Joe is also 0 but I want this included)..

iName imonth Amount
JOE 1 10
JOE 2 -5
JOE 3 -5

Any help would be appreciated

Thanks

modified on Wednesday, May 13, 2009 5:16 PM

Re: Filter where sum is not Zero

_Damian S_13-May-09 14:16

_Damian S_

13-May-09 14:16

You need to include iMonth in your select list and grouping if you want Joe to be returned as follows:

iName imonth Total
JOE 1 10
JOE 2 -5
JOE 3 -5

because the Sum of the Amount is not 0 for each month.

Here's the adjusted query:

Select iName, imonth, sum(Amount) as Total
From TableA
group by iName, imonth
having Sum(Amount) <> 0

Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!!

Booger Mobile - Camp Quality esCarpade 2010

Using object database db4o for data access

netquake13-May-09 6:32

netquake

13-May-09 6:32

Hi!

In case you're interested, the db4o guys added support for IUpdatable and IQueryable. You can run LINQ queries against this .NET native object database.

http://developer.db4o.com[^]

Also, there's a quick reference guide (6 pages) for .NET developers.

http://refcardz.dzone.com/refcardz/getting-started-db4o[^]

Best!!

not be able to open SQLSERVER 2005 error 80040e4d

Royaltvk13-May-09 4:54

Royaltvk

13-May-09 4:54

HI,

Iam facing problem to connect Sqlserver 2005 with this connection string

Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=master;DataSource=RAYALU\SQLEXPRESS

iam Able to connect sqlserver 2000 with same above mentioned connection string.

I need some OLEDB connection string provider which works on both SqlServer2000
and Sqlserver2005.

Please Guide how to connect atleast Sqlserver2005.

Thanks,
Rayalu

Re: not be able to open SQLSERVER 2005 error 80040e4d

Ian McCaul13-May-09 6:02

Ian McCaul

13-May-09 6:02

Look at www.connectionstrings.com[^] the problem you are having I believe is with your provider. The provider clients for Sql Server 2000 and 2005 are different.

How I can get the Fields DataBase names of X Table ?

E_Gold13-May-09 4:34

E_Gold

13-May-09 4:34

Hi
How I can get the Fields Database names of X Table ?

(Query or C# cod..... or both)

(i work with Access)

thank's in advance

Re: How I can get the Fields DataBase names of X Table ?

Henry Minute14-May-09 8:38

Henry Minute

14-May-09 8:38

Your question got voted down at least partly because there are loads of hits from Google that answer this.
BTW not by me.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Get list of OleDb providers with C#

musefan13-May-09 4:25

musefan

13-May-09 4:25

Hi All,

I am currently working on a part of my project (using windows forms in C#.Net) which involves creating connection string based on values.

I have a simple form where the user can specify the type of database (SQL Server, Access etc.) and then give specific connection information based on the database type selected.

One thing I would like to be able to do is to allow the user to specify the OleDb provider when using an access database. I can easily allow free text entry for the provider but it would be much more user friendly if I could give the user a drop down of available providers for selection.

So... Does anybody know have I could retrieve such a list?

Thanks

...Oh, and before anybody wants to complain, I have had no luck with google

Life goes very fast. Tomorrow, today is already yesterday.

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