Написал программу к своей курсовой(hotel) на .NET 2.0 под mssql, но в итоге столкнулся с парой проблем которые незнаю как решить:
1. Соединение с базой данных проходит нормально, делаю какое-тодействие например регистрирую пользователя, потом пытаюсь просмотреть список комнат, и получается при повторном обращении к БД выскакивает ошибка:
System.InvalidOperationException: The connection was not closed. The connection's current state is open.
хотя вроде везде соединение закрываю, и потом заново открываю.
2. Когда пытаюсь выселить клиента выдает тоже ошибку и кидает на код в вижуал студии.
http://s49.radikal.ru/i126/1005/e8/7a7f77a65eb1.jpg
вот весь исходник той формы
| Код |
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Hotel
{
public partial class retribution : Form
{
private SqlConnection connection;
private string roomNo;
public retribution()
{
InitializeComponent();
}
public retribution(SqlConnection con)
{
connection = con;
InitializeComponent();
}
//if (Convert.ToInt32(roomNo) % 100 <= 10)
private void button3_Click(object sender, EventArgs e)
{
roomNo = this.textBox1.Text;
string query = @"SELECT regNumber, FIO FROM Client WHERE regNumber IN (SELECT regNumber FROM BookingAndLiving WHERE roomNo = " + roomNo + ")";
connection.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(query, connection);
da.Fill(ds, "Information");
dataGridView2.AutoGenerateColumns = true;
dataGridView2.DataSource = ds;
dataGridView2.DataMember = "Information";
connection.Close();
if (Convert.ToInt32(roomNo) % 100 <= 10)
{
this.textBox2.Text = ds.Tables["Information"].Rows[0][0].ToString();
this.label2.Enabled = false;
this.textBox2.Enabled = false;
}
}
private void button1_Click(object sender, EventArgs e)
{
string query = @"SELECT regNumber, roomNo, arrivingDate, leavingDate, bookingPay, DATEDIFF(day, arrivingDate, leavingDate)*
(SELECT price FROM RoomType WHERE RoomType.roomType = (SELECT type FROM Room WHERE Room.roomNo = " + roomNo + @")) AS Sheet, telephoneSheet,
(bookingPay + (SELECT price FROM RoomType WHERE RoomType.roomType = (SELECT type FROM Room WHERE Room.roomNo = " + roomNo + @")) + telephoneSheet) AS total
FROM BookingAndLiving WHERE regNumber = " + this.textBox2.Text;
connection.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(query, connection);
da.Fill(ds, "Sheet");
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Sheet";
connection.Close();
}
private void button2_Click(object sender, EventArgs e)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"INSERT INTO Archive SELECT Client.regNumber, FIO, roomNo, sex, document, docNumber, birthdayDate, address, homeTelephone, arrivingDate, leavingDate
FROM Client INNER JOIN BookingAndLiving ON Client.regNumber = BookingAndLiving.regNumber
WHERE Client.regNumber = " + this.textBox2.Text;
command.CommandText += @" DELETE FROM BookingAndLiving WHERE regNumber = " + this.textBox2.Text;
command.CommandText += @" DELETE FROM Client WHERE regNumber = " + this.textBox2.Text;
if (Convert.ToInt32(roomNo) % 100 <= 10)
command.CommandText += @" UPDATE Room SET status = 'free' WHERE roomNo = " + textBox1.Text;
else
command.CommandText += @" UPDATE Room SET clientsNumber = clientsNumber - 1 WHERE roomNo = " + textBox1.Text;
command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
}
}
}
|
|