locked
Inserting data into table and updating a column of the same table RRS feed

  • Question

  • User-1994446809 posted

    <p>Hello Forum,</p>
    <p>I am trying to insert data into a table and updating a column of the table at the click of a button. From my table structure below, after inserting into the table, I want to get the ID of the user and pass it into another column; that is updating the column.</p>
    <p>But when I insert data, I get a NULL value in the CreatedBy column. May I ask for help to correct this and get the Uid and update in CreatedBy column?</p>
    <table width="774" height="52" style="height:111px; width:854px">
    <tbody>
    <tr style="height:35px">
    <td style="width:68.4px; height:35px">
    <p>Uid(int)</p>
    </td>
    <td style="width:87.6px; height:35px">
    <p>email (varchar 50)</p>
    </td>
    <td style="width:95.6px; height:35px">
    <p>pass (varchar 50)</p>
    </td>
    <td style="width:112.4px; height:35px">
    <p>UserRole (varchar 50)</p>
    </td>
    <td style="width:92.4px; height:35px">
    <p>Name (varchar 50)</p>
    </td>
    <td style="width:93.2px; height:35px">
    <p>CreatedBy (int)</p>
    </td>
    <td style="width:102.8px; height:35px">
    <p>image (varbinary MAX)</p>
    </td>
    <td style="width:114px; height:35px">
    <p>CreateDate (datetime)</p>
    </td>
    </tr>
    <tr style="height:15px">
    <td style="width:68.4px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:87.6px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:95.6px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:112.4px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:92.4px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:93.2px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:102.8px; height:15px">
    <p>&nbsp;</p>
    </td>
    <td style="width:114px; height:15px">
    <p>&nbsp;</p>
    </td>
    </tr>
    </tbody>
    </table>
    <p>From the code below, I wanted to get the Uid from the&nbsp;<strong>private string GetInvitedBy(int id)&nbsp;</strong>&nbsp;(highlighted in yellow ) and then pass it and update it into CreatedBy column (as shown in the green highlighted area.</p>
    <p><strong>Does anyone have solution for me Please ?</strong></p>
    <p>Here is my code:</p>
    <pre class="prettyprint lang-cs">using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Web.Security;
    using System.Xml.Linq;
    using System.Configuration;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;
    using System.IO;

    public partial class SignUp : System.Web.UI.Page
    {
    SqlCommand cmd = new SqlCommand();
    SqlDataAdapter sda = new SqlDataAdapter();
    DataSet ds = new DataSet();
    SqlConnection con = new SqlConnection(&quot;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True&quot;);

    protected void Page_Load(object sender, EventArgs e)
    {
    mailtxtbx.Focus();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
    SqlDataReader dr;
    using (SqlCommand cmd = new SqlCommand())
    {
    cmd.Parameters.Clear();
    cmd.CommandText = &quot;Select * from [Users] where email=@email and Name=@Name &quot;;
    cmd.Parameters.AddWithValue(&quot;@email&quot;, mailtxtbx.Text);
    cmd.Parameters.AddWithValue(&quot;@Name&quot;, txtname.Text);
    cmd.Connection = con;
    con.Open();
    dr = cmd.ExecuteReader();
    }
    if (dr.HasRows)
    {
    dvMessage.Visible = true;
    Div1.Visible = false;
    lblMessage.Text = &quot;User Already Exists&quot;;
    lblMessage.ForeColor = System.Drawing.Color.Red;
    mailtxtbx.Text = &quot;&quot;;
    lblsuccess.Visible = false;
    lblMessage.Visible = true;
    //Label3.Visible = false;
    }
    else
    {
    con.Close();

    if (mailtxtbx.Text != &quot;&quot; &amp; pass.Text != &quot;&quot; &amp; conpass.Text != &quot;&quot; &amp; txtname.Text != &quot;&quot;)
    {
    if (pass.Text.Trim() == conpass.Text.Trim())
    {
    if (Filedoc.PostedFile.FileName != &quot;&quot;)
    {
    if (check1.Checked)
    {
    int Uid = -1;
    byte[] image;
    Stream s = Filedoc.PostedFile.InputStream;
    BinaryReader br = new BinaryReader(s);
    image = br.ReadBytes((Int32)s.Length);
    // define query to be executed
    using (SqlCommand cmd = new SqlCommand())
    {
    cmd.Parameters.Clear();
    cmd.CommandText = @&quot;INSERT INTO Users (email, pass, UserRole, Name, image, CreateDate) VALUES (@email,@pass,@UserRole,@Name,@image,@CreatedDate); SELECT SCOPE_IDENTITY()&quot;;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue(&quot;@email&quot;, mailtxtbx.Text.Trim());
    cmd.Parameters.AddWithValue(&quot;@pass&quot;, pass.Text.Trim());
    cmd.Parameters.AddWithValue(&quot;@UserRole&quot;, 'A');
    cmd.Parameters.AddWithValue(&quot;@Name&quot;, txtname.Text.Trim());
    cmd.Parameters.AddWithValue(&quot;@image&quot;, image);
    cmd.Parameters.AddWithValue(&quot;@CreatedDate&quot;, DateTime.Now);
    cmd.Connection = con;
    con.Open();
    object returnObj = cmd.ExecuteScalar();
    con.Close();
    if (returnObj != null)
    {
    int.TryParse(returnObj.ToString(), out Uid);
    }
    }

    if (Uid &gt; 0)
    {
    <span style="background-color:#ccffcc">if (!string.IsNullOrEmpty(Request.QueryString[&quot;Uid&quot;]))
    {
    // string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString[&quot;Id&quot;]));
    using (SqlCommand cmd = new SqlCommand())
    {
    cmd.Parameters.Clear();
    cmd.CommandText = &quot;UPDATE Users SET CreatedBy = @CreatedBy WHERE Uid = @Uid&quot;;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue(&quot;@Uid&quot;, Uid);
    cmd.Parameters.AddWithValue(&quot;@CreatedBy&quot;, GetInvitedBy(Convert.ToInt32(Request.QueryString[&quot;Uid&quot;])));
    cmd.Connection = con;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    }
    }</span>

    using (SqlCommand objCMD = new SqlCommand())
    {
    objCMD.Parameters.Clear();
    objCMD.CommandText = @&quot;INSERT INTO UserWallet (Uid, email, Name, amount) VALUES (@Uid, @email, @Name, @amount)&quot;;
    objCMD.CommandType = CommandType.Text;
    objCMD.Parameters.Add(&quot;@Uid&quot;, SqlDbType.Int, 50).Value = Uid;
    objCMD.Parameters.Add(&quot;@email&quot;, SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
    objCMD.Parameters.Add(&quot;@Name&quot;, SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
    objCMD.Parameters.Add(&quot;@amount&quot;, SqlDbType.Float, 100).Value = 0; //Change type here accordingly
    objCMD.Connection = con;
    con.Open();
    objCMD.ExecuteNonQuery();
    con.Close();
    lblsuccess.Visible = true;
    Div1.Visible = true;
    lblsuccess.Text = &quot;Successfully Signed Up&quot;;
    lblsuccess.ForeColor = System.Drawing.Color.Green;
    lblMessage.Visible = false;
    dvMessage.Visible = false;
    mailtxtbx.Text = &quot;&quot;;
    pass.Text = &quot;&quot;;
    conpass.Text = &quot;&quot;;
    txtname.Text = &quot;&quot;;
    }
    }
    }
    else
    {
    dvMessage.Visible = true;
    lblMessage.Visible = true;
    lblMessage.Text = &quot;Please Check Box&quot;;
    lblsuccess.Visible = false;
    lblMessage.ForeColor = System.Drawing.Color.Red;
    }
    }
    else
    {
    dvMessage.Visible = true;
    lblMessage.Visible = true;
    lblMessage.Text = &quot;Upload your document&quot;;
    lblsuccess.Visible = false;
    lblMessage.ForeColor = System.Drawing.Color.Red;
    }
    }
    else
    {
    dvMessage.Visible = true;
    lblMessage.Visible = true;
    lblMessage.Text = &quot;Passwords don't match&quot;;
    lblMessage.ForeColor = System.Drawing.Color.Red;
    conpass.Text = &quot;&quot;;
    lblsuccess.Visible = false;
    }
    }
    else
    {
    dvMessage.Visible = true;
    lblMessage.Visible = true;
    lblMessage.ForeColor = System.Drawing.Color.Red;
    lblMessage.Text = &quot;*All Fields Are Required*&quot;;
    lblsuccess.Visible = false;
    }
    }
    }
    <span style="background-color:#ffff00"> private string GetInvitedBy(int id)
    {
    string invitedBy = &quot;&quot;;
    using (SqlConnection con = new SqlConnection(&quot;Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True&quot;))
    {
    using (SqlCommand cmd = new SqlCommand(&quot;SELECT Uid FROM Users WHERE Uid = @Id&quot;))
    {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue(&quot;@Id&quot;, id);
    cmd.Connection = con;
    con.Open();
    invitedBy = Convert.ToString(cmd.ExecuteScalar());
    con.Close();
    }
    }

    return invitedBy;
    }</span>
    }</pre>
    <p></p>
    Friday, July 31, 2020 3:50 PM

Answers

  • User475983607 posted

    The problem is that when inserting data into the table, I get a NULL value in the column named "CreatedBy"; but I want to get the Id and pass it into that column

    Debugging and troubleshooting is a necessary skill in programming.  You need learn the skill.  Visual Studio comes with a debugger and you should take the time to learn how to use the tool.

    https://docs.microsoft.com/en-us/visualstudio/debugger/navigating-through-code-with-the-debugger?view=vs-2019

    https://docs.microsoft.com/en-us/visualstudio/debugger/watch-and-quickwatch-windows?view=vs-2019

    When you share a lot of code on the forum it is up to you to tell the community where the problem is.   You can find where the problem  by single step through your code.   Otherwise; the community sees are a lot of potential errors and code written in a way we do not approve of.  It also helps if you make an effort to share formatted code.  The idea is to make it as easy as possible for the community to help you.  

    I had to take your original post, copy it to an HTML file,  run the code through a formatter, finally copy the formatted code into an editor just so I can follow the code.   That's a lot of work.   I'm sure you looking at formatted code in the editor why do you think it is okay for us to unformatted code?  IMO,  It's disrespectful.  Below is your code an a quick analysis.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Web.Security;
    using System.Xml.Linq;
    using System.Configuration;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;
    using System.IO;
    
    public partial class SignUp: System.Web.UI.Page {
      SqlCommand cmd = new SqlCommand();
      SqlDataAdapter sda = new SqlDataAdapter();
      DataSet ds = new DataSet();
      SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
    
      protected void Page_Load(object sender, EventArgs e) {
        mailtxtbx.Focus();
      }
    
      protected void Button1_Click(object sender, EventArgs e) {
        SqlDataReader dr;
        using(SqlCommand cmd = new SqlCommand()) {
          cmd.Parameters.Clear();
          cmd.CommandText = "Select * from [Users] where email=@email and Name=@Name ";
          cmd.Parameters.AddWithValue("@email", mailtxtbx.Text);
          cmd.Parameters.AddWithValue("@Name", txtname.Text);
          cmd.Connection = con;
          con.Open();
          dr = cmd.ExecuteReader();
        }
        if (dr.HasRows) {
          dvMessage.Visible = true;
          Div1.Visible = false;
          lblMessage.Text = "User Already Exists";
          lblMessage.ForeColor = System.Drawing.Color.Red;
          mailtxtbx.Text = "";
          lblsuccess.Visible = false;
          lblMessage.Visible = true;
          //Label3.Visible = false;
        }
        else {
          con.Close();
    
          if (mailtxtbx.Text != "" & pass.Text != "" & conpass.Text != "" & txtname.Text != "") {
            if (pass.Text.Trim() == conpass.Text.Trim()) {
              if (Filedoc.PostedFile.FileName != "") {
                if (check1.Checked) {
                  int Uid = -1;
                  byte[] image;
                  Stream s = Filedoc.PostedFile.InputStream;
                  BinaryReader br = new BinaryReader(s);
                  image = br.ReadBytes((Int32) s.Length);
                  // define query to be executed
                  using(SqlCommand cmd = new SqlCommand()) {
                    cmd.Parameters.Clear();
                    cmd.CommandText = @"INSERT INTO Users (email, pass, UserRole, Name, image, CreateDate) VALUES (@email,@pass,@UserRole,@Name,@image,@CreatedDate); SELECT SCOPE_IDENTITY()";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@email", mailtxtbx.Text.Trim());
                    cmd.Parameters.AddWithValue("@pass", pass.Text.Trim());
                    cmd.Parameters.AddWithValue("@UserRole", 'A');
                    cmd.Parameters.AddWithValue("@Name", txtname.Text.Trim());
                    cmd.Parameters.AddWithValue("@image", image);
                    cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                    cmd.Connection = con;
                    con.Open();
                    object returnObj = cmd.ExecuteScalar();
                    con.Close();
                    if (returnObj != null) {
                      int.TryParse(returnObj.ToString(), out Uid);
                    }
                  }
    
                  if (Uid > 0) {
                    if (!string.IsNullOrEmpty(Request.QueryString["Uid"])) {
                      // string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString["Id"]));
                      using(SqlCommand cmd = new SqlCommand()) {
                        cmd.Parameters.Clear();
                        cmd.CommandText = "UPDATE Users SET CreatedBy = @CreatedBy WHERE Uid = @Uid";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@Uid", Uid);
                        cmd.Parameters.AddWithValue("@CreatedBy", GetInvitedBy(Convert.ToInt32(Request.QueryString["Uid"])));
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                      }
                    }
    
                    using(SqlCommand objCMD = new SqlCommand()) {
                      objCMD.Parameters.Clear();
                      objCMD.CommandText = @"INSERT INTO UserWallet (Uid, email, Name, amount) VALUES (@Uid, @email, @Name, @amount)";
                      objCMD.CommandType = CommandType.Text;
                      objCMD.Parameters.Add("@Uid", SqlDbType.Int, 50).Value = Uid;
                      objCMD.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
                      objCMD.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
                      objCMD.Parameters.Add("@amount", SqlDbType.Float, 100).Value = 0; //Change type here accordingly
                      objCMD.Connection = con;
                      con.Open();
                      objCMD.ExecuteNonQuery();
                      con.Close();
                      lblsuccess.Visible = true;
                      Div1.Visible = true;
                      lblsuccess.Text = "Successfully Signed Up";
                      lblsuccess.ForeColor = System.Drawing.Color.Green;
                      lblMessage.Visible = false;
                      dvMessage.Visible = false;
                      mailtxtbx.Text = "";
                      pass.Text = "";
                      conpass.Text = "";
                      txtname.Text = "";
                    }
                  }
                }
                else {
                  dvMessage.Visible = true;
                  lblMessage.Visible = true;
                  lblMessage.Text = "Please Check Box";
                  lblsuccess.Visible = false;
                  lblMessage.ForeColor = System.Drawing.Color.Red;
                }
              }
              else {
                dvMessage.Visible = true;
                lblMessage.Visible = true;
                lblMessage.Text = "Upload your document";
                lblsuccess.Visible = false;
                lblMessage.ForeColor = System.Drawing.Color.Red;
              }
            }
            else {
              dvMessage.Visible = true;
              lblMessage.Visible = true;
              lblMessage.Text = "Passwords don't match";
              lblMessage.ForeColor = System.Drawing.Color.Red;
              conpass.Text = "";
              lblsuccess.Visible = false;
            }
          }
          else {
            dvMessage.Visible = true;
            lblMessage.Visible = true;
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = "*All Fields Are Required*";
            lblsuccess.Visible = false;
          }
        }
      }
      private string GetInvitedBy(int id) {
        string invitedBy = "";
        using(SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True")) {
          using(SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE Uid = @Id")) {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Connection = con;
            con.Open();
            invitedBy = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
          }
        }
    
        return invitedBy;
      }
    }

    There is one place where CreadteBy is set.  The logic is dependent on creating a new Identity (Uid) and the existence of a Uid querystring.  

      if (Uid > 0) {
        if (!string.IsNullOrEmpty(Request.QueryString["Uid"])) {
          // string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString["Id"]));
          using(SqlCommand cmd = new SqlCommand()) {
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE Users SET CreatedBy = @CreatedBy WHERE Uid = @Uid";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Uid", Uid);
            cmd.Parameters.AddWithValue("@CreatedBy", GetInvitedBy(Convert.ToInt32(Request.QueryString["Uid"])));
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
          }
        }
    

    The SQL in GetInvitedBy() returns an int (Uid) not a string.  This is a bug but keep in mind it is not clear if execution makes it to GetInvitedBy().  To get here the Uid querystring must exist which means there is another page or link involved.  

    It seems like you are not single stepping through the code because it would be very obvious what the code is doing if you are watching each line execute.   

      private string GetInvitedBy(int id) {
        string invitedBy = "";
        using(SqlConnection con = 
    new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True")) { using(SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE Uid = @Id")) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Id", id); cmd.Connection = con; con.Open(); invitedBy = Convert.ToString(cmd.ExecuteScalar()); con.Close(); } } return invitedBy; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 1, 2020 11:36 AM

All replies

  • User303363814 posted

    Can you make your question readable, please?  And shorter - remove everything that is not relevant to the exact problem that you have.

    Saturday, August 1, 2020 12:40 AM
  • User-1994446809 posted
    The problem is that when inserting data into the table, I get a NULL value in the column named "CreatedBy"; but I want to get the Id and pass it into that column
    Saturday, August 1, 2020 4:14 AM
  • User475983607 posted

    The problem is that when inserting data into the table, I get a NULL value in the column named "CreatedBy"; but I want to get the Id and pass it into that column

    Debugging and troubleshooting is a necessary skill in programming.  You need learn the skill.  Visual Studio comes with a debugger and you should take the time to learn how to use the tool.

    https://docs.microsoft.com/en-us/visualstudio/debugger/navigating-through-code-with-the-debugger?view=vs-2019

    https://docs.microsoft.com/en-us/visualstudio/debugger/watch-and-quickwatch-windows?view=vs-2019

    When you share a lot of code on the forum it is up to you to tell the community where the problem is.   You can find where the problem  by single step through your code.   Otherwise; the community sees are a lot of potential errors and code written in a way we do not approve of.  It also helps if you make an effort to share formatted code.  The idea is to make it as easy as possible for the community to help you.  

    I had to take your original post, copy it to an HTML file,  run the code through a formatter, finally copy the formatted code into an editor just so I can follow the code.   That's a lot of work.   I'm sure you looking at formatted code in the editor why do you think it is okay for us to unformatted code?  IMO,  It's disrespectful.  Below is your code an a quick analysis.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Web.Security;
    using System.Xml.Linq;
    using System.Configuration;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;
    using System.IO;
    
    public partial class SignUp: System.Web.UI.Page {
      SqlCommand cmd = new SqlCommand();
      SqlDataAdapter sda = new SqlDataAdapter();
      DataSet ds = new DataSet();
      SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");
    
      protected void Page_Load(object sender, EventArgs e) {
        mailtxtbx.Focus();
      }
    
      protected void Button1_Click(object sender, EventArgs e) {
        SqlDataReader dr;
        using(SqlCommand cmd = new SqlCommand()) {
          cmd.Parameters.Clear();
          cmd.CommandText = "Select * from [Users] where email=@email and Name=@Name ";
          cmd.Parameters.AddWithValue("@email", mailtxtbx.Text);
          cmd.Parameters.AddWithValue("@Name", txtname.Text);
          cmd.Connection = con;
          con.Open();
          dr = cmd.ExecuteReader();
        }
        if (dr.HasRows) {
          dvMessage.Visible = true;
          Div1.Visible = false;
          lblMessage.Text = "User Already Exists";
          lblMessage.ForeColor = System.Drawing.Color.Red;
          mailtxtbx.Text = "";
          lblsuccess.Visible = false;
          lblMessage.Visible = true;
          //Label3.Visible = false;
        }
        else {
          con.Close();
    
          if (mailtxtbx.Text != "" & pass.Text != "" & conpass.Text != "" & txtname.Text != "") {
            if (pass.Text.Trim() == conpass.Text.Trim()) {
              if (Filedoc.PostedFile.FileName != "") {
                if (check1.Checked) {
                  int Uid = -1;
                  byte[] image;
                  Stream s = Filedoc.PostedFile.InputStream;
                  BinaryReader br = new BinaryReader(s);
                  image = br.ReadBytes((Int32) s.Length);
                  // define query to be executed
                  using(SqlCommand cmd = new SqlCommand()) {
                    cmd.Parameters.Clear();
                    cmd.CommandText = @"INSERT INTO Users (email, pass, UserRole, Name, image, CreateDate) VALUES (@email,@pass,@UserRole,@Name,@image,@CreatedDate); SELECT SCOPE_IDENTITY()";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@email", mailtxtbx.Text.Trim());
                    cmd.Parameters.AddWithValue("@pass", pass.Text.Trim());
                    cmd.Parameters.AddWithValue("@UserRole", 'A');
                    cmd.Parameters.AddWithValue("@Name", txtname.Text.Trim());
                    cmd.Parameters.AddWithValue("@image", image);
                    cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                    cmd.Connection = con;
                    con.Open();
                    object returnObj = cmd.ExecuteScalar();
                    con.Close();
                    if (returnObj != null) {
                      int.TryParse(returnObj.ToString(), out Uid);
                    }
                  }
    
                  if (Uid > 0) {
                    if (!string.IsNullOrEmpty(Request.QueryString["Uid"])) {
                      // string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString["Id"]));
                      using(SqlCommand cmd = new SqlCommand()) {
                        cmd.Parameters.Clear();
                        cmd.CommandText = "UPDATE Users SET CreatedBy = @CreatedBy WHERE Uid = @Uid";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@Uid", Uid);
                        cmd.Parameters.AddWithValue("@CreatedBy", GetInvitedBy(Convert.ToInt32(Request.QueryString["Uid"])));
                        cmd.Connection = con;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                      }
                    }
    
                    using(SqlCommand objCMD = new SqlCommand()) {
                      objCMD.Parameters.Clear();
                      objCMD.CommandText = @"INSERT INTO UserWallet (Uid, email, Name, amount) VALUES (@Uid, @email, @Name, @amount)";
                      objCMD.CommandType = CommandType.Text;
                      objCMD.Parameters.Add("@Uid", SqlDbType.Int, 50).Value = Uid;
                      objCMD.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = mailtxtbx.Text.Trim();
                      objCMD.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtname.Text.Trim();
                      objCMD.Parameters.Add("@amount", SqlDbType.Float, 100).Value = 0; //Change type here accordingly
                      objCMD.Connection = con;
                      con.Open();
                      objCMD.ExecuteNonQuery();
                      con.Close();
                      lblsuccess.Visible = true;
                      Div1.Visible = true;
                      lblsuccess.Text = "Successfully Signed Up";
                      lblsuccess.ForeColor = System.Drawing.Color.Green;
                      lblMessage.Visible = false;
                      dvMessage.Visible = false;
                      mailtxtbx.Text = "";
                      pass.Text = "";
                      conpass.Text = "";
                      txtname.Text = "";
                    }
                  }
                }
                else {
                  dvMessage.Visible = true;
                  lblMessage.Visible = true;
                  lblMessage.Text = "Please Check Box";
                  lblsuccess.Visible = false;
                  lblMessage.ForeColor = System.Drawing.Color.Red;
                }
              }
              else {
                dvMessage.Visible = true;
                lblMessage.Visible = true;
                lblMessage.Text = "Upload your document";
                lblsuccess.Visible = false;
                lblMessage.ForeColor = System.Drawing.Color.Red;
              }
            }
            else {
              dvMessage.Visible = true;
              lblMessage.Visible = true;
              lblMessage.Text = "Passwords don't match";
              lblMessage.ForeColor = System.Drawing.Color.Red;
              conpass.Text = "";
              lblsuccess.Visible = false;
            }
          }
          else {
            dvMessage.Visible = true;
            lblMessage.Visible = true;
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = "*All Fields Are Required*";
            lblsuccess.Visible = false;
          }
        }
      }
      private string GetInvitedBy(int id) {
        string invitedBy = "";
        using(SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True")) {
          using(SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE Uid = @Id")) {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Connection = con;
            con.Open();
            invitedBy = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
          }
        }
    
        return invitedBy;
      }
    }

    There is one place where CreadteBy is set.  The logic is dependent on creating a new Identity (Uid) and the existence of a Uid querystring.  

      if (Uid > 0) {
        if (!string.IsNullOrEmpty(Request.QueryString["Uid"])) {
          // string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString["Id"]));
          using(SqlCommand cmd = new SqlCommand()) {
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE Users SET CreatedBy = @CreatedBy WHERE Uid = @Uid";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Uid", Uid);
            cmd.Parameters.AddWithValue("@CreatedBy", GetInvitedBy(Convert.ToInt32(Request.QueryString["Uid"])));
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
          }
        }
    

    The SQL in GetInvitedBy() returns an int (Uid) not a string.  This is a bug but keep in mind it is not clear if execution makes it to GetInvitedBy().  To get here the Uid querystring must exist which means there is another page or link involved.  

    It seems like you are not single stepping through the code because it would be very obvious what the code is doing if you are watching each line execute.   

      private string GetInvitedBy(int id) {
        string invitedBy = "";
        using(SqlConnection con = 
    new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True")) { using(SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE Uid = @Id")) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Id", id); cmd.Connection = con; con.Open(); invitedBy = Convert.ToString(cmd.ExecuteScalar()); con.Close(); } } return invitedBy; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 1, 2020 11:36 AM
  • User-1994446809 posted

    Oh! so sorry about that. i didnt notice that; that should have happened when I edited the thread. Anyway, I have been able to resolve the problem. Thank you

    Monday, August 3, 2020 9:12 AM