|
|
Hi All,
i have 25 XML file. i had make Dataset using below code.
dsObj.ReadXml(strFilePath.Trim());
now i have 25 dsObj. now i need to retrive data from diffrent-diffrent dsObj. so its take a lot of time to read and all values from the dsObj.
So Please Help me that i am make a New One objDataSet that will have
objDataSet.Tables.Count==25
what i am put on this objDataSet Inner Join in this objDataSet. but how ???????/
Pavan Pareta
|
|
|
|
|
Hi
First of all Merry Cristmas all of you.
My question is:
I have an table in my database where one of the columns have default value = GETDATE() and this column also marked as NOT NULL. I.e. when i insert an row in this table by mean of QueryAnalyizer and INSERT statement
INSERT INTO MyTable (Col1, Col2) VALUES (1,2) and suppose what Col3 is the described column. All work fine.
But when i isnsert this row by using DataGridView (this DataGridView have 2 displayed column where may be enetered significant (not default) values for table) and try to save this changes i have exception (Col3 cannot be NULL),
Plaese help: HOW i can avoid this.
Thank
|
|
|
|
|
Actually this is a very good question.
I have assumed that you used typed dataset in your code (that is bounded to the DataGridView).
* The nature of the ADO.NET is a disconnected way of work. Therefore I thought first that we need to define the default property in the DS. When the value of a DateTime column in the DataGridView and the underline DS isn't supplied it is assigned with its default value. So I have tried to accomplish this solution by defining a default value for the DateTime column in the DS's schema. After reading W3C and MSDN definitions of the DateTime constants I have added the following to the schema:
default="2006-01-01T00:00:00"
but surprise, surprise:
I have got the following statement (which actually states that the DS cannot be generated):
"Failed to generate code. Invalid Primitive Type: System.DateTime. Only CLS compliant primitive types can be used. Consider using CodeObjectCreateExpression."
Somehow it seems to be a bug, or maybe the DateTime type should be declared different in the schema when using defaults ??? (Does anybody know something about it ???)
After searching the web a while I have found that
urn:schemas-microsoft-com:xml-msprop can be used. In nutshell it can be used to access the DS's columns that are DBNull.
So this is how the schema looks like now:
<xs:schema
...
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"
...
>
...
<xs:element name="d" type="xs:dateTime" codegen:nullValue="2006-01-01T00:00:00" minOccurs="0" />
...
But this isn't the end! In order to actually pass the default value to the DB we should assign this value to the desired row by providing the RowChanged event, like follows:
<br />
...<br />
tds.t.tRowChanged += new tRowChangeEventHandler(t_tRowChanged);<br />
...<br />
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = e.Row.d;<br />
}<br />
}<br />
Brrrr... this is a very ugly solution.
* Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.
Uri
|
|
|
|
|
Thanks for contribution
Very usefull
Already one question: You wrote
---
* Another way to solve this problem is to avoid to insert values that are NULLs (or DBNulls) i.e. when the values are nulls generate insert sql statement that just omits those values. When omiting the null values the default values of the DB will be applied.
---
How i can accomplish this?
I suppose this may be done just of substition of InsertCommand proerty of adapter class, but this don't work
Already you wrote:
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = e.Row.d;
}<br />
}<br />
---
And another thing, you suppose what dateTime column has default value, but
in my case one actually not default(one defined when row insert in db by GETDATE() function)
Regrats
|
|
|
|
|
Hi,
1. The cleanest way to do it in my opinion is to use SPs. The SP will test using sql whether the DateTime is NULL and if so won't insert it.
<br />
...<br />
SqlCommand insertCommand = new SqlCommand(...);<br />
insertCommand.CommandType = CommandType.StoredProcedure;<br />
insertCommand.CommandText = "YourSPNameHere";<br />
...<br />
2. It seems wrong but it's ok.
if you read about nullVale in urn:schemas-microsoft-com:xml-msprop (with replacement value) you will find that it just provides a mean to access DBNull cell without throwing an exception, but it doesn't assigns that default value to the DS's cell.
Therefore this code just assigns that default value to the desired cell.
3. Yes, you are right.
You can change the code I have provided to be:
<br />
private void t_tRowChanged<br />
(object sender, TDS.tRowChangeEvent e)<br />
{<br />
if(e.Action == DataRowAction.Add)<br />
{<br />
e.Row.d = System.DateTime.Now;<br />
}<br />
}<br />
or to use SP as I have stated early.
Uri
|
|
|
|
|
|
Dear all,
i have tow tables 'Users' and 'Applications'
every user may be has no,one or more applications
how to write a SQL satement that get me every user who hasn't application.
in other word i want user without application...
thanks.
|
|
|
|
|
It would be helpful to answer if you state the field names, type
and with which field both tables are connected.
I think you have to do something like:
select userId from user_table where (Application_table.applCnt = 0 and Application_table.userId = user_table.userId)
if both tables are connected by userId.
Thanks & Regards,
Suman
|
|
|
|
|
select * from Users
where not exists (
select A.AppID from Applications A
where A.AppID = Users.AppID
)
|
|
|
|
|
SELECT DISTINCT Users.IDApp FROM Users WHERE Users.IDApp NOT IN (SELECT Application.IDAp FROM Application)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
Hi
I have a problem of filling null value in the column.
I have written the following query,
select c.column_name,t.constraint_type from all_cons_columns c, user_constraints t where t.table_name=c.table_name and c.table_name='student'
student table
student id - primary key
studentname - varchar
but the oracle query returns only the column having primary or foreign key. not returing the studentname. i want student name also with constraint type=null
we can do this is sql server by isnull(c.constraint_type,'')
but how to do the same in oracle? Any one help me please.........
kesavan
|
|
|
|
|
NK7 wrote: we can do this is sql server by isnull(c.constraint_type,'')
For Oracle try out, nvl(c.constraint_type,''). I think that should be equivalent.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
First Off, I'm new to code project so if I post something thats already posted sorry. But I'm hoping to find someone who can help out with this build error that I'm receiving on an oledbadapter.insertcommand.
I'm loading data into a datagrid for review. After reviewing the data I'm using a sub procedure to load the data into the database from the datagrid.
"oledbadapter.InsertCommand(Insert Into table1 from )"
error(s):
(249): Comma, ')', or a valid expression continuation expected.
(249): Property access must assign to the property or use its value.
I'm having problems figuring out what to put after the "from" statement. If this is not enough information just let me know and also let me know if I'm posting this right with out being irate about it.
Swish
|
|
|
|
|
Second attempt at this as my connection broke the first time round and IE lost the post.
Swisher24 wrote: "oledbadapter.InsertCommand(Insert Into table1 from )"
error(s):
(249): Comma, ')', or a valid expression continuation expected.
(249): Property access must assign to the property or use its value.
Okay. The OleDbDataAdapter class doesn't have a method called InsertCommand (see the class reference on MSDN[^]). It does, however, have a property called InsertCommand so your code should read something like this:
oledbadapter.InsertCommand = someSqlString
Swisher24 wrote: I'm having problems figuring out what to put after the "from" statement
Okay, this second part is that you are using an INSERT command. You get data "from" a table, you put data "into" a table. You have already specified which table you are putting data "into". See the INSERT Command reference[^] on MSDN.
Your command needs to be something along the lines of:
INSERT INTO Table1(Column1, Column2, Column3) VALUES ('value1', 'value2', 'value3')
So, you need to specify the columns that the data will be going into and the values of the data.
I've not used Data Adapters much (they don't fit most of the applications I've written) so I'm going on 3 year old knowledge that you typically specify the values as a series of parameters. If your database is SQL Server the parameter name is in the format @paramName, if you are using Access the parameters are a simple ? placeholder (so remember that the order of the columns must match the order of the columns in the DataTable being passed into the adapter)
Does this help?
|
|
|
|
|
Thanks Colin but I've figured out another way to take the data from the datagrid into the database.
Swish
|
|
|
|
|
Hi All,
I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005)
i.e Add/drop a table, column, constraint etc. on a ongoing basis.
The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc.
So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database.
I have a couple of solutions in my mind but feel that they are not very efficient.
1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem.
2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there.
Can somebody please provide me some guidelines/tips on how to go ahead about this.
Thanks in advance.
PJ
|
|
|
|
|
Does anyone know how codeproject stores its articles ??? in a field of type "ntext" in a text file ???
|
|
|
|
|
Hello. I have a sql statement that searches money.
SELECT [fldItemId] FROM tblCost where ([fldCost] BETWEEN 0 AND 2100)
the tbl has about 40 records in it.When I use the sql server interface that was provided by godaddy, and use the same sql statement, I get about 20 records that match. But when I use a web page (aspx using c#), I get no results. If anyone can offer me any advice, it would be great. I have been on this for a few days and I want to be able to search my records by cost.
|
|
|
|
|
When you say you get no results. What do you mean? How do you know? Are you binding it to something? If so, have you done the following?
item.DataSource = myDataSource;
item.DataBind();
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am binding to a data grid.
The following code is a snippit of a function that searches the database.
Sql = "SELECT [fldItemId] FROM tblCost where ";
Sql += "([fldCost] BETWEEN 0 AND 100)";
conn.Open();
com = new SqlCommand(Sql, conn);
reader = com.ExecuteReader();
while (reader.Read())
_ItemIdList.Add((int)reader["fldItemId"]);
conn.Close();
}
return _itemIdList;
To call the function, I use
GridSearchResults.DataSource = SearchControl.Search(query);
GridSearchResults.DataBind();
I ran other statements that did not look in the fldCost and I got records back. (btw fldCost is sql money type)
|
|
|
|
|
Have you tried putting a breakpoint on this line
<br />
GridSearchResults.DataSource = SearchControl.Search(query);<br />
and seeing if you actually get any results, but they are just not being displayed?
Also, sql money gets mapped to a decimal in c#
ChrisB
|
|
|
|
|
OK, I found what was wrong. It seems that I was clearing my list and returning a blank list. I founf this out because I ran a count in the while(reader.Read()) loop and it said 42 when the datagrid was empty.
Thanks everyone for your help.
|
|
|
|
|
Select a.*, b.* from Customers a,(Select OrderID,Orderdate,CustomerID from Orders) b where a.CustomerID = b.CustomerID
facing a prob while modifying this one through my program.. the code seems right.. not v good with sql.. is the syntax of this query correct?
|
|
|
|
|
This syntax is not correct. It seems as though you are trying to do this:
SELECT a.*, b.OrderID, b.OrderDate, b.CustomerID
FROM Customers a
INNER JOIN Orders B
ON a.CustomerID = b.CustomerID
This returns all of the records that match the conditions in BOTH a and b. Note that it is generally not good practice to use * to retrieve columns. This is just lazy practice and should be discouraged as much as possible. Instead of doing this, retrieve the parameters explicitly.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
