|
in Access SQL you have to set opening brackets after the from and closing after each join, when you have more than one join in the select.
you can also set the access mdb to ANSI 92 syntax (SQL Server) in extras/options/tables,querys then you dont have to set the INNER or the brackets.
Thomas
SELECT USR.USER_FULLNAME, CMP.C_COMPANYNAME, CMP.C_COUNTRY, CLRS.*,ORD.*
FROM (((ORDERS ORD
INNER JOIN COMPANIES CMP ON ORD.O_CUSTOMERID = CMP.C_COMPANYID)
INNER JOIN COLORS CLRS ON CLRS.C.ORDERID = O.O_ORDERID)
INNER JOIN USERS USR ON USR.USER_ID = ORD.O_WHOGET)
WHERE 1=1 ORDER BY ORD.O_ORDERDATE
thomas
|
|
|
|
|
thank u thomas for replaying,
i solved this problem, as writing a query (access stored procedure)
|
|
|
|
|
I have posted this problem a few times now in other areas. I have received some repsoinses but all suggestions fail. I have placed many statments in the code to verify that the data is being read. IT is. The only issue is the dataBind statement. I'm getting very frustrated!
I have many examples of how to bind a dropdownlist but NONE match the code that is generated in VS .NET. Since I am new to .NET and web programming I am having difficulty following the gerneated code. I am convinced my problem is in the way I am binding the data. The eror I get is
"Index 0 is not non-negative and below total rows count."
and it ocurs in the following line
DropDownList1.DataBind();
Here is some of the code. If you are intereseted in helping me I can post all of it.
<br />
private void Page_Load(object sender, System.EventArgs e)<br />
{<br />
if (!Page.IsPostBack) <br />
{<br />
BindListToDropDown();<br />
}<br />
}<br />
<br />
#region Web Form Designer generated code<br />
override protected void OnInit(EventArgs e)<br />
{<br />
InitializeComponent();<br />
base.OnInit(e);<br />
}<br />
<br />
private void InitializeComponent()<br />
{ <br />
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();<br />
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();<br />
this.dbListOfAirports1 = new dbListOfAirports();<br />
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();<br />
((System.ComponentModel.ISupportInitialize)(this.dbListOfAirports1)).BeginInit();<br />
this.DropDownList1.DataBinding += new System.EventHandler(this.Page_Load);<br />
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;<br />
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {<br />
new System.Data.Common.DataTableMapping("Table", "tblAirport", new System.Data.Common.DataColumnMapping[] {<br />
new System.Data.Common.DataColumnMapping("AirportCode", "AirportCode"),<br />
new System.Data.Common.DataColumnMapping("AirportID", "AirportID"),<br />
new System.Data.Common.DataColumnMapping("AirportName", "AirportName"),<br />
new System.Data.Common.DataColumnMapping("City", "City"),<br />
new System.Data.Common.DataColumnMapping("Latitude", "Latitude"),<br />
new System.Data.Common.DataColumnMapping("Longitude", "Longitude"),<br />
new System.Data.Common.DataColumnMapping("State", "State")})});<br />
this.oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Data Source=""D:\MGDevelopment\SIMDAC Viewer\DB\simdac_be.mdb"";Jet OLEDB:Engine Type=5rovider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";<br />
this.dbListOfAirports1.DataSetName = "dbListOfAirports"; <br />
this.dbListOfAirports1.Locale = new System.Globalization.CultureInfo("en-US");<br />
this.oleDbSelectCommand1.CommandText = "SELECT AirportCode, AirportID, AirportName, City, Latitude, Longitude, State FROM" +<br />
" tblAirport";<br />
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;<br />
((System.ComponentModel.ISupportInitialize)(this.dbListOfAirports1)).EndInit();<br />
<br />
}<br />
#endregion<br />
<br />
private void BindListToDropDown()<br />
{<br />
try <br />
{<br />
oleDbConnection1.Open();<br />
OleDbDataReader OleDbReader = this.oleDbSelectCommand1.ExecuteReader();<br />
<br />
oleDbConnection1.Close();<br />
DropDownList1.DataSource=OleDbReader;<br />
DropDownList1.DataTextField = "AirportName";<br />
DropDownList1.DataValueField="AirportID";<br />
DropDownList1.DataBind();<br />
<br />
}<br />
catch(OleDbException ex) <br />
{<br />
|
|
|
|
|
Just a shot in the dark as I'm a sleft-taught hobbiest, but I would write your try catch like this:
private void BindListToDropDown()
{
try
{
oleDbConnection1.Open();
OleDbDataReader OleDbReader = this.oleDbSelectCommand1.ExecuteReader();
DropDownList1.DataSource=OleDbReader;
DropDownList1.DataTextField = "AirportName";
DropDownList1.DataValueField="AirportID";
DropDownList1.DataBind();
oleDbConnection1.Close();
}
catch(OleDbException ex)
{
Notice I don't close the connection until after the DataBind.
Let me know if that works.
Jerry
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
|
|
|
|
|
Didn't work.
Thanks for the effort! i think it has something to do with the reader. Most examples I find do not use the reader.
Brian
|
|
|
|
|
Ok, it ws a shot in the dar.
Does the error point to either one of these lines of code?
DropDownList1.DataTextField = "AirportName";
DropDownList1.DataValueField="AirportID";
Jerry
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
|
|
|
|
|
if(IsPostBack)<br />
{<br />
string SQL="";<br />
switch(ddlLibrarySelection.SelectedItem.Text)<br />
{<br />
case"The Whole Library":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"ASP.NET":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"C#":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"C++":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"DirectX":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"Game Programming":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"SQL":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
case"Visual Basic":<br />
SQL=ddlLibrarySelection.SelectedValue.ToString();<br />
break;<br />
}<br />
string DbPath = Server.MapPath("bin/LibBooksProg.mdb"); <br />
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"+ <br />
@"Data Source="+DbPath+";"; <br />
OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr); <br />
OleDbCommand Commandobj = new OleDbCommand( SQL,MyOleDbConn); <br />
MyOleDbConn.Open(); <br />
dgTestIt.DataSource= Commandobj.ExecuteReader();<br />
dgTestIt.DataBind(); <br />
MyOleDbConn.Close(); <br />
}<br />
}
Jerry
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
|
|
|
|
|
I am surprised that the previous code didn't work. I tried it using the pubs database and it worked fine. Make sure you close the database after calling DataBind. I tried closing before and got an error.
Following is my code:
<br />
private void BindListToDropDown()<br />
{<br />
string query = "select * from Authors";<br />
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();<br />
SqlDataReader dr = cmd.ExecuteReader();<br />
this.DropDownList1.DataSource = dr;<br />
this.DropDownList1.DataTextField = "au_lname";<br />
this.DropDownList1.DataValueField = "au_id";<br />
this.DropDownList1.DataBind();<br />
conn.Close();<br />
}<br />
|
|
|
|
|
If you want to bind the data to the dropdownlist you will need to use the dataset fill function.
Page_Load
sqlDataAdapter1.Fill(dataSet11);
DropDownList1.DataBind();
that's it!
Hope it helps...
Illegal Operation
WannaBe and GonnaBe Systems Developer
|
|
|
|
|
Tried this and got the message
An invalid data source is being used for DropDownList1. A valid data source must implement either IListSource or IEnumerable.
Thanks for the effort!
Brian
|
|
|
|
|
Hello,
I have a problem with Interbase/Firebird database. I need to monitor the changes in database in specific select. Therefore I have created a view and a trigger after_update on that view. But if there´s an update made directly on the table which is included in the view, an event is not raised. It is raised only when I make the update on the view. This behavior seems to me strange.
I thank you for ideas.
|
|
|
|
|
Hey dats not a great problem. Its the property of a view. If u create a trigger in table dat will perfectly work for a view. But in case of creating a trigger for a view it will not work on a table as only changes done on view can have an impact on the table but not for the triggers raised.
|
|
|
|
|
Thanks for your reply, but I think it doesn't solve my problem. I want to database raises the event only if some of the columns included in my view are updated.
For example: I have got two tables in database. T1 and T2. T1 consists of columns C11 and C12, T2 constists of columns C21,C22,C23,C24.
The view is:
Select C11, C22, C23 from T1 one, T2 two where one.C12=two.C24
I want to create trigger, which is raised when some of C11,C22 or C23 are updated.
Of course I can create a trigger on both tables, on event notification refresh view and check if it changes, but this the way I'd like to avoid because of much code writing and much data downloading.
|
|
|
|
|
Hello all,
If I have 2 tables, Inv1, col is "QTY" and Inv2, col is "InStock".
Can I take add qty and instock to gether, and then put the result in INSTOCK. Kinda of like an UPDATE.
Thanks!
|
|
|
|
|
You mean this:
update table2 set inStock=Instock+(select qty from table1 where table1.ID=table2.ID)
You will need to change the where clause.
<italic>Work hard, Work effectively.
|
|
|
|
|
In MSSQL has anyone got a way of returning, for example, 10 records, starting at the 20th record (or, more likely 25 records starting at the 25025th record)? I know about the TOP clause but, short of using a dreaded NOT IN... sub query which is horrendously slow, I really am at a loss.
Thanks
Drew.
|
|
|
|
|
|
Thanks Colin - that worked just fine.
Cheers,
Drew.
|
|
|
|
|
Hi, I have implemented the code below used for accepting parameters to search on a table. Rather than use the standard dynamic sp approach, I decided to use this approach. What I want to know, is if this way would prove slower as I am evaluating Nulls using IsNull, compared to doing the same with Dynamic SQL:
use Northwind
CREATE PROCEDURE SearchProducts (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE (IsNull(@ProductName, ProductName) = ProductName) AND
(IsNull(@UnitPrice, UnitPrice) = UnitPrice) AND
(IsNull(@UnitsInStock, UnitsInStock) = UnitsInStock)
GO
|
|
|
|
|
A quick bit of examining the execution plan reveals that for this procedure, when called with a product name but not the other fields, the query optimiser chooses to use a clustered index scan - basically just reading the table from start to end. This is typically the least efficient operation in SQL Server's armoury, although it may get used in preference to an index if the index isn't very selective (if a lot of results are found in the index, the cost of the bookmark lookups, which may well be out of order with respect to the table, may outweigh the cost of just reading the table).
When performing a SELECT with only ProductName in the WHERE clause, it performs an index seek on the ProductName index then a bookmark lookup to return the whole row.
I rewrote your query to a pattern I've used before:
CREATE PROCEDURE SearchProducts2 (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE
(ProductName = @ProductName OR @ProductName IS NULL) AND
(UnitPrice = @UnitPrice OR @UnitPrice IS NULL) AND
(UnitsInStock = @UnitsInStock OR @UnitsInStock IS NULL)
GO This had the same effect as yours did, which did surprise me a little.
SQL Server will compile a query plan the first time using the arguments supplied at that time, and will normally cache the plan and reuse it next time. If the arguments that are used will be wildly divergent - lots of values identical in the table but with a few exceptions - you might find that the cached plan isn't good for some values but is for others. Using the WITH RECOMPILE option when creating the procedure forces SQL Server not to cache the plan. Alternatively you can specify WITH RECOMPILE with the EXEC statement to force a recompile at that time.
So I think dynamic SQL is preferable. You can still use parameters with a query string - and you should, to simplify your code (no need to escape special characters) and to avoid the possibility of SQL injection. You simply use a variable name (e.g. @ProductName) in the query text and add a parameter to the command object, the same as you would if you were calling a stored procedure.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have a treeview control. I was able to generate the tree nodes for this treeview control by reading the structure of the tree from a database table. At each treenode, I show a check box (CheckBoxes is true). On the Sql Server Side, I have a table which has the following structure:
TreeStruct (NodeID int, NodeDesc varchar(50), NodeState tinyint)
This table is pre populated with information in the order in which the tree will be constructed.
I want to be able to do the following:
(a) whenever the CheckState property changes (i.e., a user Checks/Unchecks a node, I want to commit the change to the database
(b) Let the user make as many changes as possible to the Checkboxes, which will change the CheckState property of the nodes. When the user is done, he/she can press the save button, and all these changes will be written to the database.
I am not sure how to bind the data I read from the database to the TreeNode(s) so that I can make the above two things happen.
Help appreciated.
|
|
|
|
|
Has anyone had any problems installing the latest download install for SQLExpress?
I've tried several times installing it on my machine; it churns along perfectly fine until towards the end of the install while 'verifying user accounts' then it tells me I don't have admin rights. But I do!!. Full admin rights.
This is the message that it splits at me:
TITLE: Microsoft SQL Server 2005 CTP Setup
----------------------------------------
Please use an admin account when installing on XP SP1 Systems.
Setup cannot proceed with non-admin accounts on XP SP1 systems.
Refer to the product documentation for more information.
Any ideas ?
I Dream of Absolute Zero
|
|
|
|
|
I'm curious: Why don't you have SP2 installed?
Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
|
|
|
|
|
XP SP2 has not been issued as part of the company standard build as yet.
I guess I could go against their back and install it on my machine anyway. 
I Dream of Absolute Zero
|
|
|
|
|
hi at all,
i've create in the past some application with c# that used access database.
Anyway I can see in the web that much people works with mysql sintax and mysql connections inside their applications.
So i've thought to use this technology, but i think it is different than access database.
Does the user need mysql demon installed on his pc to use my application mysql-based?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
