code not working

parthiban karnan

i tried the below code for restricting no of daily bookings to 10.. but did'nt work..

records inserting after 10

public DataSet GetEmployees()
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=EmpDB;User ID=sa;Password=sa");
con.Open();
SqlCommand cmd = new SqlCommand("Select count(ID) from carbookingdetails where createdon=Getdate()", con);
intCount= cmd.ExecuteScalar()
if (intCount>10)
alert("Booking is over for today");
else
{
//do Insertion Process in carbookdetails table.
}

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravikumar G13y ago
Problem with query: Getdate() functions returns date with time also, that's why the quesry is not working. Use: select count(id) from tablename where day(getdate()) and month(getdate()) and year(getdate()).
0
Riddhi Valecha13y ago
Hi..

What count are you looking for??

If its total number of rows, user count(*).

Eg - select count(*) from tablename;
0
bhuvana santhi13y ago
Hi

int intcount = Convert.ToInt32(cmd.ExecuteNonQuery());
// intCount = cmd.ExecuteScalar();
if(intcount>10)

what value you are getting in the intcount variable after executing the query?
0
parthiban karnan13y ago
i got the count using the query...how to use the query in code below...

using (SqlConnection con = new SqlConnection("Data Source=parthi-pc\\sqlexpress;Integrated Security=true;Initial Catalog=employee"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select count(id) from joinees where year(regdate) = year(getdate()) and month(regdate) = month(getdate()) and day(regdate) = day(getdate()) ", con);

int intcount = Convert.ToInt32(cmd.ExecuteNonQuery());
// intCount = cmd.ExecuteScalar();
if(intcount>10)

{

Label1.Text = " daily booking limit exceeded";

}
// gvUserInfo.DataSource = dr;
//gvUserInfo.DataBind();

else
{

sqlstr = "INSERT INTO joinees([name], [email],[phoneno]) values ('" + txtName.Text.ToString() + "','" + txtEMAIL.Text.ToString() + "','" + txtPHONE.Text.ToString() + " ')";
// SqlCommand cmd = new SqlCommand(sqlstr, con);

// cmd.ExecuteNonQuery();
con.Close();
Label1.Text = " Thank You for Joining .";
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Visible = false;
txtName.Text = "";
txtEMAIL.Text = "";
txtPHONE.Text = "";

0
bhuvana santhi13y ago
have you got the count value?
0
bhuvana santhi13y ago
Your columnname should be datetime column for example regdate should be the column name not id.. For example:
select count(id) fromdailybookings
where year(date) = year(getdate())
and month(date) = month(getdate())
and day(date) = day(getdate())
0
parthiban karnan13y ago
try the query..did'net get output..

Select count(id) from JOINEES where year(id)= year(getdate()) and month(id) = month(getdate()) and day(id)=day(getdate())
0
bhuvana santhi13y ago
Hi you cant get the count value.. Because getdate() returns the current date and time. how does it match with the previous records? so, you need to check as year(columnname) = year(getdate()) and month(columnname) = month(getdate()) and day(columnname) = day(getdate()) use this query in the where condition
0
parthiban karnan13y ago
executed the query below:

Select count(ID) from JOINEES where REGDATE=Getdate()

not getting count value
0
bhuvana santhi13y ago
Hi wat is the count value it returns from the database.. do you get the correct count value by executing that query
0
parthiban karnan13y ago

executed the below code for restricting daily user registrants to 10..data not inserting..

protected void btnSave_Click(object sender, EventArgs e)
{

using (SqlConnection con = new SqlConnection("Data Source=parthi-pc\\sqlexpress;Integrated Security=true;Initial Catalog=employee"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select count(ID) from JOINEES where REGDATE=Getdate()", con);
int intcount = Convert.ToInt32(cmd.ExecuteNonQuery());
// intCount = cmd.ExecuteScalar();
if(intcount>10)

{

Label1.Text = " daily booking limit exceeded";

}
// gvUserInfo.DataSource = dr;
//gvUserInfo.DataBind();

else
{

sqlstr = "INSERT INTO joinees([name], [email],[phoneno]) values ('" + txtName.Text.ToString() + "','" + txtEMAIL.Text.ToString() + "','" + txtPHONE.Text.ToString() + " ')";
// SqlCommand cmd = new SqlCommand(sqlstr, con);

// cmd.ExecuteNonQuery();
con.Close();
Label1.Text = " Thank You for Joining .";
Label1.ForeColor = System.Drawing.Color.Green;
Label1.Visible = false;
txtName.Text = "";
txtEMAIL.Text = "";
txtPHONE.Text = "";
0
Riddhi Valecha13y ago
Your error is -
intCount= cmd.ExecuteScalar()
-----------
Correct statement is -

int count = convert.toint32(cmd.executescalar());

if(count>0)
{
alert("");

}
else
{
// Insertion.
}
------------
Hope this helps. If yes, do mark the answer as accepted. Please ignore the spelling mistakes.
0
Suthish Nair13y ago
Select count(ID) from carbookingdetails where createdon=Getdate()

Run above query in SQL Server Management Studio and check the count.

Also try converting to int ..

int.Parse(cmd.ExecuteScalar().ToString()); or (int)cmd.ExecuteScalar();
0