ODBC question - Database Discussion Boards

Colin Angus Mackay9-Jul-04 11:52

9-Jul-04 11:52

In the stored procedure you just SELECT the relevant data. In the .NET application open a SqlConnection, create a SqlCommand to call the Stored Procedure and then call SqlCommand.ExecuteQuery() to get a DataReader to extract it*

Does this help?

*You can also uses crazy things like DataAdapters and DataSets, but y'know I don't really like them

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

Re: Retrieve Data from Stored Proc

mikasa9-Jul-04 12:10

mikasa

9-Jul-04 12:10

Sorry, I should have been more specific. Unfortunately, I do need to get this data into a DataTable or DataSet. Anyone have an idea? I know the DataAdapter uses a DataReader internally, but I don't know how I could implement this...I'm sure it would be super difficult.

Re: Retrieve Data from Stored Proc

rudy.net9-Jul-04 12:29

rudy.net

9-Jul-04 12:29

In the "Server Explorer" window select the connection to your database that contains the stored procedure and drag it to your form. This will create a SqlConnection object and a SqlCommand object, for example conn and cmd.
Then you can fill your DataSet with the following commands:

conn.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet("datasetName");
dataAdapter.Fill(dataset);
conn.Close();

Re: Retrieve Data from Stored Proc

mikasa12-Jul-04 5:46

mikasa

12-Jul-04 5:46

Yeah, that's not going to work either, tried that before...

The problem is that the Stored Proc is Dynamically creating Columns and Data depending on what Parameters you pass to it. Even in the Designer, I can't get it to Generate a DataSet because ADO.NET what hard-defined structures of Data when working with the Designer. However, I can run the Data Preview and it works fine, but that is because in the "Preview" mode, the data is being retrieved through a DataReader, not a DataTable.

Why can't the DataCommand Object just return a DataTable like it did in VB6!? Sigh | :sigh:

Re: Retrieve Data from Stored Proc

Michael Potter12-Jul-04 8:56

Michael Potter

12-Jul-04 8:56

I have no trouble executing SPs like you descibe. Here is a sample of code I just wrote today:

