Inserting Rows into Typed DataSets - Database Discussion Boards

syed saba8-Jun-04 21:47

8-Jun-04 21:47

When iam extracting a date from SQL Svr 7 and i want it to be in a specific format, isn't there a SQL command that will do the formatting?
system date '2004-06-09 13:43:41.363' i want this date in 'mm\dd\yy' format

If so, could you give me the syntax and the associated style codes.

Thanks
syed saba

Re: How i Can format Date in Sql Server 7.0

Hesham Amin9-Jun-04 6:50

Hesham Amin

9-Jun-04 6:50

this works on 2000
check it on 7 :
select convert(varchar(10),DateValue,1)

Re: How i Can format Date in Sql Server 7.0

-Dr_X-9-Jun-04 15:30

-Dr_X-

9-Jun-04 15:30

I concur with hspc. There are about 20 options for formatting the date. Look at the help on convert in SQL which will give you the parameters and examples.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

getting value from newly inserted row

sharonz8-Jun-04 20:54

sharonz

8-Jun-04 20:54

Hi,

I have a question. I'm inserting a row into a table in Access DB (without using the command builder), one of the coloumns (the primary key, actually) in the table is an AutoNumber - which gets updated when I insert.
Does anyone know how can I get the coloumn's value?

thanks!

Re: getting value from newly inserted row

Edbert P9-Jun-04 20:34

Edbert P

9-Jun-04 20:34

SELECT @@IDENTITY works for Access 2000 and later.

Edbert P.
Sydney, Australia.

Re: getting value from newly inserted row

sharonz9-Jun-04 20:50

sharonz

9-Jun-04 20:50

Thank you so much! on both answers!

Probelm in Displaying records in 2 data grids

orpus8-Jun-04 20:27

orpus

8-Jun-04 20:27

Hi ..
please see the code below..
I have two tables Centrals and Devices both having Central_Id as a common field..I am able to display the Centals table value in the first datagrid (dataGirdCentral).However on click of each cell on the central_id i want the corresponding entries of Devices table to be shown in the 2nd datagrid dataGridDevices..how do i do that...

private void loadCentralDevices()
{
ds = new DataSet();
//Getting schema of Centrals table
DataTable centralDt = new DataTable("Centrals");
String query = "Select * from Centrals";
OleDbDataAdapter da = new OleDbDataAdapter(query,ConfigurationSettings.AppSettings["MsAccess_ConnectString"]);
da.FillSchema(centralDt, SchemaType.Source);
ds.Tables.Add(centralDt);

//Getting schema of Devices table
DataTable deviceDt = new DataTable("Devices");
String query1 = "Select * from Devices";
da = new OleDbDataAdapter(query1,ConfigurationSettings.AppSettings["MsAccess_ConnectString"]);
da.FillSchema(deviceDt,SchemaType.Source);
ds.Tables.Add(deviceDt);

DataRelation dr = new DataRelation("Central_Devices_Relation",
centralDt.Columns["Central_ID"], deviceDt.Columns["Central_Id"]);
ds.Relations.Add(dr);

//create a dataview of the data
DataView centralVw = new DataView(ds.Tables["centralDt"]);
//giving access to Centrals table
centralVw.AllowDelete=true;
centralVw.AllowEdit = true;
centralVw.AllowNew = true;
//set the grid source to the author view
dataGridCentral.DataSource = centralVw;
//hook up the event handler
dataGridCentral.CurrentCellChanged+= new EventHandler(this.dataGridCentral_CellChanging);

}

private void dataGridCentral_CellChanging(object sender, EventArgs eArgs)
{
????? what do i write here to get the corresponding values?
}

Breath dot net

Inserting Rows into Typed DataSets

Member 11376038-Jun-04 17:43

Member 1137603

8-Jun-04 17:43

Hi All,

I have an issue with inserting new rows into a typed dataset. The dataset has two datatables (e.g. Order, OrderDetails). Both tables have a primary key (not nullable) and there is a foreign key constraint (and relation) between the OrderDetails and Order datatable.

The problem comes when trying to insert a new row into the Order (parent) table. Because I haven't been to the DB to get the next sequence number (i'm using Oracle), there is no value on the primary key. Obviously I can't add this row to the datatable unless i turn constraints off which I don't want to do. I also don't want to have to go to the database everytime I add a new row to the datatable as it could result in a stack of roundtrips to the database (each orderdetail insert would require another round trip)

Surely there is another way that I'm not aware of? What would be good is that for any new rows that are created, I put a GUID or something in the PK which would allow me to insert and relate child rows etc.. but I don't know if this is even possible or if it's best practice.

Thanks in advance for any help!

Re: Inserting Rows into Typed DataSets

Edbert P9-Jun-04 20:16

Edbert P

9-Jun-04 20:16

