Bug : RefreshSchema sets SQLiteCommandBuilder command texts to NULL
Effect : SQLiteCommandBuilder builds the complex command texts , if u need to append second qry , it fails when the tableAdapter Update is called later. Hi guys , sent this also last week as non-subscribed member. Appreciate if you can take a look , it means that you cannot append to the command texts built by SqlCommandBuilder. The example of getting back the value of the DB auto-inc after an insert would be very common , and is causing a lot of problems in a project I am working on Many thanks Using SqlClient.SqlCommandBuilder See MS class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil der.aspx If you modify the command text , you must call RefreshSchema <http://msdn.microsoft.com/en-us/library/system.data.common.dbcommandbuilder .refreshschema.aspx> , to refresh the underlying T-Sql commands To generate INSERT, UPDATE, or DELETE statements, the SqlCommandBuilder uses the SelectCommand <http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapte r.selectcommand.aspx> property to retrieve a required set of metadata automatically. If you change the SelectCommand <http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapte r.selectcommand.aspx> after the metadata has been retrieved, such as after the first update, you should call the RefreshSchema <http://msdn.microsoft.com/en-us/library/system.data.common.dbcommandbuilder .refreshschema.aspx> method to update the metadata. In this example , used to retrieve the value of an auto-inc after an insert has been done on e.g. a grid , note the modified command text private void button20_Click_1(object sender, EventArgs e) { string qry = @" SELECT DepartmentID,name,groupname,modifieddate from humanResources.department"; SqlDataAdapter mAdaptDept = new SqlDataAdapter(qry, gConnSqlDb); DataTable mTableDept = new DataTable(); SqlCommandBuilder builder = new SqlCommandBuilder(mAdaptDept); SqlCommand cmd1 = builder.GetInsertCommand(); // debug to get value of cmd1.CommandText // INSERT INTO [humanResources].[department] ([name], [groupname], // [modifieddate]) VALUES (@p1, @p2, @p3) cmd1.CommandText += "; SELECT SCOPE_IDENTITY() AS DepartmentID;"; // MUST call this to refresh the T-SQL generated commands builder.RefreshSchema(); // debug to get value of cmd1.CommandText // debug to get value of cmd1.CommandText // INSERT INTO [humanResources].[department] ([name], [groupname], // [modifieddate]) VALUES (@p1, @p2, @p3); SELECT SCOPE_IDENTITY() AS DepartmentID; mAdaptDept.InsertCommand = cmd1; // mAdaptDept.Update(mTableDept); // when called later , the new auto-inc is retrieved and inserted to the datatable } Using Sqlite. SQLiteCommandBuilder , similar snippet private void button1_Click(object sender, EventArgs e) { string qry = @" select employeeId,lastName,FirstName,Title,birthdate from employees"; SQLiteDataAdapter mAdaptDept = new SQLiteDataAdapter(qry, gConnTest); DataTable mTableDept = new DataTable(); SQLiteCommandBuilder builder = new SQLiteCommandBuilder(mAdaptDept); SQLiteCommand cmd1 = builder.GetInsertCommand(); // debug to get value of cmd1.CommandText // INSERT INTO [Employees] ([LastName], [FirstName], [Title], [BirthDate]) // VALUES (@param1, @param2, @param3, @param4) cmd1.CommandText += "; SELECT last_insert_rowid() AS employeeId;"; // MUST call this to refresh the T-SQL generated commands builder.RefreshSchema(); // debug to get value of cmd1.CommandText // has gone to NULL mAdaptDept.InsertCommand = cmd1; // mAdaptDept.Update(mTableDept); // when called later , crash !! . no command text !! } Sean J Moynihan Information Technology ServiceS 086-816-8801 042-9336059 www.itss.ie _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