public static DataSet GetEventList(
     int activeState, 
     int eventId,
     int eventDateStatus,
     DateTime eventDateStart,
     DateTime eventDateEnd,
     int createDateStatus,
     DateTime createDateStart,
     DateTime createDateEnd,
     int ntdStatus,
     int eventTypeId,
     string firstName,
     string lastName)
{
     DataSet ds = null;
     SqlCommand cmd = null;
     SqlDataAdapter da = null;

     SqlConnection conn = GetConnection();
     if (conn != null)
     {
          try
          {
               cmd = new SqlCommand("EventListGet",conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter p = cmd.Parameters.Add("@ActiveState",SqlDbType.Int);
               p.Value = activeState;
                    
               p = cmd.Parameters.Add("@EventId",SqlDbType.Int);
               p.Value = eventId;

               p = cmd.Parameters.Add("@EventDateStatus",SqlDbType.Int);
               p.Value = eventDateStatus;
                    
               p = cmd.Parameters.Add("@EventDateStart",SqlDbType.DateTime);
               p.Value = eventDateStart;
                    
               p = cmd.Parameters.Add("@EventDateEnd",SqlDbType.DateTime);
               p.Value = eventDateEnd;

               p = cmd.Parameters.Add("@CreateDateStatus",SqlDbType.Int);
               p.Value = createDateStatus;
					
               p = cmd.Parameters.Add("@CreateDateStart",SqlDbType.DateTime);
               p.Value = createDateStart;
					
               p = cmd.Parameters.Add("@CreateDateEnd",SqlDbType.DateTime);
               p.Value = createDateEnd;

               p = cmd.Parameters.Add("@NTDState",SqlDbType.Int);
               p.Value = ntdStatus;

               p = cmd.Parameters.Add("@EventTypeId",SqlDbType.Int);
               p.Value = eventTypeId;

               p = cmd.Parameters.Add("@FirstName",SqlDbType.VarChar,50);
               p.Value = firstName;

               p = cmd.Parameters.Add("@LastName",SqlDbType.VarChar,50);
               p.Value = lastName;
					
               da = new SqlDataAdapter(cmd);
               ds = new DataSet();
               da.Fill(ds);
          }
          catch (Exception ex)
          {
               MessageBox.Show(ex.Message,"Load Event List Failed");
               if (ds != null)
               {
                    ds.Dispose();
                    ds = null;
               }
          }
          finally
          {
               if (da != null) da.Dispose();
               if (cmd != null) cmd.Dispose();
               if (conn != null) conn.Close();
          }
     }

     return ds;
}

Re: Retrieve Data from Stored Proc

mikasa12-Jul-04 10:56

mikasa

12-Jul-04 10:56

Ok, this is very similar to what I tried. Do you think it matters if the Stored Proc is creating and using a Temporary Table (ex. ##TempTable)? I get errors on my Temp Table in the Designer. It doesn't make any sense because I can run this in the Query Analyzer without fail every time!

Re: Retrieve Data from Stored Proc

Michael Potter12-Jul-04 11:18

Michael Potter

12-Jul-04 11:18

Are you sure the table is being DROP'ed properly at the end of your SP?

What is the error you are getting?

ODBC question

Anonymous9-Jul-04 10:04

Anonymous

9-Jul-04 10:04

Has anyone experienced a problem with using an odbc driver where it retrieves some records without any problems, but other records are coming back as it was from a different world?

Here's the layout, I'm reading data from a dataflex table using Connx ODBC driver. When I run a simple SQL query statement on a query tool, for ex, Select * From tablename I noticed half the rows are garbage. Here's the funny part, I run a report, Cystal Reports, using their own driver to connect to the database and all the rows are fine.

Any advice would be helpful. Thanks.

DataReader vs DataSet or other solution, please help

metaskull9-Jul-04 5:18

metaskull

9-Jul-04 5:18

Perhaps someone can help me out here. I'm working on a Microsoft Management Console style application. I'm storing all the data to populate my Treeview, listview, menuitems, etc.. in SQL, becuase they're often gonig to change. Also, I'll be needing to make alot of calls to data, because I'm building in alot of filters for the data as well. For example, the user will often wish to filter results in the listview, new nodes will be added and deleted from the treeview... etc. On one hand, I can use a dataset, which is very useful since I'll have a local datastore that I can query againts as much as I want without having to make a new database connection. Plus I can update, insert, etc if I need to without changing my sql data and commit the changes when I'm ready. But as we all know, datasets are not cheap. On the hand I can just use datareaders and get whatever data I need when I need it from sql. Probably would employ connection pooling to ease the pain on DB connections, but still... I wanted to avoid making tons of database calls. Not sure what to do, I'm sure there are other ways to get this done, anybody have any ideas? Thank you.

Regards,
MSk

Re: DataReader vs DataSet or other solution, please help

Colin Angus Mackay9-Jul-04 10:20

Colin Angus Mackay

9-Jul-04 10:20

metaskull wrote:
I'm storing all the data... in SQL, becuase they're often going to change.

To me that one sentence says DataReader. (BTW, did you know that a DataSet uses a DataReader to populate it.)

If you use a DataSet then you are pulling the data across and storing it in one structure, you are then going to copy that data into your Tree & List Views and MenuItems.

metaskull wrote:
On one hand, I can use a dataset, which is very useful since I'll have a local datastore that I can query againts as much as I want without having to make a new database connection

You know that if you pull across too much data then the cost of querying into the DataSet becomes more expensive than the roundtrip to the database server? You need to ask yourself how much data are you bringing across.

* Disclaimer: I am not a fan of DataSets. I think they are too bulky for everyday use. IMO they are fine for putting together a scratch application that will be thrown away, but I much prefer pulling the data across through a DataReader and building my own business objects directly.

Data comparison

Shanmukeswara Rao8-Jul-04 22:20

Shanmukeswara Rao

8-Jul-04 22:20

Hi all
I have data in MS access 97 getting from two deferent sources, I need to compare these data based up on a field called “NAME” Text data type. Here there is a problem i.e. small variation in spelling in the data, what is the best approach to get a list of matched names or partly matched records in both tables.

Regards
shanmuk

Help Please in the installation of Mysql

JawedVikia808-Jul-04 20:33

JawedVikia80

8-Jul-04 20:33

Jawed Akhter
I need Help in the installation of Mysql.
Secondly from where i get free mysql

Thanks

Re: Help Please in the installation of Mysql

Mekong River10-Jul-04 6:51

Mekong River

10-Jul-04 6:51

Try to read the information from the following website: http://dev.mysql.com/doc/mysql/en/Installing.html[^] it might give you some idea on how to install MySql.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

Re: Which is the free and right version of Mysq

JawedVikia8014-Jul-04 21:55

JawedVikia80

14-Jul-04 21:55

Tell me Which is the free and right version of Mysq

Re: Which is the free and right version of Mysq

Mekong River17-Jul-04 5:08

Mekong River

17-Jul-04 5:08

I learn PHP from "PHP/MySQL Programming for the absolute beginner" (ISBN: 1-931841-32-2). I think it is version 4.2.1 Confused | :confused:

and I use windows 2000. This version of PHP come from the CDROM.

But I think you should find here http://dev.mysql.com/downloads/[^] for the latest version.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

mulitple choice search

Sudee8-Jul-04 17:22

Sudee

8-Jul-04 17:22

hello.

Can anyone show me how can I do multiple choice searching in a table? I am writing store procedure.

Suppose there are 3 search options on a page.

store procedure searching
@a int,
@b int,
@c int
as
select a,b,c,d,e
from grandtable
where a=@a and/or bhal bhal bhal

I am a very begineer for SQL.

thank,
Sudeep

Some dudes gotta know how to develop a prg. and some dudes just never get it at all.

DTS transforming text to datetime format

leezardd8-Jul-04 6:16

leezardd

8-Jul-04 6:16

My source is a delimited text file containing a date in the form of yyyymmdd. I would like to use the transformation in DTS to convert the data to a datetime column in my destination table. I have been using the import wizard and cannot seem to figure out how to do this. All help is greatly appreciated.

Re: DTS transforming text to datetime format

Grimolfr8-Jul-04 9:03

Grimolfr

8-Jul-04 9:03

You should be able to implicitly convert text in that format to a datetime. Just stuff the data into a datetime column, and the the SQL Server handle the conversion automatically.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: DTS transforming text to datetime format

leezardd8-Jul-04 9:22

leezardd

8-Jul-04 9:22

When I try to let SQL server handle the conversion I receive this error:
ActiveX Scripting Transform "AxScriptXform' encountered an invalid data value for 'EmpAnnvDate' destination column.

Re: DTS transforming text to datetime format

Grimolfr9-Jul-04 2:17

Grimolfr

9-Jul-04 2:17

Have you verified that all of your records contain valid dates in that format?

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: DTS transforming text to datetime format

leezardd9-Jul-04 4:41

leezardd

9-Jul-04 4:41

Yes, each record in the text file contains a vaild date. I am testing with a small file that only contains 8 records.

Multiple Queries in ADO

brdavid7-Jul-04 14:39

brdavid

7-Jul-04 14:39

I'm stumped on how to perform the following:

I want to programmically create two queries, either physically or virtually (doesn't matter), then build an SQL statement that applies both queries into one table. For example

SQL.Format("SELECT * FROM TABLEA")
Create QueryA
SQL.Format("SELECT * FROM TABLEB")
Create QueryB
SQL.Format("SELECT * FROM QueryA JOIN QueryB ON ...");
Open(SQL)
{
parse records
}

I have been given such tips as Stored Procedures but I could get even the simpliest of SP to work in AccessXP. Is such a procedure possible to code in ADOP or will I have to have these queries hard coded in Access?

Thanks!

Re: Multiple Queries in ADO

Michael Potter8-Jul-04 3:29

Michael Potter

8-Jul-04 3:29

You may be looking for the UNION operator. Example:

SELECT
     LastName,
     FirstName
FROM
     CustomerTable

UNION ALL

SELECT
     NameLast,
     NameFirst
FROM
     EmployeeTable

UNION: Returns distinct rows in Access. No duplicate rows.
UNION ALL: Returns all rows in Access. Duplicate rows possible.

Re: Multiple Queries in ADO

brdavid8-Jul-04 4:26

brdavid

8-Jul-04 4:26

not quite. The queries are already mapped out in Access and i even created a third query that incorporates Query1 and Query2, however I wanted to do this programmically using ADO so instead of using the hard coded queries, I want to programmically make them and apply them as I would in Access. After doing some of my own digging, I think I can achieve this through using the ADOCommand class. Does this sound correct? I noticed a company out there managed to create an active control that can create queries through the ADO control, however I don't know if they can be saved to be used in a further query.

To be more precise, I have mapped out what I need accomplished for an SQL. This as been my whole project and pretty much what all of my previous questions were leading up too. I have a grotesquely complicated query to hash out and I have gotten what I need to work through Access however it took 3 queries: Query1 which grabs all of my Skills and Subskills, Query2 which is a UNION of all custom created SubSkills from all the Customized tables the user created and Query3 is the combination of query1 and query2 joined together. My question now, which should be the final step is figuring out how I can achieve what I have setup in Access through ADO.

Perhaps I have complicated the issue futher than need be but after trying all kinds of different layouts for queries I can't get it to work any other way. If I could somehow make Query1 and Query2 work in the same query then it would solve not having to make two separate queries and join them in a third. Perhaps my database design is faulty but not being an expert, lacking any thorough documentation on SQL and database design it's the best I could come up with and I guess I'll have to live with it and code around it until I can get a better understanding of Database design. In any event, without crying a river here, Big Grin | :-D

, does it sound like I'm on the right track? Unsure | :~

Re: Multiple Queries in ADO

Michael Potter8-Jul-04 5:01

Michael Potter

8-Jul-04 5:01

I guess I don't follow what you are trying to do. If you have a set of skills which can have many subskills and many custom subskills, it would seem you could just use UNION to join them all together.

It seems that the common table between both queries is the main Skills table. Have both queries join to it and then merge them with a UNION ALL. If you have multiple custom subskills tables, just keep appending more UNIONs. I am not sure why you would want to use a JOIN between Query1 & Query2.

Then again, I probably do not fully understand your problem.

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