数据库访问不起作用 [英] Database access doesn't work

问题描述

嗨:)



我一直在努力为我的小公司做一个项目。



我买了一本书来学习和测试SQL如何使用C＃和所有东西。

使用他们在书中提供的数据库工作得很好。



但是现在，当我尝试按照他们的指示制作我自己的数据库时，有些东西不起作用，因为我无法连接它。



我很确定我在C＃中的代码是正确的，因为如果我用Northwind表替换表Sunfuki的名称，我的查询和其他一切工作正常。



如果我查看SQL Server Management Studio，两个数据库都在同一个树中，服务器名称相同。

我尝试过比较两个表安全选项和我看不到任何不同。



在等待解决方案时，我制作了我需要在测试中使用的表格数据库所以我可以处理代码..但我真的不知道我在新数据库中做错了什么:(



我正在使用SQL Server 2008 R2& Microsoft Visual C＃2010 Express。



我的谢谢，如果有人可以提供帮助！



有效的代码好的如下：

Hi :)

I''ve been trying to work on a project for my little company.

I bought a book to learn and test how SQL works with C# and everything.
Working with the database they provide in the book worked just fine.

But now, when I try to make my own database following their instructions, there is something that doesn''t work because I can''t connect to it.

I''m pretty sure my code in C# is correct because if I replace the name of the table "Sunfuki" with the "Northwind" table my queries and everything else works just fine.

If I look in SQL Server Management Studio, both DB are in the same tree, same server name.
I tried to compare both tables Security Options and I can''t see anything different.

While waiting for solutions, I made the tables I need to use in the "test" database so I could work on the code.. but I really have no idea what I did wrong with the new DB :(

I''m using SQL Server 2008 R2 & Microsoft Visual C# 2010 Express.

My thanks if anyone can help with that !

Code that works just fine is as follows :

private void Btn_Chercher_Click(object sender, EventArgs e)
       {

           try
           {
               SqlCommand cmd;
               SqlConnection ctn;
               SqlDataReader lecteur;
               ctn = new SqlConnection();
               ctn.ConnectionString = "server=KARINE-ALIEN\\SQLEXPRESS;" + "Trusted_Connection=yes;" + "database=Northwind; " + "connection timeout=10";
               ctn.Open();
               cmd = new SqlCommand();
               cmd.Connection = ctn;
               cmd.CommandText = "SELECT * FROM categories";
               lecteur = cmd.ExecuteReader();
               while (lecteur.Read())
               {
                   Console.WriteLine(lecteur.GetInt32(0));
               }
               lecteur.Close();
               ctn.Close();
           }
           catch (Exception x)
           {
               Console.WriteLine(x.ToString());
           }

       }

现在如果我用Sunfuki更改数据库名称（我创建的新的）并更改了它的新查询：

Now if I change the Database name with Sunfuki (the new one I created) and change the query for the new one it looks like :

private void Btn_Chercher_Click(object sender, EventArgs e)
       {

           try
           {
               SqlCommand cmd;
               SqlConnection ctn;
               SqlDataReader lecteur;
               ctn = new SqlConnection();
               ctn.ConnectionString = "server=KARINE-ALIEN\\SQLEXPRESS;" + "Trusted_Connection=yes;" + "database=Sunfuki; " + "connection timeout=10";
               ctn.Open();
               cmd = new SqlCommand();
               cmd.Connection = ctn;
               cmd.CommandText = "SELECT No_Membre FROM Liste_Membres";
               lecteur = cmd.ExecuteReader();
               while (lecteur.Read())
               {
                   Console.WriteLine(lecteur.GetInt32(0));
               }
               lecteur.Close();
               ctn.Close();
           }
           catch (Exception x)
           {
               Console.WriteLine(x.ToString());
           }

       }

如果我使用将异常详细信息复制到剪贴板，我得到：



System.InvalidCastException被捕获

消息=指定演员表无效。

Source = System.Data

StackTrace：

在System.Data.SqlClient.SqlBuffer.get_Int32（）

在System.Data.SqlClient .SqlDataReader.GetInt32（Int32 i）

at WindowsFormsApplication1.Form1.Btn_Chercher_Click（Object sender，EventArgs e）in C：\ Users \ Karine\Desktop\Présencesetceintures \Présencesset ceintures \Form1.cs：第37行
InnerException：

If I use "Copy exception detail to the clipboard", I get :

System.InvalidCastException was caught
Message=Specified cast is not valid.
Source=System.Data
StackTrace:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at WindowsFormsApplication1.Form1.Btn_Chercher_Click(Object sender, EventArgs e) in C:\Users\Karine\Desktop\Présences et ceintures\Présences et ceintures\Form1.cs:line 37
InnerException:

推荐答案

你在该代码片段中唯一投射的地方就是当你尝试从数据库中获取值：

The only place you are casting in that code fragment is when you try to get the value from the database:

Console.WriteLine(lecteur.GetInt32(0));

这意味着第一列中的值您的表（或Sunfuki表）不是整数值。

在表定义中查看SSMS - 当您使用SELECT * FROM ...检索记录时，它返回它们按定义顺序排列，所以如果第一列不是整数（或包含空值），你就会遇到问题。



为了避免这种情况（和由于其他原因，请始终列出要返回的列，并按名称访问它们：

Which implies that the value in the first column of your table (or the "Sunfuki" table) is not an integer value.
Have a look in SSMS at the table definition - when you retrieve records with "SELECT * FROM..." it returns them in definition order, so if the first column isn''t an integer (or contains a null value) you will get a problem.

To avoid this (and for other reasons) always list the columns you want to return, and access them by name:

cmd.CommandText = "SELECT Id, UserName FROM Liste_Membres";
lecteur = cmd.ExecuteReader();
while (lecteur.Read())
{
    Console.WriteLine(lecteur.GetInt32("Id"));
}

BTW：使用UPPER CASE for SQL是个好主意保留字 - 在编写复杂查询时更容易发现它们。

BTW: It''s a good idea to use UPPER CASE for SQL reserved words - it makes it easier to spot them when writing complex queries.

在阅读了一段时间并在众多论坛中搜索后，我终于找到了我的错误。



我在自己的DataBase中选择的字段不是int，它只是一个小的。



我不得不使用

After reading for a while and searching in numerous forums, I finally found my mistake.

The field i was selecting in my own DataBase was not an "int", it was a smallint.

I had to use

Console.WriteLine(lecteur.GetSqlInt16(0));

我在以下网址找到了数据类型列表：



http://msdn.microsoft.com/en-us/library/ms131092%28v=sql。 105％29.aspx [ ]



我对那些对我的问题提出意见的人表示感谢，这是所有帮助的总和导致我的解决方案。

I found the list of the data types at :

http://msdn.microsoft.com/en-us/library/ms131092%28v=sql.105%29.aspx[]

My thx to everyone who gave their input on my problem, it was the sum of all the help that lead to my solution.

这篇关于数据库访问不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！