|
can be easily found in the MSDN! everything can be done by using the DataAdapter.
example from MSDN:
' Assumes connection is a valid SqlConnection.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM Categories", connection)
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Categories SET CategoryName = @CategoryName " & _
"WHERE CategoryID = @CategoryID", connection)
adapter.UpdateCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Dim parameter As SqlParameter = adapter.UpdateCommand.Parameters.Add( _
"@CategoryID", SqlDbType.Int)
parameter.SourceColumn = "CategoryID"
parameter.SourceVersion = DataRowVersion.Original
Dim dataSet As DataSet = New DataSet
adapter.Fill(dataSet, "Categories")
Dim row As DataRow = dataSet.Tables("Categories").Rows(0)
row("CategoryName") = "New Category"
adapter.Update(dataSet, "Categories")
-walter
|
|
|
|
|
Dear friends,
I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Imtiaz
|
|
|
|
|
Imtiaz Murtaza wrote: I want to insert a new row in my table which is supposed to be the ditto copy of one of the previous rows. Now the problem is that there is one field in my table having datatype text. This column value is not getting copied from source to destination and i get error from SQL server that it is not allowed to copy text, nvarchar and binary types.
With SQL Server 2000:-
Insert Into t4 (f1,f2) Select f1, f2 From t4 Where f1=2
Here f2 field is of type text in t4 table. This query is successfully executing. I guess you are using functions with text type field, which may be a reason for the error.
Imtiaz Murtaza wrote: Now if i convert the column value to varchar then it gets coppied. But i dont want to convert it to varchar because varchar has a limit of 8K characters, and my text column may contains more than 8K characters; and they get truncated while converting to varchar.
Please tell me how to copy the text datatype value from one row to the other.
Try to follow this technique:
Insert Into t4 (f1, f2) Select f1, Substring(f2,1,8000) + Substring(f2,8001,8000) from t4 where f1=3
Hope this may help.
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Need the steps for importing .dbf file to MS-Access through C# code
Need the steps for exporting MS-Access table to .dbf file(dBaseIII).
|
|
|
|
|
I have merged two datasets using "Dataset.Merge" command, which the data is retrived from two different dBase-III databases of same structure.
Can I create a new database of using dataset, if yes, how...?
Regards,
Kishore.
|
|
|
|
|
i want taht mdb file in vs2005 support utf8 . how? 
|
|
|
|
|
hi all,
first of all Merry Christmas to all of you.
Problem : I'm using MYSQL database, windows forms amd a Grid View. when i'm assigning a DataSet as Datasource to GridView its not working though my dataset has records. Any special configuration is needed ot any property has to be set.
Any type of help will be apprecited.
Regards,
Rahul
|
|
|
|
|
please first check your dataset is data exist or not if yes then you just try it.
I thing it is very use full for you.
DataGrid1.DataSource = objDs.Tables[0].DefaultView();
Enjoy
Pavan Pareta
|
|
|
|
|
I'm not able connect my front end "Borland developer Studio 2006" with postgreSQL 8.1.I'm developing my application with ASP.NET and C#.NET in "Borland developer studio 2006" environment.Please help in this matter by sending the "connection string" and "namespace to use" in db connection..
I'm using layer coding in ASP.NET and C# (code behind).I'm new to postgreSQL 8.1.4,previously i'm using SQL Server 2000,in that i use this codes to connect my front end.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace suyam
{
public class Election
{
public SqlConnection con=new SqlConnection("initial catalog=election;user id=sa");
public SqlCommand cmd;
public SqlDataReader dr;
DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
hari
|
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
