Re: I want help on Sql query - Database Discussion Boards

Soleil couchant10-Jun-07 20:24

10-Jun-07 20:24

Hi there,

I have a stock table and a barcode table. A stock can have many barcode records and a barcode belongs to only one stock.

How can I query the database, so as it returns me those stock which have more than one barcode?

Thanks for helping,

David

Re: Count related records

andyharman10-Jun-07 22:22

andyharman

10-Jun-07 22:22

select StockCode, count(*) BarcodeCount
from BarCode
group by StockCode
having count(*) > 1

user mysql [modified]

WhiteGirl2310-Jun-07 19:22

WhiteGirl23

10-Jun-07 19:22

Hi,
I use mysql on my project and I can connect to mysql with my IP,this is a good thing.
But the problem is that I can't create a user.
My sintax is:

cmd1.CommandText = "GRANT ALL PRIVILEGES ON test .* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;"<br />
cmd1.ExecuteNonQuery()<br />
<br />
cmd1.CommandText = "flush privileges;"<br />
cmd1.ExecuteNonQuery()<br />

The problem is that when I connect to mysql with localhost this command works and when I connect with my IP it doesn't work.

Thank .

Re: user mysql

ScottM110-Jun-07 21:23

ScottM1

10-Jun-07 21:23

The error doesn't appear to be anything to do with the above command as it works with localhost.

WhiteGirl23 wrote:
I use mysql on my project and I can connect to mysql with my IP,this is a good thing.

Can you execute any command at all when using your IP? e.g A simple select statement.

WhiteGirl23 wrote:
it doesn't work.

What does it do when it doesn't work? Does it throw any exception?

There are 10 types of people in the world, those who understand binary and those who dont.

Re: user mysql

Paddy Boyd11-Jun-07 2:48

Paddy Boyd

11-Jun-07 2:48

What user are you connecting to the DB with?

Re: user mysql

WhiteGirl2311-Jun-07 20:56

WhiteGirl23

11-Jun-07 20:56

Now it works.
I found a sql command for creating user which works with IP and localhost.

Thanks

I want help on Sql query

Parameswar Mal10-Jun-07 2:07

Parameswar Mal

10-Jun-07 2:07

Hi Friends I have a problems
I have a table Employee
two fields Name, Age
Name Datatype is varchar()
Age Datatype is Int()
the table look likes this
Name.........Age
Ram.............23
Steve............22
when i swap this two fields using this query"update Employee set Age = Ename,Ename=Age;'
then the error occured
error is "Syntax error converting the varchar value 'Ram' to a column of data type int."

If i changed the datatype of Age is "Int" to "Varchar" then the query runs.
My question is if i don't changed the datatype then how i swap the two fields
Plz help me....

Re: I want help on Sql query

mr_lasseter10-Jun-07 5:57

mr_lasseter

10-Jun-07 5:57

It seems to me you don't understand the difference between a varchar column versus an int column. A varchar column is used to store text while an int column is used to store numbers.

You cannot do what you are trying do, becuase you cannot put Text into an Integer column, which is why you get the error message 'Syntax error converting the varchar value 'Ram' to a column of data type int'.

Further, I don't see any reason why you would ever want to do this, as it just doesn't make any sense.

Mike Lasseter

Re: I want help on Sql query

dotnetSong10-Jun-07 12:18

dotnetSong

10-Jun-07 12:18

"cast age as varchar" convert data type for select caluse,dot know update

Re: I want help on Sql query

dotnetSong10-Jun-07 12:20

dotnetSong

10-Jun-07 12:20

how about 'Cast age as varchar', not sure

Sql query

Parameswar Mal10-Jun-07 1:10

Parameswar Mal

10-Jun-07 1:10

hi want to find the out puts

There are 2 fields namely name,mark
The following are the values in the field
name....mark
A...........10
A...........10
B...........20
C...........15
C............5

my output should be in the form :-----
name...........mark
A................20
B................20
C................10
Please write a query for this ???

Re: Sql query

Colin Angus Mackay10-Jun-07 1:43

Colin Angus Mackay

10-Jun-07 1:43

Parameswar Mal wrote:
Please write a query for this ???

Please specify the route between the input and output. It is not obvious.

A seems to add the marks
C substracts one from the other. What are the rules for this? Given that a SQL works on SET based operations with no intrinsic order how do you work out which number should be subtracted from the other.
I don't know what B does as there is only one of them.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Sql query

Arun.Immanuel10-Jun-07 15:28

Arun.Immanuel

10-Jun-07 15:28

Are you trying to find the average or sum?
If C's mark is wrong, then
select A,sum(mark) from tblName group by name order by name

If A's mark is wrong, then
select A,avg(mark) from tblName group by name order by name

Regards,
Arun Kumar.A

Check other open connections

Joe Smith IX9-Jun-07 23:57

Joe Smith IX

9-Jun-07 23:57

Hi there,

