Can this Class Function Properly?

[gggram2000]

Can anyone tell me if this class will function properly...I'm using MS Visual Studio 2005, using C#. I notice there's no C# forum but hopefully u guys are familiar with the language...than ks in advance

class esodbconnection
    {
        public DataViewManager fnGetConnectedToDatabase(string dbname, string tablename, string tablename1, string whereclass, string groupbyclass)
        {
            ResortApplication.conf configdb = new conf();

            //this is creating the connection string
            string xmldb = configdb.fnGetKeyConfig(dbname);

            //here I declare my command string
            string sSQL = "select * from " + tablename + tablename + whereclass + groupbyclass;

            //Creates an SQL Data Adapter
            SqlDataAdapter dataAdapter = new SqlDataAdapter();

            //select the stuff into the data adapter
            dataAdapter.SelectCommand = new SqlCommand();
            try
            {
                dataAdapter.SelectCommand.CommandText = sSQL;

                //this is creating the database connection
                dataAdapter.SelectCommand.Connection = new SqlConnection(xmldb);

                //open connection
                dataAdapter.SelectCommand.Connection.Open();
                dataAdapter.SelectCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {                            
                MessageBox.Show("Error in connection ..." + ex.Message);
            }
            //Instantiate a DataSet
            DataSet mydataset = new DataSet();

            //Gets a collection that provides the master mapping
            //between a source table and a DataTable
            dataAdapter.TableMappings.Add("Table", tablename);

            /*A data adapter object utilizes the Fill method 
            to populate a DataSet or a DataTable object with data 
            retrieved by a SELECT command. */
            dataAdapter.Fill(mydataset);
           //When binding a DataSet, .NET automatically uses the corresponding
            //DataViewManager provided through the DataSet.DefaultViewManager property	
            DataViewManager dviewmanager = mydataset.DefaultViewManager;
            dataAdapter.SelectCommand.Connection.Close();
            return dviewmanager;
        }
    }

[dip_developer]

seems nothing wrong except the SQL.....
string sSQL = "select * from " + tablename + tablename + whereclass + groupbyclass;

same table name twice in the sql .......

check for that...

[gggram2000]

seems nothing wrong except the SQL.....
string sSQL = "select * from " + tablename + tablename + whereclass + groupbyclass;

same table name twice in the sql .......

check for that...

I have a problem, and yea i named it differently [tablename1].

The problem is when i try calling a function using that class...for example:

//creating an instance of the class that connects to the database and creates views
            ResortApplication.esodbconnection esodbcon1 = new esodbconnection();

            //telling the class what database to connect and what table
            DataViewManager mydbview = esodbcon1.fnGetConnectedToDatabase("xmldb", "dbo.Last()", "", "", "");

            //assign the data source to the data view
            comboBox.DataSource = mydbview;
            //specify what to display in the comboBox
            comboBox.DisplayMember = "dbo.Last().GuestLastName";

In which dbo.Last() is my function created in Sql server 2005. & GuestLastName is the name of the table column I want to display on the comboBox.

When I run the program and click on the command to display on the comboBox I get this instead of GuestLastName:
" System.Data.Dat aViewManagerLis tItemTypeDescri ptor "

I really would like to know how I can make it display the column. I'm calling functions in most of my commands. Any help will be greatly appreciated!

George

[Plater]

Hmm, I think I see what's wrong, but not sure how to explain it.

You have an sql function dbo.Last()
It returns a result set, (some number of rows with various columns), one of those columns has the name "GuestLastName" .
You would want to put JUST the column name for the display member.

[gggram2000]

Hmm, I think I see what's wrong, but not sure how to explain it.

You have an sql function dbo.Last()
It returns a result set, (some number of rows with various columns), one of those columns has the name "GuestLastName" .
You would want to put JUST the column name for the display member.

Yeah I believe I specify the column name with this code:

     comboBox.DisplayMember = "dbo.Last().GuestLastName";

My function looks like this:

ALTER FUNCTION [dbo].[Last]()
RETURNS TABLE
AS
Return SELECT s.Date, g.GuestFirstName, g.GuestLastName
        From Guest AS g, Sales As s
        WHERE g.GuestID = s.GuestID And s.SalesStatus = 'ToGuestInvoice'
        GROUP BY Date, GuestFirstName, GuestLastName

But from here I specify I want the column name GuestLastName. Is that what you are telling me?

George

[Plater]

Try making this:

comboBox.DisplayMember = "dbo.Last().GuestLastName";

be this:

comboBox.DisplayMember = "GuestLastName";

[gggram2000]

Try making this:

comboBox.DisplayMember = "dbo.Last().GuestLastName";

be this:

comboBox.DisplayMember = "GuestLastName";

It gives the same view...no GuestLastName. Same result...

[Plater]

Hmm, you are using a DataViewManager as your datasource, I don't think that's a valid datasource?
I've DataTables, DataSets and DataViews used, maybe you have to pick a specific DataView out of your DataViewManager ?

Or try changing your function to return mydataset instead of the viewmanager?

[gggram2000]

Hmm, you are using a DataViewManager as your datasource, I don't think that's a valid datasource?
I've DataTables, DataSets and DataViews used, maybe you have to pick a specific DataView out of your DataViewManager ?

Or try changing your function to return mydataset instead of the viewmanager?

Ok, thanks. I'll try that and let u know if it works.

[gggram2000]

Hmm, you are using a DataViewManager as your datasource, I don't think that's a valid datasource?
I've DataTables, DataSets and DataViews used, maybe you have to pick a specific DataView out of your DataViewManager ?

Or try changing your function to return mydataset instead of the viewmanager?

Hey Plater, what I did is to use an SQL statement in the program instead of using the function. My code looks like this:

//create an instance of the class that connects to the database and creates views
                    ResortApplication.esodbconnection esodbcon1 = new esodbconnection();

                    //tell the class what database to connect and what table            
                    DataViewManager mydbview = esodbcon1.fnGetConnectedToDatabase("xmldb", "Guest", "Sales", "Guest.GuestID=Sales.GuestID AND Sales.SalesStatus = 'ToGuestInvoice' ", "GuestFirstName");

                    //assign the data source to the data view
                    comboBox.DataSource = mydbview;
                    //specify what to display in the combobox
                    comboBox.DisplayMember = "GuestFirstName";

When I run the command to display Gues First Name it displays this error:
"Error in connection...In correct syntax near '='. "

I think it's a problem in my class...in the whereclass, im not sure how to fix the class to support a where statement like that. What do you think?

[Plater]

yeah, that would signify that there is something wrong in your sql statement I think?

[gggram2000]

yeah, that would signify that there is something wrong in your sql statement I think?

Well I tested it on a function and it worked fine...i believe the class needs some adjustment, i'm just not sure how.

[Plater]

Right. But the syntax used in code to enter values into the string will be different from that of a stored procedure.

[gggram2000]

Right. But the syntax used in code to enter values into the string will be different from that of a stored procedure.

Hmm...do you know have any links where I can find relevant examples so I can figure out the syntax?

[Plater]

http://www.google.com/search?source=ig&hl=en&rlz=&q=using+stored+procedures+in+.net&btnG=Google+Search