Populate Textbox.Text with result of SQL Query : The Official Microsoft ASP.NET Forums

Reply

Populate Textbox.Text with result of SQL Query

Nov 19, 2012 07:06 PM|LINK

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.

Reply

Re: Populate Textbox.Text with result of SQL Query

Nov 20, 2012 04:33 AM|LINK

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

Please Mark as Answer if the post helps you.
My BLOG

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 20, 2012 06:51 AM|LINK

grunwaldtx
grunwaldtx

Hello,

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

Reply

grunwaldtx

Member

1 Points

8 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 20, 2012 04:23 PM|LINK

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

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 12:15 AM|LINK

grunwaldtx
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();
    ………………
}

Reply

grunwaldtx

Member

1 Points

8 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 12:27 AM|LINK

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.

Reply

grunwaldtx

Member

1 Points

8 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 12:27 AM|LINK

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.

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 12:41 AM|LINK

grunwaldtx
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

Reply

iGulfam

Contributor

4794 Points

947 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 03:06 AM|LINK

grunwaldtx

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

Please Mark as Answer if the post helps you.
My BLOG

Marked as answer by grunwaldtx on Nov 21, 2012 03:15 PM

Reply

sravanisrav

Member

470 Points

107 Posts

Re: Populate Textbox.Text with result of SQL Query

Nov 21, 2012 11:17 AM|LINK

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();

Mark as answer,if my post helps you..

Thanks & Regards
Sravani

Populate Textbox.Text with result of SQL Query RSS

9 replies

grunwaldtx

Populate Textbox.Text with result of SQL Query

iGulfam

Re: Populate Textbox.Text with result of SQL Query

Decker Dong ...

Re: Populate Textbox.Text with result of SQL Query

grunwaldtx

grunwaldtx

Re: Populate Textbox.Text with result of SQL Query

Decker Dong ...

Re: Populate Textbox.Text with result of SQL Query

grunwaldtx

grunwaldtx

Re: Populate Textbox.Text with result of SQL Query

grunwaldtx

Re: Populate Textbox.Text with result of SQL Query

Decker Dong ...

Re: Populate Textbox.Text with result of SQL Query

grunwaldtx

iGulfam

Re: Populate Textbox.Text with result of SQL Query

grunwaldtx

sravanisrav

Re: Populate Textbox.Text with result of SQL Query

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft