Two DataViews of the Same database Table but different columns

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - 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: Two DataViews of the Same database Table but different columns Tag: 129504
  - 2
    - 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: Two DataViews of the Same database Table but different columns Tag: 129491
  - 3
    - 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: Two DataViews of the Same database Table but different columns Tag: 129490
  - 4
    - 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: Two DataViews of the Same database Table but different columns Tag: 129489
  - 5
    - 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: Two DataViews of the Same database Table but different columns Tag: 129488
  - 6
    - 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: Two DataViews of the Same database Table but different columns Tag: 129487
  - 7
    - 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: Two DataViews of the Same database Table but different columns Tag: 129486
  - 8
    - 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: Two DataViews of the Same database Table but different columns Tag: 129480
  - 9
    - 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: Two DataViews of the Same database Table but different columns Tag: 129477
  - 10
    - 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: Two DataViews of the Same database Table but different columns Tag: 129476
  - 11
    - 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: Two DataViews of the Same database Table but different columns Tag: 129475
  - 12
    - 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: Two DataViews of the Same database Table but different columns Tag: 129468
  - 13
    - 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: Two DataViews of the Same database Table but different columns Tag: 129466
  - 14
    - 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: Two DataViews of the Same database Table but different columns Tag: 129465
  - 15
    - 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: Two DataViews of the Same database Table but different columns Tag: 129463
  - 16
    - 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: Two DataViews of the Same database Table but different columns Tag: 129460
  - 17
    - 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: Two DataViews of the Same database Table but different columns Tag: 129446
  - 18
    - 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: Two DataViews of the Same database Table but different columns Tag: 129432
  - 19
    - 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: Two DataViews of the Same database Table but different columns Tag: 129427
  - 20
    - 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: Two DataViews of the Same database Table but different columns Tag: 129425
  - 21
    - 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: Two DataViews of the Same database Table but different columns Tag: 129423
  - 22
    - 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: Two DataViews of the Same database Table but different columns Tag: 129421
  - 23
    - 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: Two DataViews of the Same database Table but different columns Tag: 129420
  - 24
    - 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: Two DataViews of the Same database Table but different columns Tag: 129413
  - 25
    - 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: Two DataViews of the Same database Table but different columns Tag: 129395
- Next
  - 1
    - 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: Two DataViews of the Same database Table but different columns Tag: 129393
  - 2
    - 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: Two DataViews of the Same database Table but different columns Tag: 129372
  - 3
    - 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: Two DataViews of the Same database Table but different columns Tag: 129367
  - 4
    - 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: Two DataViews of the Same database Table but different columns Tag: 129365
  - 5
    - How can I join 2 bianry files together back to back? How can I join 2 bianry files together back to back? Thanks dee Tag: Two DataViews of the Same database Table but different columns Tag: 129363
  - 6
    - 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: Two DataViews of the Same database Table but different columns Tag: 129358
  - 7
    - 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: Two DataViews of the Same database Table but different columns Tag: 129356
  - 8
    - 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: Two DataViews of the Same database Table but different columns Tag: 129355
  - 9
    - 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: Two DataViews of the Same database Table but different columns Tag: 129353
  - 10
    - <<< 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: Two DataViews of the Same database Table but different columns Tag: 129352
  - 11
    - 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: Two DataViews of the Same database Table but different columns Tag: 129351
  - 12
    - 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: Two DataViews of the Same database Table but different columns Tag: 129336
  - 13
    - 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: Two DataViews of the Same database Table but different columns Tag: 129328
  - 14
    - 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: Two DataViews of the Same database Table but different columns Tag: 129319
  - 15
    - 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: Two DataViews of the Same database Table but different columns Tag: 129309
  - 16
    - 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: Two DataViews of the Same database Table but different columns Tag: 129303
  - 17
    - 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: Two DataViews of the Same database Table but different columns Tag: 129302
  - 18
    - 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: Two DataViews of the Same database Table but different columns Tag: 129296
  - 19
    - 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: Two DataViews of the Same database Table but different columns Tag: 129294
  - 20
    - 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: Two DataViews of the Same database Table but different columns Tag: 129292
  - 21
    - 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: Two DataViews of the Same database Table but different columns Tag: 129284
  - 22
    - 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: Two DataViews of the Same database Table but different columns Tag: 129280
  - 23
    - 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: Two DataViews of the Same database Table but different columns Tag: 129279
  - 24
    - 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: Two DataViews of the Same database Table but different columns Tag: 129278
  - 25
    - typed dataset tableadapters and update (basic question) Hi, I'm using ado.net 2.0 and I have this strongly typed dataset that has this table that has some custom sql update queries. The thing is, my customDataSetTableAdapter.myTable has no generic "Update()" method. It only has some ".myCustomUpdate(param1,param2)" method, that I can't use to send all the changes at once, like I could do with the .update(). So when I have all the changes made to the dataset in memory, how is it that I'm supposed to persist them in the real database table? Thanks. Tag: Two DataViews of the Same database Table but different columns Tag: 129270

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

Top

Re: Two DataViews of the Same database Table but different columns by Cor

Cor
Mon Jun 05 01:23:13 CDT 2006

Scott

It is possible in version 2.0.

Be than aware that you are working with *copies* of the datatable and not
real with views.

Dataview.ToTable(boolean, columns())
http://msdn2.microsoft.com/en-us/library/wec2b2e6(VS.80).aspx

I hope this helps,

Cor

"S Lemen" <SLemen@discussions.microsoft.com> schreef in bericht
news:FAAD33FC-F873-4E0E-99CA-E652A4E2B042@microsoft.com...
> 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

Top