Re: Filter where sum is not Zero - Database Discussion Boards

scottgp14-May-09 4:32

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.

Re: Get list of OleDb providers with C#

Mark Shteinberg25-Jul-10 6:10

Mark Shteinberg

25-Jul-10 6:10

Hi I`m searching for the same thing.
The only thing i have found is using SQL function
EXEC master..xp_enum_oledb_providers

Is it possible to store a large array in Excel... [modified]

new_phoenix13-May-09 4:03

new_phoenix

13-May-09 4:03

so that a customized user function can access it in order to search through it? The array should not be stored in a spreadsheet but it should be stored programmatically as a global variable.

It appears that there are limitations to the size of a function file so it cannot be stored in the customized function file itself. Plus, it does not make sense for the function to load the array each time that the function is called.

modified on Wednesday, May 13, 2009 10:27 AM

Re: Is it possible to store a large array in Excel...

_Damian S_13-May-09 14:09

_Damian S_

13-May-09 14:09

What are you trying to achieve? Why the need for the large array? What is it doing currently? How large is large?

Have you considered storing the array data in a table in whatever database you are using, and accessing it that way? Much better than trying to mangle Excel to behave like a database.

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

Booger Mobile - Camp Quality esCarpade 2010

Re: Is it possible to store a large array in Excel...

new_phoenix13-May-09 15:24

new_phoenix

13-May-09 15:24

I need to develop a specialized user function that will search an array consisting of 16500+/- records and two columns. If I were to load the array each time the function is used, and if there were 10,000 records in the spreadsheet to whic the formula should be applied, it would take a great deal of time. Thus, it would be more appropriate to store the array as a global variable and to only search throuh the array in the function.

Is it possible to store the array as a global variable and to access the variable inside a customized function? To create a customized function, press Alt-F11 key at the same time and then "Insert > Module". A new customized function can be written here, but it has a certain file size limitation that will not accommodate the two dimensional array. To use the new function select "Insert > Function".

Re: Is it possible to store a large array in Excel...

_Damian S_13-May-09 16:20

_Damian S_

13-May-09 16:20

How is the array going to help you more then having the 16500 records in a table, and using SQL to extract the records you want?

What specifically are you doing?

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

Booger Mobile - Camp Quality esCarpade 2010

Re: Is it possible to store a large array in Excel...

new_phoenix15-May-09 3:48

new_phoenix

15-May-09 3:48

Read this article:

http://office.microsoft.com/en-us/excel/HA011117011033.aspx?pid=CL100570551033[^]

OK. I have an array created inside the function, and then I search through the array for a match of a value and return the second value as the result. The problem is that it does not make sense to create the array each time the function is run. Can it be stored some place else so that the function has access to it?

colorize partial cell in rowset of listview

sal2113-May-09 1:45

sal21

13-May-09 1:45

How to colorize partial background of item in rowset of listview?
example:
have my_var=75 colorize 3/4 of item, have my_var=50 colorize 1/2 of itema, have my_var=72 colorize ecc...
Similar a progressbar but stop for filling based value in my_var...

Re: colorize partial cell in rowset of listview

Eddy Vluggen13-May-09 3:30

Eddy Vluggen

13-May-09 3:30

sal21 wrote:
How to colorize partial background of item in rowset of listview?

You can "enable" drawing your own stuff by using owner-drawing[^] for the ListBox. Next, use the e.Graphics.FillRectangle method to fill your cell to the desired value. There's an example on drawing your own ListBox-parts.

Good luck Smile | :)

I are troll Smile | :)

Re: colorize partial cell in rowset of listview

sal2113-May-09 3:35

sal21

13-May-09 3:35

but i need for vb6 classic, no net.
tks.

error message when adding new sqldatabase item!

Hussam Fattahi12-May-09 21:27

Hussam Fattahi

12-May-09 21:27

When i try to add a SQL Database item from "Add New Item" menu to my ASP.NET project i always get this error message

an error occurred while processing the local data file:
key not valid for use in specified state.

Help, thanks

MSAccess Query Problem using LIKE

M Riaz Bashir12-May-09 20:06

M Riaz Bashir

12-May-09 20:06

Hi,

Kindly let me know that, How may I use following Oracle Query into MSACCESS ?

SELECT PCODE,PNAME FROM PRODUCT WHERE PNAME LIKE('%APPLE%')

Thank you in Advance
(Riaz)

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