locked
Populate Textbox.Text with result of SQL Query RRS feed

  • Question

  • User-1000576058 posted

    I am new to this forum, as well as to VB.Net and ASP.net.  I am familiar with Visual Basic.

    I am trying to get a few textboxes to populate from an SQL Query to my databse on page_load

    I know that my connection string is correct.  I know that the SQL Query is of the proper sytax and will return a result in the query builder...

    For brevity purposes let me show what I have tried to do:

     Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            Dim sSQL As String
            Dim EODDataSource As New SqlDataSource()
            EODDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("EODConnectionString1").ToString()
            EODDataSource.SelectCommandType = SqlDataSourceCommandType.Text
    
    
            sSQL = " FROM EndOfDay WHERE (MONTH(DateTimeStamp) = DATEPART(month, GETDATE())) AND (DAY(DateTimeStamp) = DATEPART(day, GETDATE()))"
    
            KU.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKia)" & sSQL
            MU.Text = EODDataSource.SelectCommand = "SELECT SUM(NewMitsu)" & sSQL
            UU.Text = EODDataSource.SelectCommand = "SELECT SUM(Used)" & sSQL
            TotalU.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKia, NewMitsu, Used)" & sSQL
            KF.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront)" & sSQL
            MF.Text = EODDataSource.SelectCommand = "SELECT SUM(NewMitsuFront)" & sSQL
            UF.Text = EODDataSource.SelectCommand = "SELECT SUM(UsedFront)" & sSQL
            TotalF.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront, NewMitsuFront, UsedFront)" & sSQL
            KB.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaBack)" & sSQL
            MB.Text = EODDataSource.SelectCommand = "SELECT SUM(NewMitsuBack)" & sSQL
            UB.Text = EODDataSource.SelectCommand = "SELECT SUM(UsedBack)" & sSQL
            TotalB.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaBack, NewMitsuBack, UsedBack)" & sSQL
            KG.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront, NewKiaBack)" & sSQL
            MG.Text = EODDataSource.SelectCommand = "SELECT SUM(NewMitsuFront, NewMitsuBack)" & sSQL
            UG.Text = EODDataSource.SelectCommand = "SELECT SUM(UsedFront,UsedBack)" & sSQL
            TotalG.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack)" & sSQL
            KPV.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront, NewKiaBack) / NewKia" & sSQL
            MPV.Text = EODDataSource.SelectCommand = "SELECT SUM(NewMitsuFront,NewMitsuBack) / NewMitsu" & sSQL
            UPV.Text = EODDataSource.SelectCommand = "SELECT SUM(UsedFront,UsedBack) / Used" & sSQL
            TotalPV.Text = EODDataSource.SelectCommand = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack) / SUM(NewKia, NewMitsu, Used)" & sSQL
    
     End Sub
    
    
    
    
    I realize this does not work.  Again ?IU am a beginner at this, but any help would be greatly appreciated.
    Monday, November 19, 2012 3:06 PM

Answers

  • User-1971614856 posted

    InvalidOperationException was unhandled by user code

    ExecuteScalar: Connection property has not been initialized.

    I missed the opening connection part. Please use the following code. Problem was that Executescalar requires an open DB Connection.

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                Dim con As System.Data.SqlClient.SqlConnection
                Dim ConnString As String = ConfigurationManager.ConnectionStrings("EODConnectionString1").ToString()
    
                Dim sSQL As String = " FROM EndOfDay WHERE (MONTH(DateTimeStamp) = DATEPART(month, GETDATE())) AND (DAY(DateTimeStamp) = DATEPART(day, GETDATE()))"
    
                con = New SqlConnection(ConnString)
                con.Open()
    
                Dim cmd As System.Data.SqlClient.SqlCommand = Nothing
                cmd = New System.Data.SqlClient.SqlCommand("", con)
                cmd.CommandType = System.Data.CommandType.Text
    
                cmd.CommandText = "SELECT SUM(NewKia)" & sSQL
                KU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsu)" & sSQL
                MU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = SELECT SUM(Used)" & sSQL
                UU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront)" & sSQL
                KF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront)" & sSQL
                MF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront)" & sSQL
                UF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewMitsuFront, UsedFront)" & sSQL
                TotalF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack)" & sSQL
                KB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuBack)" & sSQL
                MB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedBack)" & sSQL
                UB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack, NewMitsuBack, UsedBack)" & sSQL
                TotalB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack)" & sSQL
                KG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront, NewMitsuBack)" & sSQL
                MG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack)" & sSQL
                UG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack)" & sSQL
                TotalG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack) / NewKia" & sSQL
                KPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront,NewMitsuBack) / NewMitsu" & sSQL
                MPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack) / Used" & sSQL
                UPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack) / SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalPV.Text = cmd.ExecuteScalar()
    
                con.Close()
                cmd.Dispose()
                con.Dispose()
            End If
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 20, 2012 11:06 PM

