I AM GETTING A SYSTEM.DATA.SQLCLIENT.SQLEXCEPTION ERROR WHEN ATTEMPTING TO
INSERT DATA INTO A SINGLE TABLE THROUGH A GRID
//If this is due to a spelling error, i will
//inflict a ritual beating upon myself !!!!!!!!!!!!
TABLE SCRIPT
/* 04 September 2004 03:15:04 User: Server: (local) Database:
Payslip Application: MS SQLEM - Data Tools*/BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Allowances
(
AllowanceID int NOT NULL IDENTITY (1, 1),
AllowanceDesc nvarchar(50) NULL,
AllowanceAmt decimal(19, 4) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Allowances ON
GO
IF EXISTS(SELECT * FROM dbo.Allowances)
EXEC('INSERT INTO dbo.Tmp_Allowances (AllowanceID, AllowanceDesc,
AllowanceAmt)
SELECT AllowanceID, AllowanceDesc, AllowanceAmt FROM dbo.Allowances
TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Allowances OFF
GO
DROP TABLE dbo.Allowances
GO
EXECUTE sp_rename N'dbo.Tmp_Allowances', N'Allowances', 'OBJECT'
GO
ALTER TABLE dbo.Allowances ADD CONSTRAINT
PK_Allowances PRIMARY KEY CLUSTERED
(
AllowanceID
) ON [PRIMARY]
GO
GRANT SELECT ON dbo.Allowances TO public AS dbo
GRANT UPDATE ON dbo.Allowances TO public AS dbo
GRANT INSERT ON dbo.Allowances TO public AS dbo
GRANT DELETE ON dbo.Allowances TO public AS dbo
COMMIT
THIS IS THE CODE USED TO PERFORM THE INSERT, THE UPDATE,DELETE AND SELECT
WORKS FINE
//Populate allowances datagrid
// ****************************** ALLOWANCES
********************************
SqlConnection connGetAllowances = new SqlConnection(
"data source=chrisnote;initial catalog=Payslip;integrated
security=SSPI;persist security info=False;workstation id=CHRISNOTE;");
dsModify = new DataSet();
daModify = new SqlDataAdapter();
//Get the data
SqlCommand cmdSelect = connGetAllowances.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "SELECT AllowanceID," +
" AllowanceDesc,AllowanceAmt FROM Allowances";
// ******************************** Insert data
SqlCommand cmdInsert = connGetAllowances.CreateCommand();
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = "INSERT INTO Allowances " +
"(AllowanceID, AllowanceAmt, AllowanceDesc)" +
"VALUES (@AllowanceID, @AllowanceAmt,@AllowanceDesc)";
//Create insert parameters
cmdInsert.Parameters.Add("@AllowanceID",SqlDbType. Int,4,"AllowanceID");
cmdInsert.Parameters.Add("@AllowanceAmt",SqlDbType .Decimal, 9,"AllowanceAmt");
cmdInsert.Parameters.Add("@AllowanceDesc",SqlDbTyp e.NVarChar,
50,"AllowanceDesc");
cmdInsert.Parameters["@AllowanceID"].SourceVersion = DataRowVersion.Original;
//Set the dataadapter and fill the dataset
daModify.SelectCommand = cmdSelect;
daModify.UpdateCommand = cmdUpdate;
daModify.InsertCommand = cmdInsert;
daModify.DeleteCommand = cmdDelete;
daModify.Fill(dsModify, "Allowances");
//Bind the datasources to the datagrid
dgAllowances.DataSource = dsModify;
dgAllowances.DataMember = "Allowances";
THE FOLLOWING CODE IS MEANT TO UPDATE THE TABLE
private void btnAcceptManageChanges_Click(object sender, System.EventArgs e)
{
daModify.Update(dsModify, "Allowances");
}
Is there anywhere that i can get sample code of how to
populate, edit, add new and delete data from a datagrid ?
Thanks
Christopher
