We created a Windows Form To Update a Table in SQL, First I Click Enter ID to retrieve Details from Database, then After changing some Data , when I click on Update Button , Its Give an error : "Procedure or function 'UPDATE' expects parameter '@Id', which was not supplied."
Windows Form Design :Click here
Error : Click here
Code For Windows :
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 WindowsFormsApplication12
{
public partial class Update : Form
{
string connectionString = @"Data Source=AMAR;Initial Catalog=Hotel;Integrated Security=True";
public Update()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
{
TestObject t = null;
string spName = "Get";
//string queryText = "Select * from TestTable where Id = " +txtId.Text;
SqlConnection conn = new SqlConnection(connectionString);
//SqlCommand com = new SqlCommand(spName, conn);
SqlCommand com = new SqlCommand(spName, conn);
com.Parameters.AddWithValue("@Id", ID.Text);
com.CommandType = CommandType.StoredProcedure;
conn.Open();
using (SqlDataReader reader = com.ExecuteReader())
{
t = new TestObject();
while (reader.Read())
{
t.Id = reader["ID"].ToString();
t.Status = reader["Status"].ToString();
t.FName = reader["FirstName"].ToString();
t.LName = reader["LastName"].ToString();
t.Addr = reader["Address"].ToString();
t.City = reader["City"].ToString();
t.State = reader["State"].ToString();
t.Country = reader["Country"].ToString();
t.PhoneNo = reader["PhoneNo"].ToString();
t.Email = reader["EmailId"].ToString();
t.Pin = reader["Pincode"].ToString();
t.CheckIn = reader["CheckIn"].ToString();
t.CheckOut = reader["CheckOut"].ToString();
t.AdultNo = reader["AdultNo"].ToString();
t.ChildNo = reader["InfantNo"].ToString();
t.InfantNo = reader["InfantNo"].ToString();
t.RoomNo = reader["RoomNo"].ToString();
};
}
Statustxt.Text = t.Status;
txtfName.Text = t.FName;
txtlName.Text = t.LName;
txtAddr.Text = t.Addr;
City.Text = t.City;
State.Text = t.State;
Country.Text = t.Country;
PhoneNo.Text = t.PhoneNo;
EmailID.Text = t.Email;
Pincode.Text = t.Pin;
CheckIN.Text = t.CheckIn;
CheckOut.Text = t.CheckOut;
Adult.Text = t.AdultNo;
Child.Text = t.ChildNo;
Infant.Text = t.InfantNo;
RoomNo.Text = t.RoomNo;
}
}
private void Update_Load(object sender, EventArgs e)
{
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string Stat = Statustxt.Text;
string FirstName = txtfName.Text;
string LastName = txtlName.Text;
string Address=txtAddr.Text;
string Cities=City.Text;
string States= State.Text;
string Countries =Country.Text;
string PhoneNos= PhoneNo.Text;;
string EmailId= EmailID.Text;
string PinCode=Pincode.Text;
string CIn=CheckIN.Text;
string COut=CheckOut.Text;
string AdultNo=Adult.Text;
string ChildNo=Child.Text;
string InfantNo=Infant.Text;
string RoomNos=RoomNo.Text;
TestObject obj = new TestObject();
obj.Stat=Statustxt.Text;
obj.FirstName = txtfName.Text;
obj.LastName = txtlName.Text;
obj.Address=txtAddr.Text;
obj.Cities=City.Text;
obj.States= State.Text;
obj.Countries =Country.Text;
obj.PhoneNos= PhoneNo.Text;;
obj.EmailId= EmailID.Text;
obj.PinCode=Pincode.Text;
obj.CIn=CheckIN.Text;
obj.COut=CheckOut.Text;
obj.AdultNo=Adult.Text;
obj.ChildNo=Child.Text;
obj.InfantNo=Infant.Text;
obj.RoomNos=RoomNo.Text;
string spName = "UPDATE";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(spName, conn);
conn.Open();
com.Parameters.AddWithValue("@Stat", obj.Stat);
com.Parameters.AddWithValue("@FirstName", obj.FirstName);
com.Parameters.AddWithValue("@LastName", obj.LastName);
com.Parameters.AddWithValue("@Address", obj.Address);
com.Parameters.AddWithValue("@Cities", obj.Cities);
com.Parameters.AddWithValue("@States", obj.States);
com.Parameters.AddWithValue("@Countries", obj.Countries);
com.Parameters.AddWithValue("@PhoneNos", obj.PhoneNos);
com.Parameters.AddWithValue("@EmailId", obj.EmailId);
com.Parameters.AddWithValue("@PinCode", obj.PinCode);
com.Parameters.AddWithValue("@CIn", obj.CIn);
com.Parameters.AddWithValue("@COut", obj.COut);
com.Parameters.AddWithValue("@AdultNo", obj.AdultNo);
com.Parameters.AddWithValue("@ChildNo", obj.ChildNo);
com.Parameters.AddWithValue("@InfantNo", obj.InfantNo);
com.Parameters.AddWithValue("@RoomNos", obj.RoomNos);
com.CommandType = CommandType.StoredProcedure;
com.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Customer Details updated in system");
}
}
}
SQL STORE PROCEDURE:
USE [Hotel]
GO
/****** Object: StoredProcedure [dbo].[Insert] Script Date: 04/01/2015 11:24:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UPDATE]
@Id int,
@Stat nvarchar(100),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@Address nvarchar(100),
@Cities nvarchar(100),
@States nvarchar(100),
@Countries nvarchar(100),
@PhoneNos int,
@EmailId nvarchar(100),
@PinCode int,
@CIn nvarchar(100),
@COut nvarchar(100),
@AdultNo int,
@ChildNo int,
@InfantNo int,
@RoomNos int
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE [Hotel].[dbo].[Details] SET
[Status] = @Stat,
[FirstName] = @FirstName,
[LastName] = @LastName,
[Address] = @Address,
[City] = @Cities,
[State] =@States ,
[Country] = @Countries,
[PhoneNo] = @PhoneNos,
[EmailId] = @EmailId,
[Pincode] = @PinCode,
[CheckIn] = @CIn,
[CheckOut] = @COut,
[AdultNo] = @AdultNo,
[ChildNo] = @ChildNo,
[InfantNo] = @InfantNo,
[RoomNo] = @RoomNos
WHERE ID = @Id
END
