|
Hi all,
I'm writting importing data from Excel module. But there is a bug I can't fix.
Example: I have Student Name field (I want type of this field is Text) in Excel file, the value of first row is "John", the value of second row is "901". Then I read data from this file by using ADO.NET, the Student Name column in DataTable I receiving has double type. And the value "John" of first row is lost, it becomes DBNULL.
If I input value "'901" for second row, it will run well. But I dont want to use this way. Is there any way to solve this problem, fixing the type of field in Excel?
Note: I had fix type of field in Excel by using Format Cells, but it didn't work.
Please help me!!!!!
Thanks,
Thai Nam
|
|
|
|
|
You might be helped by knowing that there are some options you can apply in the connection string for your ADO.NET connection. The site http://www.connectionstrings.com is a great reference - here's what it posts for an Excel connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
I'm thinking in your case you want the IMEX=1 extended property... maybe the HDR=No property too?
|
|
|
|
|
i have a table like this
|Name|Number|
Sasuko 09
Nichol 07
Joy 07
Michael 09
Simon 09
i try with the following query to get the value integer "2" that is the count of all the row have distinct value in this case 09 and 07:
SELECT DISTINCT TableInformation.Kind
FROM TableInformation;
but i get this:
|Temp|
09
07
if i do Select Count('DISTINCT Number') FROM TableInfo i get 5 that is the number of all the rows
then if i do Select Count(DISTINCT Number) FROM TableInfo i can't run couse sintax error!
What is the correct query?
|
|
|
|
|
Already answered in your first thread in the C# Forum...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
is it possible to store a word document in an SQL table? If so what type of field would that be and how would I do that?
Thanks
Tom
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
|
Mike, Do you know if I would be able to open the document back up is the person wanted to edit it? I'm just storing it as a binary right? So if I pull it back out as a binary and save it as a temp for editing, that should work right?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Hi Tom - yup, that's the rub. You would want your code that accesses the binary in your app to also save it to the user's machine (temp file sounds good) for editing in Word.
|
|
|
|
|
Hai,
In my stored procedure I need to get the field value of "First Name".
So I tried
select First Name from table1
Since the space is coming between First and Name,these words is considered as key words.
So How I can query the table field 'First Name'
Thanks very much.
Pls show me the right way.
Thank You,
Rahul.P.menon
|
|
|
|
|
Hai,
I got it..
Use
select [First Name] from Table1
Thank You,
rahul.P.Menon
|
|
|
|
|
|
toxcct wrote: it is simply not a good thing to place spaces inside identifier names...
consider revising your schema.
Good point by toxcct, could not agree more 
|
|
|
|
|
HI,
Im using Visual Studio Express 2005 to develop my application Im just trying to INSERT a MYSQL query into the database.Im using Managed and unmanaged C++ The following is my code:
MySqlConnection^ Mycon = gcnew MySqlConnection("Server=localhost;database=test;UserID=admin;Password=;Port=3306");
Mycon->Open();
MySqlCommand^ cmd = gcnew MySqlCommand("SELECT * FROM Patient", Mycon);
cmd = gcnew MySqlCommand("INSERT INTO patient(PID,Title,FirstName,MiddleName,Surname) VALUES ('+@txtPID+','"+cmbTitle+"','"+txtFname+"','"+txtMname+"','"+txtSurname+"')", Mycon);
I get the following error:
Error:
An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
Additional information: Duplicate entry '0' for key 1
can someone pleassse help me!!!!
Many thanks
hbjs
makaveli
-- modified at 22:22 Wednesday 18th January, 2006
|
|
|
|
|
sorry i figured it out!
makaveli
|
|
|
|
|
Is there a way to auto-connect to specified database engines when SQL Server Management Studio is started ?
...cmk
Save the whales - collect the whole set
|
|
|
|
|
Simpley register the server under the Database Engine folder in the Registered Servers window.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hmmm, i had done that but the servers only show up in the Registered Servers window, not the Object Explorer window.
I still need to explicitly connect to the servers i want to view in the Object Explorer window.
What i want is for SQL Management Studio to start, not prompt me to connect to a server, and display a list of pre-defined servers in the Object Explorer window.
...cmk
Save the whales - collect the whole set
|
|
|
|
|
Hi guys (and girls)
I have the folowing situation, a Person table, a Company table and a Contract table.
I can make contracts with both persons and companies alike so in the Contract table must be a referece to either a person or a company. In this case there are only two tables that need to link to the Contract table, but lets suppose there can be any number.
My initial approach is to create another table let's say Entity that has a column named EntityID that is the PK. In the Person table there is a PersonID column that is a PK for the Person table and a FK from the EntityID in the Entity table. The same applies to the Company table having a CompanyID column that is the PK for the Company table and a FK from the EntityID in the Entity table. The ID columns are GUIDs.
I use the EntityID as a FK in the Contract table to link either Person or Company to a contract in a SupplierID and a ClientID column.
While this design works, there is a small problem: acidentally deleting either a person or a company can leave an orphaned EntityID with no corespondece to the Person or Company table wich will leave the contract without one of its parts.
Is there other database design appropriate for this situation that overcomes this issue using referential integrity?
-- modified at 18:18 Wednesday 18th January, 2006
Just came up with another ideea, but it involves creating in the Entity table a new column for each referenced table (in this case Person and Company). In this case PersonID and CompanyID columns will be added to the Entity table and they will be FK from the Person and the Company table, for each Entity row only one of them having a value. After that, the row's EntityID will be used in the Contract table.
This works but new columns have to be created for each referenced table.
Is there any other solution?
|
|
|
|
|
You have a couple of options.
One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table.
Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity.
Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables.
But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.
|
|
|
|
|
Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity.
Well, it is not just the Contracts tabel involved, there are also invoices and all kinds of tabels that store documents and other data that can be applyed to either a persion or a company. And in this case there are only two root tabels but one can imagine a situation that has any number. Adding columns for every one in all the related tabels is not a viable option.
The Entity tabel offers a level of abstraction and restricts modifications to a single tabel.
Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables.
Again, creating intermediary tabels for every posible case is not a viable option.
But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.
Yes, this is the ideea. Entity is the core of the sistem. It links for example the Address table with the specific Person or Company, as a person or a Company can have multiple addresses.
So far the best solution is the one I came up the second time, addind a new column in the Entity table for each new table. And not only that, the EntityID equals the value of the column that has the value set(in this case PersonID or CompanyID). That is important because on the data layer I can create a Dataset that has a relation between let's say Person and Address.
One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table.
I want to restrict as much as possible the use of triggers to enforce referential integrity.
What I was wandering if is there a way to create what would logically be a column with multiple FK's that at any time it will corespond with only one PK in the reffered tabels. In this case EntityID would either link to PersonID or CompanyID so later adding other tabels considered as "entities" would be very easy.
|
|
|
|
|
I think you're making the problem a little more complicated than it needs to be. Check out one-to-one relationships. That should enable you to enforce referential integrity between the tables. The only thing you'd need to add logic to enforce is having a record in the Person table if and only if the EntityType is "Person." And similarly for each of the other entity types. Even that isn't entirely necessary, if you'd rather put logic into your views and stored procedures to make different joins based on the EntityType (a little tricky, but hardly impossible).
|
|
|
|
|
I'm tryin to create a unique id for each row in the database(SQL SERVER), I don't want to use the standard convention of uniqueidentifer. but to create my own which takes an abbreviation say 'pm' adds the year '06' and finally adds the next row number in sequence.
eg.
id = 'pm' + '06' + '0001'
Has anyone any ideas of how to do this. I have something working but it's not entirely correct.
gfhg
|
|
|
|
|
You could use a combination key, of, say:
1. PK_ID int IDENTITY (1, 1) NOT NULL
2. Abbreviation_of_whatever varchar(10) NOT NULL
3. Year int NOT NULL
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hi, I'm not sure if this is the right medium but I have a question to ask. What are some of the tables you would include in an ER diagram for creating a database for a encyclopedia using SQL. I really would appreciate your help!
-- modified at 11:09 Wednesday 18th January, 2006
|
|
|
|
|
kids17 wrote: What are some of the tables you would include in an ER diagram for creating a database for a encyclopedia using SQL
Gosh! That's a bit vague.
Okay an encyclopaedia is made up of entries. An entry can have cross references (normally, these days it would be hyperlinks within the text), but lets say there is a See Also section at the end of each article rather than through the text.
So an Articles table, and a References table.
I can't think of much else because an encyclopaedia is a free flowing thing.
What do you think needs to be included?
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-- modified at 11:29 Wednesday 18th January, 2006
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
