Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
Hello!
I have got a problem in accessing databases in C#.I dd ths in both vb.net and java.But when i run my test programme it gives an exception "No value given for one or more required parameters.".There are only four fields in my database..EmployeeN0,EmployeeName,LoginDate,LoginTime..here is my code..I will be very thankful if anyone cn gve a cmmnt.
 
Update:
I am still getting the exception.
Exception: "OLedb Exception was unhandled"
"No value given for one or more required parameters."
 
In ma database there is only one record.I want to make sure whether the entered record by a user exists or not..
 
Code:
private int SqlCommand(string s) {
            int count_emp = 0;
            using (condata = new OleDbConnection()) {
                try {
                    condata.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=Employee Details.mdb";
                    using(comdata=condata.CreateCommand()){
                       comdata.CommandText=s;
                       comdata.CommandType = CommandType.Text;
                       //OleDbParameter employnember = comdata.CreateParameter();
                       //employnember.ParameterName=
                       condata.Open();
                       count_emp = (int)comdata.ExecuteScalar();
                       return count_emp;
                    }
                }
                catch(Exception ex){
                    MessageBox.Show("There is an unhandled exception" + Environment.NewLine + "in accesing the database");
                    return 1000;
                }
                finally{
                  if(condata.State==ConnectionState.Open){
                      try {
                        condata.Close();
                      }
                      catch(Exception ex){
                          MessageBox.Show("Error ocurred while closing the database" + Environment.NewLine + "Try again later!");
                      }
                  }
                }
            }
        }
        void txtnum_LostFocus(object sender, System.EventArgs e)
        {
            string fnd_data = "select count(EmployeeN0) from Employee where EmployeeN0=" + "'" + txtnum.Text + "'";
            MessageBox.Show(SqlCommand(fnd_data).ToString());
            /*int c*** = 0;
            string fnd_data = "select count(EmployeeN0) from Employee where EmployeeN0=" + "'" + txtnum.Text + "'";
            SqlCommand(fnd_data);
            while(!rddata.Read()){
                c*** = (int)rddata.GetValue(0);
            }
            MessageBox.Show(c***.ToString());*/
 
        }
Posted 24-Apr-11 8:09am
Edited 24-Apr-11 18:45pm
v2
Comments
Karl Sanford at 24-Apr-11 13:22pm
   
Could you please include the exception details in the question? The stack trace information will tell us more about where this exception is happening.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your while-check is wrong. That needs to be:
 
while(rddata.Read())
 
I don't think that's the cause of your error though. Is it possible that txtnum.Text is empty?
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Sameera,
 
Looks like you are from SL. I am too Smile | :)
 
There are couple of problems in your code.
1. Nishant pointed out one in the while loop
2. You should include the following line of code in the SqlCommand() method after the 2nd line.
comdata.CommandType = CommandType.Text;
3. In OldDbCommand there are 2 main methods you can use to execute SQL.
   - ExecuteReader() : This method is used to execute SELECT queries
   - ExecuteNonQuery() : This method is used to execute DELETE, UPDATE, INSERT like queries
 
So in your SqlCommand() method you need to remove the 4th line. Since what you are trying to execute is only a SELECT sql. If you want to execute a INSERT or DELETE or UPDATE, you need to create another method for it and use the ExecuteNonQuery() method in it.
 
Try these things out and get back to us with any exception details or messages that you get and the line numbers in which the exceptions occur. The more information we have, easier and faster we can help you.
 
Hope this helps, regards
Pasan.
 
