CODEWITHSUNDEEP
c#sql-serverdatagrid
zen_1991
zen_1991

Reputation: 629

Writing to SQL Server Database

I am having trouble writing my datagrid changes to the database, i am trying to type in the changes on the grid and then when Button_Add_Car is pressed i execute this code and write changes to the database but nothing is being written to the database.

private void Button_Add_Car(object sender, RoutedEventArgs e)
    {
        SqlConnection cn = new SqlConnection();
        DataSet dt = new DataSet();
        SqlDataAdapter da;
        SqlCommandBuilder cmdBuilder;

        cn.ConnectionString = (String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLFunctions.connectSQL.SQLSERVER_ID, SQLFunctions.connectSQL.SQLDatabaseName, SQLFunctions.connectSQL.SQLServerLoginName, SQLFunctions.connectSQL.SQLServerPassword));
        cn.Open();
        da = new SqlDataAdapter("SELECT * FROM Cars", cn);

        cmdBuilder = new SqlCommandBuilder(da);

        da.Fill(dt);
        da.Update(dt);
        cn.Close();
}
  1. Am i on the right track using this method?
  2. Am i using the correct SQL Query? I am confused between the SELECT/INSERT as i have found examples where people are using both to achieve what i want to do. Surely i should be using the INSERT statement.

I made my own custom SQL Command to manually insert into the database so it is in fact working:

SQLCmd("INSERT INTO Cars (Date, Time) VALUES(2014-10-10, '12:00:00')");

EDIT 1:

Thanks to marc_s i managed to achieve some sort of inserting but i believe i need to modify the value section to be inside an IF Statement which will check if it is a null or not and change value back to cr.Date and cr.Time as i am making use of a list. I am unsure of how to utilize the if statement in this way because it is currently entering blank rows, although its a step in the right direction:

        CarRecord cr = new CarRecord();
        carRecords.Add(cr);

        SqlConnection con = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLFunctions.connectSQL.SQLSERVER_ID, SQLFunctions.connectSQL.SQLDatabaseName, SQLFunctions.connectSQL.SQLServerLoginName, SQLFunctions.connectSQL.SQLServerPassword));
        con.Open();
        SqlCommand comm = new SqlCommand("INSERT INTO Cars VALUES (@Date, @Time)", con);
        SqlDataAdapter da = new SqlDataAdapter(comm);

        da.SelectCommand.Parameters.Add(new SqlParameter("@Date", SqlDbType.NVarChar)).Value = DBNull.Value;
        da.SelectCommand.Parameters.Add(new SqlParameter("@Time", SqlDbType.NVarChar)).Value = DBNull.Value;
        da.SelectCommand.ExecuteNonQuery();

        DataTable dt = new DataTable();     
        SqlCommandBuilder builder = new SqlCommandBuilder(da);

        da.Update(dt);
        con.Close();

Upvotes: 0

Views: 611

Answers (1)

T J
T J

Reputation: 110

lets take your first code example.

take a look at the last 3 lines, first thing you do is to copy data from the table Cars and store that into the DataSet named dt.
then immediately after you store this dataset back into the database, without actually doing any changes. if dot net is smart enough it wont do anything, since you didn't change anything between the fill and the update call.

what you probably should be doing is get the dataset from the datagrid or similar and store that one instead.
or do as you have started on in your second example of when you identity that a row is updated take the data from that row and construct an insert (or update) query to the database.

Upvotes: 2

Related Questions