Re: Beginner Q: How to store and retrieve an Image in ADO.NET? - Database Discussion Boards

using sp_tables and ADO recordset problem?

Bart-Man4-Dec-02 8:37

4-Dec-02 8:37

Greetings,

I have been trying to use the "sp_tables" procedure from ADO, and it seems to return a recordset with the count of "0". I am working with
the VC++ .Net studio under windows 2000.

I am connected to the sql server with "no" intial catolog using ADO.
I have a database within the system called "MyTestDB", and I tried
to access the tables from within the database but I get a recordset
of "0" count.

I can however use the "SQL Enterprise Manager" to send the command
and get a listing of my tables, but for some reason ADO just isn't
working correctly.

Here is a sample of the sql code I am using.
----------------------------------------
USE MyTestDB EXEC sp_tables

Any ideas, or suggestions?
Thanks in advance for any help! Smile | :)

Re: using sp_tables and ADO recordset problem?

leppie4-Dec-02 9:32

leppie

4-Dec-02 9:32

rather do this (C# but should be readable)

sqlConnection.Open();
sqlConnection.ChangeDatabase("MyTestDB");

then setup a SqlCommand to handle the stored procedure like you did in the sp_databases run it while the connection is open. I dont think a dataadapter will work here. Just loop thru the SqlDataReader...or fixed the connection string Smile | :)

Cheers

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Re: using sp_tables and ADO recordset problem?

Bart-Man4-Dec-02 10:13

Bart-Man

4-Dec-02 10:13

Hello,

Thanks for your response. Smile | :)

It seems that the current database interface that I am using is ADO 2.6, and I can't use "ChangeDatabase"
which is not in that interface.

Is there a way to do this in the older versions of ADO?

Re: using sp_tables and ADO recordset problem?

leppie4-Dec-02 10:20

leppie

4-Dec-02 10:20

Have you tried running "USE MyTEstDB" as a command 1st and then run the sp_tables stored procedure?

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Re: using sp_tables and ADO recordset problem?

Richard Deeming5-Dec-02 0:17

Richard Deeming

5-Dec-02 0:17

Try EXEC MyTestDB.dbo.sp_tables

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Re: using sp_tables and ADO recordset problem?

Bart-Man5-Dec-02 3:52

Bart-Man

5-Dec-02 3:52

Hello,

The "EXEC MyTestDB.dbo.sp_tables" WORKED!!!
Thanks that is exactly what I needed. Smile | :)

Beginner Q: How to store and retrieve an Image in ADO.NET?

matthias s.4-Dec-02 0:33

matthias s.

4-Dec-02 0:33

Hi *.*!

I don't seem to figure out how to store an Image in the database. I have thumbnail-sized images (up to 5k) and need to store them. What db-datatype should I use? How can I effectively read and write the value?

Thanks in advance!

Matthias

In theory, there is no difference between theory and practice. In practice, however, there is.

(unknown author)

Re: Beginner Q: How to store and retrieve an Image in ADO.NET?

Mazdak4-Dec-02 2:02

Mazdak

4-Dec-02 2:02

http://codeproject.com/cs/database/albumviewer.asp[^]

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Beginner Q: How to store and retrieve an Image in ADO.NET?

matthias s.4-Dec-02 2:17

matthias s.

4-Dec-02 2:17

Thanks!

Matthias

In theory, there is no difference between theory and practice. In practice, however, there is.

(unknown author)

DataView row filtering

Dr_Sh0ck3-Dec-02 19:20

Dr_Sh0ck

3-Dec-02 19:20

Hi, ALL. There is a small problem. A have two tables in master-detail relationships (farther-child). Master table is bound to DataView object. How can I apply filtering conditions on the child table. In other words, how can I refer to child table within RowFilter string.

Best way to check if a database exists, so it can be created if it doesn't exist using SQL server?

Bart-Man3-Dec-02 6:47

Bart-Man

3-Dec-02 6:47

Hello,

I was wondering what the best way to check if a database exists, so it can be created if it doesn't in SQL server?
Right now I just use a try catch block, and see if it fails, but is there a better way since the "_com_error &e" try catch shows the same hresult on all sql errors when using Visual C++ .Net.

Thanks in advance for any help!!

Re: Best way to check if a database exists, so it can be created if it doesn't exist using SQL server?

leppie3-Dec-02 8:46

leppie

3-Dec-02 8:46

You can just run the sp_databases stored procedure via a SqlCommand to get a list of all the databases. Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Re: Best way to check if a database exists, so it can be created if it doesn't exist using SQL server?

Bart-Man3-Dec-02 9:35

Bart-Man

3-Dec-02 9:35

Excellant that is exactly what I needed!! Thanks, it worked great!
Thanks again!!! Smile | :)

How to set AllowZeroLength property to "Yes" ?

Smitha Nishant3-Dec-02 0:36

Smitha Nishant

3-Dec-02 0:36

Hi all,

