UI code:
Field names and values not matching please help
Login :
Password :
submit
--------------------------------------------
table design:
id int
UserName varchar(15)
Password1 int
-----------------------------------------
cs page
protected void btnSubmit_Click(object sender, EventArgs e)
{
BAL_AboutUs obj = new BAL_AboutUs();
obj.UserName = txtUserName.Text;
obj.Password1 = Convert.ToInt32(txtPwd.Text);
BAL_AboutUs.AboutUsInsert(obj);
}
}
-----------------------------------------------------------
BAL.cs :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for BAL_AboutUs
/// </summary>
public class BAL_AboutUs
{
#region fields
private string _UserName;
private int _Password1;
#endregion
#region properties
public string UserName
{
get { return _UserName; }
set { _UserName = value; }
}
public int Password1
{
get { return _Password1; }
set { _Password1 = value; }
}
#endregion
#region method
public static void AboutUsInsert(BAL_AboutUs obj)
{
DataBase.Connect();
DataBase.StoreprocedureExecuteQuery("[SamsKarata_Sp_AboutUs]", "@SamsUserName,@SamsPwd", "I", "0", obj.UserName,
obj.Password1.ToString());
DataBase.Close();
}
#endregion method
}
--------------------------------------------------------
DAL
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
public class DataBase
{
private static SqlConnection CONDB = new SqlConnection();
private static SqlDataAdapter ADPTR;
public static int LoginId=0;
public static string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
public static void Connect()
{
if (CONDB.State != ConnectionState.Open)
{
CONDB = new SqlConnection(connStr);
//CONDB = new
SqlConnection("server=176.9.35.208;uid=ajoyzcom_ajoyzclient60;pwd=ajoyz12345!@#$%;database=ajoyzcom_ajoyzinfo;");
//CONDB = new
SqlConnection(@"server=176.9.35.208;uid=ajoyzcom_ajoyzclient41;pwd=ajoyz12345!@#$%;database=ajoyzcom_ajoyzcom_ajoyzoffice;");
// CONDB = new SqlConnection(@"Data Source=NSOFTWAR-2A600C\SQLEXPRESS;Initial Catalog=SMART_CAMPUS;Integrated Security=True");
//CONDB = new SqlConnection(@"server=176.9.35.208;uid=ajoyzcom_ajoyzclient60;pwd=ajoyz12345!@#$%;database=ajoyzcom_ajoyzinfo;");
CONDB.Open();
}
}
public static void Close()
{
try
{
CONDB.Close();
}
catch(Exception ex)
{
#if DEBUG
throw ex;
#endif
}
}
public static SqlConnection GetConnect()
{
return CONDB;
}
public static void ExecuteQuery(string QueryString)
{
// Connect();
SqlCommand cmd = new SqlCommand(QueryString, CONDB);
cmd.ExecuteNonQuery();
// Close();
cmd.Dispose();
}
public static int ExecuteQueryReturn(string QueryString)
{
// Connect();
SqlCommand cmd = new SqlCommand(QueryString, CONDB);
int ID= Convert.ToInt32(cmd.ExecuteScalar());
// Close();
cmd.Dispose();
return ID;
}
public static int NextNum(string TableName, string FieldName, int StartingNum)
{
//Connect();
SqlCommand cmd = new SqlCommand("select MAX(" + FieldName + ") from " + TableName, CONDB);
object ExeReturn = cmd.ExecuteScalar();
// Close();
if (ExeReturn.ToString() != "")
{
Int32 dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();
return dMax < StartingNum ? StartingNum : dMax + 1;
}
else
{
cmd.Dispose();
return StartingNum;
}
}
public static int NextNum(string TableName, string FieldName,string Condition, int StartingNum)
{
//Connect();
SqlCommand cmd = new SqlCommand("select MAX(" + FieldName + ") from " + TableName+" where "+Condition, CONDB);
object ExeReturn = cmd.ExecuteScalar();
// Close();
if (ExeReturn.ToString() != "")
{
Int32 dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();
return dMax < StartingNum ? StartingNum : dMax + 1;
}
else
{
cmd.Dispose();
return StartingNum;
}
}
public static int NextNum(string TableName, string FieldName)
{
//Connect();
SqlCommand cmd = new SqlCommand("select MAX(" + FieldName + ") from " + TableName, CONDB);
object ExeReturn = cmd.ExecuteScalar();
// Close();
Int32 dMax=0;
if (ExeReturn.ToString() != "")
{
dMax = Convert.ToInt32(ExeReturn);
cmd.Dispose();
}
return dMax;
}
public static int FieldCount(string FieldName, string TableName, string Condition)
{
SqlCommand cmd = new SqlCommand("SELECT COUNT(" + FieldName + ") FROM " + TableName + " WHERE " + Condition, CONDB);
int count = (int)cmd.ExecuteScalar();
return count;
}
public static DataTable Query(string QueryString)
{
DataTable DT = new DataTable();
ADPTR = new SqlDataAdapter(QueryString, CONDB);
ADPTR.Fill(DT);
return DT;
}
public static DataSet Query(string QueryString, string Table)
{
DataSet DS = new DataSet();
DS.Tables.Clear();
ADPTR = new SqlDataAdapter(QueryString, CONDB);
ADPTR.Fill(DS, Table);
return DS;
}
public static bool RowExists(string TableName, string Condition)
{
// Connect();
SqlCommand cmd = new SqlCommand("select * from " + TableName + " where " + Condition, CONDB);
if (cmd.ExecuteScalar() == null)
{ return false; }
{ return true;}
}
public static object GetField(string FieldName, string TableName, string Condition)
{
// Connect();
SqlCommand cmd = new SqlCommand("select " + FieldName + " from " + TableName + " where " + Condition, CONDB);
return cmd.ExecuteScalar();
// Close();
}
public static object GetField(string FieldName, string TableName)
{
// Connect();
SqlCommand cmd = new SqlCommand("select " + FieldName + " from " + TableName , CONDB);
return cmd.ExecuteScalar();
// Close();
}
public static void InsertInto(string tableName, string fieldList, params string[] values)
{
StringBuilder qry = new StringBuilder("insert into " + tableName + " (" + fieldList + ") values(");
for (int i = 0; i < values.Length; i++)
{
values[i] = Common.KeyValidate(values[i]);
qry.Append(values[i] + ((i == (values.Length - 1)) ? ") " : ","));
}
ExecuteQuery(qry.ToString());
}
public static int InsertIntoReturn(string tableName, string fieldList, params string[] values)
{
StringBuilder qry = new StringBuilder("insert into " + tableName + " (" + fieldList + ") values(");
for (int i = 0; i < values.Length; i++)
{
values[i] = Common.KeyValidate(values[i]);
qry.Append(values[i] + ((i == (values.Length - 1)) ? ") " : ","));
}
return ExecuteQueryReturn(qry.ToString());
}
public static void UpdateTable(string tableName,string condition, string fieldList, params string[] values)
{
string[] fieldNames = fieldList.Split(',');
if (fieldNames.Length != values.Length)
throw new Exception("DataAccess : Field names and values not matching...");
StringBuilder qry = new StringBuilder("Update " + tableName + " Set ");
for (int i = 0; i < values.Length; i++)
{
values[i] = Common.KeyValidate(values[i]);
qry.Append(fieldNames[i] + " = " + values[i] + ((i == (values.Length - 1)) ? " " : ","));
}
qry.Append(" where " + condition);
ExecuteQuery(qry.ToString());
}
public static void StoreprocedureExecuteQuery(string ProcedureName, string Parameters, params string[] values)
{
SqlCommand cmd = new SqlCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(',');
if (fieldNames.Length != values.Length)
throw new Exception("DataAccess : Field names and values not matching...");
for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
cmd.ExecuteNonQuery();
}
public static DataTable StoreprocedureExecuteQueryDTReturned(string ProcedureName, string Parameters, params string[] values)
{
SqlCommand cmd = new SqlCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(',');
if (fieldNames.Length != values.Length)
throw new Exception("DataAccess : Field names and values not matching...");
for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt= new DataTable();
cmd.ExecuteNonQuery();
adp.SelectCommand = cmd;
adp.Fill(dt);
return dt;
}
public static string StoreprocedureExecuteQueryReturned(string ProcedureName, string Parameters, params string[] values)
{
SqlCommand cmd = new SqlCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(',');
if (fieldNames.Length != values.Length)
throw new Exception("DataAccess : Field names and values not matching...");
for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string s= reader.GetValue(0).ToString();
reader.Close();
return s;
}
//string s = cmd.Parameters["@id"].ToString();
reader.Close();
return "";
}
public static SqlDataReader BindData_Reader(string ProcedureName, string Parameters, params string[] values)
{
SqlCommand cmd = new SqlCommand(ProcedureName, CONDB);
cmd.CommandType = CommandType.StoredProcedure;
string[] fieldNames = Parameters.Split(',');
if (fieldNames.Length != values.Length)
throw new Exception("DataAccess : Field names and values not matching...");
for (int i = 0; i < values.Length; i++)
{
string Para = fieldNames[i].ToString().Trim();
string val = values[i].ToString().Trim();
cmd.Parameters.AddWithValue(Para, val);
}
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static void insertimage(string ImagePath, string StudId,string Mode)
{
byte[] data = null;
string Query = "";
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
if (Mode == "1")
{
Query = "Insert into AD_StudentImage values('" + StudId + "',@ImageData1)";
}
else if (Mode == "2")
{
Query = "Insert into AD_StaffImage values('" + StudId + "',@ImageData1)";
}
SqlCommand cmd = new SqlCommand(Query, CONDB);
cmd.Parameters.Add(new SqlParameter("@ImageData1", (object)data));
cmd.ExecuteNonQuery();
}
public static void updateimage(string ImagePath, string StarName, string Actor, string EditCode)
{
byte[] data = null;
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
string Query = "update Actors set ActorName='" + StarName + "' ,Gender='" + Actor + "',Image=@ImageData1 where Id=" + EditCode +
"";
SqlCommand cmd = new SqlCommand(Query, CONDB);
cmd.Parameters.Add(new SqlParameter("@ImageData1", (object)data));
cmd.ExecuteNonQuery();
}
public static void updateimage(string ImagePath, int StudId,string Mode)
{
byte[] data = null;
string Query="";
FileInfo fInfo = new FileInfo(ImagePath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(ImagePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
if(Mode=="1")
{
Query = "update AD_StudentImage set StudImage=@ImageData1 where StudId=" + StudId + "";
}
else if (Mode == "2")
{
Query = "update AD_StaffImage set StaffImage=@ImageData1 where StaffId=" + StudId + "";
}
SqlCommand cmd = new SqlCommand(Query, CONDB);
cmd.Parameters.Add(new SqlParameter("@ImageData1", (object)data));
cmd.ExecuteNonQuery();
}
}
error :
Field names and values not matching please help