SQL搜索到Label [英] SQL search to Label

问题描述

谁将帮助我继续使用>>>> ????<<<<<<下面...



在这个项目中，我正在看表Klanten



在KlantenBOXfilteren（）中，我在列表框中搜索我的记录。在选择客户端（通过名称naam显示）后，我得到一个ID。



在KlantenContactBOX（）我使用ID字段（再给manny一个关系）搜索来自客户的所有联系人



现在我想在KlantenInfo（）中制作类似的解决方案我想加载客户表有多个标签。




中的示例字段


表文件名Labelname

Naam = LB_KlantenNaam

Adres = LB_KlantenAdres

Telefoon = LB_KlantenTelefoon



我需要什么代码写在>>>> ????<<<<<

 private void KlantenBOXfilteren（）
 {
 string queryZoekOrganisatie =Select * From Klanten WHERE Organisatie like @ZoekOrganisatie ; 
 
使用（connection = new SqlConnection（connectionstring））
 using（SqlCommand command = new SqlCommand（queryZoekOrganisatie，connection））
 using（SqlDataAdapter adapter = new SqlDataAdapter（command）） 
 
 
 {
 command.Parameters.AddWithValue（@ ZoekOrganisatie，％+ TX_KlantenZoeken.Text +％）; 
 
 DataTable Klantenlijst = new DataTable（）; 
 adapter.Fill（Klantenlijst）; 
 
 ListBoxKlanten.DisplayMember =Organisatie; 
 ListBoxKlanten.ValueMember =ID Klanten; 
 ListBoxKlanten.DataSource = Klantenlijst; 
} 
} 
 
 // Klanten联系人personen 
 private void KlantenContactBOX（）
 {
 string queryKLantID =select * From Klantencontact WHERE FK_ID_Klant = @SELKlantID; 
 
使用（connection = new SqlConnection（connectionstring））
 using（SqlCommand command = new SqlCommand（queryKLantID，connection））
 using（SqlDataAdapter adapter = new SqlDataAdapter（command）） 
 
 {
 command.Parameters.AddWithValue（@ SELKlantID，ListBoxKlanten.SelectedValue）; 
 
 DataTable KlantenContactlijst = new DataTable（）; 
 adapter.Fill（KlantenContactlijst）; 
 
 listBoxKlantenContact.DisplayMember =Naam; 
 listBoxKlantenContact.ValueMember =FK_ID_klant; 
 listBoxKlantenContact.DataSource = KlantenContactlijst; 
} 
} 
 
 // klant informatie inzien 
 private void KlantenInfo（）
 {
 string queryKLantinfoID =select * From Klanten WHERE ID Klanten = @SELKlantinfoID; 
 
使用（connection = new SqlConnection（connectionstring））
 using（SqlCommand command = new SqlCommand（queryKLantinfoID，connection））
 using（SqlDataAdapter adapter = new SqlDataAdapter（command）） 
 
 {
 command.Parameters.AddWithValue（@ SELKlantinfoID，ListBoxKlanten.SelectedValue）; 
 
>>>> ????<<<<< 
} 
} 

解决方案

尝试这样的事情：

 私有  void  KlantenInfo（）
 {
  const   string  queryKLantinfoID =   SELECT TOP 1 naam，Adres，Telefoon FROM Klanten WHERE [ID Klanten] = @SELKlantinfoID; 
 
 使用（SqlConnection connection =  new  SqlConnection（connectionstring））
 使用（SqlCommand command =  new  SqlCommand（queryKLantinfoID，connection））
 {
 command.Parameters.AddWithValue（  @ SELKlantinfoID，ListBoxKlanten.SelectedValue）; 
 
 connection.Open（）; 
 使用（SqlDataReader reader = command.ExecuteReader（CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow））
 {
 < span class =code-keyword> if （reader.Read（））
 {
 LB_KlantenNaam.Text = Convert.ToString（reader [   naam]）; 
 LB_KlantenAdres.Text = Convert.ToString（reader [  Adres]）; 
 LB_KlantenTelefoon.Text = Convert.ToString（reader [  Telefoon]）; 
} 
  else  
 {
 LB_KlantenNaam.Text =  string  .Empty; 
 LB_KlantenAdres.Text =  string  .Empty; 
 LB_KlantenTelefoon.Text =  string  .Empty; 
} 
} 
} 
} 

NB：您似乎正在存储字段中的 SqlConnection 。这是个坏主意;相反，你应该在每个方法中使用一个局部变量，就像你有 SqlCommand 对象一样。