I updated the parent table for my application before using the PK as FK for the child table, but you might want to try this:
There is a property called DataRow.SetParentRow that sets the parent row for the child table.
I always specify this property before I update the child table, e.g.

<code>parentRow.Key = KeyValue
parentRow.Field1 = Value1
parentRow.Field2 = Value2</code>

//I usually update parent row here before using the value but you might not need to update it before adding the child row.

<code>childRow.Key = parentRow.Key
childRow.Field1 = Value1
childRow.Field2 = Value2
childRow.SetParentRow(parentRow)</code>

//Update child row

This might help you link the two tables along with Key Fields and Foreign Key Fields setting in the dataset.
Remember that you need to update the parent table before the child table for insertion.

I hope it helps.

Edbert P.
Sydney, Australia.

VC++ ADO Recordset Close Vs NULL

Nemikesoria8-Jun-04 4:47

Nemikesoria

8-Jun-04 4:47

Hi, I am debugging a COM Dll that uses ADO thru smart pointers (#import) and when recordsets are used, they were being Released with Release (); I have used recordsets in the past thru smart pointers and always used to call Close (); and then assign NULL to the recordset pointer,

so what I did with the "recordset->Release ();" lines was to change them to: "recordset = NULL;", but I wonder if I should explicitly Close them?

does anybody know how this should be? thanks a lot,

Nemike

Nemike

sql reporting services

robmays8-Jun-04 4:03

robmays

8-Jun-04 4:03

i have add reporting services to my production machine and the home page does not display its toolbar so i can not manage my reports? why do i not have a toolbar in my reporserver?

ADO & GUID & C++

Mobileware8-Jun-04 0:46

Mobileware

8-Jun-04 0:46

How to add/change/read GUID data type field Sleepy | :zzz:

on SQL server with ADO (C++)

I want to do a ETL tool. ADO is feasible?

utiao7-Jun-04 21:27

utiao

7-Jun-04 21:27

use ado or ado.net to complete a etl tool, is it feasible?
I use visual C++.net, everybody, what do you think which develop tool is better? ado or ado.net?
give me some advice! thank you!

Re: I want to do a ETL tool. ADO is feasible?

Anonymous11-Jun-04 9:47

Anonymous

11-Jun-04 9:47

Hello,

Interestingly enough, I am working on an ETL tool using ADO.NET and SQL Server at the moment.

So far so good though the system is still in its infancy. And I'm learning as I go so keep in mind there may be better ways to do things other than I have done it.

Our system is focused on batch processing and so far I've run into the following with ADO.NET

1. Using DataSet for batch processing may not be viable on large tables because it uses up a lot of memory.
2. Using DataReader for batch processing large tables is great but you are not able to update the records in batch once you are done.
3. You could perform updates while you are traversing the datareader, but this results in a lot of network traffic while processing.

I'm currently trying to figure out a good way to batch the updates so that I can send them back to the database in chunks but havent figured out how I'm going to do that yet.

Hope this helps. I haven't tried using ADO through interop so I can't comment on effeciency comparison between the two.

Oracle REF Cursor and Generic DAL

Vasudevan Deepak Kumar7-Jun-04 17:55

Vasudevan Deepak Kumar

7-Jun-04 17:55

Hi:

I have a small issue with Oracle Ref Cursor in dealing with a generic DAL (Data Access Component) component.

I am currently developing a generic Data Access Component targetting SQLServer, Oracle and DB2.

Only for Oracle Stored Procedures dealing with REF CURSORs, I am not able to make a mapping to a corresponding .NET DataType. The DAL can deal only with .NET datatype.

Please suggest me some workarounds to deal with this issue.

The provider is ODP.NET and Oracle 9.1 server. .NeT Framework version is 1.1

Deepak Kumar Vasudevan
Personal Web: http://www24.brinkster.com/lavanyadeepak/default.asp
I Blog At: http://deepak.blogdrive.com/

Problem using table aliases in ADO.net dataset

frank217-Jun-04 11:06

frank21

7-Jun-04 11:06

Hello, I have an access db that uses a recursive structure to store data i.e. there is a parts table (but there are three levels in the structure feature bill, level1 package, and then parts but they are all considered a part in the database I am extracting the nesting ability from an attribute in the parts table called style. Depending on the value of style i.e. style = 0 its a part, style = 1 it is level1package and style = 2 it is a feature bill) I am trying to generate a report that will show me for every feature bill, what level1 packages are in it and what parts are in each one of those packages. So I am essentially showing all the hierarchical levels of nesting. I am attempting to do this buy using a dataset that has multiple instances of my packages table(packages table is essentially the child element of the parts table) and linking the child node to the parent node each time until I reach the maximum amount of nesting I want. Can anyone help me out with setting up the dataset correctly? I am running into the following error:

:System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
at System.Data.DataSet.FailedEnableConstraints()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
at System.Data.DataTable.EndLoadData()
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

I am not sure how to fix the problem and any help would be greatly appreciated, thanks in advance!

Frank

Return ID from Insert Query

RDoes7-Jun-04 3:56

RDoes

7-Jun-04 3:56

Hi all,

Is it possible to get a return value from an Insert Query.
Like

Dim oCmd as OleDBCommand
Dim oConn As OleDBCommand
Dim sCommand as string

sCommand = "INSERT INTO MyTable (ID, MID, Person VALUES (1256, 'Joe Doe')

'Return the New ID number ?
oCmd = New OleDbCommand(sCommand, oConn)
oCmd.ExecuteNonQuery()

The table has a autonumber Column "ID".
When I Add a new row I want now the new ID number

Thanks, Confused | :confused:

Re: Return ID from Insert Query

GISnet7-Jun-04 5:43

GISnet

7-Jun-04 5:43

after your code:

oCmd = new OleDbCommand("SELECT @@IDENTITY", oConn);
int nId = (int)oCmd.ExecuteScalar();

Microsoft .NET - Come on! I need the Traffic!

code for db access in C#

Md Saleem Navalur7-Jun-04 2:32

Md Saleem Navalur

7-Jun-04 2:32

Hi,

Could any one share with me the source in C# for adding, updating and deleting data in a SQL server database using ADO.NET.

A simple windows application would be of great help.

Thanks
Saleem.

Re: code for db access in C#

GISnet7-Jun-04 5:48

GISnet

7-Jun-04 5:48

You'll probably get replies that answer the request, but I'd like to put in my two cents. Instead of coding the Select, Insert, Update, Delete statements inside your app, as the responses you'll get will (according to your request) show, I'd suggest that you call stored procedures with parameters.

This will allow you to separate your SQL from your application, and provide protection from SQL injection attempts.

Microsoft .NET - Come on! I need the Traffic!

export to access

suzyb7-Jun-04 1:52

suzyb

7-Jun-04 1:52

I have a function into which is passed a DataTable. In this function I want to export this DataTable to an MS Access database but can't seem to figure out how to do this whilst making it as generic as possible.

Anyone got any ideas, suggestions on how to do this?

SuzyB

If I had a better memory I would remember more.

Re: export to access

GISnet7-Jun-04 5:54

GISnet

7-Jun-04 5:54

Well, if you want to put some burdon on whoever is using this, just create a CSV and force them to import it into Access.

If you'd rather automate it, I'd suggest you name the access table
System.Data.DataTable.TableName

Start off my creating a reference to the Microsoft Access 11.0 COM Object, then use the

Microsoft.Office.Interop.Access

namespace to create your databae and tables.

Microsoft .NET - Come on! I need the Traffic!

Re: export to access

Mike Ellison7-Jun-04 9:59

Mike Ellison

7-Jun-04 9:59

Hi Suzy. Another alternative to create an MS Access table would be to use the ADOX library through COM interop. Here is a comprehensive article[^] on CodeProject that describes this, with a specific example of creating an Access database.

db @ pocket pc

ting6687-Jun-04 0:23

ting668

7-Jun-04 0:23

HI~
I would like to ask
Do ADO.Net not support in pocket pc platform?
what are the data type can be supported in ".cdb" database?
Is there any alternative database can be used in pocket pc (beside CEDE and SQL server)?

Re: db @ pocket pc

Richard Jones8-Jun-04 6:18

Richard Jones

8-Jun-04 6:18

I am using XML on PPC. Check out this snippet from Inventory sample program:

    Private Sub loadXMLData()<br />
<br />
        Try<br />
<br />
            prog.Value = 1<br />
            sbStatus.Text = "Loading XML data..."<br />
<br />
            prog.Value = 2<br />
            Dim fs As New FileStream(txtXMLFileName.Text, FileMode.Open)<br />
<br />
            prog.Value = 3<br />
            Dim xr As New XmlTextReader(fs)<br />
<br />
            prog.Value = 4<br />
            data.ReadXml(xr)<br />
<br />
            prog.Value = 8<br />
            sbStatus.Text = "Loaded XML data"<br />
            xr.Close()<br />
            fs.Close()<br />
<br />
        Catch e As System.IO.FileNotFoundException<br />
<br />
            prog.Value = 8<br />
            MessageBox.Show("The file '" + txtXMLFileName.Text + "' could not be found. Please select a different file name.")<br />
<br />
        Catch e As XmlException<br />
<br />
            prog.Value = 8<br />
            sbStatus.Text = "Exception occured: " + e.ToString()<br />
<br />
        End Try<br />
<br />
    End Sub<br />

where data is a DataSet.

"For all of our languages, we cannot communicate" - Christy Moore, Natives

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.