Database

Re: SQL permissions (uugghh!!)

12-Jul-04 5:36

I have a database under development. For testing I created a couple of tables with the user account 'test_schema' which is their owner.

If I use the SQL below to query the structure of the table as 'sa' then some of the fields come back as null whereas if I perform the same query as 'test_schema' I get all the fields correctly.

DECLARE @table_name varchar(128);
SET @table_name = 'single_pkey';

SELECT	cols.COLUMN_NAME as column_name, 
	cols.DATA_TYPE as type, 
	cols.CHARACTER_MAXIMUM_LENGTH AS length, 
	CASE WHEN cols.IS_NULLABLE = 'No' THEN 0 ELSE 1 END as is_nullable,
	CASE WHEN pks.column_name IS NULL THEN NULL ELSE cols.ORDINAL_POSITION END AS primary_key,
	cols.ORDINAL_POSITION as position,
	pks.column_name as pks_column_name -- For testing
FROM INFORMATION_SCHEMA.COLUMNS cols
LEFT OUTER JOIN (SELECT kcu.COLUMN_NAME as column_name
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
		ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	WHERE tc.TABLE_NAME = @table_name
	AND CONSTRAINT_TYPE='PRIMARY KEY') as pks 
	ON pks.column_name = cols.COLUMN_NAME
WHERE TABLE_NAME = @table_name
ORDER BY cols.ORDINAL_POSITION

As the 'sa' user the [primary_key] column in the result set is always NULL when the owner if 'test_schema'.

Does anyone have any ideas that might help me out?
[If I've missed off some important piece of information then let me know - I'm sure I am just missing something obvious, but I just can't see it right now]

"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!

Steven Campbell12-Jul-04 7:54

Steven Campbell

12-Jul-04 7:54

In SQL 2000 it is possible that there are multiple tables called the same thing, owned by different users. Is that the case in your database?

my blog

Re: SQL permissions (uugghh!!)

Colin Angus Mackay12-Jul-04 8:02

Re: ListView wiht Dataset

12-Jul-04 8:02

Not in this particular database. Although for other parts of the system (there will be lots of databases) that may be the case - So, I'll have to take that into account elsewhere.

ListView wiht Dataset

orpus11-Jul-04 19:37

orpus

11-Jul-04 19:37

Hi..
In my form, I have three combo boxes, one Add button one Save button
and a listview.

Step 1:The comboboxes is being populated by a dataset.
Step 2: On clicking the add button, the listview should get populated with the contents of the combobox.text.
I can repeat Step2 a number of times, each time selecting a different value in the comboBox and adding in the ListView
Step 3: on clicking the Save button, all the data in the listview should be saved in a database.

How will I accomplish this the task? How do i save the Columns and fields of the ListView into a dataset

plz help with code or explanation

Thanks

Breath dot net

brdavid13-Jul-04 15:17

brdavid

13-Jul-04 15:17

First off, you need to decide which database utilities you are planning to code with, that is, are you going to use ADO, DAO, OLEDB, etc. ADO is usually meant for VB users, but I find myself using it without too much hassel in VC++. DAO is meant more for Access users and OLEDB I believe is Active Xish. There is also the cut and dry ODBC which has been around longer. Anyways, tons of material here on CodeProject to help you figure out what is the best for you. After deciding that then move onto your application. There is a wonderful ADO wrapper class here on CodeProject I use. I think there are few others for DAO and ODBC. Get familiar with those.

To save data into a database the following psuedocode can be followed.

OpenDatabaseConnection(...)
Recordset = OpenRecordset(...)
Recordset.AddNew(...)
Recordset.SetFieldValue(Field /*database field*/, Value/*the value to go into that field*/);
Recordset.Update(...)

I would check out the examples here on CodeProject going under the link http://www.codeproject.com/database/

TONS of examples with code that should answer most of your questions.

ms access on web

Asif Rehman11-Jul-04 17:43

Asif Rehman

11-Jul-04 17:43

hi code guys!
I've a web database LOGINS(ms access).
DSN is DSN_pkbiz

I want to access db from within c# app through ODBC.NET.
pls tell me about the exact connection string.
the rest I'll do.

Time is a good teacher but unfortunately it kills all his students.

Re: ms access on web

Mike Ellison11-Jul-04 17:49

Mike Ellison

11-Jul-04 17:49

Hi there. Try looking up http://www.connectionstrings.com[^].

Mysql Free download

JawedVikia8010-Jul-04 19:16

JawedVikia80

10-Jul-04 19:16

Hi there....
Friends is this possible to get Mysql free of charge .
And from where i get the software.
Help will be appriciated .
Thanks

Arjan Einbu11-Jul-04 3:10

Arjan Einbu

11-Jul-04 3:10

This can be easily be found using Google...

JawedVikia8013-Jul-04 21:49

JawedVikia80

13-Jul-04 21:49

I am just new in the field .So please tell me that whic version is better for me and which mirror is better i am living in Pakistan .
Thanks

Mike Ellison11-Jul-04 3:12

Mike Ellison

11-Jul-04 3:12

Hi. Check out http://dev.mysql.com/downloads/[^].

Retrieve Data from Stored Proc

Steven Campbell11-Jul-04 5:39

Steven Campbell

11-Jul-04 5:39

g-o-o-g-l-e: (v) To run something or someone through Google, the first step in researching anything - http://www.lostremote.com/story/jargon.htm

my blog

mikasa9-Jul-04 11:09

9-Jul-04 11:09

Ok, does anyone know how to retrieve Data that is generated from a Stored proc into an ADO.NET DataSet or DataTable? This used to be very easy in VB6... WTF | :WTF:

I just need a sample to lead me on the right track.

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

mikasa9-Jul-04 12:10

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.

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();

mikasa12-Jul-04 5:46

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:

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;
}

mikasa12-Jul-04 10:56

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!

DataReader vs DataSet or other solution, please help

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.

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