In SQL Server, is there anyway to check other open connections (who else currently connected to the database)?

Thanks.

Re: Check other open connections

Mike Dimmick10-Jun-07 0:12

Mike Dimmick

10-Jun-07 0:12

sp_who reports all current connections.

The procedure takes its data from the sysprocesses view (SQL Server 2000) or the sys.dm_exec_connections dynamic management view in SQL Server 2005.

Stability. What an interesting concept. -- Chris Maunder

Documentation tool for SQl SERVER DATABASE

xbiplav9-Jun-07 17:57

xbiplav

9-Jun-07 17:57

I want to document sql server express database.
Is there any free tool available to document sql server express database?

Could you people provide me link or something?

X

I can't execute an ms access query in Ado .net ? :(

Hamed Musavi9-Jun-07 4:01

Hamed Musavi

9-Jun-07 4:01

Here's what I do:

strProcName = name of the query in access that works fine in access itself.

using (OdbcConnection cnn = new OdbcConnection("Dsn=LclPMedicHist;"))
{
    using (OdbcCommand cmd = new OdbcCommand (strProcName, cnn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        OdbcParameter prm;

        prm = new OdbcParameter("szName", OdbcType.NText, 50);
        prm.Value = Name;
        cmd.Parameters.Add(prm);

        ....

        cnn.Open();
        cmd.ExecuteNonQuery();
    }
}

ExecuteNonQuery() throws an exception:

An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll

Additional information: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL 
statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I've even tried to use "?" as parameters names, or put "PROCEDURE " directly before query name. It didn't work. Can any one help me please? I'm really a newbie in C# and .Net.

Re: I can't execute an ms access query in Ado .net ? :(

Paul Conrad9-Jun-07 5:57

Paul Conrad

9-Jun-07 5:57

Hamed Mosavi wrote:
cmd.CommandType = CommandType.StoredProcedure;

have you tried using CommandType.Text? Access only has queries, no stored procs (unless it is an Access Database App that ties in with SQL Server).

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Re: I can't execute an ms access query in Ado .net ? :(

Hamed Musavi10-Jun-07 0:20

Hamed Musavi

10-Jun-07 0:20

Paul Conrad wrote:
have you tried using CommandType.Text?

Yes. No difference. I don't know. perhaps Ado .Net does not support it Confused | :confused:

I transfered queries into my C# code Frown | :(

I'm still wondering what exactly others use in place of mdb files, when the customer can not afford SQL Server or even does not need that at all(Small databases that has not many users).

// "Life is very short and is very fragile also." Yanni
while (I'm_alive)
{
cout<<"I love programming.";
}

SQL query

webhay9-Jun-07 0:35

webhay

9-Jun-07 0:35

I have two customer and order table how can i return all customer has no orders.
Thank you
hoho

Re: SQL query

P_pk9-Jun-07 1:38

P_pk

9-Jun-07 1:38

i didn't get the clear picture.give the condition by using 'where'

PPK

Re: SQL query

Krish - KP9-Jun-07 2:14

Krish - KP

9-Jun-07 2:14

your question is not clear. can you please provide table structure and sample data. which will help us in solving your query.

Regards
KP

Re: SQL query

webhay9-Jun-07 2:27

webhay

9-Jun-07 2:27

Thank you for you reply.

I have customer table and Customer order and there is a PK in the customer table and FK in the order table i need to get all coustomer has no record in the order tables

Thank you

Re: SQL query

Krish - KP9-Jun-07 3:32

Krish - KP

9-Jun-07 3:32

this can be done using two methods
1. outer join
2. not exists
you can choose one on these two.

Regards
KP

Local databse

maksim3108-Jun-07 16:38

maksim310

8-Jun-07 16:38

Hi,

I am writing a program in VB.NET (VS2005 Pro) that essentially simulates an neurology experiment. This program's data shoould consiste of 8 independent tables, each having 13 columns and up to 100,000 rows in each table. To make it simpler, all data will be in string format.
Other requirments:
1. The tables needs to be sortable like a dictioanry where 1 column will act as a key.
2. The user needs to be able to see the data in a tabular format.
3. The data must be held on the user's local machine, not a server and not remotley.
4. Needs to easily be readable by Excel, doesn't matter how. It can convert to an XML file and then somehow be automatically converted to xls.

I am thinking of using a databse becasue I would like to eventually create a login screen to link previous experiment values to a user, but that is not essential. I used dynamic multi-dimensiaonal arrays but find it difficult to work with, and extremley difficult to sort.

I was looking at the idea of using an XML database which will probbaly solve any issues of excel compatability as well as provide support for displaying the information on a website sometime in the future. I can't figure out how this all works.

Can anyone recommend a way to display a large amounts of generated data on a local computer, and almo make it save-able. I also like Oracle's 10g Express thing, but don't know if it would work for my specific problem becasue the cusotmer support people at Oracle don't speak English very well.

Thank You,

Maksim
UCLA, Neurology

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