SQL select statement to Label

[TIM_M_91]

OK so I have two classes in the first class is where the database is handled, in the second class is where I want the output from my database select statment in be displayed on a label. How do I get the result to a label on Invoice.aspx?

DBdatabase.cs

public static void surnameSelect(string Ssession)
        {

            cmd = new SqlCommand("Select Surname from Users Where Username = '" + Ssession + "'", con);


            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception in DBHandler", ex);
            }
            finally
            {
                con.Close();
            }
        }

Invoice.aspx.cs

string b = Session["LoginSession"].ToString();
            DBdatabase.ForenameSelect(b);

[JorgeM]

lable1.text = DBdatabasse.ForenameSelect(b);

[TIM_M_91]

When I tried

 Label11.Text = DBdatabase.ForenameSelect(b);

I got an error: Cannot implicity convert void to string

[JorgeM]

Sorry, I know vb, not c#.

Basically you want that function to return a string value so you can assign it to the text value of the label.

It looks to me like you are still missing code in the function to read from your data source.

[TIM_M_91]

So how would I change my code to read then execute the single result to a label on my webpage?

[JorgeM]

Ok, so here is a VB example. Note that the DB connection string information is stored in the web.config file. You can specify the DB connection info here if you do not want to keep the DB info in a central file.

Function ForenameSelect(ByVal Ssession As String) As String

        Dim sqlConn As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBString").ConnectionString)
        Dim sqlCmd As New System.Data.SqlClient.SqlCommand
        Dim sqlRdr As System.Data.IDataReader

        ForenameSelect = ""

        Try
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            sqlCmd.CommandText = "Select Surname from Users Where Username = '" & Ssession & "'"

            sqlRdr = sqlCmd.ExecuteReader
            While sqlRdr.Read()
                ForenameSelect = sqlRdr.GetString(0)
            End While
            sqlRdr.Close()
            sqlConn.Close()

        Catch ex As Exception
            ' Do something if there is an error.
        End Try

    End Function

[JorgeM]

I copied and pasted the above code in an online VB to C# converter. Here were the results. I have very little knowledge of the c# syntax so you'll need to validate it.

public string ForenameSelect(string Ssession)
{
    string functionReturnValue = null;

    System.Data.IDbConnection sqlConn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBString").ConnectionString);
    System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
    System.Data.IDataReader sqlRdr = null;

    functionReturnValue = "";

    try {
        sqlConn.Open();
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandText = "Select Surname from Users Where Username = '" + Ssession + "'";

        sqlRdr = sqlCmd.ExecuteReader();
        while (sqlRdr.Read()) {
            functionReturnValue = sqlRdr.GetString(0);
        }
        sqlRdr.Close();
        sqlConn.Close();

    } catch (Exception ex) {
        // Do something if there is an error.
    }
    return functionReturnValue;

}

[TIM_M_91]

OK so how can I send output to a label from my database class to my invoice.aspx.cs?

[JorgeM]

In invoice.aspx.cs, you are calling the function and assigning the value to hte text property of the label.

Label11.Text = DBdatabase.ForenameSelect(b);

For you to be able to access that function from any page, it would have to be a Shared Public Function. AGain, I am not familiar with c#, so I am sorry that I cannot provide you with the proper syntax.

For now, just place a simple function inside of the invoice.aspx.cs file and make sure that your function works. If it does as it should, then do the research on how to create a public shared function.

[kvprajapati]

In order to prevent SQL-Injection and escape data, you must have to use parameterized SQL statement and do not call ExecuteNonQuery() method to fetch the value/data. You should have to use ExecuteReader() or ExecuteScalar() method.

One important suggestion : Always use "using" block. "using" block will call the "dispose()" method of ADO objects.

public static string surnameSelect(string Ssession)
  {
    string surname=string.Empty;
    using(SqlConnection cn=new SqlConnection())
     {
      using(SqlCommand cmd=new SqlCommand())
       {
         cmd.ConnectionString="your_connection_string";
         cmd = new SqlCommand("Select Surname from Users Where Username =@username);
         cmd.Connection=cn;
         cmd.Parameters.AddWithValue("@username",Ssession);
         cn.Open();
         surname=cmd.ExecuteScalar().ToString();
         cn.Close();
         return surname;   
        }
      }
 }

and call this method,

string surname=DBdatabase.ForenameSelect(b);

[TIM_M_91]

Thanks for reply working now