Two DataViews of the Same database Table but different columns
Hi,
I'm new to ADO.Net and I want to DataViews containing different columns
of the same database table. Is it possible without creating 2 dataadpaters?
Thank you,
Scott Tag: About Load-on-Demand ADO.NET skill. Tag: 129509
sql server 2005 crashes by sqlbulkcopy when the column size does not match
dear all,
i am having this strange problem.
What i have done is i load a datatable to the sql server 2005 using
the sqlbulkcopy. when the column size of the destination table is
less than the value of the mapped column of the datatable instead of
throwing exception the sql server 2005 instead crashes. the only
exception that gets caught is the transport level error( TCP Provider,
error: 0 .......)
what is happening ???? i am so confused.
here is the part of the code:
.........
SqlConnection oSQLConn = new
SqlConnection();
oSQLConn.ConnectionString =
Config.GetConnectionString();
oSQLConn.Open();
try{
System.Data.SqlClient.SqlBulkCopy bc=
new System.Data.SqlClient.SqlBulkCopy(oSQLConn,
SqlBulkCopyOptions.TableLock, null);
..........................
............................// other part of the code the initializes
the
// DataTable with name dataTable
bc.BatchSize = 500;
bc.DestinationTableName = "desttable";
bc.WriteToServer(dataTable);
bc.close();
} catch(Exception exe){}
.....
if anyone knows what is wrong.. or what is going wrong then it
would be a great help to me.
this is causing a lot of headache....
regards,
kamal Tag: About Load-on-Demand ADO.NET skill. Tag: 129504
How to udpate and alter procedure at once.
I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read from
a text file.
I want to sql to update a record in myTable, and alter a procedure.
The text file is like:
--------------------------------------------------------------------------------
UPDATE myTable set Col1='text'
ALTER procedure [dbo].[EmptyData]
as
Delete from Acc;
Delete from BodyMind;
Delete from Checks;
Delete from CheckTemp;
--------------------------------------------------------------------------------
when I execute the ExecuteNonQuery, it result into an error: The Alter
command must in the first line.
But if I put the Update command at last line, it become a part of the ALTER
procedure.
How can I udpate and alter procedure at once. Tag: About Load-on-Demand ADO.NET skill. Tag: 129491
Loading data into an Access Table from CSV or XML
I have lots of old data files, which I am adding to an Access Database.
I read somewhere, that you can select directly on a CSV file or XML by
changing the Connection String - Extended Property - or I could have
been dreaming.
So to save a lot of coding and before I break everything, is it
possible to do something like the following
COPY CSV into an Access Table
INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from mycsv.csv
COPY XML into an Access Table
INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from myxml.xml
Using OleDbCommand.executeNonQuery
If it is possible, some sample code would be very helpful
Thanks for any help - Richard Tag: About Load-on-Demand ADO.NET skill. Tag: 129490
create *.cub file from DataSet
Hi,
The environment is .NET 1.1, VS2003. (can also be .NET
2.0 +VS2005)
Suppose I have downloaded a DataSet from the server to the client machine.
Now I want to create a *.cub file from this DataSet and save it to disk.
I tried using ADOMD.NET but dodn't know how to connect to a DataSet.
Is it at all doable?
Thanks
GM
P.S. I have already asked this question on sqlserver.olap group, but still
didn't get any response. Tag: About Load-on-Demand ADO.NET skill. Tag: 129489
HELP!!! Error installing SQL Express 2005....
I have VS 2005 Pro installed, but I tried to install SQL Express (as I did
not know if the "developer" edition that installs with 2005 Pro was the same
as my customers would be using).
I have got to the "SQL Server Database Services" line in the setup progress
screen, and I get "SQL Server Setup has encountered the following problem:
[Microsoft][SQL Native Client]Communication link failure. To continue,
correct the problem, and then run SQL Server Setup again."
I'd be happy to "Correct the problem..." if the error msg actually told me
what it was or how to fix it.
When I copied the text I also got this link (which basically says "we don;t
know")... "For help, click:
http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=60009&EvtType=lib%5codbc_statement.h%40sqlStatement%40OdbcStatement%3a%3aexecute_batch%40HotfixDBEINST%4013%40xea69 "
I am leaving everything right where it is (and, yes, I backed up my system
before embarking upon this little adventure).
Anybody seen this one? Tag: About Load-on-Demand ADO.NET skill. Tag: 129488
Send Recieve data to insert into tables using Socket
Hi,
I have to develop a Client Server application. What I want is that the
server will listen on a specific port and client will connect to the server
and then send a request to retrieve/insert some data from/into the database.
Database is placed on server side.
What is the best way to send and retrive a large amount of data from a
database over the sockets?
Thanks in anticipation.
Regards,
Ahmad Jalil Qarshi Tag: About Load-on-Demand ADO.NET skill. Tag: 129487
how to set the value of the parameter for the SelectCommand?
Hi,
The gridview linked to this datasource shows nothing. The problem is the
value of the variable 'a' which is not passed, i'm afraid ...
The code-behind contains the value to be passed:
public a As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load
a = "phil"
End Sub
the aspx contains:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Provider=....."
ProviderName="System.Data.OleDb"
SelectCommand="SELECT [name],[city] FROM [mytable] where [name]= @name">
<SelectParameters>
<asp:Parameter Name="name" DefaultValue=a />
</SelectParameters>
</asp:SqlDataSource>
Thanks
Phil Tag: About Load-on-Demand ADO.NET skill. Tag: 129486
SQL Connection still working after KILL the process in SQL
I would like to know why is this happening:
We do have a multithreading windows application in C#. Each thread
establish its own SQL connection to an SQL 2003 server,. Those are
worker threads for remote proccess, and each one needs to mantain its
own SQL connection and have connection pooling turned off due to company
rules.
There is also an SQL job in the database, which look for idle connections
and KILL them (KILL spid). However, some times the remote "idle" process
wasn't "idle", and tries to access again the database. The connection then
fails with an expected "General Network Error".
However, the conection does *STILL* working, at least from a code
perspective: Still doing queries, still getting data, etc, just that error
the *first time* it is used since its process ID was KILL in the database.
We did some review, and found that a new connection is made each time we
use the connection, and immediately disconnected upon completition...
The new connection does have the same connection string that the killed
one, and connection pooling is disabled ( pooling=false; ) for all the
connections .
Why is this happening?
Is there a way to avoid it ?
************************************************
Here's part of the code. It is not the whole code, nor working, just to show
how i am using the connection. All of this is inside a worker thread
----------------------
string scad = "";
scad = string.Format("server={0:s};database={1:s};user id={2:s};pwd={3:s};",
gv.SVRTerm, gv.BDTerm, cve_usuario, password);
scad += string.Format("pooling=false;");
-------------------------
oConn = new SqlConnection(scad);
oConn.Open();
-----------------------
while (true == bActivo){
-----------------
try{
// sql is the process to realize in the server
// it's taken from an string array with predefied queries
using(SqlCommand ocmd = new SqlCommand(sql, oConn)){
ocmd.CommandType = CommandType.Text;
using(SqlDataAdapter oda = new SqlDataAdapter()){
oda.SelectCommand = ocmd;
oda.Fill(oDS);
oda.Dispose();//
}
ocmd.Dispose();
}
-----------------------
}catch(SQLException ex){
log(ex);
}
----------------
}
The connection it's established once at the thread start, an closed at
thread end.
The thread must not crash nor hang over no one circumpstance (and it has not
hung nor crash AFAIK ) . In the worst case will log and send a message to the
remote server, for that one kill the client process of this thread, and
shutdown itself. Tag: About Load-on-Demand ADO.NET skill. Tag: 129480
InvalidCastException in DataRow
Hello,
This is a compactframework windows app.
Iam trying read from a DataRow.
myTextBox.Text=myDataRow("MyColumn")
If there is a value for the myDataRow("MyColumn") then the statement
executes. But if the column is empty I get invalidCastException, When
casting from number etc..
I have tried CTYPE() or even check for "Is Nothing", just accesing the
column gives me the exception.
How can I prevent the exception from occuring. Underlying DataField is
nvarchar(250) from SQLCE databse.
Thanks
jay Tag: About Load-on-Demand ADO.NET skill. Tag: 129477
ANN: C# Online.NET Open Soure List
FOR IMMEDIATE RELEASE
The Open Source Movement has yet to play as critical a role in the
development of C# systems as it has in the development of Java
systems. Perhaps, it never will. But, for those who cannot afford
Microsoft licensing fees or who cannot wait for Microsoft to address a
particular domain, open source is a viable alternative.
C# Resources - Open Source is a new list of C# open source projects
which are both free and open source. The recent sale of the JBoss
Application Server to Red Hat for $350 million illustrates vividly
that there is a difference.
C# Resources - Open Source is here:
http://www.csharp-online.net/csow/index.php?title=CSharp_Resources_-_Open_Source
If you know of a C# open source project which is not yet listed,
please add it under the appropriate category.
Visit C# Online.NET for the best online C# resource.
C# Online.NET
A wiki-based C# and .NET resource featuring articles, code snippets,
forums, study guides, and tutorials.
http://wiki.csharp-online.net/
http://blog.csharp-online.net/
http://forum.csharp-online.net/
[ ANN: C# Online.NET Open Soure List ] Tag: About Load-on-Demand ADO.NET skill. Tag: 129476
multiple types of queries and stored procedures
I'm using MySql and ADO.net
I find myself in a situation where I need to execute many different types of
queries. Basically they are all getting the same type of data, but with
different WHERE clauses and different levels of required data.
For example, one query "GetAllTest" will return ALL the test records in a
table and all of the fields.
Another query might be "GetRecentTests" which should only get the results
from the last 10 days and I don't need to get all the fields, just a subset.
I'm using a DataReader for performance reasons and my Dal layer is
populating business entities with the results from the DataReader.
My questions are:
1) What is the best practice for executing different types of queries with
different filter criteria? My Sprocs are pretty long and to make a handful
of them that vary only slightly (IE: WHERE _type LIKE 'Single', WHERE type
LIKE 'Single' AND ID > 100, etc) and all share the same joins and selects
seems like an incredible amount of redundancy.
I've thought about using straight SQL rather than sprocs, then I could have
a query builder class that could build the queries for me based on filter
criteria. I don't want to go away from sprocs if I don't have to, they are
so much more maintainable ;0)
2) Business entity population - I currently populate my business entities
in the body of my DAL method (IE: GetAllTests) - this poses the same problem
as #1, I would be repeating this code over and over for every DAL method
that is responsible for getting results with slightly different filters.
Seems like a ton of wasted code.
I thought about creating an ObjectBuilder class that would take a DataReader
and populate my objects for me, I could then use this in my various DAL
methods. Is there another way? How do you all do this?
These 2 problems must be something that many of you deal with on a regular
basis. Do you have any ideas for me?
I just though of one more, I could use a VIEW to keep all my joined results,
then I would query against the VIEW with my filters, that would save me some
redundancy in the sprocs I guess... but that VIEW would get pretty big as
time goes on.
I'm stuck, I need some hints, pointers, tips, clues, etc ;0)
Thanks for any help,
Steve Tag: About Load-on-Demand ADO.NET skill. Tag: 129475
why different syntaxes in Commnad lines?
Hi,
I saw three different syntaxes in command lines:
UpdateCommand="UPDATE [mytable] SET [name] = @name ...
UpdateCommand="Update `mytable` set `name`=? ...
UpdateCommand="UPDATE [mytable] SET [name] = ? ...
Does the three do the same?
Why those differences?
Thanks
Phil Tag: About Load-on-Demand ADO.NET skill. Tag: 129468
Query builder parameter problem
Using VS2005 Pro and dotnet 2.0
I am trying to add parameters in the query builder tool while configuring my
data adapter using the data adapter configuration wizard. I right-click
somewhere on the builder pane and then select properties. At the bottom of
the properties window, there is a line that says 'Query Parameter
Identification'. I click the ellipses to the right and I have three items to
change - Prefix characters, Suffix characters, Escape characters. I put a
Prefix character in the box (In this case I have tried the @ and % symbols),
click ok and close the properties box. So far so good.
Then in the filter column for the row I want to filter, I put the parameter
name I want - in this case @UserID. The builder puts an '=' sign in front.
Again, everything is fine.
I click the Execute query button at the bottom left and the builder asks me
for the parameter data. I input '222' and click ok. I get the results I was
expecting. Great! But now the problem starts.
I click Ok to exit the builder then click next on the data adapter
configuration wizard. I get the following error:
Generated Select statement
Error in WHERE clause near '@'
Unable to parse query text.
When I click finish, I get a message box saying "An unexpected error has
occurred. Error Message: Object reference not set to an instance of an
object."
I can't seem to get it to accept my parameters even though when I execute
the query within the builder, everything works as expected. Has anyone had
this same problem or can you recreate it?
Thanks,
Jay Tag: About Load-on-Demand ADO.NET skill. Tag: 129466
how to use a stored procedure of Access in VWD?
Hi,
I defined a query in Access.
I created a AccessDataSource in an aspx page using Visual Web Developer.
I specify Access, oledb etc ... and in the window "how do you like to
retrieve data from db?"
i choose "specify a cusstom sql or stored procedure".
I go to next, but here, the radio input of "stored procedure" is not
enabled.
How can i specify a stored procedure?
Thanks for help
Phil Tag: About Load-on-Demand ADO.NET skill. Tag: 129465
A severe error occurred on the current command. sp_AddMergePublication
This is a follow up to a posting I made which I can no longer update
(http://groups.google.co.uk/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/272695bcd2e5e375/f2bbe776bafcb1e6#f2bbe776bafcb1e6)
Issuing the command "sp_addmergepublication" via ADO.NET against a SQL
Server 2000 SP3a instance results in the error "A severe error occurred
on the current command. The results, if any, should be discarded".
This is an issue with SQL Server SP3a and is resolved with SP4
Thanks Tag: About Load-on-Demand ADO.NET skill. Tag: 129463
Can't fill dataset whith varchar(350)
Why is it that when I try to fill my dataset with a column of type
varchar(350) I get a error message "Object reference not set to an instance
of an object? I my application bring back only 250 characters in the column
and it works. I can query all of the data into Excel using my ODBC, so what
do I need to do with the ADO dataset object to get it to play along?
Detailed Error Message:
Server Error in '/BIC_WEB_TEST' Application.
--------------------------------------------------------------------------------
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.
Source Error:
Line 30: baseconnection.ConnectionString = ConnectString
Line 31: baseconnection.Open()
Line 32: BICDataAdapter.Fill(BICDataSet, "screening")
Line 33: End Sub
Line 34:
Source File: c:\inetpub\wwwroot\BIC_WEB_Test\ODBCTest.aspx.vb Line: 32
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an
object.]
System.Data.Common.Odbc32.SQLGetData(HandleRef StatementHandle, Int16
ColumnNumber, Int16 TargetType, HandleRef TargetValue, IntPtr BufferLength,
IntPtr& StrLen_or_Ind) +0
System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb)
System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
BIC_WEB_TEST.ODBCTest.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\BIC_WEB_Test\ODBCTest.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET
Version:1.1.4322.2032 Tag: About Load-on-Demand ADO.NET skill. Tag: 129460
newbie working with generated code (vb.net & visual studio 2003)
Hi,
I'm using VB.Net to create a windows form that will insert data from an
xml file into an SQL Server Express database.
As I'm new to VB.Net, i'm using the drag and drop method in Visual
Studio 2003 for datasets, dataadapters etc.
I open a file and insert photo details about an employee.
The employee will have more than one photo/photo details.
I have a function that opens the file, counts the amount of photos
related to the employee in the xml file and then inserts into the
database.
<employee_detail>
<lastname>smith</lastname>
<firstname>tom</firstname>
</employee_detail>
<photo_detail>
<filename>\my documents\my pictures\hpim0025.jpg</filename>
<description>la;ksdfj</description>
</photo_detail>
<photo_detail>
<filename>\my documents\my pictures\hpim0026.jpg</filename>
<description>alkjf</description>
</photo_detail>
I have two tables in the database, employee_detail and photo_detail
I'm using autogenerated primary keys in both tables (empid, photoid)
I'm posting empid as a foreign key to photoid
I use SELECT @@IDENTITY AS empid
This works okay if i've just got one photo_detail element but i want to
use more than one.
I took the code from the "Windows Generate Region" and put it in my own
function. After reading in the file into my DataSet, I count the amount
of photo_detail elements and want to do that many inserts into the
database.
Am I allowed do the following:
*********************************************************************
'cut from "Windows Generated" and pasted into Private Sub Open_Click
() function
Me.SqlDataAdapter7.InsertCommand = Me.SqlInsertCommand7
Me.SqlDataAdapter7.SelectCommand = Me.SqlSelectCommand7
Me.SqlDataAdapter7.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "photo_detail", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("photoid", "photoid"), New
System.Data.Common.DataColumnMapping("empid", "empid"), New
System.Data.Common.DataColumnMapping("filename", "filename"), New
System.Data.Common.DataColumnMapping("description", "description")})})
Me.SqlSelectCommand7.CommandText = "SELECT photoid, empid, filename,
description FROM photo_detail"
Me.SqlSelectCommand7.Connection = Me.SqlConnection2
Me.SqlInsertCommand7.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@empid", System.Data.SqlDbType.Int,
4, "empid"))
Me.SqlInsertCommand7.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@filename",
System.Data.SqlDbType.VarChar, 2147483647, "filename"))
Me.SqlInsertCommand7.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@description",
System.Data.SqlDbType.VarChar, 2147483647, "description"))
For st As Integer = 1 To val Step 1
Me.SqlInsertCommand7.CommandText = "INSERT INTO photo_detail(empid,
filename, description) VALUES (@empid, " & _
"@filename, @description)"
Me.SqlInsertCommand7.Connection = Me.SqlConnection2
Next
I then use SqlDataAdapter7.Update(DataSet1)
At the moment I'm just getting null in the foreign key empid field of
the photo_detail table.
Is it possible to use the code from "Windows Generated" elsewhere in
the code ? Is this a completely stupid way of doing this ?
I wanted to get a handle on .Net and thought that the drag and drop
methods would be a quick way to ease into it, for what would seem a
simple problem.
Apologies if i've phrased this badly and many thanks in advance
alan Tag: About Load-on-Demand ADO.NET skill. Tag: 129446
Connection remain open
hi everybody
I have an assembly that contain Datasets, this DataSets connect to one SQL
server 2000 database and retrive information to be use for a web Service. The
problem is that when the Datasets (TableDataAdapter) connect to the database
this leave one connection open (for each user), I used the using and Dispose
keyword for try to close this connection but nothing. Somebody know how to
fix this. I am using Net 2.0
Best Regards
Victor Hugo Pozo
MCAD Net Tag: About Load-on-Demand ADO.NET skill. Tag: 129432
Datatable Datetime column remove zeroes
I have added calculated column to datatable and expression looks like this:
"somecolumn + datetimecolumn1"
now, i do not want zeroes from datetime, just date.
How to do it?
Thanx.
my DataTable is filled via adapter from SqlServer. Tag: About Load-on-Demand ADO.NET skill. Tag: 129427
datagridview
Hi,
How to put conbobox column or any other column into datagridview.
please provide me with code in c#.
Thanks in advanc
--
mahimam_200
-----------------------------------------------------------------------
mahimam_2004's Profile: http://www.hightechtalks.com/m1
View this thread: http://www.hightechtalks.com/t37752 Tag: About Load-on-Demand ADO.NET skill. Tag: 129425
Executing MS Access Queries using ADO.NET
I'm currently trying to run queries in an Access database using
ADO.NET. My code works when executing a query whose name has no spaces
but errors out if I try to run a query whose name has spaces in it.
Example:
When running a query named: "My Query" I receive an error
message saying a query with the name "My" cannot be found.
Is there any way to get around this problem?
Thanks. Tag: About Load-on-Demand ADO.NET skill. Tag: 129423
Connection
I mm developping a winforms application. I use a form do include, alter and
delete data. I created a connection using the wizard. I would like that each
user uses their own password to log in the SQL Server. I noticed the the
connection string is in a file named APPNAME.EXE.CONFIG, and the
XXXDataSet.Designer.cs file reads this connectionstring to log into the SQL.
I would like to know if there is a way to change this connection string, at
runtime, except changing the APPNAME.EXE.CONFIG file. Modifing the
APPNAME.EXE.CONFIG works fine. The problem is that the password of the last
user remain in the file. Tag: About Load-on-Demand ADO.NET skill. Tag: 129421
where clause
I have an arbitrary number of values in the WHERE clause:
select foo
from bar
where
foobar = '1'
and foobar = '2'
...
and foobar = 'n-1'
and foobar = 'n'
What's the best way to program this?
Thank you. Tag: About Load-on-Demand ADO.NET skill. Tag: 129420
invalid path error while accessing FOXPRO table from c#
HI All,
i have a problem accessing a DBF file from c#. this is the code i have
specified
string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\mmyyyy.dbf;Extended Properties=DBASE IV;";
OleDbConnection objConnn = new OleDbConnection();
objConnn.ConnectionString = strConnection;
try
{
objConnn.Open();
}
catch(Exception eee)
{
MessageBox.Show(eee.ToString());
}
objConnn.Close();
it says
System.Data.OleDb.OleDbException: 'c:\mmyyyy.dbf' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at WindowsApplication2.Form1.button3_Click(Object sender, EventArgs
e) in d:\documents and settings\administrator\my documents\visual
studio projects\windowsapplication2\form1.cs:line 426
it is strange because i have been connection to the local computer not
a networkd one and + i have been accessing this since past few days
without a problem
i have installed Microsoft's oledb drivers for foxpro + MDAC
PN :- i have tries this connection string also
"Provider=VFPOLEDB.1;Data Source="C:\mmyyyy.dbf;"
thanks,
abhay. Tag: About Load-on-Demand ADO.NET skill. Tag: 129413
ADODC refresh method fails after upgrading from XP SP1 to XP SP2
After upgrading from XP SP1 to XP SP2 get "operation is not allowed when the
object is open" error messege. the app in production stop working properly.
The refresh method is available, but fails. Here is the code:
With Adodc1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
Info=False;Data Source=" & App.Path + "\PhoneNumbers.mdb" & ";
Mode=Read|Write"
.RecordSource = "select * from PhoneNumbers Order By Name"
.Refresh
End With
I need a solution in production,i.e I won't install the IDE in production.
Any ideas?
Tony Tag: About Load-on-Demand ADO.NET skill. Tag: 129395
Provider independent data access: creating a data adapter from a
I have written a provider independent ado.net utility class for
executing queries. One of the routines looked like this in .net 1.1:
static public DataSet ExecuteQuery(IDbConnection connection, String sql)
{
connection.Open();
DataSet result = new DataSet();
IDbDataAdapter adapter = /* magic to create an adapter from a
connection*/
// The sybase adapter's implement IDisposable, so wrap with a using.
using(adapter as IDisposable)
{
adapter.SelectCommand.CommandText = sql;
adapter.SelectCommand.Connection = conn;
adapter.Fill(result);
}
return result;
}
The magic code to create the adapter was ugly: it essentially performed
a dictionary lookup on the type of connection, and created the resulting
adapter type. If IDbConnection had a method called CreateAdapter,
analagous to CreateCommand, this would have been cleaner.
With the introduction of ADO.NET 2.0 and the DbProviderFactory class, I
was hoping that some of the ugliness would go away. Unfortunately, I am
running into some problems. Namely
1- DbConnection still does not have a CreateAdapter method
2- DbConnection does not offer a way to get back to the
DbProviderFactory that created it.
So I still cannot create the correct adapter given only a generic
DbConnection. I am looking for suggestions on how to code my routine. So
far, I have thought of these
1- Make the user pass in the DbProviderFactory that goes with the
connection. CONS: forces users to use DBProviderFactory. We have a lot
of code that doesn't.
2- Stick with the dictionary lookup (Dictionary<ConnType, AdapterType>.
CONS: DLL dependencies explode.
3- Use a different dictionary lookup (Dictionary<ConnType,
DBProviderFactory>. CONS : A little messy, but better than 2.
4- Don't use provider specific adapters at all, just use a generic one.
Like this:
class GenericAdapter : System.Data.Common.DbDataAdapter
{
}
static public DataSet ExecuteQuery(DbConnection connection, String sql)
{
connection.Open();
DataSet result = new DataSet();
GenericAdapter adapter = new GenericAdapter();
adapter.SelectCommand = conn.CreateCommand();
adapter.SelectCommand.CommandText = sql;
adapter.Fill(result);
return result;
}
I have tested this with two databases, Sybase ASE and ASA, and it seems
to work. Are there any serious downfalls to not using the vendor
specific adapter type? Is this code safe?
H^2 Tag: About Load-on-Demand ADO.NET skill. Tag: 129393
scope_Identiy() problem vs @@Identity with daAdapter.InsertCommand
Hello,
I am having a problem using Scope_Identity() when inserting a row to a sql
server (sql server 2000 standard Edition) table using a sql
DataAdapter.InsertCommand. The row gets inserted but it does not display on
the datagridview on my client app (vb2005). I just get a blank row. But if
I use @@Identity in the return statement then the new row shows up correctly
in the datagridview. The code I am using follows. Note: I used this same
code on Sql server 2000 personal edition - loaded on my workstation, and I
did not have a problem using Scope_Identity(). But on the Standard Edition -
which is server based - I have a problem using Scope_Identity(). The newly
inserted row shows up in the datagridview but does not display any data when
I use Scope_Identity(). If I clear the dataset (ds) and refill it - then
the new row shows up with the new data. If I use @@Identity then the new
rows shows up and displays the new data in the datagridview right away. Is
there something I need to change when using Scope_Identity()?
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim dr As DataRow, i As Integer
dr = ds.Tables("tbl1").NewRow
ds.Tables("tbl1").Rows.Add(dr)
da.InsertCommand = New SqlCommand
da.InsertCommand.Connection = conn
'--this insert command returns data correctly
'da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1,
@p2); Select tID, tName, fld2 from tbl1 Where tID = @@Identity"
'--this insert command does not display any return data
da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1,
@p2); Select tID, tName, fld2 from tbl1 Where tID = Scope_Identity()"
da.InsertCommand.Parameters.Add(New SqlParameter("@p1", SqlDbType.VarChar,
50))
da.InsertCommand.Parameters.Add(New SqlParameter("@p2", SqlDbType.VarChar,
50))
da.InsertCommand.Parameters("@p1").Value = "test5"
da.InsertCommand.Parameters("@p2").Value = "test6"
da.Update(ds, "tbl1")
'--this will write the new Identity value when using @@Identity but not
Scope_Ident
Console.WriteLine("*" & dr.Item(0).ToString & "*")
End Sub
Thanks,
Rich Tag: About Load-on-Demand ADO.NET skill. Tag: 129372
insert parent-child rows with transaction
hi,
i have a typed dataset containing two data tables and a data relation
which links the two tables in a parent-child manner.
i use a sqltransaction to control the insertion of records into the
database.
here is the code for the insertion.
foreach (SeminarData.ActivityRow activity in dsSeminar.Activity) {
int newActiviyId = InsertActivity(activity);
foreach (SeminarData.ActivitySpeakerRow speakerRow in
activity.GetChildRows("relActivity_ActivitySpeaker")) {
speakerRow.ActivityId = newActiviyId;
InsertActivitySpeaker(speakerRow);
}
}
the two Insertxxx() methods each takes a datarow and sets up a
datacommand and executes the command.
after i have inserted a parent row, i take the returned
auto-incremented ID of the parent row, assign it to the child row, and
try to insert the child row.
however, because this is done in a transaction, the new ID of the
parent row is probably not yet inserted into the database, so i keep
getting this error:
"ForeignKeyConstraint relActivity_ActivitySpeaker requires the child
key values (691) to exist in the parent table."
691, is this case, is the new ID of the parent row which is supposed to
have been inserted.
It seems that I may have two options,
1. commit the transaction before inserting the child row. but that
defeats the whole purpose of using transaction as the first place.
2. Somehow update the in-momery parent rows with the new IDs. however,
when i try to assign the new IDs to each parent row, i get error
message saying that the ID column is read-only, cannot be updated,
.understandable.
So, how can i insert parent-child rows within a transaction?? I don't
want to go for a dataadapter, it would be too much change to the
dataaccess code. please enlighten me. Tag: About Load-on-Demand ADO.NET skill. Tag: 129367
OleDbDataAdapter.Fill throws an error (DB_E_BADROWHANDLE) when .NET 2.0 is installed.
When I use the OleDbDataAdapter.Fill to fill a DataSet with a
ADODB.Recordset an error occurs ("No error message available, result
code: DB_E_BADROWHANDLE(0x80040E04)."
If only the .NET Framework 1.1 is installed everything works fine.
What's wrong (or changed)? Tag: About Load-on-Demand ADO.NET skill. Tag: 129365
sorting in parent form
Hi
I'm trying to sort a parent form(vb.net), can anybody help
thanks
From http://www.developmentnow.com/g/7_0_0_0_0_0/dotnet-framework-adonet.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com Tag: About Load-on-Demand ADO.NET skill. Tag: 129358
sorting of parent forms
Hi
I can't seem to get the code right to sort the parent form, i can get the
child form to sort.
Can anyone help
Thanks
A
--
Message posted via http://www.dotnetmonster.com Tag: About Load-on-Demand ADO.NET skill. Tag: 129356
Implement ADO.NET Row Level Pessimistic Locking
I am trying to implement record level pessimistic locking using ADO.NET
and SQL Server 2000. A reduced structure of the table is shown:
CarrierName(varchar) CarrierCode(varchar) RefundStatus(int)
------------------------------------------------------------------------
--------
US Postal Service USPS 1
US Postal Service USPS 2
Canada Postal Service CPC 1
UK Postal Service RMG 1
UK Postal Service RMG 2
I have a win form application(C#), which on a button click selects only
the RMG records and then updates these records. The requirement is that,
during this operation these RMG records must be pessimistically locked
so that no other application can update these records during this time,
but the USPS and CPC records should remain updateable.
After browsing through different forums, I realised that ADO.NET does
not support pessimistic concurrency directly but there is a workaround
using transaction. This is how I implemented the solution:
private const string DBConnectionString = "Database=BnBCSO;
Server=10.20.1.19;UID=sa;PWD=rebaca;" + "Connection
Timeout=15;Enlist=False;Max Pool Size=150; Min Pool Size=5;";
private IsolationLevel oIsolationLevel = IsolationLevel.Serializable;
private string SelectStatement = "select * from PackageRefund where
CarrierCode = 'RMG'";
private void Process()
{
SqlConnection oConn = null;
SqlTransaction oTrxn = null;
SqlDataAdapter oDataAdapter = null;
DataSet ds = new DataSet();
try
{
oConn = new SqlConnection( DBConnectionString );
oConn.Open();
oTrxn = oConn.BeginTransaction( oIsolationLevel );
SqlCommand oCommand = new SqlCommand(
SelectStatement, oConn, oTrxn );
oDataAdapter = new SqlDataAdapter( oCommand );
oDataAdapter.Fill( ds );
for( int i = 0; i < ds.Tables[0].Rows.Count; i++ )
{
Thread.Sleep( 5000 );
ds.Tables[0].Rows[i]["RefundStatus"] = 4;
}
SqlCommandBuilder oCommandBuilder = new
SqlCommandBuilder( oDataAdapter );
oDataAdapter.Update( ds );
oTrxn.Commit();
}
catch( Exception exc )
{
oTrxn.Rollback();
}
finally
{
if( oDataAdapter != null )
{
oDataAdapter.Dispose();
oDataAdapter = null;
}
if( oTrxn != null )
{
oTrxn.Dispose();
oTrxn = null;
}
if( oConn != null )
{
oConn.Close();
oConn.Dispose();
oConn = null;
}
}
}
private void cmdStart_Click(object sender, System.EventArgs e)
{
try
{
Thread oThread = new Thread( new ThreadStart(
Process ) );
oThread.Start();
}
catch( Exception exc )
{
}
}
The issue is that after I have started the program, I go to SQL Query
Analyzer and execute these sql statements separately:
update [TABLE] set RefundStatus = 3 where CarrierCode = 'RMG'
update [TABLE] set RefundStatus = 3 where CarrierCode = 'USPS'
update [TABLE] set RefundStatus = 3 where CarrierCode = 'CPC'
If record level pessimistic locking was properly implemented, then the
USPS and CPC updates should have succeded while the RMG one should
wait/timeout. But in this case, all the three update statements are
waiting and eventually timing out, possibly waiting for the lock to be
released. That means that this solution has implemented a table lock
rather than a row lock.
What do I have to change if I want only the selected records to be
locked while the other records in the same table should be updateable.
Thanks.
--
Sent via .NET Newsgroups
http://www.dotnetnewsgroups.com Tag: About Load-on-Demand ADO.NET skill. Tag: 129355
How to disable a Constrain
I am use the new feature of TableAdapter to develop a web application.
I use a ObjectDataSouce to link to a TableAdapter
The TableAdapter have two constrains.
How can I disable one of the contrain when the ObjectDataSouce get data? Tag: About Load-on-Demand ADO.NET skill. Tag: 129353
<<< State of the art web solution Online>>>
<<< State of the art web solution Online>>>
WickedInnovations has an extensive working experience in E-commerce
solutions, web site design , website development , graphic design ,
search engine optimization , database integration, multimedia
presentations, corporate Identity and a lot more.
Wicked Innovations have proven our ability to deliver professional and
state of the art web solutions for many e-business verticals.
Multimedia
Flash Animation
Demos/Presentation
Flash E-Commerce
Video Editing /Encoding
Web
HTML Websites
Flash Websites
User Interface Design
Web/ Flash Templates
Programming
Custom Web App Development
Script Installation & Revision
bsite Administration
Production Support
WickedInnovations specializes in, from complex integrated solutions for
business and advanced multimedia products.
Why spend more if you can get the same quality, productivity and
performance from an offshore partner?
http://wickedinnovations.com/contact.html
http://www.wickedinnovations.com/request.html Tag: About Load-on-Demand ADO.NET skill. Tag: 129352
Silent install for SQL Express?
If I develop a SQL Express DB for my app, how do I distribute SQL Express
also? Is there a silent install for SQL Express (like for MSDE)?
Also, how do I put my DB in the new SQL Express instance and attach to it
instead of to my local development copy? Tag: About Load-on-Demand ADO.NET skill. Tag: 129351
oos! I got an "invalid authorization specification" when testing my connection to Access. What's going on???
I've been working on this app for some time now. Been developing with vs 2005 vb.net and an Access database. Something happened and I can't figure
out what caused it or how to correct it.
I drop a OleDBAdapter on the form and the data adapter configuration wizard appears. I then click on "New Connection" to choose an Access db other
than the one being displayed. The "Add Connection" window appears and the data source box shows "Microsoft Access Database File (OLE DB)". Good so
far. I click on browse and select the Access db I want to use. The new file name appears in the database file box. I click on "Test Connection" and
the "invalid authorization specification" error pops up and prohibits me from setting up the new coonection.
There are no passwords on this db.
What did I do to cause this???
Thanks,
Hexman
P.S. In the Provider property is "SQLNCLI.1", which I can't change. Tag: About Load-on-Demand ADO.NET skill. Tag: 129336
RE: Update() and stored procedures
I want to update for one stored procedure. How? Here the exist stored, ..
create procedure abc as
select d2.maso,d2.namthang,d2.n1,d2.n2,d2.n3,d1.*
from dmns d1 left outer join bangchamcong d2
on (d1.maso=d2.maso and '200607'=d2.namthang)
and i want: ...
update proc abc set n1='1'
go
is it right? pls help me!
From http://www.developmentnow.com/g/7_2003_7_0_0_124105/Update-and-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com Tag: About Load-on-Demand ADO.NET skill. Tag: 129328
Load typed dataset
What is the preferred way to read (not write) the records of a typed
dataset in C# without use of a form?
thanks,
Jeff Tag: About Load-on-Demand ADO.NET skill. Tag: 129319
SQLHelper STored Procedure Output Parameter
Hi, All
How to get the output parameter's value when you use the SQLHelper
(Microsoft Data Access Block)?
When I try to access my ourput parm I got the following error.
System.NullReferenceException: Object reference not set to an instance
of an object.
I thought it is because there is a statement cmd.Parameters.Clear() in
ExecuteNonQuery function, but even I comment this parameter clear
statement, it still caught the same error.
Before ExecuteNonQuery return, The output value is still in the output
parm. but when return to SqlHelper.ExecuteNonQuery(), the output
parameter value got lost while the vlaue of input parameter is still
there.
Did I miss something? Or SQLHelper can not return output parameter?
Help Please
---------------------------------------------------------------------------=
=AD------------------
public string TestOutput(int pFlag){
SqlParameter[] parm =3D new SqlParameter[3];
parm[0] =3D new SqlParameter("@flag",
SqlDbType.Int,4);
parm[0].Value =3D pFlag;
parm[1] =3D new SqlParameter("@result_txt",
SqlDbType.VarChar,32);
parm[1].Direction =3D
ParameterDirection.InputOutput;
parm[2] =3D new SqlParameter("@result_txt",
SqlDbType.Int,4);
parm[2].Direction =3D
ParameterDirection.InputOutput;
// parm[2].Direction =3D ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(ConnectionString,
"UDSP_OUTPUT_TEST",
parm);
return parm[1].Value.ToString();
}
---------------------------------------------------------------------------=
=AD-----------------
CREATE PROCEDURE UDSP_OUTPUT_TEST
@flag int,
@result_txt varchar (32) output,
@result_code int output
AS
begin
set nocount on
if @flag=3D1
begin
select @result_txt =3D 'ERROR RETURNED FROM SP'
select @result_code =3D -1
return
end
else
begin
select @result_txt =3D 'SUCCEED RETURNED FROM SP'
select @result_code =3D 0=20
end=20
set nocount off=20
end Tag: About Load-on-Demand ADO.NET skill. Tag: 129309
System.Data.ObjectSpaces - Is this namespace included in framework 2.0
I am trying to figure out if object spaces are included in .net framework
2.0.
If so, I am trying to find the dll called System.Data.ObjectSpaces.dll
Does anybody know this? Tag: About Load-on-Demand ADO.NET skill. Tag: 129303
Silent install for SQL Express?
ANybody know how to do a silent install for SQL Express? I need to know if
this is possible to make my decision to use SQL Express or Access as a
database solution. Tag: About Load-on-Demand ADO.NET skill. Tag: 129302
Why choose SQL Express over Access?
Why would you choose SQL Express (which requires an installed application to
work) over the simplicity of an Access database which has no dependencies? Tag: About Load-on-Demand ADO.NET skill. Tag: 129296
Suddenly slow ado.net connections
Last week we were running some code that connects to two different SQL 7
servers. On Friday, the connection times were normal (almost instananeous).
Today, from all of our development boxes, the connections are taking 5-7
seconds to open. The app is written using the 2.0 framework.
In our code we
connect
do some work
disconnect
Operations that took seconds on Friday are taking nearly an hour today.
Our connection strings didn't change and we are using the native SQLClient
namespace.
For a test we decided to try creating a test app that opens a connection
then while that connection is open, open several more connections to see if
pooling comes into play. Oddly enough it seems pooling isn't being used.
Each connection still takes 5-7 seconds to open. The connection string we
are using is simply "Server=server;Database=db;Integrated Security=SSPI" -
nothing out of the ordinary.
We even sent so far as to create a test app in VB6 using ADO 2.7 to see if
it was something on the our network. That test app opens the connections as
normal (nearly instantaneous) so the problem appeared to be with ADO.NET
We then coded up the same simple test app in VS2003 and it ran just fine.
At this point we are at a loss and thinking something is up with ADO.NET and
the 2.0 framework on all our dev boxes.
Has anyone got any ideas on what we are seeing?
Here is our actual test app code (server and db changed for posting)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New
SqlClient.SqlConnection("Server=server;Database=db;Integrated Security=SSPI")
cn.Open()
End Sub
In VB6/ADO and VB.NET 2003/ADO.NET 1.1 this connects in less than one
second. In VB.NET 2005/ADO 2.0 this takes 5-7 seconds. Tag: About Load-on-Demand ADO.NET skill. Tag: 129294
Getting "A severe error occurred on the current command" after SP4
Codebase has been in place for 1.5 years. Current version running since
12/2005. First use of a command object that has 14 parameters.
Now I get this error on first use, subsequents work fine. sproc work
actually completes, but error is raised. Tag: About Load-on-Demand ADO.NET skill. Tag: 129292
Boolean datatype column refuses to Copy to Bit datatype SQL Table
I have a table that I generate the schema for by way of
da.FillSchema(ds,schemaType.Source, StringLoadTable)
One of my columns (numerous columns actually) are created as System.Boolean
however the actual SQL column that the schema is pulled from is of datatype
Bit. I can successfully add data to this table in my dataset using "True"
and "False" for the columns that are created as Boolean, however when I
attempt to do some BCP operations on the table the BCP fails because the data
is not of Bit type and cannot be converted from String to Boolean (or so says
the error). So instead of "True" and "False" I attempted to use 1 and 0 in
my data, however when I do this I can not add the data to the table because
""String was not recognized as a valid Boolean.Couldn't store <0> in
feetypeinvfg Column. Expected type is Boolean.""
--
Matt
www.Fiddelke.org Tag: About Load-on-Demand ADO.NET skill. Tag: 129284
Table.DisplayExpression
Hi,
What can you do with the Table.DisplayExpression property?
I tried to use this as a custom name for my table but only table columns are
allowed?
Thanks
Bart Tag: About Load-on-Demand ADO.NET skill. Tag: 129280
dataset question
i have bound a datset to a datagrid control. but on the dataset i have
hidden on of the columns, by using the following
data.Tables(0).Columns(0).ColumnMapping = MappingType.Hidden
my question is that i want to be able to allow the users the ablility to
change the values in the datagrid or add new rows.
My question is how do i add the values into the hidden column so that i can
update the database?
using vb. net 2003
Many thanks
Simon Whale Tag: About Load-on-Demand ADO.NET skill. Tag: 129279
Looking For Example That Opens Connection To Access Database At Run Time
I am looking for a small example program which will create, at
run-time, a connection to an Access database. I would like the example
to have a navigation bar and display a table from the database. Please
only post code for a working small program.
If necessary explain the code or how it was created.
Thanks,
Chris Lusardi Tag: About Load-on-Demand ADO.NET skill. Tag: 129278
I want to improve my C# ADO.NET skills. I want to study how to handle
Load-On-Demand and Paging Skills in C#. Is there any information to study?