SqlDateTime overflow error inserting a null date into a nullable sql2000 datetime column using ado.net/vb.net, 1.1 framework
I'm trying to insert a null date into a nullable sql2000 datetime column,
but i'm getting this error:
An unhandled exception of type 'System.Data.SqlTypes.SqlTypeException'
occurred in system.data.dll
Additional information: SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM.
See code below.
Any help is greatly appreciated!
Chris
-------START----------------------
Imports System.Data.SqlClient
MyFct(ByVal SomeDate As DateTime)
...
cmd.CommandText = "MyStoredProc"
cmd.Parameters.Add(New SqlParameter("@SomeDate", SqlDbType.DateTime, 8))
'IsDBNull(SomeDate) is True at this point (aka, #12:00:00 AM#).
cmd.Parameters("@SomeDate").Value = SomeDate
cn.Open()
cmd.ExecuteNonQuery() 'This line gives error (above).
end function
-----------------------------
SQL Server 2000 Stored Proc:
CREATE PROCEDURE MyStoredProc
@SomeDate datetime
AS
INSERT INTO MyTable (
SomeDate
)
VALUES (
@SomeDate
)
-----------------------------
SQL Server 2000 Partial Tbl Def:
CREATE TABLE [MyTable] (
...
[SomeDate] [datetime] NULL
)
-----------END------------------ Tag: test Tag: 95152
Simple 'NULL' / Databiding prob got me stumped..
Ok, I'm new at this and learning as I go, but under extreme pressure
from work.. So many bugs and little tricky obsticals have got me very
far behind, and this is the latest..
I made a simple databinding form, but not using the wizard, because it
doesn't use the SQL driver.
I'm keeping it simple right now with a very normal table with a
primary key 'SIN' (social insurance number). Well I'm following
along in the book to do the whole goto next record, delete etc.. All
works.. But the problem is ADDING a record. Whenever I try, it simply
says "column 'SIN' does not allow nulls". Well no $#1T. I have a
value in the field, but it simply erases it right away and gives me
that message. I've been searching for DAYS on this, and nobody has
seemed to have it figured out in this scenario. It seems to me like
an extremly common issue..So why all the headache? None of the books
examples seem to deal with this....
btw, I've tried:
setting a default value on the SQL table itself.
changing the xml schema to not allow nulls.
changing the xml schema to assign a default value. Tag: test Tag: 95146
Simple 'NULL' / Databiding prob got me stumped..
Ok, I'm new at this and learning as I go, but under extreme pressure
from work.. So many bugs and little tricky obsticals have got me very
far behind, and this is the latest..
I made a simple databinding form, but not using the wizard, because it
doesn't use the SQL driver.
I'm keeping it simple right now with a very normal table with a
primary key 'SIN' (social insurance number). Well I'm following
along in the book to do the whole goto next record, delete etc.. All
works.. But the problem is ADDING a record. Whenever I try, it simply
says "column 'SIN' does not allow nulls". Well no $#1T. I have a
value in the field, but it simply erases it right away and gives me
that message. I've been searching for DAYS on this, and nobody has
seemed to have it figured out in this scenario. It seems to me like
an extremly common issue..So why all the headache? None of the books
examples seem to deal with this....
btw, I've tried:
setting a default value on the SQL table itself.
changing the xml schema to not allow nulls.
changing the xml schema to assign a default value. Tag: test Tag: 95145
XML and Nulls
I have a table with 3 columns (CustomerID, CustomerFirstName,
CustomerLastName). I select the one row from that table and put it into a
dataset then write an XML file. However, the CustomerFirstName value is NULL
and it did not write a <CustomerFirstName> tag to the XML file.
I then take the XML file to another application that reads it into a
dataset. I remove the one row from the dataset then create a new datarow
that specifices what the CustomerFirstName. However because the
<CustomerFirstName> tag to the XML file was not in the XML file, it will not
allow be to add it the XML file. What can I do? Tag: test Tag: 95144
SqlHelper.ExecuteNonQueryTypedParams
Can anyone post their experience using the ExecuteNonQueryTypedParams
method of the SQLHelper class? It seems a little too easy for
updating rows in a dataset. Is there something I'm missing that's
going to bite in the end?
Thanks, Brian Tag: test Tag: 95138
dataGrid detail view refresh
Hi,
I click the "+" sign on my datagrid to display a detail view.
I then edit the detail table by code and call AcceptChanges(), the datagrid
would
automatically collasp and go back to the master view.
How do I stop the detail view on a datagrid from going back to the master
view?
Thanks
david Tag: test Tag: 95131
Oracle Stored Procedure Problem
I must call this function PS/SQL :
FUNCTION get_referente (tiporef IN number,impridref IN number, perrorcode
OUT NUMBER)
RETURN requicur IS
wrk_cursor requicur;
--
BEGIN
--
perrorcode := 0;
--
OPEN wrk_cursor FOR
SELECT NOME, COGNOME, RECAPITO
FROM REFERENTE
WHERE IMPRID=impridref AND TIPO = tiporef;
RETURN wrk_cursor;
--
EXCEPTION
WHEN OTHERS THEN perrorcode := SQLCODE;
END get_referente;
I use this code :
OleDbConnection oOleDbConnection =new OleDbConnection();
String sConnString ="Provider=OraOLEDB.Oracle;Data
Source=xxxxxx;User ID=asterif;Password=asterif";
oOleDbConnection = new OleDbConnection(sConnString);
OleDbDataAdapter myCommand =new
OleDbDataAdapter("ui.get_referente",oOleDbConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add("tiporef",DbType.VarNumeric);
myCommand.SelectCommand.Parameters["tiporef"].Direction=ParameterDirection.Input;
myCommand.SelectCommand.Parameters.Add("impridref",DbType.VarNumeric);
myCommand.SelectCommand.Parameters["impridref"].Direction=ParameterDirection.Input;
myCommand.SelectCommand.Parameters.Add("perrorcode",DbType.VarNumeric);
myCommand.SelectCommand.Parameters["perrorcode"].Direction=ParameterDirection.Input;
DataSet ds=new DataSet();
myCommand.Fill (ds,"Dataset");
But the fill method raise error.
How can i define a return value of function (Cursor type) ?
Thanks Tag: test Tag: 95121
Data Provider independent code
Hi,
I would like to have a data layer that is independent from the data provider
and ADO.NET. What I mean is that I would like to have a project that is
reusable in other projects, which use SQL Server, Sybase, or MySQL.
The goal is to avoid duplicating code as much as possible. For example, I
would like to have one "Fill" method which works for any data provider.
Is this possible at all? Any link to articles/code?
Thanks
Mike Tag: test Tag: 95117
How to pass a table array to an Oracle stored procedure
I have an Oracle SPROC which takes as one of its IN parameters a table array
(e.g. type charArray is table of varchar2(255)...)
How can I call that SPROC and pass it the table array from ADO.NET?
Thanks. Tag: test Tag: 95113
Which data provider has better performance for Oracle
Hi All,
Can any one suggest which data provider to use for a better performance with
Orace database:
1. ODP.Net (Oracle data provider for .Net) or
2. Microsoft data provider for Oracle
This is important as we are getting performance issues and team thinks this
due to the new MS.Net provider.
Thanks in Advance,
Alok Tag: test Tag: 95111
How to disable child rows in a grid
I have a grid whose datasource is a dataset and whose member is KeywordSets.
It works fine, except for one irritant. There are two other grids on the
page, whose datamembers are relations between KeywordSets and child tables.
On the KeywordSet grid in the left hand side there is a box with a plus sign
in the middle. If I click it I get the choice of seeing the child views.
Trouble is, I can't get back to the parent view.
I'd like to disable the plus sign, but haven't found a member that will do
it.
Can somebody help?
dennis Tag: test Tag: 95108
Running SQL script through VB windows installer
Hi all,
I have a SQL script(pretty big in size) generated by Enterprise manager.
I need to run that script through VB to install database schema on my
database.
It includes creation of tables, users and stored procedures.
I have written a VB program, which reads the script from a text file and
use Command.executeNonQuery.
I have the program ready, but with problems :
1. If I incluide the SQL script as it is,
It throws exception : Incorrect syntax near 'GO'
2. If I exlude all 'GO's from the script,
It creates the tables and users successfully but throws exception :
Stored Procedures have to be on top in batch query.
3. If I move the procedures to the top, It throws exception at second
porcedure saying : Invalid varable or invalid sytax near procedure .
4. If I move all stored procedures to individual txt files, it works.
Now, as the acript is huge and application is still under development, I
want to work with a single file which i just generate from enterprise
manager.
Is it possible ???
Thnx in advance Tag: test Tag: 95101
after installing XP SP2 SQL Server does not exist or access denied.
Hi,
I just installed Windows XP SP2 on my previous installation of XP SP1.
Since it is intalled, my ado.net connection on my programs do not work
anymore; i receive this exception message:
"SQL Server does not exist or access denied."
I setted port TCP 1433 and 1434 on the firewall.cpl and it did not fix the
problem.
Is some tips for finding why it do not work anymore? do i have to
re-install sql server? it works well by itself, it is the connection from
my vb.net programs that not work anymore.
Thanks
Serge
sergbouc@aei.ca Tag: test Tag: 95098
Determining cost of a query
G'day,
I've had a decent look around, and cant seem to find any way to get the
cost of a query from anything in the System.Data namespace. I hope I am
missing something obvious. What we want to do is have an optional step
where we prepare a query and/or somehow obtain the cost (or estimated rows
returned?) prior to execution and retrieval, and have an escape route if too
high.
TIA
Radek Tag: test Tag: 95084
OLEDB Provider throws exception "Specified cast is not valid"
We are trying to use a 3rd party OLEDB driver that apparently works well
under VB6.
The driver appears to be installed correctly, as we can access data via the
driver using Excell.
Just excecuting the following lines...
dim oledbCnctn as New OleDbConnection(<CnctnString>)
oldedbCnctn.Open()
throws the exception "Specified cast is not valid" (please see details
below).
All connection string parameters except the provider-name are ignored (ie.
they are correct as per the Excell test above), but altering them has no
effect on the exception thrown.
As we are aware, no one else has used this OLEDB driver within in the .NET
environment before, and we posses no experience in OLEDB driver development.
We access SQL data with no problems.
Any ideas as to what we should be doing/trying greatly appreciated.
Should an existing OLEDB driver automatically work in the .NET environment,
or could some modifications be required first?
Many thanks for any help.
Hugo
[InvalidCastException: Specified cast is not valid.]
System.Data.OleDb.OleDbException..ctor(IErrorInfo errorInfo, Int32
errorCode, Exception inner)
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hResult,
OleDbConnection connection, Object src)
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
It looks as tho the OLEDB driver is returning a result value that cannot be
cast to an integer - this maybe because the driver reports a failure or it
experenced a problem?
FYI: The 3rd-party driver provides a SQL-like interface to an ISAM-like file
system of flat files. Tag: test Tag: 95083
Datagrid Multi Row Select???
Hi,
I need to delete all the selected records from a Datagrid, but i cannot seem
to work out how from code you can tell what records are selected in order to
build my adapter delete command?
Any help would be great.
thanks
Darryn Tag: test Tag: 95082
XML and dataset
I have a question. I have data base table with some data. I have to
run a query from the table and i have to show this data in XML file. I
know i could load into dataset and then say dataSet.GetXML or some
thing like that. But the data for each row is suppose to go on
different nodes. i Have schema available in in for XSD file. How do i
put the data in the appropariate child node and return the XML
venkat Tag: test Tag: 95074
ASPX pages failing when SQL Server busy
I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.
Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.
The sproc does lots of joining, aggrigates and sorting, and makes the tempdb
grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.
Can anyone suggest what's going wrong?? I suspect tempdb, if only because of
it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)
Thanks,
John Tag: test Tag: 95073
XML - building a complex XML document
I need to build a complex XML document. By complex, it has many elements
embedded within other elements.
At first I was hoping I could build a DataSet, and then just use the
WriteXML method to save the file. Unfortunately, I'm not having much luck
with that (I cannot figure out how to build the parent/child relationships).
Let me give an example.
I need to create the following XML (the format is not up to me, so please
don't suggest that I simplify the output structure, as that is out of my
control):
<document>
<header>
<title>Fred</title>
<page>1</page>
</header>
<item>
<number>123</number>
<name>
<value>test1</value>
</name>
</item>
<item>
<number>456</number>
<name>
<value>test2</value>
</name>
</item>
Now, I can create the <document> and <header>/<item> level elements. But I
cannot seem to create the elements within those (especially <name> in
<item>, as that contains elements itself) using the DataSet object.
What is going to be the best/easiest way to do this?
Thanks!
-Scott Tag: test Tag: 95071
DataRow.RowState read-only, workaround
I've read many postings expressing frustration that the RowState
property of a DataRow object is readonly. Here's a workaround that
works for me. Below, the function adds a new row to a datatable and
sets the rowstate as specified. You can take the general idea and
adapt it to your needs. The key to this solution is realizing that
individual rows in the table have the .AcceptChanges method.
private Function AddNewRow( _
ByVal dt As DataTable, _
ByVal rowState As DataRowState, _
ByVal rowData() as object) As DataRow
Dim row As DataRow
row = dt.Rows.Add(rowData)
Select Case rowState
Case DataRowState.Added
'do nothing, default state is Added
Case DataRowState.Unchanged
row.AcceptChanges()
Case DataRowState.Deleted
row.AcceptChanges()
row.Delete()
Case DataRowState.Modified
row.AcceptChanges()
row.BeginEdit()
row.EndEdit()
Case Else
Throw New Exception("Unsupported rowstate")
End Select
Return row
End Function
-- Craig Yellick, Alto Tag: test Tag: 95069
Data access app block connections
Hi all,
Glad to join you all...
Does the Data Access Application Block closes the connections associated to
him automaticly, After executing a command. Or do I have to close the
connections manualy?
Jud Tag: test Tag: 95063
Insert record into Paradox table
OK, I'm trying to do something very simple...add a record to a
Paradox table, but for the life of me I can't seem to get it right.
Here is a code snippet:
----------------------------
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\MyDirectory;Extended Properties=Paradox 5.x;";
OleDbConnection connParadox = new OleDbConnection(connStr);
OleDbDataAdapter daParadox = new OleDbDataAdapter();
OleDbCommand selCmd = new OleDbCommand("SELECT * FROM
MASTER_TABLE",connParadox);
daParadox.SelectCommand = selCmd;
string insCommand = "insert into MASTER_TABLE (ID,CODE) VALUES
(9999,'99999')"; // This statement works using SQL Explorer in
Delphi.
OleDbCommand insCmd = new OleDbCommand(insCommand, connParadox);
daParadox.InsertCommand = insCmd;
//Fill the dataset.
System.Data.DataSet dsParadox = new System.Data.DataSet();
daParadox.Fill(dsParadox); // This works...I get records in the
DataSet.
...
// Update the table. // This doesn't work...compiles OK,
doesn't even raise an error, but no table update.
daParadox.Update(dsParadox);
dsParadox.AcceptChanges();
----------------------------
I've also used the SQL statment:
insert into MASTER_TABLE IN C:\\MyDirectory\\MASTER_TABLE.db paradox
(ID,CODE) VALUES (9999,'99999')
but I get the same thing...nothing.
Could someone tell me what the heck I'm doing wrong, or point me to
some code
sample I could examine. Tag: test Tag: 95062
NullReferenceException during draw from DataView.IsOriginalVersion(int)
Hi,
I have a winforms application written in C#, framework 1.1. It
contains several grids that get updated frequently.
Sporatically, we've been getting NullReferenceException while the
control draws itself. The last method called is
DataView.IsOriginalVersion(int)...
I've read a few other posts with similiar issues, but no real
solution. Anyone else having these problems? Found any solutions?
Thanks,
-beau
Here are two traces:
=================== Trace 1
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance
of an object.
at System.Data.DataView.IsOriginalVersion(Int32 index)
at System.Data.DataRowView.get_Item(Int32 ndx)
at DevExpress.Data.DataViewDataAdapter.GetRowValue(Int32 rowHandle,
Int32 columnHandle)
at DevExpress.Data.CustomDataController.GetRowValue(Int32
rowHandle, Int32 columnHandle)
at DevExpress.XtraGrid.Views.Base.ColumnView.GetRowCellValue(Int32
rowHandle, GridColumn column)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowCellDrawInfo(GridRowInfo
ri, GridColumnInfoArgs ci, GridCellInfo cell, GridColumnInfoArgs
nextColumn, Boolean calcEditInfo)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowCellsDrawInfo(GridRowInfo
ri, GridColumnsInfo columnsInfo)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowDrawInfo(GridRowInfo
ri, GridRow row, GridRow nextRow)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowsDrawInfo()
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcGridInfo()
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.Calc(Graphics
g, Rectangle bounds)
at DevExpress.XtraGrid.Views.Grid.GridView.CheckViewInfo()
at DevExpress.XtraGrid.Views.Base.BaseView.Draw(GraphicsCache e)
at DevExpress.XtraGrid.GridControl.OnPaint(PaintEventArgs e)
at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs
e, Int16 layer, Boolean disposeEventArgs)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraEditors.Container.EditorContainer.WndProc(Message&
m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam)
======================= Trace 2
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance
of an object.
at System.Data.DataView.IsOriginalVersion(Int32 index)
at System.Data.DataRowView.get_Item(Int32 ndx)
at DevExpress.Data.DataViewDataAdapter.GetRowValue(Int32 rowHandle,
Int32 columnHandle)
at DevExpress.Data.CustomDataController.GetRowValue(Int32
rowHandle, Int32 columnHandle)
at DevExpress.XtraGrid.Views.Base.ColumnView.GetRowCellValue(Int32
rowHandle, GridColumn column)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowCellDrawInfo(GridRowInfo
ri, GridColumnInfoArgs ci, GridCellInfo cell, GridColumnInfoArgs
nextColumn, Boolean calcEditInfo)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowCellsDrawInfo(GridRowInfo
ri, GridColumnsInfo columnsInfo)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowDrawInfo(GridRowInfo
ri, GridRow row, GridRow nextRow)
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcRowsDrawInfo()
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.CalcGridInfo()
at DevExpress.XtraGrid.Views.Grid.ViewInfo.GridViewInfo.Calc(Graphics
g, Rectangle bounds)
at DevExpress.XtraGrid.Views.Grid.GridView.CheckViewInfo()
at DevExpress.XtraGrid.Views.Base.BaseView.Draw(GraphicsCache e)
at DevExpress.XtraGrid.GridControl.OnPaint(PaintEventArgs e)
at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs
e, Int16 layer, Boolean disposeEventArgs)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraEditors.Container.EditorContainer.WndProc(Message&
m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam) Tag: test Tag: 95061
Looking For Driver
I don't mind paying.
Looking for a driver that will allow me to use a dataadapter in visual
studio to access a IBM COBOL database on the internet.
I do vb.net windows applications. Thank you for any help. Tag: test Tag: 95060
Need help with Parent/Child table update
Sorry if this has been addressed before, but I can't find a good reference
for what I'm trying to do.
I'm trying to figure out the "best" way to design and code a windows app.
that allows a user to enter data that will be stored in two related tables
in an Access 2000 database. (ya-ya, I know, use SQL Server...can't right
now).
I've got a grid bound to a typed Data Set. The DS contains two tables
Orders and Items, related by a unique Order ID. Users select a grid row and
the all the data for the cooresponding record is displayed on a data entry
form (unbound) for viewing and/or editing. I'm having problems updating and
deleting child records in the database. Parent rows work OK. I feel like
I'm missing something basic in the handling of Data Sets during updates and
can't find any examples of the best way to handle updates of parent/child
tables using Access. Can someone point me to a good explaination? TIA.
Steve Tag: test Tag: 95059
Get DB tables list and Cols list
All,
What's fastest/easiest way to get a list of tables within a DB and a list
of cols within DB. I want to check and update my DB structure at runtime...
prefer without SQL-DMO...
MTIA
wardeaux Tag: test Tag: 95056
Sybase Processes Problem
Processes are staying open. I am closing my connections in my code,
but they do not close on the server. Once I reach the Max # of
processes everything bombs out.
The is Sybase ASE 12.0 running on Sun Solaris 9. I am using the
Sybase .NET Provider v1.1.
Any ideas?
TIA Tag: test Tag: 95055
Performance Testing....
Hi,
Now I am developing C#.net Web Application. It is almost completed. Now I
want to check performance testing of it. I want...
1) Create 100 user environments, mean 100 users simultaneously request a
same page I want to see response time and load on server.
2) As per my idea we can use test project it has web base test and load test
inbuilt in Microsoft visual studio Whidbey.
Any one has idea about Load test in Microsoft visual studio Whidbey?
BYE.
Samir (Software Developer, Ahmedabad, INDIA). Tag: test Tag: 95053
Case-sensitive collation and queries
Hi all,
I installed a Ms SQL Server 2000 instance with a case-sensitive collation,
but I have databases with case-insensitive collations. When I try to make a
ExecuteQuery through ADO.NET on case-insensitive database tables I need to
set the CommandText in a case-sensitive manner, otherwise I get error
messages : I have a table named MyTable, if CommandText is "SELECT * FROM
myTable" => error message : object 'myTable' does not exist and "SELECT *
FROM MyTable" works fine.
1. This is the correct behaviour ? Any workaround ? It's hell to remember
exactly the column or table names
2. How can I find the sql server collation ? (programatically)
I'm using .NET Frmw 1.0
Thanks Tag: test Tag: 95051
Creating XML file with many-to-many relations with a dataset
I need to retrieve data from sql sever db that contains link tables which
provide
many-to-many functionality. Than I need to convert this data to xml file
with nesting parent and child relations. The XML need not contain the link
tables only their function (relations).
Should I use the DataSet object and the DataSet.GetXml() method to this
purpose?
I would greatly appreciate an example preferably in C#.
(I have already seen examples with one-to-many, so please don't refer me to
such).
Thank you in advance.
Hilel. Tag: test Tag: 95050
ADO.NET converting numerical zero to NULL
I am seeing a funny effect with ADO.NET - or am I missing some
documented behavior here??
I have a stored proc that I need to call, and for that purpose, I
create an array of type SQLParameter[], and initialize it with
parameters and their values.
When I fill my parameters, everything seems to work just fine, except
when I need to fill an INT parameter with the value of 0 (zero).
Here's a sample:
SqlParameter[] oParams = new SqlParameter[15];
oParams[0] = new SqlParameter("@lSprachID", 1); // works fine
......
oParams[9] = new SqlParameter("@lSomeValue", 0); // bombs out
The trouble is - instead of creating a parameter no. 9 with a value of
0 (zero), ADO.NET seems to convert this into a NULL parameter, and my
stored proc then complains that it doesn't allow NULL values in that
particular column.
If I chance it to
oParams[9] = new SqlParameter("@lSomeValue", SqlDbType.Int);
oParams[9].Value = 0;
and then call my stored proc, everything's just fine.
I don't get it - why would ADO.NET convert a parameter which I create
and initialize with a numerical value of "0" (zero) to a NULL
parameter?? Is that works-as-designed (and if so, WHERE is that
documented??). Is there anything I can do (except use the work around)
to change this behaviour??
Thanks!
Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch Tag: test Tag: 95044
Cast output parameter from SqlGuid to Guid
Hi,
I had a look at MSDN and it clearly sais that SqlGuid can be casted as Guid.
To execute a stored procedure I have the following function (note the s.proc
does not return any recordsets, just sets the output value):
function updateEntry(ref System.Guid valueID, ...){
...
cmd.ExecuteNonQuery();
//get the output value now:
valueID = (System.Guid)cmd.Parameters["@valueID"].Value;
....
}
Please note that the valueID is passed by reference. The code works fine
if I don't pass a value (i.e. set the parameter to DBNull): the valueID gets
set as expected. The problem occurs when I do pass a value. I get an
Invalid Cast exception?!
If I use the following sequence for getting the valueID, the code works
fine, but as you can guess, it seems a bit too much:
....
//get the output value now:
object o = cmd.Parameters["@valueID"].Value;
valueID = new Guid(o.ToString());
...
Any ideas what I am missing in the first version?
Thanks,
Goran Tag: test Tag: 95043
DataRelation's Nested property is seemingly ignored
I have a strongly typed dataset (generated from Visual Studio schema
designer) containing two related elements. In the resulting dataset class,
the DataRelation's Nested property is set to true. Reading a conforming XML
data document works fine, but when I create new child elements and write out
the full dataset, the new child elements are not nested in the parent.
For example, Staff and StaffMember are related by the DataRelation
relationStaff_StaffMember whose Nested property is true. After adding a new
StaffMember, "Lucas Gavitt", to the StaffDS dataset, the resulting file
looks like this:
<?xml version="1.0" standalone="yes"?>
<StaffDS xmlns="http://mydomain.com/StaffDS.xsd">
<Staff>
<StaffMember FirstName="Holli" LastName="Elliott">
<IsTeacher>true</IsTeacher>
<IsActive>true</IsActive>
</StaffMember>
<StaffMember FirstName="Kristin" LastName="Barker">
<IsTeacher>true</IsTeacher>
<IsActive>true</IsActive>
</StaffMember>
</Staff>
<StaffMember FirstName="Lucas" LastName="Gavitt">
<IsTeacher>true</IsTeacher>
<IsActive>true</IsActive>
</StaffMember>
</StaffDS>
Any reason why the Nested property would be ignored for the new element?
Thanks!
Kristin Tag: test Tag: 95041
OLEDB data reader not reading Extended ASCII values properly -Help
In our intranet website Login passwords are encrypted using a=
function in Login=2Easp and stored in Oracle database=2E When a user=
logs in his password information are retrieved from the oracle=
table with recordset object, then we encrypt the password=
entered by the user with same function and match both values,=
accordingly user may allow or deny login=2E Now we started working=
on a new project using ASP=2ENET but OLEDB data reader not reading=
the ASCII values of password correctly=2E I tried with both Oracle=
connection and OLEDB connection
We are using =2ENet framework 1=2E1 version
Please help me to overcome this problem; otherwise I have to=
change the encryption in existing web application which is=
stabilized and running since 2001
-----------------------
Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/)
<Id>5K+wzhK910KAvisPqp6dEA=3D=3D</Id> Tag: test Tag: 95040
OledbCommand queries
I thought that a parmeterized query to an access database utilized
parameter position and not name. However, I find that I can not get a
successful ExecuteNonQuery() result unless the names of the parameters
added to the oledbcommand.parameters.add("Name", OLEDBType) statements
match the name of the parameter in the access database. Needless to
say, position also is required. Am I getting this wrong or do name in
addition to position matter?
Thanks, E Bender Tag: test Tag: 95036
output parameter values not displaying properly
I have a SQL Server stored procedure that outputs 4 parameters and a
recordset. They are outputting just fine in Query Analyzer, but come out as
[@gcLatestNav] = 1/1/0001
[@pubLatestNav] = 0
[@gcPreviousNav] = 1/1/0001
[@pubPreviousNav] = 0
-- Here is my stored procedure --
Create Procedure fn3MainNav
@gcLatestNav int = 1 output,
@pubLatestNav smalldatetime = '5/5/2002' output,
@gcPreviousNav int = 1 output,
@pubPreviousNav smalldatetime = '5/5/2002' output
as
SELECT TOP 1
@gcLatestNav = gc1.gc_id,
@pubLatestNav = gc1.published
SELECT TOP 1
@gcPreviousNav = gc1.gc_id,
@pubPreviousNav = gc1.published
FROM general_content gc1
-- Here's the relevant section of my .aspx code --
<% = pubLatestNav %>
<% = gcLatestNav %>
<% = pubPreviousNav %>
<% = gcPreviousNav %>
-- Here's my code behind --
Public Class mainNav
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
"server=myserver_2;database=mdb;uid=sa;pwd=mypwd;"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "fn3MainNav"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatestNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output, False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubLatestNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcPreviousNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output, False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubPreviousNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
End Sub
Public gcLatestNav As Integer
Public pubLatestNav As String
Public gcPreviousNav As Integer
Public pubPreviousNav As String
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
gcLatestNav = Me.SqlCommand1.Parameters("@gcLatestNav").Value
pubLatestNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubLatestNav").Value,
DateFormat.ShortDate)
gcPreviousNav = Me.SqlCommand1.Parameters("@gcPreviousNav").Value
pubPreviousNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubPreviousNav").Value,
DateFormat.ShortDate)
End Sub
End Class
from database right but showing
--
_____
DC G Tag: test Tag: 95027
Sync with desktop app
Hi
We have an app with access db backend which is used to enter daily orders.
Is it possible to upload these orders daily to a windows mobile 2003 device
(we are using xda http://www.my-xda.com/), to allow each client to sign
their order on delivery on the wm2003 device and then download all accepted
orders back to the desktop app? What I am not sure is how to upload/download
data from desktop (or server) to the wm2003 device and how to detect and
record client signature.
The xda's connect to individual PCs through usb and also connect to the
windows 2003 server uisng gprs.
Thanks
Regards Tag: test Tag: 95026
"Failed to get schema for this stored proceure"
I'm trying to configure an SqlDataAdapter to a stored procedure that has no
recordsets, but rather a handful of output parameters. VS.Studio gives me
"Failed to get schema for this stored procedure"
--
_____
DC G Tag: test Tag: 95019
datagrid it's not showing relationships
Hi. I have a strong type dataset. I have populated data in it. I added
relationships because the generated code it's not including it.
My problem is that I added the relations for some tables, not all of them
but when binding the datagrid the relationships are not appearing.
What can I do ? Tag: test Tag: 95017
Change password programatically for access database
Hi there,
I was wondering if it is at all possible to programatically change a
password for an access database using VB.NET? Do I use a specific SQL
command to perform such a task? Thanks in advance.
Nick. Tag: test Tag: 95016
certain column is unchangeable
Hi, all
I have three columns in a datagrid, so what I need to do to make certain column
unchangeable? For example, user can view column 1,2 and 3, but they can not
change data in column 1.
TIA. Tag: test Tag: 95007
How do I update child keys when I update their string data?
I'm new to VB .NET and I've made an Access database with several
one-to-many relationships. I want to display the data in a datagrid
and give users the ability to edit the database. It's simple enough
to import the database into a DataSet and display the string data that
the keys point to using SQL INNER JOIN statements, but once the data
has been updated, how do I use the new string data values to update
the child keys that need to be returned to the database? Do I need to
import the parent tables in their entirety and make a relational
dataset? If anyone could point me to some example code that would be
very helpful. Tag: test Tag: 95004
Lots of code
Hi All,
I have a connection and a table called Settings.
Now i want to execute "Select Value from settings where setting='CID'" read
this value Add One to this value and store it.
Do i need 10 lines of code to do this or did i miss some cool feature
Rob. Tag: test Tag: 95001
Problems with Password Protected Access DB
I can access a password protected mdb file just fine when I write a VB
dotnet windows application.
However using the same connection string and running in a VB dotnet web
application I get an exception:
"The Microsoft Jet database engine cannot open the file
'C:\Inetpub\wwwroot\fpdb\NewGoodGas.mdb'. It is already opened exclusively
by another user, or you need permission to view its data."
It is not already opened so it must be the permission thing.
Any ideas?
Dan
P.S. Sorry for the double posting - didn't know which ng this should go to. Tag: test Tag: 95000
how to access and display an output parameter
I would like to know how to set an output parameter to a variable and output
it on a web form:
This didn't work:
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatest", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, ???THEVARIABLE???))
<% = THEVARIABLE %>
--
_____
DC G Tag: test Tag: 94993
In memory sort a DataTable in DataSet
Hi,
I have a DataSet, returned by a stored procudure, which has only on
DataTable. At this step there is no sort in DataTable. Is there is a way I
can do to sort DataRows in this DataTable (not from select statement of sql
query) in memory?
Thanks
--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy Tag: test Tag: 94991
help trying to pass in a parameter
I am trying to pass an input parater to SQL Server. The parameter is a URL
querystring variable. I can't seem to get it to pass a variable, only a
literal value...
Help!
In the following example, the @archiveType parameter accepts the "all"
sample value. If I replace "all" with Request.QueryString("archiveType"),
it doesn't work. What am I doing wrong?
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[myProcedure]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@archiveType",
System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, "all"))
---------------------------------
Here's the procedure:
CREATE PROCEDURE myProcedure
@archiveType varchar(5)
as
SELECT col1
FROM table1
WHERE col1 = @archiveType
--
_____
DC G Tag: test Tag: 94983
SQLDbType Error. Need quick solution please.
I'm using abstract interfaces to implement my business solution, and can't
seem to find a way to pass SmallMoney to my stored procedure without casting
up to a SqlDataParameter.
I've tried DbType.Decimal, DbType.Currency, and DbType.Double.
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "MyProc";
cmd.CommandType = CommandType.StoredProcedure;
{...}
IDataParameter pqty = cmd.CreateParameter();
pqty.ParameterName = "@Quantity";
pqty.DbType = DbType.Currenccy;
{...}
cmd.Parameters.Add(pqty);
s.Add(ppolldate);
cmd.Prepare();
{...}
cmd.ExecuteNonQuery(); //<-- Code breaks here.
I get the infamous System Error with the following Inner Exception:
"Error converting data type money to smallmoney."
Please Help. Thanks.
--ROBERT Tag: test Tag: 94982
SP fails, but OleDbCommand does not throw error!
BlankI have found a major problem with the OleDb portion of ADO.NET. If you
are using it to call a stored procedure and the procedure has an internal
error of some type, the sp will fail, but the OleDbCommand object will not
throw any type of error.
For example, I have a proc that does an insert and I pass in a value for one
of the parameters that will cause a foreign key violation. The call the
proc will complete but nothing will be inserted. I can create that same
insert statement and call it with an OleDbCommand object and will get the
error.
Other than a return value, does anyone know how to get a call to an SP to
throw an error when it fails??????
--
John Mark Howell Tag: test Tag: 94980