P.S. : I was just about to go to sleep, but I wanted to help you understand how to write this code easily and more robustly. So I re-wrote your logic, to show how I would write it, in a small application. Take a look at this code and try to understand it and let me know of any problems. On top of it all, I hope I did not confuse you Smile | :)
 
                using (OleDbConnection connection = new OleDbConnection())
            {
                try
                {
                    connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=Employee Details.mdb";
                    using (OleDbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "select count(EmployeeN0) from Employee where EmployeeN0 = @EmployeeNo";
                        command.CommandType = CommandType.Text;
 
                        OleDbParameter employeeNumberParameter = command.CreateParameter();
                        employeeNumberParameter.ParameterName = "@EmployeeNo";
                        employeeNumberParameter.Value = txtnum.Text;
                        command.Parameters.Add(employeeNumberParameter);
 
                        connection.Open();
 
                        // Normal method : Method 1
                        reader = command.ExecuteReader();
                        reader.Read();
                        int c = reader.GetInt32(0);
                        MessageBox.Show(c.ToString());
 
                        // Method 2
                        // This method will only return the value of the first cell of the first row
                        int d = (int)command.ExecuteScalar();
                        MessageBox.Show(c.ToString());
                    }
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        try { connection.Close(); }
                        catch { }
                }
            }
 
Cheers Big Grin | :-D
  Permalink  
v4
Comments
Sameera Fonseka at 24-Apr-11 21:51pm
   
Dear pasan,
 
Thank you very much for your kind attention on this problem and ur cntrbutn.I have done this in vb.net.But here coding is much different.Thank you very much for being so kind engh to have ur time on ths.tkx alot.
 
sameera
Sameera Fonseka at 24-Apr-11 22:13pm
   
Hi Pasan,
If u hv got time to hve ur attntn wud u please tel me the use of this statement..
"OleDbParameter employeeNumberParameter = command.CreateParameter();
"
I can't understand the true meaning of it.Thank you.
 
Sameera
PasanRatnayake at 25-Apr-11 0:25am
   
Hi Sameera,
 
That line is there for a reason. Look at the line below which taken out from your code.
 
string fnd_data = "select count(EmployeeN0) from Employee where EmployeeN0=" + "'" + txtnum.Text + "'";
 
In commercial application development, this is considered very bad practice in developing applications. This is due to a hacking approach named "SQL Injection" (http://en.wikipedia.org/wiki/SQL_injection) which can be used to destroy your database or get access to the restricted data.
 
There are numerous ways to prevent this, but the way I've done it is the easiest. You can read more on it (http://www.dotnetperls.com/sqlparameter). What happens there is, in SQL you can define parameters in the SQL using the "@" sign.
 
select count(EmployeeN0) from Employee where EmployeeN0 = @EmployeeNo << notice the "@EmployeeNo" . This is how you define a parameter in SQL. So in this SQL there is only one parameter and it's name is "@EmployeeNo". So what you need to do in the code is to pass data into this parameter.
 
OleDbParameter employeeNumberParameter = command.CreateParameter(); // Creates a parameter from the command, but does not add to it
employeeNumberParameter.ParameterName = "@EmployeeNo"; // Set the name of the parameter
employeeNumberParameter.Value = txtnum.Text; // Set the value of the parameter. When the SQL is executed, this value will replace the "@EmployeeNo" which is taken care by whatever the database software you use, in your case, by MS Access
command.Parameters.Add(employeeNumberParameter); // You have to add the parameter you created to the command, other wise the code will look for the parameter details and throw an exception saying it is missing.
 
Hope this helps :) Regards
Sameera Fonseka at 25-Apr-11 2:08am
   
Dear Pasan,
Thank you very much for the information.I never knew it.Thnk u very much for ur kindnes.
Unfrtntly i am still getiing following exception.
 
"OLedb Exception was unhandled"
"No value given for one or more required parameters."..if u hv got time plz hv ur luk on ths.is this an error in ma databse?
PasanRatnayake at 25-Apr-11 2:46am
   
Hey there,
 
I've got 2 suggestions.
1. Change your SQL to something very simple like "SELECT COUNT(*) FROM Employee" and comment the code where the parameter object is created and added. Run the application and see, if it works, that should be the case where "txtnum" does not have a value
2. Check your database connection string. I am not that familiar with OleDBConnections with Access. Something says to me that the problem lies in your connection string.
Try using this class to construct your connection string? (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnectionstringbuilder.aspx)
 
Hope this helps :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,803
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,421
3 Manas Bhardwaj 4,841
4 Maciej Los 4,330


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 24 Apr 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100