Combining 2 sql statements using Ole and not Sql
-
Hi, I have 2 sql statements which will run in Access: private string m_cmdInitial = "Select Distinct Mid([Attribute Name],1,1) AS Initial FROM [Attributes]"; private string m_cmdCustmrs = "SELECT * FROM [Attributes] WHERE(Attributes.[Attribute Name]) LIKE '{0}*'"; The thing is that using SQL server the two sql statements can be combined using the format Function of ASP.net just by putting semicolon between the statements. The purpose is as follows: I am using 2 data lists. The first one is just a list of letters on top from which the user can choose a letter on which the term starts. The second data list shows the results(Customers datalist). So the first sql statement is an argument for the second sql(the Like '{0}*' statement. Does anyone know how to do this using access version and not sql server? Thank you. Here is a code where the sql statements will be used: private void BindData(string initial) { OleDbConnection conPubs = new OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("DB//DataDictionary.mdb")) ; // Store the initial ViewState["Initial"] = initial; // string _cmdText = m_cmdInitial; // string _cmdTextSecond = m_cmdCustmrs; string _cmdText = m_cmdInitial +";"; _cmdText += String.Format(m_cmdCustmrs, initial); // _cmdText += ";"; OleDbDataAdapter _adapter = new OleDbDataAdapter(_cmdText, conPubs); DataSet _data = new DataSet(); _adapter.Fill(_data,"Attributes"); // Rename tables _data.Tables[0].TableName = "Initials"; // _data.Tables[1].TableName = "Attributes"; // Reset selected and edit items Customers.SelectedIndex = -1; Customers.EditItemIndex = -1; Picker.DataSource = _data.Tables["Initials"].DefaultView; Picker.DataBind(); Customers.DataSource = _data.Tables["Initials"].DefaultView; Customers.DataBind(); conPubs.Close(); }
-
Hi, I have 2 sql statements which will run in Access: private string m_cmdInitial = "Select Distinct Mid([Attribute Name],1,1) AS Initial FROM [Attributes]"; private string m_cmdCustmrs = "SELECT * FROM [Attributes] WHERE(Attributes.[Attribute Name]) LIKE '{0}*'"; The thing is that using SQL server the two sql statements can be combined using the format Function of ASP.net just by putting semicolon between the statements. The purpose is as follows: I am using 2 data lists. The first one is just a list of letters on top from which the user can choose a letter on which the term starts. The second data list shows the results(Customers datalist). So the first sql statement is an argument for the second sql(the Like '{0}*' statement. Does anyone know how to do this using access version and not sql server? Thank you. Here is a code where the sql statements will be used: private void BindData(string initial) { OleDbConnection conPubs = new OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("DB//DataDictionary.mdb")) ; // Store the initial ViewState["Initial"] = initial; // string _cmdText = m_cmdInitial; // string _cmdTextSecond = m_cmdCustmrs; string _cmdText = m_cmdInitial +";"; _cmdText += String.Format(m_cmdCustmrs, initial); // _cmdText += ";"; OleDbDataAdapter _adapter = new OleDbDataAdapter(_cmdText, conPubs); DataSet _data = new DataSet(); _adapter.Fill(_data,"Attributes"); // Rename tables _data.Tables[0].TableName = "Initials"; // _data.Tables[1].TableName = "Attributes"; // Reset selected and edit items Customers.SelectedIndex = -1; Customers.EditItemIndex = -1; Picker.DataSource = _data.Tables["Initials"].DefaultView; Picker.DataBind(); Customers.DataSource = _data.Tables["Initials"].DefaultView; Customers.DataBind(); conPubs.Close(); }
First of all, use parameterized queries rather than string concatenation. Second, why are you trying to execute these at the same time? ~Javier Lozano
-
First of all, use parameterized queries rather than string concatenation. Second, why are you trying to execute these at the same time? ~Javier Lozano