locked
Combine 2 SQL Query RRS feed

  • Question

  • User1109811461 posted

    hello all expertise here..got a problem

    i want to so count all  point that has been make by a student, at the same time i want to display the details of the information, my question is how to do that,

     

    below this is a student information query:

    SELECT MataMerit.JenisMerit, MataMerit.IdMerit, MataMerit.Tarikh, MataMerit.MataMerit, MataMerit.KeteranganMerit, MaklumatPelajar.IdPelajar, MataMerit.StatusPengMerit FROM MataMerit INNER JOIN MaklumatPelajar ON MataMerit.IdPelajar = MaklumatPelajar.IdPelajar WHERE (MataMerit.IdPelajar = @IdPelajar); SELECT Sum(MataMerit) FROM MataMerit Where IdPelajar=@IdPelajar

     

    and below this is point query  for the student

    SELECT Sum(MataMerit) FROM MataMerit Where IdPelajar=IdPelajar

     

    i want to combine this two query because want to display in gridview

    this two query above has been test, and can work well

     

    i'm new in this concept..need help

    Friday, November 23, 2012 3:33 AM

Answers

  • User-1685971342 posted

    Your error completely shows that the query returns nothing which in turn is NULL, so it can't cast it to toint.

    There has to be some problem there in the query or query making. I just can't understand.

    lblIdPelajar.Text - does it store the proper text? Did you debug through it?

    You want ""Tiada Kesalahan Disiplin Yang Telah Disahkan"" to be displayed when there is no data? And if your query does not return anyhing, ExecuteScalar will get null. You can check if cmd.ExecuteScalar() is null then GridMerit.EmptyDataText = "Tiada Kesalahan Disiplin Yang Telah Disahkan".

    Or make a blank datatable and assign the above text to rows(0).column(0) by adding a blank row to that. Then assign that datatable as datasource to your grid.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2012 8:22 AM
  • User3866881 posted

    afastars

    afastars

    Hi,

    You must define a parameter token before applying this,here's an example——

    Dim totalmerit As Integer
            Dim mySQLConnection = New SqlConnection()
            mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString
            mySQLConnection.Open()
            Dim cmd As New SqlCommand()
            cmd.Connection = mySQLConnection
            cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar"
            cmd.Parameters.AddWithValue("@IdPelajar",Your Real Value Here)         
    lbltotal.Text = cmd.ExecuteScalar().ToString()
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2012 8:30 PM

