在做课程设计,一个图书馆管理系统,将图书查询界面与图书导入界面放在了一起。在未导入前,查询功能没有问题,导入后,查询不到结果,sql语句返回值为-1
enter code here//图书查询sql
private void sqlRegion()
{
sqlR = "";
if (tbBookId.Text.Trim() != string.Empty)
{
sqlR += "and Bno ='" + tbBookId.Text.Trim() + "'";
// sqlR += "and Bno like '% "+tbBookId.Text.Trim()+"%'";
}
if (tbName.Text.Trim() != string.Empty)
{
sqlR += "and Bname like '%" + tbName.Text.Trim() + "%'";
}
if (tbWriter.Text.Trim() != string.Empty)
{
sqlR += "and Bauthor like '%" + tbWriter.Text.Trim() + "%'";
}
if (tbPublish.Text.Trim() != string.Empty)
{
sqlR += "and Bpub like '%" + tbPublish.Text.Trim() + "%'";
}
if (tbPublishDate.Text.Trim() != string.Empty)
{
sqlR += "and Bdate like '%" + tbPublishDate.Text.Trim() + "%'";
}
if (tbPrice.Text.Trim() != string.Empty)
{
sqlR += "and Bprice like '%" + tbPrice.Text.Trim() + "%'";
}
if (tbBookClass.Text.Trim() != string.Empty)
{
sqlR += "and Bclass like '%" + tbBookClass.Text.Trim() + "%'";
}
if (tbISBN.Text.Trim() != string.Empty)
{
sqlR += "and BISBN like '%" + tbISBN.Text.Trim() + "%'";
}
}
//图书查询
private void btnserarch_Click(object sender, EventArgs e)
{
dt.Clear();
ds.Clear();
if (tbBookId.Text.Trim() == string.Empty && tbName.Text.Trim() == string.Empty
&& tbWriter.Text.Trim() == string.Empty && tbPublish.Text.Trim() == string.Empty
&& tbPublishDate.Text.Trim() == string.Empty && tbPrice.Text.Trim() == string.Empty
&& tbBookClass.Text.Trim() == string.Empty && tbISBN.Text.Trim() == string.Empty)
{
myCon.Open();
sql = "select * from Book";
cmd = new SqlCommand(sql, myCon);
cmd.ExecuteNonQuery();
sda = new SqlDataAdapter("select * from Book", myCon);
sda.Fill(ds);
dgvBookSearchResult.DataSource = ds.Tables[0].DefaultView;
myCon.Close();
if (ds.Tables[0].Rows.Count > 0)
{
dgvBookSearchResult.DataSource = ds.Tables[0].DefaultView;
myCon.Close();
return;
}
else
MessageBox.Show("没有找到相关的记录!");
return;
}
string sqlRegSearch;
sqlRegion();
sqlRegSearch = "select * from Book where 1=1" + sqlR;
myCon.Open();
cmd = new SqlCommand(sqlRegSearch, myCon);
cmd.ExecuteNonQuery();
int count;
count = Convert.ToInt32(cmd.ExecuteNonQuery());
sda = new SqlDataAdapter(sqlRegSearch, myCon);
sda.Fill(ds);
//dgvBookSearchResult.DataSource = ds.Tables[0].DefaultView;
myCon.Close();
if (ds.Tables[0].Rows.Count > 0)
{
dgvBookSearchResult.DataSource = ds.Tables[0].DefaultView;
myCon.Close();
return;
}
else
MessageBox.Show("没有找到相关的记录!");
}
enter code here private void btnImport_Click(object sender, EventArgs e)
{
try
{
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tbPath.Text.Trim() + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strcon);
conn.Open();
string strExcel;
strExcel = string.Format("select * from [Sheet1$]");
OleDbDataAdapter oda = new OleDbDataAdapter(strExcel, strcon);
oda.Fill(ds, "sheet1");
myCon.Open();
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(myCon))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = "Book";//目标表
bcp.WriteToServer(ds.Tables[0]);
MessageBox.Show("成功!");
conn.Close();
return;
}
}
catch (Exception ex)
{
//"插入的图书与数据库中有重复!"
MessageBox.Show(ex.Message);
return;
//System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
myCon.Close();
}
}
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}
##