public int RoleTempInsertWithRoleID(string[] connectionValues, ROLEDTO oROLEDTO)
{
int roleId = 0;
int maxRoleId = 0;
int maxTempRoleId = 0;
int result = 0;
try
{
using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
{
StringBuilder varname1 = new StringBuilder();
varname1.Append("SELECT MAX(ROLE_ID) AS MAX_ROLE_ID n");
varname1.Append("FROM SROLE n");
varname1.Append("WHERE 1=1 n");
varname1.Append("AND COMPANY_ID=?COMPANY_ID n");
varname1.Append("AND PRODUCT_ID=?PRODUCT_ID;");
oCloudConnection.CommandText = varname1.ToString();
oCloudConnection.Parameters.Clear();
oCloudConnection.Parameters.Add(new Parameter { Name = "COMPANY_ID", Value = oROLEDTO.COMPANY_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "PRODUCT_ID", Value = oROLEDTO.PRODUCT_ID });
using (IDataReader dr = oCloudConnection.ExecuteReader())
{
while (dr.Read())
{
maxRoleId = DataReaderHelper.GetValue<int>(dr, "MAX_ROLE_ID");
}
dr.Close();
}
}
using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
{
StringBuilder varname1 = new StringBuilder();
varname1.Append("SELECT MAX(ROLE_ID) AS MAX_ROLE_ID n");
varname1.Append("FROM SROLETEMP n");
varname1.Append("WHERE 1=1 n");
varname1.Append("AND COMPANY_ID=?COMPANY_ID n");
varname1.Append("AND PRODUCT_ID=?PRODUCT_ID;");
oCloudConnection.CommandText = varname1.ToString();
oCloudConnection.Parameters.Clear();
oCloudConnection.Parameters.Add(new Parameter { Name = "COMPANY_ID", Value = oROLEDTO.COMPANY_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "PRODUCT_ID", Value = oROLEDTO.PRODUCT_ID });
using (IDataReader dr = oCloudConnection.ExecuteReader())
{
while (dr.Read())
{
maxTempRoleId = DataReaderHelper.GetValue<int>(dr, "MAX_ROLE_ID");
}
dr.Close();
}
}
if (maxRoleId == 0 && maxTempRoleId == 0)
{
oROLEDTO.ROLE_ID = "1";
}
else if (maxRoleId > maxTempRoleId)
{
result = maxRoleId + 1;
oROLEDTO.ROLE_ID = result.ToString();
}
else
{
result = maxTempRoleId + 1;
oROLEDTO.ROLE_ID = result.ToString();
}
using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
{
StringBuilder varname1 = new StringBuilder();
varname1.Append("INSERT INTO SROLETEMP n");
varname1.Append("( n");
varname1.Append("COMPANY_ID, n");
varname1.Append("PRODUCT_ID, n");
varname1.Append("ROLE_ID, n");
varname1.Append("ROLE_NAME, n");
varname1.Append("ROLE_DESCRIPTION, n");
varname1.Append("ROLE_TYPE, n");
varname1.Append("EVENT_ACTION, n");
varname1.Append("EVENT_STATUS, n");
varname1.Append("CREATED_DATETIME, n");
varname1.Append("CREATED_USER, n");
varname1.Append("CREATED_USER_CC, n");
varname1.Append("CREATED_MACHINE, n");
varname1.Append("CREATED_MACHINE_IP, n");
varname1.Append("MODIFIED_DATETIME, n");
varname1.Append("MODIFIED_USER, n");
varname1.Append("MODIFIED_USER_CC, n");
varname1.Append("MODIFIED_MACHINE, n");
varname1.Append("MODIFIED_MACHINE_IP n");
varname1.Append(") n");
varname1.Append("VALUES n");
varname1.Append("( n");
varname1.Append("?COMPANY_ID, n");
varname1.Append("?PRODUCT_ID, n");
varname1.Append("?ROLE_ID, n");
varname1.Append("?ROLE_NAME, n");
varname1.Append("?ROLE_DESCRIPTION, n");
varname1.Append("?ROLE_TYPE, n");
varname1.Append("?EVENT_ACTION, n");
varname1.Append("?EVENT_STATUS, n");
varname1.Append("?CREATED_DATETIME, n");
varname1.Append("?CREATED_USER, n");
varname1.Append("?CREATED_USER_CC, n");
varname1.Append("?CREATED_MACHINE, n");
varname1.Append("?CREATED_MACHINE_IP, n");
varname1.Append("?MODIFIED_DATETIME, n");
varname1.Append("?MODIFIED_USER, n");
varname1.Append("?MODIFIED_USER_CC, n");
varname1.Append("?MODIFIED_MACHINE, n");
varname1.Append("?MODIFIED_MACHINE_IP n");
varname1.Append(");");
oCloudConnection.CommandText = varname1.ToString();
oCloudConnection.Parameters.Clear();
oCloudConnection.Parameters.Add(new Parameter { Name = "COMPANY_ID", Value = oROLEDTO.COMPANY_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "PRODUCT_ID", Value = oROLEDTO.PRODUCT_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "ROLE_ID", Value = oROLEDTO.ROLE_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "ROLE_NAME", Value = oROLEDTO.ROLE_NAME });
oCloudConnection.Parameters.Add(new Parameter { Name = "ROLE_DESCRIPTION ", Value = oROLEDTO.ROLE_DESCRIPTION.Trim() });
oCloudConnection.Parameters.Add(new Parameter { Name = "ROLE_TYPE", Value = oROLEDTO.ROLE_TYPE });
oCloudConnection.Parameters.Add(new Parameter { Name = "EVENT_ACTION", Value = oROLEDTO.EVENT_ACTION });
oCloudConnection.Parameters.Add(new Parameter { Name = "EVENT_STATUS", Value = oROLEDTO.EVENT_STATUS });
oCloudConnection.Parameters.Add(new Parameter { Name = "CREATED_DATETIME", Value = oROLEDTO.CREATED_DATETIME });
oCloudConnection.Parameters.Add(new Parameter { Name = "CREATED_USER", Value = oROLEDTO.CREATED_USER });
oCloudConnection.Parameters.Add(new Parameter { Name = "CREATED_USER_CC", Value = oROLEDTO.CREATED_USER_CC });
oCloudConnection.Parameters.Add(new Parameter { Name = "CREATED_MACHINE", Value = oROLEDTO.CREATED_MACHINE });
oCloudConnection.Parameters.Add(new Parameter { Name = "CREATED_MACHINE_IP", Value = oROLEDTO.CREATED_MACHINE_IP });
oCloudConnection.Parameters.Add(new Parameter { Name = "MODIFIED_DATETIME", Value = oROLEDTO.MODIFIED_DATETIME });
oCloudConnection.Parameters.Add(new Parameter { Name = "MODIFIED_USER", Value = oROLEDTO.MODIFIED_USER });
oCloudConnection.Parameters.Add(new Parameter { Name = "MODIFIED_USER_CC", Value = oROLEDTO.MODIFIED_USER_CC });
oCloudConnection.Parameters.Add(new Parameter { Name = "MODIFIED_MACHINE", Value = oROLEDTO.MODIFIED_MACHINE });
oCloudConnection.Parameters.Add(new Parameter { Name = "MODIFIED_MACHINE_IP", Value = oROLEDTO.MODIFIED_MACHINE_IP });
if (oCloudConnection.ExecuteQuery() > 0)
{
roleId = Convert.ToInt32(oROLEDTO.ROLE_ID);
}
return roleId;
}
}
catch (Exception ex)
{
DMSLogger.Write(ex);
throw;
}
}
this code throws an error System.Exception: ‘42601: syntax error at or near “:” and I tried several times but couldn’t solve this error throws from if (oCloudConnection.ExecuteQuery() > 0)
Also the ExecuteReader() methord convrets ? to : because this uses a PostgreSQL database
I want to correct this error. I tried removing ? marks but it gave another error System.Exception: ‘42703: column “company_id” does not exist but in my database column company_id is present. Also I try running this using similar MSSql database (ExecuteReader() change ? to @) and it work fine.