code not working

Question

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.
}

Ravikumar G · Answer

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

Riddhi Valecha · Answer

Hi.. What count are you looking for?? If its total number of rows user count(*). Eg - select count(*) from tablename;

bhuvana santhi · Answer

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?

parthiban karnan · Answer

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 = ;

bhuvana santhi · Answer

have you got the count value?

bhuvana santhi · Answer

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

parthiban karnan · Answer

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

bhuvana santhi · Answer

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

parthiban karnan · Answer

executed the query below: Select count(ID) from JOINEES where REGDATE=Getdate() not getting count value

bhuvana santhi · Answer

Hi wat is the count value it returns from the database.. do you get the correct count value by executing that query

parthiban karnan · Answer

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 = ;

Riddhi Valecha · Answer

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.

Suthish Nair · Answer

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