467,175 Members | 1,355 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,175 developers. It's quick & easy.

Datagrid Insert Statement

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

Nov 16 '05 #1
  • viewed: 1966
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by adove | last post: by
7 posts views Thread by Pierluigi Terzoli | last post: by
8 posts views Thread by Brent Burkart | last post: by
9 posts views Thread by tshad | last post: by
2 posts views Thread by Channa | last post: by
4 posts views Thread by jaYPee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.