I am trying to create a table in an MS Access DB, from my app. I want to set the "AllowZeroLength" property of a field in the new table to "yes". How can i specify that in the "Create table" statement ? Or atleast in an "Alter table" statement ?

Thanks
Smitha

It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.

Question about Select Statement

Mazdak2-Dec-02 9:28

Mazdak

2-Dec-02 9:28

I have an table like this:

Date | Priority | PrimaryKey
====== ========= ======
12/2/2002 101 1
12/3/2002 101 2
12/4/2002 101 3
12/5/2002 101 4

12/6/2002 111 5
12/11/2002 111 6
12/12/2002 111 7
12/13/2002 111 8

12/4/2002 121 9
12/14/2002 121 10
12/15/2002 121 12
12/16/2002 121 13

I want to make query command to get ONLY PRIMARYKEY of minimum date for each group of prirority:

12/2/2002 101 1
12/6/2002 111 5
12/4/2002 121 9

If I use this command:
select min(Date),PrimaaryKey from table3 where (Date< GetDate() ) group by Priority

I'll get two column but I have to only get PrimaryKey column because I want to use in WHERE of UPDATE command.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Question about Select Statement

Jeremy Oldham2-Dec-02 9:45

Jeremy Oldham

2-Dec-02 9:45

What database are you using? SQL Server 7.0, SQL Server 2000, MS Access, Oracle, etc...

If you are using SQL Server then you could do something like this:

Select a.Date, a.Priority, a.PrimaryKey
From table as a INNER JOIN
(Select Priority, Min(Date) as Date
From table
Group by Priority) as b
on a.Priority = b.Priority
and a.Date = b.date

Let me know if this doesn't work or if you are using a different database.

Jeremy Oldham

Re: Question about Select Statement

Mazdak2-Dec-02 20:22

Mazdak

2-Dec-02 20:22

Thanks for reply. Smile | :)

joldham wrote:
What database are you using?

SQLServer 2000.

I'm not at my desk to test your command but I got the point from it.Thanks again. Smile | :)

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

DataSet question

Paul Silvernail1-Dec-02 6:19

Paul Silvernail

1-Dec-02 6:19

I am playing around with a DataSet and a relational database. The following code does work...

MessageBox.Show (profileSet.Tables["Profiles"].Rows[0]["TextureID"].ToString());

My questions are:
Does the DataSet contain a table with the Text value for the TextureID key?
If yes, what would the code look like to access it?

Thanks
Paul

Re: DataSet question

Mazdak1-Dec-02 7:53

Mazdak

1-Dec-02 7:53

Paul Silvernail wrote:
Does the DataSet contain a table with the Text value for the TextureID key?

No.You have to cast to string.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: DataSet question

Paul Silvernail1-Dec-02 9:17

Paul Silvernail

1-Dec-02 9:17

Guess I should have been a little more clear. I wanted to look up the value that the TextureID identified in a Textures table. From what I can tell, I need to make another adapter to look up the value.

I know the dataset has a collection of tables and I thought (more like hoped) that tables were added for each column that was a key.

Another Access Memo Question

Paul Silvernail29-Nov-02 13:48

Paul Silvernail

29-Nov-02 13:48

Hi All,

I was trying to read a memo field with the following code ....

string strComment = TestpitReader.GetString(4);

Could someone correct what I am doing wrong ??

Thanks
Paul

Re: Another Access Memo Question

Not Active29-Nov-02 16:03

Not Active

29-Nov-02 16:03

What kind of error are you getting? Return the field as an object and check the type. Is it possible it isn't what you expect?

Re: Another Access Memo Question

Paul Silvernail29-Nov-02 17:05

Paul Silvernail

29-Nov-02 17:05

I just found the error.

My database did not have an empty string for the default value. When I tried to read a memo with no value I got a "Specified cast is not valid" error.

Thank you for responding !!

Paul

How do I run create scripts over the web?

leppie29-Nov-02 9:36

leppie

29-Nov-02 9:36

Hi

I let SQL create me a create script for some stored procedures that has changed. The problme is how do I run this. I thought dropping this as CommandText for a SqlCommand would be OK, but this is not the case. Lotsa errors with GO statement. This what I get from SQL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SelectUser]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.SelectUser
	@id bigint
AS
	SET NOCOUNT ON;
SELECT     ID, Username, Password, Email, Name, Sex, HomeTel, Language, WorkTel, CellTel, Address, City, Province, IDNr, PostalCode, JoinDate, Views, 
                      HomeShow, WorkShow, CellShow
FROM         Users
WHERE     (ID = @id)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I have pasted this code in query analyser and it runs flawlessly. What am I doing wrong?

Cheers Smile | :)

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

Sorted, kind of....

leppie29-Nov-02 12:14

leppie

29-Nov-02 12:14

The problem seems to be the GO statement. When I select all the commands between the GO's , it can run them individually. But thats a bit Eek! | :eek:

too much work.

Any suggestions how to run it as a batch?

DBHelper - SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

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