I created "User.cs" in a "Components" File for "MyProject" project.
When I try to login using Login function in "User.cs", it always has
errors at myCommand.ExecuteNonQuery();. The error message is
"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done." I thought the myCommand was wrong and checked the strSQL, but I couldn't find errors. If you could, could you please help me to fix the bugs?

The User.cs is following:
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Web;

namespace MyProject
{
public class UserDetails
{
public string VerifiedDate;
public string Prefix;
public string FirstName;
public string LastName;
public string Email;
public string UserPwd;
public string Address;
public string PhoneNumber;
public Int32 UserID;
}

public class User
{
public User()
{
}

public UserDetails Login(string strEmail, string
strPassword)
{
// Create Instance of Connection and Command
Object
String strConn
="Provider=Microsoft.Jet.OleDb.4.0;";
strConn += @"Data
Source=C:\MyProject\DB\HowardGroupWeb.mdb";
OleDbConnection myConnection = new
OleDbConnection(strConn) ;

string strSQL = "SELECT UserID, FirstName FROM
Users WHERE Email='"+strEmail+"' AND UserPwd='"+strPassword+"'";

OleDbCommand myCommand = new
OleDbCommand(strSQL,myConnection);

// Mark the Command as a SPROC
myCommand.CommandType =
CommandType.StoredProcedure;

// Add Parameters to SPROC
OleDbParameter prmEmail = new
OleDbParameter("@Email",OleDbType.VarChar,255);
prmEmail.Value = strEmail;
myCommand.Parameters.Add(prmEmail);

OleDbParameter prmFirstName = new
OleDbParameter("@FirstName",OleDbType.VarChar,255) ;
prmFirstName.Direction =
ParameterDirection.Output;
myCommand.Parameters.Add(prmFirstName);

OleDbParameter prmUPassword = new
OleDbParameter("@UserPwd",OleDbType.VarChar,255);
prmUPassword.Value = strPassword;
myCommand.Parameters.Add(prmUPassword);

OleDbParameter prmUserID = new
OleDbParameter("@UserID",OleDbType.BigInt);
prmUserID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(prmUserID);

UserDetails myUserDetails = new UserDetails();

try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

myUserDetails.FirstName =
prmFirstName.Value.ToString();
myUserDetails.UserID =
Convert.ToInt32(prmUserID.Value);

return myUserDetails;
}
catch(OleDbException ODbexc)
{
myUserDetails.FirstName =
ODbexc.ToString();
myUserDetails.UserID = 0;
return myUserDetails;
}
}

Super thank you in advance,

Hi there,

From what I can see, you're having a couple of problems.

1. You're coding a normal SQL statement, yet you mark it as a sproc
2. You're adding parameters but you're not using any code to fill those params (e.g. I'd expect something like an output param)
3. You're using ExecuteNonQuery. This is usually used for UPDATE, DELETE statements, etc.

If I were you, I'd use a normal SQL Select statement (like you have now) and then use ExecuteReader to get a OledbDataReader.

If you look at the OledbDataReader help page, you'll get a good example on how to retrieve values from the DataReader.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thank you, Imar. I fixed the bug as you suggested me.

I'm still confused about what the stored procedure is for. When I create a new row, I can use the SQL statement, "INSERT" or "UPDATE". The stored procedure is for only SQL Server?

Sorry for a lack of my knowledge.

A stored procedure is made inside the database, and you simply call this using some parameter (variables) from the code. As far as I know this should be more efficient than regular SQL execution from code.

Not only for SQL server! I have tried in in Access.

Jacob.

Thank you for explaining about the stored procedure. Make sense!

Kaz

You may also want to take a look here:

http://searchdatabase.techtarget.com...334271,00.html

and

http://msdn.microsoft.com/library/de...es_07_31vb.asp

The last link will brink up a lot of other useful links (in the menu on the left) about using Stored Procedures in SQL Server.

Regards,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.