Data binding

Question

User1309273214 posted

Hi all,
I would like to use this code, to present training score/grades:

DECLARE @SmallerCount decimal(10,2) ,@TotalCount decimal(10,2)

SELECT @SmallerCount = count(*)
FROM opc
WHERE o24 IS NOT NULL AND o24 <4

SELECT @TotalCount = count(*)
FROM opc
WHERE o24 IS NOT NULL


SELECT CAST( ((@SmallerCount/ @TotalCount) *100) AS decimal(10,2)) AS SmallerPercent,
        CAST( (( (@TotalCount- @SmallerCount)/ @TotalCount ) *100) AS decimal(10,2)) LargerPercent



But, when using the Query builder I get the error message:

The Declare SQL construct or statement is not supported.

Further, when I try to Configure the Data Source (in Visual Web Developer) to present the results in a gridview or formview, then I need to Define parameters such as Parameter Source and default value ?

I am completely lost, any suggestions how do I proceed from here would be highly appreciated.

Thanks

Answer 1

User-271186128 posted

Hi ricas,

The Declare SQL construct or statement is not supported.

What the version of the SQL Server DataBase? According to your SQL statement, I create a sample on my side, it seems that everything works well. I use VS 2013 and SQL Server Express LocalDB.

Further, when I try to Configure the Data Source (in Visual Web Developer) to present the results in a gridview or formview, then I need to Define parameters such as Parameter Source and default value ?

As for this issue, you can create a store procedure, then refer to the following articles to call the store procedure and bind GridView:

http://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx

https://msdn.microsoft.com/en-us/library/k10148y1.aspx?f=255&MSPPError=-2147217396

http://www.c-sharpcorner.com/UploadFile/f50501/inserting-and-data-binding-from-database-using-stored-store/

Best regards,
Dillion

Answer 2

User1309273214 posted

Hi Dillon

What the version of the SQL Server DataBase?

It's a MSSQL version 12 which I access via a VPN

As for this issue, you can create a store procedure, Best regards,

I am trying to figure this procedure issue out, not quite sure which part of the code that needs to be stored as a procedure - the Declare line or all of it ?

Thanks

Answer 3

User1309273214 posted

or is it possible to do this without a stored procedure ?

Thanks

Answer 4

User-271186128 posted

Hi ricas,

or is it possible to do this without a stored procedure ?

Since, your code contains multiple select statement, I suppose stored procedure is a good choice.

You can create a stored procedure using the following code:

CREATE PROCEDURE [dbo].[GetScoreGrades] 
AS
BEGIN

 SELECT @SmallerCount = count(*) 
 FROM opc 
 WHERE o24 IS NOT NULL AND o24 <4 

 SELECT @TotalCount = count(*) 
 FROM opc 
 WHERE o24 IS NOT NULL


 SELECT CAST( ((@SmallerCount/ @TotalCount) *100) AS decimal(10,2)) AS SmallerPercent, 
         CAST( (( (@TotalCount- @SmallerCount)/ @TotalCount ) *100) AS decimal(10,2)) LargerPercent
END

Then, refer to the following code to query database and bind GridView:

String strConnString =  ConfigurationManager.ConnectionStrings["conString"].ConnectionString; 
SqlConnection con =  new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetScoreGrades";
cmd.Connection = con;
try
{
    con.Open();
    GridView1.EmptyDataText = "No Records Found";
    GridView1.DataSource = cmd.ExecuteReader() ;
    GridView1.DataBind();  
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    con.Close();
    con.Dispose();
}

More details about how to use stored procedure, see: http://www.aspsnippets.com/Articles/Bind-data-to-ASPNet-GridView-using-Stored-Procedure.aspx

Best regards,
Dillion

Answer 5

User1309273214 posted

Thanks Dillon,

I think I get it to work now and understand the stored procedure

Best regards