in C# ineed2 get the table from sql by selecting date&time

Question

hai
i am a fresher and i need to do a project in that i need to fetch the data from the sql database and display by c# coding, everything is done perfectly but i got stuck in the part of fetching the data from the table in accordance with two values ie( the table from the date and time entered ex: start values in table should be the first date and time and the last value in the table should be the second date and time )plz if u have any demo code or any other ref please help

Sudhakar Chaudhary · Answer

private void button1_Click(object sender EventArgs e) { con.ConnectionString = data source=.\sqlexpress;initial catalog=main2012;integrated security=true; SqlDataAdapter adp = new SqlDataAdapter(SELECT P_NAMEP_PACKINGBAT_NOF_QNT FROM PPT WHERE F_DATE BETWEEN ' + dateTimePicker2.Value.ToShortDateString() + ' AND ' + dateTimePicker2.Value.ToShortDateString() + ' con); DataSet ds = new DataSet(); adp.Fill(ds s); if (ds.Tables[s].Rows.Count > 0) { for (int i = 0; i { dataGridView1.Rows.Add(); dataGridView1.Rows[i].Cells[0].Value = ds.Tables[s].Rows[i].ItemArray[0]; dataGridView1.Rows[i].Cells[1].Value = ds.Tables[s].Rows[i].ItemArray[1]; dataGridView1.Rows[i].Cells[2].Value = ds.Tables[s].Rows[i].ItemArray[2]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[3]; } } else { MessageBox.Show(No Record Found For Given Date.); } } private void Form1_Load(object sender EventArgs e) { dataGridView1.Columns.Add( PRODCUT NAME); dataGridView1.Columns.Add( PACKING); dataGridView1.Columns.Add( BATCH NO); dataGridView1.Columns.Add( QANTITY); ChangeDateFormat(dateTimePicker1); ChangeDateFormat(dateTimePicker2); } public void ChangeDateFormat(DateTimePicker dtp) { dtp.Format = DateTimePickerFormat.Custom; dtp.CustomFormat = dd/MM/yyyy; }

Gopal Mahadak · Answer

I think u login with windows authentication to u r sqlexpress so please add user 'root' to u r sqlexpress... steps: \sqlexpress-------->security-------->login---->right click on logins then add new login & follow steps in it.. I think it will work.. One more if u want to display data from database use c1trueDBview of Dev Product.. it is very simple & easy than Datagridview & having facility to search Data... try IT...

vinoth kumar · Answer

i receive a error report which i attached as a image

Sudhakar Chaudhary · Answer

first check your connection to sql server SqlConnection con = new SqlConnection(); con.ConnectionString=data source=.\sqlexpress;database=test;user id=root;password=vinoth;; MessageBox.Show(con.State.ToString()); if message box shows Open or Close Then Connection Working Properly.

vinoth kumar · Answer

i have made mysql password protected but here mentioned only the table name perodicdata is there any problem regarding to that if there is plz send me the syntax for that also SqlDataAdapter adp = new SqlDataAdapter(SELECT Date_timeCT1_AHCT2_AHCT3_AHCT1_WattHrCT2_WattHrMax_TemperatureMin_TemperatureVolt1Amp1Volt2Batt1_percentFullBatt1_gotchargedBatt2_percentFullBatt2_gotcharged FROM periodicdata WHERE Date_time BETWEEN ' + dateTimePicker1.Value.ToShortDateString() + ' AND ' + dateTimePicker2.Value.ToShortDateString() + ' con);

Sudhakar Chaudhary · Answer

Every thing is fine. if there are problem then check your connection and sql query. see the attachment.

vinoth kumar · Answer

hai i hve attached my full code with the database in the rar file if i enter the first date and end date according to my database datas i should obtain the datas from the date i entered this is my app . when i executed the program i got the error and not get executed to exception and database connection. plz help with this program how to clear this errors

Sudhakar Chaudhary · Answer

SELECT Date Time CT1_AHCT2_AHCT3_AHCT1_WattHrCT2_WattHrMax_TemperatureMin_TemperatureVolt1Amp1Volt2Batt1_percentFullBatt1_gotDischargedBatt2_percentFullBatt2_gotDischarged FROM PPT WHERE F_DATE BETWEEN ' + dateTimePicker2 .Value.ToShortDateString() + ' AND ' + dateTimePicker2 .Value.ToShortDateString() + ' dataGridView1.Rows[i].Cells[0].Value = ds.Tables[s].Rows[i].ItemArray[0]; dataGridView1.Rows[i].Cells[1].Value = ds.Tables[s].Rows[i].ItemArray[1]; dataGridView1.Rows[i].Cells[2].Value = ds.Tables[s].Rows[i].ItemArray[2]; dataGridView1.Rows[i]. Cells[3] .Value = ds.Tables[s].Rows[i].ItemArray[3]; dataGridView1.Rows[i]. Cells[3] .Value = ds.Tables[s].Rows[i].ItemArray[4]; dataGridView1.Rows[i]. Cells[3 ].Value = ds.Tables[s].Rows[i].ItemArray[5]; dataGridView1.Rows[i] .Cells[3] .Value = ds.Tables[s].Rows[i].ItemArray[6]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[7]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[8]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[9]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[10]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[11]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[12]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[13]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[14]; please run your query in sql express. if query run successfully then u able to get result.if there are more problem then send me full code with database.

vinoth kumar · Answer

usually i carried out the process by the code sent by u and modified it i attach the code below but it is not connected to my database i dont understand where i made mistake using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFormsApplication9 { public partial class Form1 : Form { // SqlConnection = new SqlConnection(server=localhost;user id=root;password=*****;database=test);// SqlConnection con = new SqlConnection(); public Form1() { InitializeComponent(); } private void button1_Click(object sender EventArgs e) { // con.ConnectionString = server=localhost;user id=root;password=*****;database=test; con.ConnectionString = data source=.\sqlexpress;initial catalog=periodictable;integrated security=true; SqlDataAdapter adp = new SqlDataAdapter(SELECT Date TimeCT1_AHCT2_AHCT3_AHCT1_WattHrCT2_WattHrMax_TemperatureMin_TemperatureVolt1Amp1Volt2Batt1_percentFullBatt1_gotDischargedBatt2_percentFullBatt2_gotDischarged FROM PPT WHERE F_DATE BETWEEN ' + dateTimePicker2.Value.ToShortDateString() + ' AND ' + dateTimePicker2.Value.ToShortDateString() + ' con); DataSet ds = new DataSet(); adp.Fill(ds s); if (ds.Tables[s].Rows.Count > 0) { for (int i = 0; i { dataGridView1.Rows.Add(); dataGridView1.Rows[i].Cells[0].Value = ds.Tables[s].Rows[i].ItemArray[0]; dataGridView1.Rows[i].Cells[1].Value = ds.Tables[s].Rows[i].ItemArray[1]; dataGridView1.Rows[i].Cells[2].Value = ds.Tables[s].Rows[i].ItemArray[2]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[3]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[4]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[5]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[6]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[7]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[8]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[9]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[10]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[11]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[12]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[13]; dataGridView1.Rows[i].Cells[3].Value = ds.Tables[s].Rows[i].ItemArray[14]; } } else { MessageBox.Show(No Record Found For Given Date.); } } private void Form1_Load(object sender EventArgs e) { dataGridView1.Columns.Add( Date Time); dataGridView1.Columns.Add( CT1_AH); dataGridView1.Columns.Add( CT2_AH); dataGridView1.Columns.Add( CT3_AH); dataGridView1.Columns.Add( CT1_WattHr); dataGridView1.Columns.Add( CT2_WattHr); dataGridView1.Columns.Add( Max_Temperature); dataGridView1.Columns.Add( Min_Temperature); dataGridView1.Columns.Add( Volt1); dataGridView1.Columns.Add( Amp1); dataGridView1.Columns.Add( Volt2); dataGridView1.Columns.Add( Batt1_percentFull); dataGridView1.Columns.Add( Batt1_gotCharged); dataGridView1.Columns.Add( Batt2_percentFull); dataGridView1.Columns.Add( Batt2_gotCharged); ChangeDateFormat(dateTimePicker1); ChangeDateFormat(dateTimePicker2); } public void ChangeDateFormat(DateTimePicker dtp) { dtp.Format = DateTimePickerFormat.Custom; dtp.CustomFormat = MM/dd/yyyy; } } }

Sudhakar Chaudhary · Answer

sorry i can't understand. could u explain ?

vinoth kumar · Answer

thanks now i understand the coding part i just missed by my side one more doubt in this code it requires forms to display the datas if i configure my database with this it will take that database and obtain data or i should save the data from database in the format of forms and display it

vinoth kumar · Answer

for windows application

Sudhakar Chaudhary · Answer

Windows or Web Application?