All replies

  • User-1971614856 posted

    Please try following code instead of yours. And always remember to enclose the code of Page_Load into the If Not IsPostBack Then i.e.

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
      If Not IsPostBack Then
        'Your Code Here
      End If

    This is because that the Page_Load will always be called whenever anyother event (Button Click etc) will be raised from the page and if your Code will be within the If Not IsPostBack Then then that code will not be called again and again at each event. Do Study IsPostBack for this purpose.
    Anyways, here is your required Code.

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                Dim con As System.Data.SqlClient.SqlConnection
                Dim ConnString As String = ConfigurationManager.ConnectionStrings("EODConnectionString1").ToString()
    
                Dim sSQL As String = " FROM EndOfDay WHERE (MONTH(DateTimeStamp) = DATEPART(month, GETDATE())) AND (DAY(DateTimeStamp) = DATEPART(day, GETDATE()))"
    
                Dim cmd As System.Data.SqlClient.SqlCommand = Nothing
                cmd = New System.Data.SqlClient.SqlCommand("", con)
                cmd.CommandType = System.Data.CommandType.Text
    
                cmd.CommandText = "SELECT SUM(NewKia)" & sSQL
                KU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsu)" & sSQL
                MU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = SELECT SUM(Used)" & sSQL
                UU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront)" & sSQL
                KF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront)" & sSQL
                MF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront)" & sSQL
                UF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewMitsuFront, UsedFront)" & sSQL
                TotalF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack)" & sSQL
                KB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuBack)" & sSQL
                MB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedBack)" & sSQL
                UB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack, NewMitsuBack, UsedBack)" & sSQL
                TotalB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack)" & sSQL
                KG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront, NewMitsuBack)" & sSQL
                MG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack)" & sSQL
                UG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack)" & sSQL
                TotalG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack) / NewKia" & sSQL
                KPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront,NewMitsuBack) / NewMitsu" & sSQL
                MPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack) / Used" & sSQL
                UPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack) / SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalPV.Text = cmd.ExecuteScalar()
    
                con.Close()
                cmd.Dispose()
                con.Dispose()
            End If
        End Sub



    Tuesday, November 20, 2012 12:33 AM
  • User3866881 posted

    Hello,

    I think you should use SqlCommand+SqlDataReader to handle your problem.

    Tuesday, November 20, 2012 2:51 AM
  • User-1000576058 posted

    iGulfam,

     

    Thank you for your assistance.  I have replaced the code and tested.  I am recieving the following error:

    InvalidOperationException was unhandled by user code

    ExecuteScalar: Connection property has not been initialized.

    How would I initialize

                KU.Text = cmd.ExecuteScalar()

     

     

    Many thanks in advance.

     

    Terry

    Tuesday, November 20, 2012 12:23 PM
  • User3866881 posted

    ExecuteScalar: Connection property has not been initialized.

    Please make sure that your SqlCommand's instance has an initialized SqlConnection,something like:

    using (SqlCommand cmd = new SqlCommand("……",new SqlConnection("……"))
    {
        cmd.Connection.Open();
        int result = (int)cmd.ExecuteScalar();
        ………………
    } 

    Tuesday, November 20, 2012 8:15 PM
  • User-1000576058 posted
    Decker, Thanks for the reply, and I apologize for my ignorance. Could you please explain how I would do that? I am definitely out of my league here, but this is the last thing I need to get fixed.
    Tuesday, November 20, 2012 8:27 PM
  • User-1000576058 posted
    Decker, Thanks for the reply, and I apologize for my ignorance. Could you please explain how I would do that? I am definitely out of my league here, but this is the last thing I need to get fixed.
    Tuesday, November 20, 2012 8:27 PM
  • User3866881 posted

    Could you please explain how I would do that?

    Hello again,

    I've done that above,and you can see for more information here:

    http://msdn.microsoft.com/en-gb/library/877h0y3a.aspx

    Tuesday, November 20, 2012 8:41 PM
  • User-1971614856 posted

    InvalidOperationException was unhandled by user code

    ExecuteScalar: Connection property has not been initialized.

    I missed the opening connection part. Please use the following code. Problem was that Executescalar requires an open DB Connection.

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            If Not IsPostBack Then
                Dim con As System.Data.SqlClient.SqlConnection
                Dim ConnString As String = ConfigurationManager.ConnectionStrings("EODConnectionString1").ToString()
    
                Dim sSQL As String = " FROM EndOfDay WHERE (MONTH(DateTimeStamp) = DATEPART(month, GETDATE())) AND (DAY(DateTimeStamp) = DATEPART(day, GETDATE()))"
    
                con = New SqlConnection(ConnString)
                con.Open()
    
                Dim cmd As System.Data.SqlClient.SqlCommand = Nothing
                cmd = New System.Data.SqlClient.SqlCommand("", con)
                cmd.CommandType = System.Data.CommandType.Text
    
                cmd.CommandText = "SELECT SUM(NewKia)" & sSQL
                KU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsu)" & sSQL
                MU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = SELECT SUM(Used)" & sSQL
                UU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalU.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront)" & sSQL
                KF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront)" & sSQL
                MF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront)" & sSQL
                UF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewMitsuFront, UsedFront)" & sSQL
                TotalF.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack)" & sSQL
                KB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuBack)" & sSQL
                MB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedBack)" & sSQL
                UB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaBack, NewMitsuBack, UsedBack)" & sSQL
                TotalB.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack)" & sSQL
                KG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront, NewMitsuBack)" & sSQL
                MG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack)" & sSQL
                UG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack)" & sSQL
                TotalG.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront, NewKiaBack) / NewKia" & sSQL
                KPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewMitsuFront,NewMitsuBack) / NewMitsu" & sSQL
                MPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(UsedFront,UsedBack) / Used" & sSQL
                UPV.Text = cmd.ExecuteScalar()
    
                cmd.CommandText = "SELECT SUM(NewKiaFront,NewKiaBack,NewMitsuFront,NewMitsuBack,UsedFront,UsedBack) / SUM(NewKia, NewMitsu, Used)" & sSQL
                TotalPV.Text = cmd.ExecuteScalar()
    
                con.Close()
                cmd.Dispose()
                con.Dispose()
            End If
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 20, 2012 11:06 PM
  • User1528972941 posted

    Hi,

    I think this is the exact code which you are searching for. But it is in C#:

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NTierDemoDB"].ToString());
                SqlCommand cmd = new SqlCommand("Select * from Employee where PKEmpId=" + Convert.ToInt32(e.CommandArgument), con);
                cmd.CommandType = CommandType.Text;
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Read();
                    txtEmpName.Text = dr["EmpName"].ToString();
                    txtEmpSal.Text = dr["EmpSalary"].ToString();
                    txtDOB.Text = dr["DateOfBirth"].ToString();
                    chkIsActive.Checked = Convert.ToBoolean(dr["IsActive"]);
                }
                dr.Close();
                con.Close();
    Wednesday, November 21, 2012 7:17 AM