C# Database Handling

Question

4.00/5 (1 vote)

See more:

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:

C#

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 7:09am

Sameera Fonseka

Updated 24-Apr-11 17:45pm

v2

Add a Solution

Comments

Karl Sanford 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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Nish Nishant · Accepted Answer · 2011-04-24T07:15:00

Solution 1

Your while-check is wrong. That needs to be:

C#

while(rddata.Read())

I don't think that's the cause of your error though. Is it possible that txtnum.Text is empty?

Posted 24-Apr-11 7:15am

Nish Nishant

v2

CodeHawkz · Accepted Answer · 2011-04-24T10:39:00

Hi Sameera,

Looks like you are from SL. I am too :)

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 :)

C#

    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 :D