你应避免使用 SELECT * FROM ... ;只选择您要使用的列。



我已经为您添加了 TOP 1 条款如果 Klanten 列不是主键，则查询。如果是主键，则可以省略它。



SqlDataReader class有一个 GetString 方法，看起来它会起作用。不幸的是，如果从数据库返回的数据是 NULL ，则此方法抛出 SqlNullValueException 。您可以使用 IsDBNull 方法对此进行测试，但通常更容易对原始值使用 Convert.ToString 代替。

Who will help me continue with the code at ">>>>????<<<<<" below...

In this project, I'm looking at the table "Klanten"

At "KlantenBOXfilteren()" I search my record in a listbox. After selecting the client (visualized by name "naam") I get back an ID.

At "KlantenContactBOX()" I use the ID field ( one more to manny relationship ) to search for all contacts from the customer

Now I would like to make a similar solution in "KlantenInfo()" I want to load the fields from the " customer table " in multiple labels.

Example fields in

Table filename Labelname
Naam = LB_KlantenNaam
Adres = LB_KlantenAdres
Telefoon = LB_KlantenTelefoon

What code do I need to write at ">>>>????<<<<<"

private void KlantenBOXfilteren()
        {
            string queryZoekOrganisatie = "Select * From Klanten WHERE Organisatie like @ZoekOrganisatie";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryZoekOrganisatie, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))


            {
                command.Parameters.AddWithValue("@ZoekOrganisatie", "%" + TX_KlantenZoeken.Text + "%");

                DataTable Klantenlijst = new DataTable();
                adapter.Fill(Klantenlijst);

                ListBoxKlanten.DisplayMember = "Organisatie";
                ListBoxKlanten.ValueMember = "ID Klanten";
                ListBoxKlanten.DataSource = Klantenlijst;
            }
        }

        //Klanten Contact personen
        private void KlantenContactBOX()
        {
            string queryKLantID = "select * From Klantencontact WHERE FK_ID_Klant = @SELKlantID";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryKLantID, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))

            {
                command.Parameters.AddWithValue("@SELKlantID", ListBoxKlanten.SelectedValue);

                DataTable KlantenContactlijst = new DataTable();
                adapter.Fill(KlantenContactlijst);

                listBoxKlantenContact.DisplayMember = "Naam";
                listBoxKlantenContact.ValueMember = "FK_ID_klant";
                listBoxKlantenContact.DataSource = KlantenContactlijst;
            }
        }

        //klant informatie inzien
        private void KlantenInfo()
        {
            string queryKLantinfoID = "select * From Klanten WHERE ID Klanten = @SELKlantinfoID";

            using (connection = new SqlConnection(connectionstring))
            using (SqlCommand command = new SqlCommand(queryKLantinfoID, connection))
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                
            {
                command.Parameters.AddWithValue("@SELKlantinfoID", ListBoxKlanten.SelectedValue);

                >>>>????<<<<<
            }
        }

解决方案

Try something like this:

private void KlantenInfo()
{
    const string queryKLantinfoID = "SELECT TOP 1 naam, Adres, Telefoon FROM Klanten WHERE [ID Klanten] = @SELKlantinfoID";

    using (SqlConnection connection = new SqlConnection(connectionstring))
    using (SqlCommand command = new SqlCommand(queryKLantinfoID, connection))
    {
        command.Parameters.AddWithValue("@SELKlantinfoID", ListBoxKlanten.SelectedValue);
        
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
        {
            if (reader.Read())
            {
                LB_KlantenNaam.Text = Convert.ToString(reader["naam"]);
                LB_KlantenAdres.Text = Convert.ToString(reader["Adres"]);
                LB_KlantenTelefoon.Text = Convert.ToString(reader["Telefoon"]);
            }
            else
            {
                LB_KlantenNaam.Text = string.Empty;
                LB_KlantenAdres.Text = string.Empty;
                LB_KlantenTelefoon.Text = string.Empty;
            }
        }
    }
}

NB: You appear to be storing the SqlConnection in a field. That's a bad idea; instead, you should use a local variable in each method, as you have for the SqlCommand objects.

You should avoid using SELECT * FROM ...; only select the columns you're going to use.

I've added a TOP 1 clause to your query, in case the Klanten column isn't the primary key. If it is the primary key, you can omit this.

The SqlDataReader class has a GetString method which looks like it would work. Unfortunately, if the data returned from the database is NULL, this method throws a SqlNullValueException. You could use the IsDBNull method to test for this, but it's generally easier to use Convert.ToString on the raw value instead.

这篇关于SQL搜索到Label的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！