Programatically unique
Hi,
I have have dataset on the client side and I would like to set it up so that
it cant take data entered that isnt unique on two columns. There is
obviously the unique property of each column , but they work indepedant, im
looking for something that will be unique on more than one column.
Thanks Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129635
Issue with Command Timed out with data adapter
When i try to fill a dataset with a adapter am setting the command timed out
to 15 seconds. but the querry is not timing out after 15 seconds. what may be
the issue?
Code i am using
------------------
SqlConnection objConn = new SqlConnection("Connection string");
objConn.Open();
SqlCommand objComand = new SqlCommand("select * from table1", objConn);
//Query will execute for 20 miniutes
objComand.CommandTimeout = 15; //This is not working
SqlDataAdapter sqlAdapter = new SqlDataAdapter(objComand);
//create a new dataset to hold our data
DataSet ds = new DataSet();
sqlAdapter.Fill(ds); Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129631
ADO.Net- Anyone know of a way to find all databases on my computer?
I'd like to find all the databases on my computer. In other words, would like
to find information similar to the info shown in Server Explorer in Visual
Studio .Net.
Anyone know of a way of doing this? Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129625
Created Dataset with two tables - How do I populate at runtime?
In VS 2005, I created a dataset with the dataset designer that has two tables
that have a parent-child relationship (which I also added in the designer).
Each table gets data from a stored procedure. I want to populate the dataset
at runtime, passing in a parameter from another control on the form, then
pass the dataset to a crystal report. And that is what I haven't figured out
- how to populate the dataset at run time. Can someone point me in the right
direction?
thanks
--
dchman Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129610
Programatically call extended stored procedure?
Hi,
C# / .NET Framework 1.1
I would like to query extended stored procedure like stored procedure...
However, I found that CommandType property does not have Extended Stored
Procedure value.....
I tried something like..
SqlConnection con = new SqlConnection(....);
SqlCommand command = new SqlCommand('xp_blahblahblah", con);
command.CommandType = CommandType.StoredProcedure;
.....
...
command.ExecuteScalar();
However, it always throw exception saying "Could not find store procedure
xp_blahblahblah"....
Is there any way I can use the extended stored procedure programatically?
Thanks.
..................................................................TJ Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129609
How do I compare 2 UniqueIdentifier Guids?
Hello. I have two databases, both of which store an objectGUID (from a
contact object) in a UniqueIdentifier Column.
How can I search a value picked up in one (using sqlreader) to a value
in the other table? For example, using the following:
"SELECT * FROM table1 Where objectGUID = '" & sqlReader3("guid") & "'"
This just tells me that I cannot compare objects. How do I cast them,
and what is the syntax for the sql command?
Thanks in advance! Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129605
newbie question
I have a collection of objects that can be bound directly as a data source
into a DataViewGrid... works great, but now I can't figure out how to get
the columns sorted in the order that I want, or how to hide certain columns.
Is this possible?
Paul Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129604
number of recs from select
Hi all,
I am trying to figure out how to retrieve the number of
records resulting from a select. I tried executereader
but the object does not contain the number iof records retrieved. Any
suggestions?
Thanks,
Carlos Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129603
question about client callback and gridview
Hi,
I read about client callback and i have a question.
Assume the option "sorting" is set in a gridview.
Each time the user clicks on a fieldname, the data are sorted.
My question is: are the data on each click fetched from the server, or are
they 'cached' on the client-side and so avoiding a lot of transferts
between server and browser?
If not 'cached', would it not bebetter to use the client callback
technology?
Thanks for explanation
Chris Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129599
SP w/ parameter using a DAL
Hi,
I am using the enterprise DAL that I downloaded from Microsoft. I am
trying to create a data table based SP with a passed parameter but I
keep getting a null exception error. I thought it was set-up correctly
but it doesn't appear data table is being populated. The code below
is what I have written but confused on the issue. Any help or
suggestion is greatly appreciated. I am new to C# and .Net so I am
going through growing pains
Error: An unhandled exception of type 'System.NullReferenceException'
occurred in classlibrary1.dll
Mark
private void cbPyramid_SelectedIndexChanged(object sender,
System.EventArgs e)
{
DataTable dt = new DataTable();
int i;
Department dsDept = new Department(cbPyramid.Text);
dt = dsDept.Get_Dept;
cbDept.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
cbDept.Items.Add(dt.Rows["Dept"]);
}
}
//Business object code
#region Private Members
private DataSet _dsDept;
#endregion
#region Public Properties
public DataTable Get_Dept
{
get { return _dsDept.Tables[0]; }
}
#endregion
#region Public Methods
public Department(string Pyramid)
{
//This will establish the database connection
Database db = DatabaseFactory.CreateDatabase("Test");
//This will allow you to call a SP and use parameters
DBCommandWrapper dbc =
db.GetStoredProcCommandWrapper("usp_Inv_GetDepartment");
dbc.AddInParameter("@pyramid", DbType.String, Pyramid);
db.ExecuteDataSet(dbc);
}
#endregion
} Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129598
Informix Drivers
I am using Informix ODBC to connect to my POS database. It works fine
and shows all the tables in Excel but when I connect to the ODBC in
Visual Web Developer it says Test Successed but doesn't show any tables
or other information. Any help on getting Informix working with Visual
Web Developer. Thanks. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129594
SQLTransaction IsolationLevel questions
Hello,
Sorry for posting this in more than one place, I meant to put it in
this newsgroup, not m.p.d.f.aspnet...
I have a VB.NET subroutine similar to the following:
Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal
UpdateConn As SqlConnection)
Dim trnDedupe As SqlTransaction =
UpdateConn.BeginTransaction(IsolationLevel.ReadCommitted, "Dedupe")
Dim cmdDedupe As New SqlCommand(UpdateSQL, UpdateConn,
trnDedupe)
Try
Dim iUpdated As Integer
iUpdated = cmdDedupe.ExecuteNonQuery
trnDedupe.Rollback()
Catch ex As Exception
Throw ex
Finally
If Not cmdDedupe Is Nothing Then cmdDedupe.Dispose()
End Try
End Sub
UpdateSQL looks like:
INSERT INTO MyTable1(Field1, Field2)(SELECT Field1, Field2
FROM MyTable1 WHERE MyId1 = 123);
UPDATE MyTable2 SET MyID1 = (SELECT MAX(MyID1) FROM MyTable1)
WHERE MyID1 = 123 AND MyID2 = 456;
I have a breakpoint on trnDedupe.Rollback(). I then switched to Query
Analyzer and tried to run a query SELECT * FROM MyTable1 WHERE MyID1 =
(SELECT MAX(MyID1) FROM MyTable1)
I started this query before I stepped away from my desk and it's been
running for 2-1/2 hours. Should I try another type of IsolationLevel
other than ReadCommitted. It looks like ReadCommitted might be good to
prevent someone else from inserting a record into MyTable1 in between
my INSERT and UPDATE. From the documentation:
--------------------------------------------------------------------------------------
ReadUncommitted:
Shared locks are held while the data is being read to avoid dirty
reads, but the data can be changed before the end of the transaction,
resulting in non-repeatable reads or phantom data.
ReadUncommitted:
A dirty read is possible, meaning that no shared locks are issued and
no exclusive locks are honored.
RepeatableRead:
Locks are placed on all data that is used in a query, preventing other
users from updating the data. Prevents non-repeatable reads but phantom
rows are still possible.
--------------------------------------------------------------------------------------
If I'm reading the documentation correctly, it looks like I might want
to use ReadUncommitted for my testing and ReadUncommitted when I'm
using the application in production to prevent inserts between my
INSERT and UPDATE. Would any of the other IsolationLevels be useful?
Thanks,
Eric Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129592
Need help with Case-sensitive queries in SQLServer 2000
I need to retrieve records just as they are and enforce case-sensitivy. For
example, my query return 'E1234' in place of 'e1234'. I want to return
exactly what is in the table.
How do I do that?
Thnaks
mo Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129590
Bindingsource c#
Hi there,
are there any good reference examples for using a bindingsource object ? I
new to ado.net and trying to create a simple maintenance app on an sql
mobile database.
I created a datasource and then dragged a table onto my form (Detail mode).
Where to go from there ?
thanx in advance. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129587
New to ASP.Net, Cascading DropDown List in Detail View...Please help
A) Destination Table with 4 Fields.
1) Last Name
2) First Name
3) State
4) Zip Code.
B) Look up table State/Zip Code with 2 Fields
1) State
2) Zip Code.
I created a DetailView Form in Asp.net to Insert/Update Destination
Table. Destination Table's State and Zip Code Fields are converted from
Bound Column to Item/Edit/Insert Template, Created 2 Drop Down Lists,
DDLState and DDLZIP. I am using '<%# Bind(...)%>' for both of them.
Also, I do have 2 SQLDatasource for DDL's (i.e. SqlState for DDLState
and SqlZip for DDLZIP).
My Intention is very simple:
When user select State Name from DDLState DropDownList List (example:
NJ), in DDLZIP Dropdown list it must show only Zip Codes from that
State. In order to Change the Value of DDLZIP, users must Change the
Value of DDLState. Basically DDLZIP is Child/Dependant of DDLState. I
did correctly setup proper reference to DDLState in SelectedParameters,
Control Parameters, PropertyName in SqlZip (SqlDataSource for DDLZIP).
This Cascading dropdown logic does work Fine in a Form without a
DetailView. I tested it. But I am having hard time to implement this
within Detail View.
Any Complete Example (i.e. Form with Subs) will be highly appreciated.
I will be glad if this example is based on NorthWind Database.
Thanks a lot for your time and efforts.
- Sam Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129584
Validating Data using Typed Dataset
Hi! All,
How can i validate data filling into a typed dataset?
e.g. Say I am importing data from a flat file into a typed dataset. I want
to validate that each data entering into the dataset are in match with the
dataset Schema and if any error i want to log the same row and move to
execute the next row.
How do I do this??
Regards,
Baren Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129583
Datasets fundamental
Hi there!
I'm very new to dot net envirnoment and have little bit understanding.
Due to some reason, I need to work straight forward on datasets and
webservices and xml. I don't have much hold on the same but would
appreciate if someone put lights on the given questions:
a) How datasets which has passed by value can be retreived from cache?
b) How would one know whether datasets is containing any records or
not? Is there any way to test the same? Please let me know via coding.
c) How long datasets will store in server memory?
d) How to create a dataset and publish it in cache?
Beside this, I do have other questions which will not fall into this
group like :
1) How to map datasets records to update a MYSQL Server?
Please help me out.
With Thanks!
Shyam Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129581
PLEASE tell me how to handle RowUpdated with/against a typed dataset
I have searched and searched and searched and sear.. anyway, you get
the idea.
I am trying desperately to figure out how, in "C++/CLI" (Managed
C++/2005) to handle the RowUpdated event in my code but can not find an
answer that works. I either get the always fun "error C3767:
'FrameWorks::eclipseDataSetTableAdapters::RepairDataTableAdapter::Adapter::get':
candidate function(s) not accessible" error, or other various errors.
Apparently, my strongly typed RepairDataTableAdapter does NOT expose
anything that even looks like RowUpdated.
So, given that, I thought ok, I'll do it the hard way, and try to deal
with this with untyped .. objects. So I've tried to create an adapter
that would expose RowUpdated, but then I wanted to get the existing
insert, update, ... commands from my typed adapter so that I wouldn't
completely redo all my database code. Well, that didn't work either
because those objects are also not exposed. Not even read-only!
This is what led to the error above.
Is there SOMEONE, ANYONE out there who can tell me how the frell to
handle RowUpdated with a typed adapter? I'm begging here.
Thanks,
Scott "Cmdr. Beavis" F. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129574
Open DataReader associated with this Command which must be closed first.
Hello,
I'm getting the a "There is already an open DataReader associated with
this Command which must be closed first" error message in the following
scenario. The message seems misleading, since the command isn't
associated with the datareader, but the connection is. Is there any
way to get this work work without closing my DataReader?
Thanks,
Eric
-------
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim drTests As SqlDataReader
strTestQuery = BuildTestSQL()
cmd = New SqlCommand(strTestQuery, conn)
drTests = cmd.ExecuteReader
Dim iDataRowCounter As Integer = 0
Do While drTests.Read
Dim strTest2SQL As String
strTest2SQL = BuildTest2SQL()
RunUpdateTransaction(strTest2SQL, conn)
Loop
Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal conn
As SqlConnection)
Dim cmdTest As New SqlCommand(UpdateSQL, conn)
Try
Dim trnTestTest As SqlTransaction
trnTestTest =
conn.BeginTransaction(IsolationLevel.ReadCommitted, "TestTest")
Dim iUpdated As Integer
iUpdated = cmdTest.ExecuteNonQuery
'TODO:Change rollback tran to Commit
trnTestTest.Rollback()
'trnTestTest.Commit()
Catch ex As Exception
Throw ex
Finally
If Not cmdTest Is Nothing Then cmdTest.Dispose()
End Try
End Sub Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129564
Generating a collection from a data set table
Hi,
I have a strongly typed dataset with 2 tables: Orders and OrdersDetails,
that have a parent-child relationship by OrderId.
I want to be able to work sequentually on each row in Orders and in
OrderDetails in the following manner:
foreach (TableRow HeadRow in Orders.Rows)
{
...
foreach(TableRow DetRow in OrdersDetails.Rows)
{
.....
}
}
My question is how can I make sure the inner foreach works only on the Rows
that have the same OrderId (the OrderId from the outter foreach)
Thanks Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129563
Good way to do lookups
I want to have a a set of "records" (i.e. would have used a Recordset
in the VB6 world) that contains a list of say 500 short string values
to lookup and then 500 short strings to replace them with when found.
What I'm going to use this record set for is to lookup the parts of a
street address and replace them with standard abbreviations. What I
could use guidance with is the best object to use for this record set
which will be used for lookups. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129562
merge not working
I'm trying to handle concurrency updating errors in my app - vb.net 2003,
ado.net 1.1.
I have a datatable with the data the user pulled and then changed ("dtMain")
and another that I filled after the updates failed, with the most current
data in the database ("dtConflict").
I am allowing the user to decide on a row-by-row basis how to handle the
update.
When I try to merge a particular row's current values (from "dtConflict")
into the main table ("dtMain") nothing is happening. Here is the code (I
added dtMain to a dataset "dsBig" in order to access the Merge method, which
is still unavailable to me on a datatable as I'm in ado.net 1.1):
Dim row As DataRow = CType(objCurrencyManager.Current, DataRowView).Row
Dim rowConflict As DataRow = dtConflicts.Rows.Find(row(0))
if blnPreserve = True then
dsBig.Merge(New DataRow() {rowConflict}, True,
MissingSchemaAction.Ignore)
else
dsBig.Merge(New DataRow() {rowConflict})
end if
row.ClearErrors()
The merge does not throw an exception but neither does it get the new
values.
This code is basically taken from David Sceppa's Microsoft ADO.NET (Core
Reference). Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129561
AutoIncrement and MultiStep OLE DB
Hello
this error starts to really go o nmy nerves. I Searched the i net but
found no solution for it. Maybe you can help.
My problem is, that i want to convert an Access-DB into a SQL-Server DB (i
cant use the tools, because this should work also on systems which only have
the MSDE and no access).
The error ocurres when the "AutoIncrement" Property is set to true and i
try to add the Table to the SQL-Server-DB (e.g. Catalog). The only solution i
found for a similar problem, is that the catalog has to be set prior to the
properties are manipulated. I do this, but still it doesnt work.
I use MDAC 2.8, SQL-Server 2000
What am i missing???
Thanks
Matthias
This is the code (C#):
protected void CloneColumn(ADOX.Table refTable, ref ADOX.Table
destTable, string refColumnName, string destColumnName, ref ADOX.Catalog
catDest)
{
ADOX.Column newColumn = new ADOX.ColumnClass();
newColumn.ParentCatalog = catDest;
newColumn.Name = destColumnName;
newColumn.Type = refTable.Columns[refColumnName].Type;
if (newColumn.Type==ADOX.DataTypeEnum.adDate)
newColumn.Type= ADOX.DataTypeEnum.adDBTimeStamp;
newColumn.DefinedSize = refTable.Columns[refColumnName].DefinedSize;
UpdateColumnProperties(refTable.Columns[refColumnName], ref catDest,
ref newColumn, Common.Constant.listOfSupportedProperties, "created");
destTable.Columns.Append( newColumn,
newColumn.Type,newColumn.DefinedSize);
}
protected void UpdateColumnProperties( ADOX.Column referenceColumn, ref
ADOX.Catalog catDest, ref ADOX.Column targetColumn, string[]
listOfSupportedProperties, string actionMsgString)
{
Array listOfSupportedPropertiesArray = (Array)listOfSupportedProperties;
System.Collections.ArrayList listOfSupportedPropertiesArrayList = new
System.Collections.ArrayList (listOfSupportedPropertiesArray);
System.Collections.IEnumerator propertyEnumerator = null;
propertyEnumerator = referenceColumn.Properties.GetEnumerator();
propertyEnumerator.Reset();
string propertyName = "";
while (propertyEnumerator.MoveNext()==true)
{
try
{
for(int i=0;adoConnDest.Errors.Count>0;i++)
{
string errorStr=adoConnDest.Errors[i].Description;
}
propertyName = ((ADOX.Property)propertyEnumerator.Current).Name;
if(listOfSupportedPropertiesArrayList.Contains(propertyName))
// check the property name with the allowed properties
{
try
{
Object oldObj = targetColumn.Properties[propertyName].Value;
Object newObj = referenceColumn.Properties[propertyName].Value;
if(newObj!=null)
{
if( newObj.Equals(oldObj)==false ) // check if an update is
neccessary
{
if (newObj.ToString().StartsWith("\""))
{
newObj=newObj.ToString().Replace("\"","'");
}
targetColumn.Properties[propertyName].Value = newObj;
Object changedObj =
targetColumn.Properties[propertyName].Value; // for debugging
if(oldObj!=null)
Common.MessageLogger.getInstance().WriteLogMessageToFile("\tColumn property
'" + propertyName + "' ("+oldObj.ToString()+"<>"+newObj.ToString()+")
"+actionMsgString+".");
else
Common.MessageLogger.getInstance().WriteLogMessageToFile("\tColumn property
'" + propertyName + "' (null<>"+newObj.ToString()+") "+actionMsgString+".");
}
}
}
catch(Exception error)
{
Common.MessageLogger.getInstance().WriteLogMessageToFile("Exception catched
during updating property '" +propertyName+"':"+ error.Message);
}
}
for(int i=0;adoConnDest.Errors.Count>0;i++)
{
string errorStr=adoConnDest.Errors[i].Description;
}
}
catch(Exception error)
{
Common.MessageLogger.getInstance().WriteLogMessageToFile("Exception catched
during updating property '" +propertyName+"':"+ error.Message);
}
Control.GenericEpxUpdateTool.getInstance().GetMainForm().IncreaseProgressBar();
}
} Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129559
Migration from ADO to ADO.Net with SqlClient
I am trying to get to terms with the use of updating through a data adaptor
in VS 2005.
The application allows the user to create forms and add fields and tables to
the database and so the code has to be generic.
In ADO, one can read the record,change the value and simply update:
RecordSet.Fields(FieldName)=SomeValue
RecordSet.Update
I can update a DataSet in a similar way, but how do I get the chages back to
the database? The data adaptor needs an UpdateCommand, but I cant find any
information about what this should be like.
Any help would be welcome.
--
John Austin Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129558
Oracle- and ODBC Provider missing in Visual Studio Express 2005
Hello,
I have Visual C# Express 2005 and Oracle 10g installed. After some
problems with different versions of Oracle including a restoring of a
system restore-point I now have the following problem: When I try to add
a data source to my project I can only select Access-File via OLEDB or
Local MSSQL Server file via .NET provider. As far as I know there should
also be an Oracle and an ODBC Provider available, but they are missing.
I tried to install everything new including the NET-2.0-framework, but
still the same problem. Somewhere I read, that in the machine.config
file the available providers are listed, but there are all 4 present.
Does anyone know what to do?
Thanks in advance
Sascha Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129548
SQL Migration - Varchar 8000 in SQL 2005
Hello,
We have migrated a sql server 2000 database to sql server 2005, this was
done through scripts (table, stored procedures and data).
To access this database we are using an ASP.Net 2.0 application which uses
the sqlhelper Aplication Block to connect to the database.
Everything works fine except one Stored Procedure which has an OUT
varchar(8000) parameter.
We use the following .Net Code to execute the stored procedure this stored
procedure:
aParams[2] = sSerDatos.GetParameter("@DominiosMenu", DbType.String, 8000);
aParams[2].Direction = ParameterDirection.Output;
sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams)
When we invoque the sqlcommand we get this sqlexception:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol
stream is incorrect. Parameter 3 ("@DominiosMenu"): Data type 0xE7 has an
invalid data length or metadata length.
If we change the DbType.String Size to 4000 in the .Net code everything
works, this same procedure works correctly in SQL Server 2000 with the same
.Net code.
Any help would be appreciated.
Thanks,
Sam Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129542
How to change encoding for XML Column
I am updating an xml column in SQLServer 2005, but it always gives me
an error
"XML parsing: line 1, character 38, unable to switch the encoding"
my csharp is like the following
cmd.Parameters.Add("@xml", SqlDbType.Xml);
cmd.Parameters["@xml"].Value =xmldata;
My xml have the following line
"<?xml version="1.0" encoding="UTF-8"?>..."
If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.
Please advice.
Thanks in advance.
John Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129540
Connection Pool Time Out
Hi all,
I have the following scenario:
App1: Asp.Net Application that shows reports from some SQL DB
App2: Asp.Net WebService with two services exposed for clients, and a single
â??Checkerâ?? in the Application object that checks every some time (normally 2
or 3 minutes) for conditions in the same DB, and insert some values in it
using a transaction.
When the time spans completes, the checker retrieves via a SqlDataReader the
ready items and make the insertions as shown:
SqlDataReader reader = GetReadies();
try
{
// do something
// initialize the transaction â??transâ?? and the connection â??conâ??
try
{
// insert using the transaction
trans.Commit();
}
catch(Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
con.Close();
}
}
finally
{
reader.Close();
}
In the web application I have few pages that get information from the DB,
and after a few retrievals, I got this error:
â??Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached.â??
So I review all code searching for unclosed datareaders or connections, for
nothing. I use SQLHelper in the web application and I always retrieve
datasets, anyway, I checked that all connections were closed.
Then I review the code in the webservice app, I think it is correct, isnâ??t it?
Even though I didnâ??t found unclosed connections, I started to watch the .Net
CLR Data performance object with the object Current # pooled connections. The
graph never goes over 7 connections; it oscillates between 5 and 8. I also
started a new trace in the SQLProfile to see if it audits logout events
occurs.
And still, after a while browsing in the asp.net application, I receive the
same error.
Note: the max pool size is set to 100 explicitly.
Any idea of the cause?
Thank you very much. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129539
txn isolation level
Hi,
Should the transaction isolation level persist across the reuse of a
connection from the connection pool?
From my experiment it does persist and I was not expecting it.
I was under the impression that "sp_reset_connection" will tear down all the
connection level settings and go back to the defaults.
I am using the sql .net provder with the 1.1 framework and connecting to a
sql 200 server.
Just wondering if this is a bug or by design ...
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129538
VB.NET and SQL Server 2005 - Data locked when they should be. LOOK
Hi,
I have 2 ways of selecting data from my table "Membre".
1- By permit number (memNoPermis)
2- By lastname, firstname (memNom AND memPrenom)
SAMPLE DATA:
memNoPermis: 1
memNom: Walter
memPrenom: Gregory
memNoPermis: 2
memNom: Wick
memPrenom: Timothee
memNoPermis: 3
memNom: Williams
memPrenom: Simon
He is my weird scenario.
FIRST- I run this query.
SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNoPermis = 2
It reads & locks the record ! FINE.
-----
After, if I run this query :
SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNom = 'Williams' AND M.memPrenom = 'Simon'
It tells me its lock ... but that is false. Only record id 2 should be
locked. The query above should return unlocked record id 3.
BUT
If I run the following query instead.. which ask for the same record.
SELECT M.memNoPermis, M.memNom, M.memPrenom
FROM Membre M WITH (UPDLOCK, NOWAIT)
WHERE M.memNoPermis = 3
In this case the record can be selected as if it wasn't locked (which is
the case).
Can anyone help me on this?
memNoPermis is my table PrimaryKey field.
Could my problem be related to that?
I made many tests. If I only read records with "memNoPermis" in my WHERE
clause, everything works perfectly.
BUT.
More explanation:
If I read record id 3 with memNom+memPrenom and no row it locked ! GREAT.
READS & LOCKS.
While record id 3 is locked:
If I read record id 2 (which is before 3 in the table) with memNom+memPrenom
... GREAT. READS & LOCKS.
BUT
Instead if I read record id 4 (which is after 3 in the table) .. it acts as
if I was locked already...which is wrong.
Pretty strange.
When reading with (memNom+memPrenom) in my WHERE clause, If the record I
need is after a locked record in my table, it acts as if it was locked
already.
When reading with (memNoPermis <-- PK field) in my WHERE clause, In all
cases it always works great !!
Thanks for helping me resolving this problem.
I tried putting memNoPermis+memNom+memPrenom in a UniqueKey. But it didn't
help. Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129537
DBTransaction question
Hi,
I have some code like this :
DBTransaction = DBConnection.BeginTransaction()
Assuming I don't book keep the return from this call ,
How do I
1) determine if there is a DBTransaction pending on this DBConnection and
2 ) if there is , what is the DBTransaction .
thanks
-chaz Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129536
How To Use Keyword DataDirectory For Database In Same Folder As Executable
Hello,
With a fairly complete and detailed example, how do I use the
DataDirectory keyword? What I ultimately want to do is place my
database in the same folder as the executable and have my application
find it there.
Thank you,
Christopher Lusardi Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129535
ExecuteNonQuery with error checking (or continue on error equiv)
First off I'll explain why I need something similar to ContinueUpdateOnError
feature that a data adapter has, and why I can't just use a data adapter.
Basically I'm updating a Large amount of data using BCP to copy the data to a
temp table in order to quickly move the mass amount of data from a flat file
to my database. I Then run updates on the temp table I just loaded that
insert AND update the data from the temp table to the production table. The
reason i do this is because my data (unfortunately) contains both inserts and
updates. The most efficient way I've found to do this involves using BCP to
move the data, then running SQL Statements I have generated to process the
updates and inserts from the temp table to the production table in the
database. I'm using something 'similar' to the BCP code from the PDC 05
Demo's ( http://blogs.msdn.com/dataaccess/archive/2006/01/09/510083.aspx )
Well it appears that when using try/catch if I have errors that yes it will
catch the error, however I have no control over it. The entire Update
command will then hault not updating any records. Is there some way to have
the ability to continue on with my SQL statement if it happens to hit a
record that doesn't have a corresponding foreign key or something similar?
Thanks
--
Matt
www.Fiddelke.org Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129528
Tabeladapter with row_number() over...
Hi Friends,
I have created a tableadapter (using wizard of VS2005) with some SQL query
statement. But I want to write SQL query such a way that it returns table
with one more fields, i.e. Row number.
I have tried this SQL query in SQL server 2005:
select ordernumber, row_number() over( order by ordernumber) sno from
orderhead
It's working in SQL server 2005 but not in table adapter (It gives SQL
syntax error in 'Add Query' wizard).
Please help me to add such a column in SQL.
And why should tableadapter give any error? This query is working with
dataadapter!! I mean, this code is working
Dim ds As New DataSet
Dim da As New SqlClient.SqlDataAdapter("select ordernumber,
row_number() over( order by ordernumber) sno from orderhead" _
, conStr)
da.Fill(ds, "TAB1")
Thanks in Advanced,
-Yogee Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129520
Binding to object datasource
I have created a project that contains a typed dataset. I have extended this
dataset through partial classes an daab 2.0.
I have created a datasource out of this project through the datasources
wizard in a new winforms project , I have a dropped a binding source
component on the form .
When I try to bind the bindingsource to the object datasouce , I get an
invalid property error.
If i try and expand the node containing this object datasource in the data
sources window , the i.d.e shuts down with out warning.
Using vs 2005 proefessional vb.net
Any help whatsoever would be greatly appreciated Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129519
OleDbCommand.Parameters.Add and 'IN' Comparator and MSAccess
I am using Visual Studio with a MS Access Database
I just can not work out how to create my parameter for the following
Select Fld1, Fld2 from myTable where Fld1 in (?)
The value I wish to add to the parameter collection for Fld1 is '2, 3,
17, 9, 235'
to add a single Parameter I would do the following
cmd.parameters.add("",oledbtype.integer).value = 1
There does not seem to be an OleDbType for an integer array
But I don't have a single value and I do not know how many values,
there will be in the 'IN' query
I also have other parameters in the Selection (not shown) and as
OleDbParameters, seem to be positional, with no way of changing that, I
can't just enter many '?' into the 'IN' part and only enter as many as
I need. In any case what would happen if I had multiple 'IN'
comparators.
Any ideas or suggestions, welcomed Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129514
About Load-on-Demand ADO.NET skill.
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? Tag: SqlBulkCopy with SQL2005 Column encryption Tag: 129511
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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption 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: SqlBulkCopy with SQL2005 Column encryption Tag: 129468
Hi,
Can the SqlBulkCopy class in .NET 2.0 be used in conjunction with SQL Server
2005's native column encryption?