none
Visual Studio 2011 RRS feed

  • Question

  • I am not to sure what to do!!! Please help.

    When compiling and trying to access I get the following message box coming : No value given for one or more required parameters. Here the following code that allow me to access the database.

    string user = txtUser.Text;
                string pass = txtPass.Text;
    
                using (OleDbConnection connection = new OleDbConnection(Properties.Settings.Default.Connection))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Member WHERE UserID = @userName AND UserPassword = @passWord";
                    command.Parameters.AddWithValue("@userName", user);
                    command.Parameters.AddWithValue("@passWord", pass);
                    command.Parameters.Clear();
                    try
                    {
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            
                            string username = reader["UserID"].ToString();
                            string password = reader["UserPassword"].ToString();
                            string mood = reader["UserMood"].ToString();
                           // int state = Convert.ToInt32(reader["UserState"].ToString());
                             
                            if ( !String.IsNullOrEmpty(username))
                            {
                                //person client = new person(username, password, mood, state);
                                JessengerList ChatList = new JessengerList();
                                this.Visible = false;
                                ChatList.Show();
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }

    However I assume everything is good in term of MySQL code. I see that I have tree error message on the page app.config. Its state 

    Message 3 Could not find schema information for the attribute 'sku'. C:\Users\Jean\Documents\Visual Studio 11\Projects\Jessenger\Jessenger\App.config 13 42 Jessenger
    Message 2 Could not find schema information for the attribute 'version'. C:\Users\Jean\Documents\Visual Studio 11\Projects\Jessenger\Jessenger\App.config 13 27 Jessenger
    Message 1 Could not find schema information for the element 'supportedRuntime'. C:\Users\Jean\Documents\Visual Studio 11\Projects\Jessenger\Jessenger\App.config 13 10 Jessenger

    And here is the app.config page itself.

    <?xml version="1.0"?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="Jessenger.Properties.Settings.Connection" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jean\Desktop\Jessenger.accdb"
                providerName="System.Data.OleDb" />
            <add name="Jessenger.Properties.Settings.JessengerConnectionString"
                connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Jessenger.accdb"
                providerName="System.Data.OleDb" />
        </connectionStrings>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
        </startup>
    </configuration>

    I am not familiar with visual studio and either c# i am learning and I really can't seem to find out why.

    Please and thank you help me!!!

    Wednesday, May 30, 2012 9:49 PM

Answers

  • It looks like you are clearing the parameters collection immediately after you add the parameters.  Try removing:

    command.Parameters.Clear();

    Also, I believe you need to use ? parameter markers in you SQL statement instead of names.  Values are mapped by ordinal position with OLE DB:

    command.CommandText = "SELECT * FROM Member WHERE UserID = ? AND UserPassword = ?";
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Thursday, May 31, 2012 1:54 AM
  • The command should return rows as long as you have data that matches the secified criteria.  I ran it successfully using the DDL and console app below.

    CREATE TABLE dbo.Member(
    	UserID varchar(20)
        ,UserPassword varchar(20)
        ,UserMood varchar(20)
        ,UserState varchar(20)
    	);
    
    INSERT INTO dbo.Member VALUES('a','a','a','a');

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.OleDb;
    
    namespace ConsoleApplication3
    {
        class Program
        {
            static void Main(string[] args)
            {
                string user = "a";
                string pass = "a";
    
                using (OleDbConnection connection = new OleDbConnection("Provider=SQLNCLI;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI"))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Member WHERE UserID = ? AND UserPassword = ?";
                    command.Parameters.AddWithValue("@userName", user);
                    command.Parameters.AddWithValue("@passWord", pass);
                    try
                    {
    
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
    
                        if (reader.HasRows)
                        {
                            Console.WriteLine("in");
                            reader.Read();
    
                            string username = reader["UserID"].ToString();
                            string password = reader["UserPassword"].ToString();
                            string mood = reader["UserMood"].ToString();
    
                            if (!String.IsNullOrEmpty(username))
                            {
                                Console.WriteLine(username);
                                Console.WriteLine(password);
                                Console.WriteLine(mood);
                            }
                        }
                        else
                        {
                            Console.WriteLine("no data");
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
    
            }
        }
    }


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Thursday, May 31, 2012 3:04 AM

All replies

  • It looks like you are clearing the parameters collection immediately after you add the parameters.  Try removing:

    command.Parameters.Clear();

    Also, I believe you need to use ? parameter markers in you SQL statement instead of names.  Values are mapped by ordinal position with OLE DB:

    command.CommandText = "SELECT * FROM Member WHERE UserID = ? AND UserPassword = ?";
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Thursday, May 31, 2012 1:54 AM
  • Well Thanks!! It almost works. You technically solve my issues, however when i come to read the data, my reader doesnt have any rows. How can i solve this??

    Here the newest code

                string user = txtUser.Text;
                string pass = txtPass.Text;
    
                using (OleDbConnection connection = new OleDbConnection(Properties.Settings.Default.Connection))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Member WHERE UserID = ? AND UserPassword = ?";
                    command.Parameters.AddWithValue("@userName", user);
                    command.Parameters.AddWithValue("@passWord", pass);
                    try
                    {
                        
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
    
                        if (reader.HasRows)
                        {
                            MessageBox.Show("in");
                            reader.Read();
    
                            string username = reader["UserID"].ToString();
                            string password = reader["UserPassword"].ToString();
                            string mood = reader["UserMood"].ToString();
                            int state = Convert.ToInt32(reader["UserState"].ToString());
    
                            if (!String.IsNullOrEmpty(username))
                            {
                                person client = new person(username, password, mood, state);
                                JessengerList ChatList = new JessengerList();
                                this.Visible = false;
                                ChatList.Show();
                            }
                        }
                        else
                        {
                            MessageBox.Show("In but no data detected");
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }


    Jean

    Thursday, May 31, 2012 2:16 AM
  • The command should return rows as long as you have data that matches the secified criteria.  I ran it successfully using the DDL and console app below.

    CREATE TABLE dbo.Member(
    	UserID varchar(20)
        ,UserPassword varchar(20)
        ,UserMood varchar(20)
        ,UserState varchar(20)
    	);
    
    INSERT INTO dbo.Member VALUES('a','a','a','a');

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.OleDb;
    
    namespace ConsoleApplication3
    {
        class Program
        {
            static void Main(string[] args)
            {
                string user = "a";
                string pass = "a";
    
                using (OleDbConnection connection = new OleDbConnection("Provider=SQLNCLI;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI"))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM Member WHERE UserID = ? AND UserPassword = ?";
                    command.Parameters.AddWithValue("@userName", user);
                    command.Parameters.AddWithValue("@passWord", pass);
                    try
                    {
    
                        connection.Open();
                        OleDbDataReader reader = command.ExecuteReader();
    
                        if (reader.HasRows)
                        {
                            Console.WriteLine("in");
                            reader.Read();
    
                            string username = reader["UserID"].ToString();
                            string password = reader["UserPassword"].ToString();
                            string mood = reader["UserMood"].ToString();
    
                            if (!String.IsNullOrEmpty(username))
                            {
                                Console.WriteLine(username);
                                Console.WriteLine(password);
                                Console.WriteLine(mood);
                            }
                        }
                        else
                        {
                            Console.WriteLine("no data");
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }
    
            }
        }
    }


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Thursday, May 31, 2012 3:04 AM
  • My bad, i forgot the password was changed! thanks

    Jean

    Thursday, May 31, 2012 3:18 AM
  • I should have also mentioned that you should generally use SqlClient instead of OleDb from managed code.  The .Net Provider for SQL Server provides high-performance data access from managed code.  The main code difference is that you can use the parameter names as parameter markeres instead of '?', like the original code you posted.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, June 1, 2012 2:43 AM
  • Thanks, that is interesting fact. Wonder how u learn all the different technology of database access. I have try your code but found one issues, it comes to do the connection string. Because i am using access, i shouldn't use SQL command correct?

    Jean


    Friday, June 1, 2012 5:04 AM