All replies

  • User477186420 posted

    You can create one SP and that SP will return dataset, that have 2 table from that you can bind first table into grid and use second table into your calculation

    let me know if any query

    Friday, November 23, 2012 3:36 AM
  • User306743125 posted

    Try something like..

    SELECT Sum(MataMerit.MataMerit), MataMerit.JenisMerit, MataMerit.IdMerit, 
    MataMerit.Tarikh, MataMerit.KeteranganMerit, MaklumatPelajar.IdPelajar, 
    MataMerit.StatusPengMerit 
    FROM MataMerit 
    INNER JOIN MaklumatPelajar ON MataMerit.IdPelajar = MaklumatPelajar.IdPelajar 
    WHERE (MataMerit.IdPelajar = @IdPelajar)
    GROUP BY MataMerit.JenisMerit, MataMerit.IdMerit, MataMerit.Tarikh, MataMerit.MataMerit, 
    MataMerit.KeteranganMerit, MaklumatPelajar.IdPelajar, MataMerit.StatusPengMerit

    Friday, November 23, 2012 3:37 AM
  • User-1685971342 posted

    The below query might help, try this.

    SELECT Sum(MataMerit.MataMerit), MataMerit.JenisMerit, MataMerit.IdMerit, 
    MataMerit.Tarikh, MataMerit.MataMerit, MataMerit.KeteranganMerit, 
    MaklumatPelajar.IdPelajar, MataMerit.StatusPengMerit 
    FROM MataMerit INNER JOIN MaklumatPelajar 
    ON MataMerit.IdPelajar = MaklumatPelajar.IdPelajar 
    WHERE (MataMerit.IdPelajar = @IdPelajar)
    group by MataMerit.JenisMerit, MataMerit.IdMerit, 
    MataMerit.Tarikh, MataMerit.MataMerit, MataMerit.KeteranganMerit, 
    MaklumatPelajar.IdPelajar, MataMerit.StatusPengMerit;
    

    When you want sum, you have to group by all other columns.

    Friday, November 23, 2012 3:48 AM
  • User1109811461 posted

    hi all expertise thanks for reply....

    i change my mind to display the total point.....i make it in more easy way

    i just want to display it on label..so i put this query on my Sqldatasource

    Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar

    so how to display this result of  the query above into label...i'm new in this concept neeh further help

    Friday, November 23, 2012 4:12 AM
  • User-1685971342 posted

    Use ExecuteScalar and get the value assign it to the taxt of the label

    label1.text = your return value

    Friday, November 23, 2012 4:32 AM
  • User1109811461 posted

    hi sargamlucy

    thanks for reply,  actually the return value should be what..?Cool sorry i'm new in this

    help you can help me..still in learning process....

    Friday, November 23, 2012 4:38 AM
  • User-1685971342 posted

    return value will be the result of ExecuteScalar...

    You can just google a bit, how to use ExecuteScalar, I am just being a little lazy to google it for you...

    Friday, November 23, 2012 4:40 AM
  • User1109811461 posted
     Dim totalmerit As Integer
            Dim mySQLConnection = New SqlConnection()
            mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString
    
            mySQLConnection.Open()
            Dim cmd As New SqlCommand()
            cmd.Connection = mySQLConnection
           
            Dim sql As String = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar"
            totalmerit = Convert.ToInt32(cmd.ExecuteScalar())
    
            lbltotal.Text = totalmerit
    
    


     hai Sargamlucy

    i already type my code using ExcuteScalar as you said but i got code erorr , the error said

    ExecuteScalar: CommandText property has not been initialized

    what is meant..? 

    one more thing is that my query correct...? because the value is  IdPelajar, this come from other page, it carry the value from page A to page B

     

     

     

    Friday, November 23, 2012 5:06 AM
  • User-1685971342 posted

    You have to give the query as the command text. Did you realize that you have made the sql string where you assigned the query, but where have you used it?

    Before running executescalar, you have to qrite, cmd.commandtext = sql(which is the query)

    Friday, November 23, 2012 5:16 AM
  • User1109811461 posted

    yes i already change it

    below is the latesdt code

    Dim totalmerit As Integer
            Dim mySQLConnection = New SqlConnection()
            mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString
    
            mySQLConnection.Open()
            Dim cmd As New SqlCommand()
            cmd.Connection = mySQLConnection
           
            cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar"
            totalmerit = Convert.ToInt32(cmd.ExecuteScalar())
    
            lbltotal.Text = totalmerit
    
    but still got error...see below

    Must declare the scalar variable "@IdPelajar".

    one more thing is that my query correct...? because the value is  IdPelajar, this come from other page, it carry the value from page A to page B

     

    Friday, November 23, 2012 5:22 AM
  • User-1685971342 posted

    This is what the error says. You can't just give IdPelajar in the query. You have to add it as a parameter, You have to provide a value for that. The query will not recognise IdPelajar otherwise.

    I am not too sure how to add that code, you have to google again.

    Friday, November 23, 2012 5:26 AM
  • User1109811461 posted

    this is really hard for me, because i'm new in this..but try my best

    Friday, November 23, 2012 5:34 AM
  • User1109811461 posted

    hai sargamlucy

    i got this error below

    Object cannot be cast from DBNull to other types.

    Object cannot be cast from DBNull to other types.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.
    Source Error:

    Line 50:         cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar"
    Line 51:         cmd.Parameters.AddWithValue("IdPelajar", Val(lblIdPelajar.Text).ToString())
    Line 52: totalmerit = Convert.ToInt32(cmd.ExecuteScalar()) Line 53:         'totalmerit = cmd.ExecuteScalar()
    Line 54:         lbltotal.Text = totalmerit

    do you have any ideas......?

     

     

     

    Friday, November 23, 2012 6:33 AM
  • User-1685971342 posted

    The ExecuteScalar gets you a null value and that's why when you try using convert.toint32, it says you can't convert. Did you run the query in sql? and see if it returns any value? Make sure you are passing the correct parameter... Check what is the value of the parameter... take that parameter, put in the query and run in SQL to see if you get any value.

    Friday, November 23, 2012 6:39 AM
  • User1109811461 posted

    yes this is the query i run from the sql

    select SUM(MataMerit)  from MataMerit where IdPelajar=37

    all is okay....

    Friday, November 23, 2012 6:41 AM
  • User1109811461 posted

    if i using this code below it's look like okay, but i want to select the data dynamicaly

    which the IdPelajar will depans on user select how it can be done...? 

     

    cmd.CommandText ="Select Sum(MataMerit) From MataMerit Where IdPelajar=37"

    totalmerit = Convert.ToInt32(cmd.ExecuteScalar())

    lbltotal.Text = totalmerit

    Friday, November 23, 2012 6:58 AM
  • User-1685971342 posted

    I am not sure if this below thing will work, but can you try like this?

     

    cmd.CommandText ="Select Sum(MataMerit) From MataMerit Where IdPelajar=37"
    
    Change this to
    
    cmd.CommandText ="Select Sum(MataMerit) From MataMerit Where IdPelajar=" + variable1;

    Create a variable of let's say integer type and assign the value 37... concatenate it with the query like above...

    Friday, November 23, 2012 7:13 AM
  • User1109811461 posted

    still can't resolve it

    when i do that it appear like display a sting like below

    Select Sum(MataMerit) From MataMerit Where IdPelajar=

     

    Friday, November 23, 2012 7:25 AM
  • User-1685971342 posted
    int ID = 37;
    string sqlQuery = "Select Sum(MataMerit) From MataMerit Where IdPelajar=" + ID;
    cmd.commandtext = sqlQuery;
    cmd.executescalar();

    Make sure your connection strings are all correct.

    Friday, November 23, 2012 7:35 AM
  • User1109811461 posted

    i think you misunderstood what i want...this is static right...?

    the system administrator able to select which user Id they want to see, so the code shoukd not be like that right..?

     

    Friday, November 23, 2012 7:39 AM
  • User1109811461 posted

    hi sargamlucy

    i got it this is the full code..please see below

     

      If GridMerit.Rows.Count > 0 Then
                GridMerit.EmptyDataText = "Tiada Kesalahan Disiplin Yang Telah Disahkan"
            Else
    
                Dim totalmerit As Integer
    
                Dim mySQLConnection = New SqlConnection()
                mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString
    
                mySQLConnection.Open()
                Dim cmd As New SqlCommand()
                cmd.Connection = mySQLConnection
    
                lblIdPelajar.Text = Request.QueryString("IdPelajar")
                cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@Idpelajar"
                cmd.Parameters.AddWithValue("idpelajar", lblIdPelajar.Text)
                totalmerit = Convert.ToInt32(cmd.ExecuteScalar())
                lbltotal.Text = totalmerit
            End If

    i use this code

      If GridMerit.Rows.Count > 0 Then           

      GridMerit.EmptyDataText = "Tiada Kesalahan Disiplin Yang Telah Disahkan"    

       to check the gridview has a data or not, but this code not working need help

     

    if the user got no data it will see the error message like below..so how to resolve it

     

    Object cannot be cast from DBNull to other types.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.
    Source Error:

    Line 55:             cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@Idpelajar"
    Line 56:             cmd.Parameters.AddWithValue("idpelajar", lblIdPelajar.Text)
    Line 57: totalmerit = Convert.ToInt32(cmd.ExecuteScalar()) Line 58:             lbltotal.Text = totalmerit
    Line 59:         End If
    Friday, November 23, 2012 8:09 AM
  • User-1685971342 posted

    Your error completely shows that the query returns nothing which in turn is NULL, so it can't cast it to toint.

    There has to be some problem there in the query or query making. I just can't understand.

    lblIdPelajar.Text - does it store the proper text? Did you debug through it?

    You want ""Tiada Kesalahan Disiplin Yang Telah Disahkan"" to be displayed when there is no data? And if your query does not return anyhing, ExecuteScalar will get null. You can check if cmd.ExecuteScalar() is null then GridMerit.EmptyDataText = "Tiada Kesalahan Disiplin Yang Telah Disahkan".

    Or make a blank datatable and assign the above text to rows(0).column(0) by adding a blank row to that. Then assign that datatable as datasource to your grid.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2012 8:22 AM
  • User1109811461 posted

    i'm using this code below

     

    If cmd.ExecuteScalar() Is Nothing Then

                GridMerit.EmptyDataText =

    "Tiada Maklumat Merit"

           

    Else


                totalmerit = Convert.ToInt32(cmd.ExecuteScalar())

                lbltotal.Text = totalmerit

           

    End If

     

    but still no luck so far

    Friday, November 23, 2012 8:45 AM
  • User3866881 posted

    afastars

    afastars

    Hi,

    You must define a parameter token before applying this,here's an example——

    Dim totalmerit As Integer
            Dim mySQLConnection = New SqlConnection()
            mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Default").ConnectionString
            mySQLConnection.Open()
            Dim cmd As New SqlCommand()
            cmd.Connection = mySQLConnection
            cmd.CommandText = "Select Sum(MataMerit) From MataMerit Where IdPelajar=@IdPelajar"
            cmd.Parameters.AddWithValue("@IdPelajar",Your Real Value Here)         
    lbltotal.Text = cmd.ExecuteScalar().ToString()
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2012 8:30 PM
  • User1109811461 posted

    hai decker...

    again you resolve my isssues, after try it so many time..now you give me a shineCool

    thanks again my expertise.....

    Saturday, November 24, 2012 9:55 AM