|
Hi, all:
I am totally stuck, please help. I have a datagrid on the WinForm ( using C# ). I am trying to create a combobox for one of the columns. I use the technique described in MS KB Article 323167 ( coded in VB.NET) and I port the code to C#. It works fine for the existing rows. Whe I click on the * row ( i.e., try to create a new row) on the datagrid. The combobox seems there, but when I click on the down arrow, the combobox does not dropdown. Occasionally, I can get the dropdown, but when I leave the cell, the changed value goes to the previous row ( strange!). The primary key is AutoNumber, when I click on the new row ( with "*" in front of it), I sometime get little pencil in front of the row, but sometimes I do not get it except the autonumber ( almost like it does not treat as a new row ).
Has anyone use this combobox technique and work on the new row successfully? I include the code snippet ( handling the events) here, if you can spot anything I did wrong, please let me know.
Any help is highly appreciated. Thanks in advance.
Dion
************ Code Starts here ***********
private void dataGrid1_Paint(object sender, System.Windows.Forms.PaintEventArgs e)<br />
{<br />
if ( dataGrid1.CurrentCell.ColumnNumber == 1 )<br />
{<br />
cbType.Width = dataGrid1.GetCurrentCellBounds().Width;<br />
}<br />
}<br />
<br />
private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)<br />
{<br />
if ( dataGrid1.CurrentCell.ColumnNumber == 1 )<br />
{<br />
cbType.Visible = false;<br />
cbType.Width = 0;<br />
cbType.Left = dataGrid1.GetCurrentCellBounds().Left;<br />
cbType.Top = dataGrid1.GetCurrentCellBounds().Top;<br />
cbType.Text = dataGrid1[dataGrid1.CurrentCell].ToString() + "";<br />
cbType.Visible = true;<br />
}<br />
else<br />
{<br />
cbType.Visible = false;<br />
cbType.Width = 0;<br />
} <br />
}<br />
<br />
private void dataGrid1_Scroll(object sender, System.EventArgs e)<br />
{<br />
cbType.Visible = false;<br />
cbType.Width = 0;<br />
}<br />
<br />
<br />
private void dataGrid1_Click(object sender, System.EventArgs e)<br />
{<br />
cbType.Visible = false;<br />
cbType.Width = 0;<br />
}<br />
<br />
<br />
private void cbType_TextChanged(object sender, System.EventArgs e)<br />
{<br />
if ( dataGrid1.CurrentCell.ColumnNumber == 1 )<br />
{<br />
cbType.Visible = false;<br />
<br />
if ( ( dataGrid1[dataGrid1.CurrentCell] + "") == "" )<br />
{<br />
SendKeys.Send("*");<br />
}<br />
<br />
dataGrid1[dataGrid1.CurrentCell] = cbType.Text;<br />
} <br />
}
|
|
|
|
|
i tried to add records to my database table but cant due to foreign key problem.. how do i solve tat?
i tried deleting all the relationships and i was able to add successfully but not when i add in the relationships.
2 tables involved:
tblcompany
- accountnumber (primary key)
- companyname
- address
tblcustomer
- customerid (primary key, auto generated)
- companyname [foreign key]
- username
- password
- name
- contactnumber
are these tables linked correctly? can someone help? thanks in advance
Laine
|
|
|
|
|
Using string fields as keys is most of the time A Bad Idea.
Its not really clear from your table synopsis which is the "parent" and which is the "child" in your data logic. So i'll ignore what you've done and use a fictional example.
tblCompanies
------------
CompanyId (primary key, autonumber)
CompanyName
CompanyAddress
Company PhoneNumber
tblEmployees
------------
EmployeeId (primary key, autonumber)
CompanyId (foreign key)
EmployeeName
EmployeeInsideLegMeasurement
I hope that example makes it more clear.
#include <beer.h>
|
|
|
|
|
thanks for ur reply jon.. the company table is the parent and the customer/employee table is the child.
the company data muz exist before the employee can sign up to be a member..
the data field for the companyID is alphanumeric.. and employees have problem remembering their company account number.. that's why i choose that they enter their company name instead.. this is to identify which employee from which company logs on.
any idea on how to go about designing a better database than the one i did earlier? any comments, suggestions will be deeply appreciated..
Laine
|
|
|
|
|
If you wanna make so that the employees enter alphanumeric strings, why don't you make a column with that and an ID column for key?
|
|
|
|
|
First of all:
1. What DB do you use? Oracle, SQL-Server, Access, DB2?
2. Post your error message (or error code)
3. Post your table-definitons
4. Post the INSERTs you tried.
Some things you should check:
1. Most DBs are case sensitive. So 'comp1' != 'Comp1' => master-key won't be found
2. Even if it sounds silly -> Check for typing errors!
3. Be sure to create a company before creating a customer for it.
4. You can only delete a record from tblcompany if no record of tblcustomer is referencing to it.
The included example should work (assuming accountnumber, customerid and contactnumber to be NUMBERS, the others VARCHAR)
INSERT INTO tblcompany VALUES(1, 'comp1', 'someaddress');<br />
INSERT INTO tblcompany VALUES(2, 'comp2', 'someaddress');<br />
<br />
INSERT INTO tblcustomer (companyname, username, password, name, contactnumber) VALUES ('comp1', 'uname', '***', 'name', 1);
--
karl
|
|
|
|
|
i am using sql server2000. when i try to add a record to the database, the following error occurred:
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblCustomer_tblCompany'. The conflict occurred in database 'myDatabase', table 'tblCompany', column 'accountnumber'. The statement has been terminated."
i am sure that all the spellings are correct and there are no typing mistakes anywhere.
the tables involved are:
tblcompany
- accountnumber (primary key) : varchar
- companyname : varchar
- address : varchar
tblcustomer
- customerid (primary key, auto generated) : int
- companyname [foreign key] : varchar
- username : varchar
- password : varchar
- name : varchar
- contactnumber : int
the parent would be tblcompany whereas the customer table as the child.
i am using a stored procedure:
ALTER Procedure AddCustomer
(
@CompanyName varchar(50),
@Username varchar(50),
@Password varchar(50),
@Name varchar(50),
@PhoneNo varchar(50)
)
AS
INSERT INTO tblCustomer
(CompanyName, Username, Password, Name, PhoneNo)
VALUES
(@CompanyName, @Username, @Password, @Name, @PhoneNo)
RETURN
this is how i call the stored procedure:
Public Sub processRegistration(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim CompanyName As String = txtCompany.Text
Dim Username As String = txtUsername.Text
Dim Password As String = txtPassword.Text
Dim Name As String = txtName.Text
Dim PhoneNo As String = txtContact.Text
Dim ocm As SqlClient.SqlCommand
ocm = New SqlClient.SqlCommand("AddCustomer", SqlConnection1)
ocm.CommandType = CommandType.StoredProcedure
Dim pcompanyname As SqlClient.SqlParameter
pcompanyname = New SqlClient.SqlParameter("@CompanyName", SqlDbType.VarChar, 50)
pcompanyname.Value = CompanyName
ocm.Parameters.Add(pcompanyname)
Dim pusername As SqlClient.SqlParameter
pusername = New SqlClient.SqlParameter("@Username", SqlDbType.VarChar, 50)
pusername.Value = Username
ocm.Parameters.Add(pusername)
.
.
SqlConnection1.Open()
ocm.ExecuteNonQuery()
SqlConnection1.Close()
End Sub
i have the company data in the tblcompany, but i juz couldnt add a customer to the customer table.
can anyone tell me wat is wrong?
Laine
|
|
|
|
|
ylaine wrote:
tblcompany
- accountnumber (primary key) : varchar
- companyname : varchar
- address : varchar
tblcustomer
- customerid (primary key, auto generated) : int
- companyname [foreign key] : varchar
- username : varchar
- password : varchar
- name : varchar
- contactnumber : int
The foreign key in tblcustomer references to the PRIMARY KEY of tblcompany. So it references to accountnumber and not to companyname. If you want to set a foreign key to companyname you must define it as UNIQUE in tblcompany and change the fk-constraint to point to companyname.
--
karl
|
|
|
|
|
ok.. got it.. thank you very much karl!;)
Laine
|
|
|
|
|
Hiya I am just wondering if anyone has come across this problem.
I have an SQL database which I read and write to. I write a number of items( Number, Planet Name )the first time to the database fine. The items go in fine and in order into their columns:
e.g 1. Jupitar 2. Venus 3. Mars 4. Earth
Then when I modify the table in some way, e.g like deleting all the items, and refill the table with the exact same code, they come out in a different order e.g
3. Mars 1. Jupitar 4. Earth 2. Venus
And I definitely have not changed the code and they are all definitely deleted. WEIRD!! I have also done this with ACCESS database and works fine every time, only wrong in SQL.
Does anyone have any ideas on this??
grahamoj.
|
|
|
|
|
don't cross post. waste of bandwidth.
#include <beer.h>
|
|
|
|
|
I think that you're just stuck with the way it returns them to you unless you tell it *how* to return the records to you. ORDER BY is your friend. I'm assuming that your "Number" field is your identity field. Just order by that. Your query would then be:
SELECT Number, PlanetName FROM Planets ORDER BY Number
As long as you always add them in, in the same order, you should get them back in the same order when using the ORDER BY Number clause.
Hope this helps.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
I can get a .doc inserted as a blob into the image type, but how do I get it out again and display it on a web page using C# ASP.net?
|
|
|
|
|
|
I set up ABCUpload and got an image successfully saved to my database.
When you go into the database the image is stored as a long binary data.
When you click on it to open it , there is an error
there was a problem while Access was communicating with the OLE server or ActiveX Control. Close the OLE server and restart it outside of MS Access. Then try the original operation in Access again
How can I view the image- I need it to be stored in the database and not linked to it
Please help!!
Maria
|
|
|
|
|
I would like to have a transaction which would lock a particular row in the table but leave the other available to other threads.
I tried:
set transaction isolation level serializable
....
select * from sometable with (rowlock, xlock) where ....
....
commit
I tried with 2 instances of query analyzer debugging and I realized that the lock was table-wide.
How do you make the lock row-wise.
tx
|
|
|
|
|
The column in the WHERE needed an index.
|
|
|
|
|
I am still kinda new at the database structuring stuff and I was looking for some more opinions.
Goal: to design an online inventory system, such that if I was to look up employee x I would see that he has computer Z containing video adapter y. Additionally I need to be able to look up employee x and see computer z contains windows 95. I would be nice to be able to limit the amount of information that I present to the web user, so that when he looks up employee x he sees just the basicis computer number, processor type, location, and OS type.
My thoughts:
<OL><br />
<Li>Have a list of employee that can have computer parts assigned to them.<br />
<li>Have a list of Objects that can be checked out.<br />
<li>Have a relationship between them so that one employee can have many Objects checked out.<br />
<li>Have a relationship between objects and itself so that one object can contain many other objects.<br />
</ol>
Here is where it becomes challenging, if two objects have a different set of properties(Like printers and mice) this schema would break down.
Question One Would it make sense to add these schemea chunks:
<OL><LI>A list of Properties that an object can obtain ( such as "DPI" or "mouseButtons")<LI>A relationship between Many properties and one object</OL>
This format would allow me to easily "drill down" but getting all of the information at once would be difficult...
Question Two Do you feel my Paradigm is right or wrong. How do you think I should reapproch this problem.
Question Three How would you approch this problem.
|
|
|
|
|
It sounds to me like your thoughts are on the right track. I think that you would probably have to create a separate table for each asset type (e.g. computer chasis's, keyboards, mice, video cards, etc.). Then you can set up other tables to associate one asset with another (e.g. video card x is in computer y, etc.) Then you would finally set up a table that would assocate people (employees) to assets. Then you would have all of your data available. What you display to the user is up to you. Just because you have lots of data in your DB doesn't mean you have to show it all. With a fairly simple join, you could bring back a list of pertinent things that you want the user to see rather than everything.
I think the reason that you're not getting more responses on this question is because it is quite a daunting task to design such a system if you are to do it properly. It sounds like it would present a fun challenge, but It certainly isn't simple. I think you're on the right track, though.
Good luck.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
If I use the OleDB provider i can access all sorts of automatic code generating bits in Visual Studio (c#). But if I use the ODBC provider I seem to have to hand code everything .Am I missing out on something ? is there some button I should have pushed , or are you on your own with the ODBC provider ? Even the OleDb provider for ODBC is not as good as other OleDb providers . Is it any better in the new Beta ?
Am I the only one forever playing catch up with technology , while all the juicy opportunites keep rolling by ?
|
|
|
|
|
Hi
I have an access database with two tables.
First table is called custDetails with fields:
CustID and CustName
Second table is called UsageHistory with fields:
CustID, UsagePeriod, DlyAmount, MnthlyAmount, Total
How do i show the whole of UsageHistory table and just the custName field from CustDetails table on a datagrid using a dataview?
I know how to create a connection to the database and execute the SQL statement at the start, and how to bind the datagrid to the dataview at the end. It just seems to be the middle bits that I'm unsure of that's stuffing me up ie - datasets, dataadapters and datatables. I can get my form to display the table usageHistory and all its fields on the datagrid. But when i try to add in the field custName to the datagrid that's when i have problems.
Anyhelp or any new block of code would be greatly appreciated.
Dim ds As DataSet = New DataSet()
Dim dv As New DataView()
Dim da As OleDbDataAdapter
Dim strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ClientInfo.mdb; Persist
Security Info=False"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
'Open a database connection.
cn.Open()
Dim strSql As String
strSql = "SELECT UsageHistory.*, CustDetails.CustName " & _
"FROM CustDetails INNER JOIN UsageHistory ON CustDetails.CustID " & _
"= UsageHistory.CustID;"
da = New OleDbDataAdapter(strSql, cn)
************This is the part that i'm unsure of*****
da.TableMappings.Add(?????)
' Fill the DataSet.
ds = New DataSet()
da.Fill(ds)
Dim dt As DataTable = ds.Tables.Add(???????)
*****************************************************
cn.Close()
'Make a DataView for the data.
dv = New DataView(dt)
'Bind the DataGrid control to the DataView.
dgUsage.DataSource = dv
|
|
|
|
|
I think you're trying to mix the way the data is displayed with what you are retrieving from the database. All you really need to do is set up the columns properly in your data grid and then simply bind the DataTable to the DataGrid as its data source. You don't need a DataView in this case.
On your DataGrid in the aspx page, set the AutoGenerateColumns property to false and then specify the columns you want to display in the Columns property. Only supply the columns that you want. So let's say that these are the columns your join returned:
CustID, CustName, UsagePeriod, DlyAmount, MnthlyAmount, Total
,but all you want to show are these:
CustName, UsagePeriod, DlyAmount, MnthlyAmount, Total
Just specify these columns in the Columns property in the properties panel (there's a button that opens a dialog where you add the columns).
Then your code would just look like this (this is C# code, but I think you get the idea):
DataSet dataSet = new DataSet();
dataAdapter.Fill( dataSet,"TableName");
DataTable dataTable = dataSet.Tables[0];
dgUsage.DataSource = dataTable;
dgUsage.DataBind();
In this case the entire data set that you got from the database is still avialable. You are just diplaying the pertinent columns as specified in your Columns property.
You may have heard of separating your presentation from your data before, well this is an example of how to do so. Your DataSet, DataTable, etc. should never be aware or care about how you are displaying something. Just tell your DataGrid what to do with the data it has been handed by specifying the columns to use.
A DataView is more for filtering records (rows), not columns. If I understand you correctly, you want to display all but one of the columns from the join. Is that correct?
Hope this makes sense. Let me know if you need clarification. If I've missed something, let me know.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi Matt
Thank you very much for your response. I am new to programming (three months) so any help i can get is always appreciated. I am building a windows application and could not find the autogeneratecolumns or the columns property in the properies panel which you mentioned. I was however able to implement your code and my datagrid is now showing the information that i wanted which is a big improvement
Regarding the dataview, the reason why i wanted this is so that i can allow users to filter the data either by Customer Name or usage period. I will try to get the dataview working, failing that, its just a case of generating a new Sql statement to give me the filtered information that i want.
I also noticed that the amounts and totals are displaying with more than two decimal points and showing some null field in the datagrid. I would like to do some cosmetic touch ups so that the amounts only show two decimal places. Do you know if the formatting should be done on the datagrid level or is it the datatable where the field formatting is done?
Thanks once again for your earlier response.
|
|
|
|
|
I didn't realize you were building a windows application. I thought you were building an ASP .NET app, so I can see how some of the information I provided could be confusing. I really haven't done a whole lot of windows app .NET programming yet. Sorry for misleading you.
As far as data formatting goes, I would do it in either the SQL statement or at the DataGrid. It just depends on what you are doing with the data. If all you want is to get your decimal places right, I would just do it at the datagrid, however, this requires that you use a Data Formatting Expression in the grid. This is something that gets applied to each item in a particular column. Just do a search for that phrase on google or google groups. These get set up in the columns dialog on the columns parameter (as least for ASP .NET programming).
Good luck and I'm glad it was at least a little bit helpful.
-Matt
p.s. It looks like you're doing *very* well for having only been programming for three months. I know you're not using C#, but I would recommend that you check out the "Programming C#" book from oreilly. It is very good and worthwhile. I read it pretty much cover to cover. It's the first technical book I've ever done that with. Most of the code you can port to VB .NET with some minor changes. I prefer the syntax of C# myself, but to each his own.
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hey no problems. Beleive it or not you helped me with the most crucial part which was showing the joined tables in a datagrid. I spent all day the other day trying all these complex methods retrieved from my google searches when you were right all along in saying that i was trying to mix the way the data is displayed from what was retrieved.
Your 5 lines of code has saved me from what would have surely been a day of frustration and hair tearing.
With the 5 lines of code I was able to make good progress on the GUI today with the datagrid. Tommorrow i will get on to formatting the fields. There are some time fields as well which i didn't mentioned that have appeared in an erroneous date format and i'll need to reformat them back to display time.
And thanks for the suggestion on the book Its been a steep learning curve the last three months and i'm always on the look out on how i can learn the basics and the foundation knowledge. I have the book "How to program in VB.NET" by Dietel and Dietel. I tried to cram the whole book from cover to cover but could only get to chapter 8. Its a pretty huge and detailed book and really tough going as you get further into the book. But it was enough to give me my jump start.
Once again thank you for taking the time to respond to my question which is much appreciated.
|
|
|
|
|