Auto generate customer number [ref. must be from 2 tables]

Jes Sie

Once again I needed your precious help.

I have 3 tables in sql server 2014 namely: tblBranch, tblAgents & tblCustomers. I need to have an auto generated customer number with format numbers: xx yyyy zzz, where xx is the branch code(string type), yyyy is the agent code (string type) and zzz is the customer series number also in string type. Below are my tables.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Sorry, I really don't understand this. As far as I can see, for example, you are passing "a" and "b" as parameters to SetCustomerNumber then again those same values from db (why?) and then returning code variable which you just declared.

Jes Sie9y ago

@Nitin Sontakke, thank for your advice. This is what I did so far but it returns a null value.

aspx;
Customer Registration Form

"Label1" runat="server" Text="Customer No :">	"CustomerNumber" runat="server" type="text" />
"Label12" runat="server" Text="Agent :">	"ddlBranch" DataTextField="BranchName" DataValueField="BranchCode" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlBranch_SelectedIndexChanged">
"Label9" runat="server" Text="Agent :">	"ddlAgent" DataTextField="AgentName" DataValueField="AgentID" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlAgent_SelectedIndexChanged"> "rfvAgent" runat="server" ErrorMessage="Agent is a required field." ControlToValidate="ddlAgent" InitialValue="-1" ForeColor="Red">

aspx.cs;
private void SetCustomerCode()
{
string branch = ddlBranch.SelectedValue;
string agent = ddlAgent.SelectedValue;
CiustomerDataAccess cusCode = new CiustomerDataAccess();
string code = cusCode.SetCustomerNumber(branch, agent);
CustomerName.Value =
}
protected void ddlAgent_SelectedIndexChanged(object sender, EventArgs e)
{
this.SetCustomerCode();
}
Access Layer;
public string SetCustomerNumber(string bbcode, string aacode)
{
string code = "";
using (SqlConnection con = DBConnection.GetDbCon())
{
SqlCommand cmd = new SqlCommand("SELECT b.BranchCode, a.AgentID FROM dbo.tblBranch_V2 b INNER JOIN dbo.Agents a ON b.BranchCode = a.BranchCode WHERE (b.BranchCode = @BranchCode) AND (a.AgentID = @AgentID)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@BranchCode", bbcode);
cmd.Parameters.AddWithValue("@AgentID", aacode);
con.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
try
{
bbcode = ds.Tables[0].Rows[0]["BranchCode"].ToString();
aacode = ds.Tables[0].Rows[0]["AgentID"].ToString();
}
catch (Exception)
{
throw;
}
}
}
return code;
}

Nitin Sontakke9y ago
Few reflections on your requirement.

1/ Remember the sample data is always helpful.

2/ This kind of information can be generated on the fly. There is ABSOLUTELY no need to calculate this and store in a column. The kind of logic you are trying to implement is going to cause trouble in future. Trust me!

3/ I see both Id and Code columns. I STRONGLY recommend that you USE only one of these columns from db point of view and keep another one STRICTLY for display purpose.

4/ Try your best to discourage the use of code columns and eventually get rid of them altogether.

5/ Use ONLY Id column as primary key and foreign key. Do not use codes.

6/ You didn't mention what you are intention of this? How are you going to use that column? In what column you will be storing it? When do you want to have it generated?

7/ I am not seeing any AgentCode column anywhere.

In case you want to do it on the fly, use following query:
select b.BranchCode + ' ' + replicate('0', 4- len(ltrim(str(a.AgentId)))) + ltrim(str(a.AgentId)) + ' ' replicate('0', 3- len(ltrim(str(c.CustomerId)))) + ltrim(str(c.CustomerId)), c.*
from tblCustomers c
left outer join Agents a on c.AgentId = a.AgentId
left outer join tblBranch_V2 b on a.BranchCode = b.BranchCode
Typically, this can be put in a Customer view, which is the best place to have it.