• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

C# Execute SQl View with an object

newbie trying to trigger off this statement in a Main form C#...

command.CommandText = "SELECT CustomerName FROM spCustomersandContacts WHERE CustomerCounter = " + form.Counter + ""; //Works ok and returns value

this.txtCustomerName.DataBindings.Add("Text", mydatabaseDataSet, "spCustomersandContacts.CustomerName");  //nothing happens

then populate txtbox with the result, it does run with no errors and when i put a break point ont the SQL code it does have the value OK but it does not poulate the txtbox, is my syntax wrong on the txtbox line?
note:-Definition of form.counter holds a value [Counter] returned from another form
0
wilko100
Asked:
wilko100
  • 7
  • 3
1 Solution
 
RiteshShahCommented:
you can do something like this:

Dim bindingsource As new  System.Windows.Forms.BindingSource(Me.components)
Me.bindingsource.DataMember = "Table1"
        Me.bindingsource.DataSource = ds

textBox1.DataBindings.Add _
       (New Binding("Text", bindingsource, "Name"))

this is actually VB.NET code but you can get the concept. if this will not work, let me see your code more.
0
 
RiteshShahCommented:
0
 
wilko100Author Commented:
Below is the code, i have created a datasource (RIS_ClientDataset which is not bound to any control) and used it in the txtbox lline of code but do i need this as i have manually specified it in the SQLconnection string. Is that where im going wrong? Do i need to specify the database name in this line instead of rIS_ClientDataset?

this.txtCustomerName.DataBindings.Add("Text", rIS_ClientDataSet, "spCustomersandContacts.CustomerName");  //change rIS_ClientDataset to database?

I will have a look at the reference's and VB code for examples you mentioned later today so thanks for that
private void btnSearchCustomer_Click(object sender, EventArgs e)
        {
            using (frmSearchCustomer form = new frmSearchCustomer())
            {
                if (form.ShowDialog() == DialogResult.OK)
                {
                    //this.txtCustomerName.Text = form.Counter;
                    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
                    conn.ConnectionString = @"Data Source=<SQLInstance>;Initial Catalog=RIS_Client;Integrated Security=True";
                    //setup SqlCommand and assign SQL query in command
 
                    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.Connection = conn;
                    command.CommandText = "SELECT CustomerName FROM spCustomersandContacts WHERE CustomerCounter = " + form.Counter + "";
 
                    //create one data adapter which will execute the command and fill the data into data set
                    conn.Open();
                    System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();
                    SqlDataReader reader = command.ExecuteReader();
 
                    reader.Close();
                    recordAdp.SelectCommand = command;
                    System.Data.DataSet recordSet = new System.Data.DataSet();
                    recordAdp.Fill(recordSet);
                    command.Dispose();
                    conn.Close();
 
                    this.txtCustomerName.DataBindings.Add("Text", rIS_ClientDataSet, "spCustomersandContacts.CustomerName");
                }
            }

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wilko100Author Commented:
It tried that example in the link but still not binding th txt box...


this.txtCustomerName.DataBindings.Add("Text",spCustomersandContactsBindingSource, "CustomerName");

0
 
RiteshShahCommented:
can you please provide me with your .ASPX code? you have already provided .CS code but I would like to see .ASPX also
0
 
wilko100Author Commented:
ASPX?
0
 
wilko100Author Commented:
Call me dumb but this is C# not ASP, and i still cant get this txt box to bind, anyone esle have a suggestion?
0
 
wilko100Author Commented:
im know getting this...

ERROR Child list for field spCustomersandContacts cannot be created.
after this...
this.txtCustomerName.DataBindings.Add("Text", conn, "spCustomersandContacts.CustomerName");

any ideas?
0
 
wilko100Author Commented:
Ok think i sorted it

txtCustomerName.Text = cmd.ExecuteScalar() + "";

gets me back the CustomerName which is initialally what i wanted to do HOWEVER...
What if i want to return a record, i read that executescalar returns  a single column (I.E. Customername) but what if i want to fill other txt boxes such as address and telephone number for that customer? Can anyone help?
0
 
wilko100Author Commented:
Done,

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();
                         
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            txtCustomerName.Text = (String)reader["CustomerName"];
                            txtCustomerAddress.Text = (String)reader["Address"];
                            txtPostCode.Text = (String)reader["postCode"];
                            txtCustomerTel.Text = (String)reader["MainTelephone"];
                           
                        }
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now