get DataColumn metadata

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
    - DataColumn expression to create new Guid I would like to set up a DataColumn in my table of type Guid and a new Guid(Guid.NewGuid) would be the expression that gets evaluated when ever a new row was added. I have the DataSet bound to a grid so I would like to set this up at compile time. The Guid DataColumn is also the primary key so it has to be set when a row is added. Thanks, Marc Tag: get DataColumn metadata Tag: 120127
  - 2
    - User Controls and ADO Connections I have 3 User Control .ascx files that are included in a base .aspx file. In each ascx file, as well as the base aspx file, I make ADO.Net calls that require a datatbase connection. It appears that I have to open and close the connection in each file (i.e. I can not reuse the open connection between all 4 pages)? This means that I must open and close the connection 4 times when servering one page (base aspx and 3 ascx files). Is there any way that I can accomplish this without so opening and closing the connection so many times? Wouldn't this become a scalability problem if I continually have to open and close connections multiple times on each page? Tag: get DataColumn metadata Tag: 120112
  - 3
    - problem with axMsChart in scal of x and y axis Hello Guys, I working in C#. I have problem with axMschart contorl. I m not able to change the scale type of x axis. By default it is DateTime. I want it to float/double. Please help out me . thanks in advance Tag: get DataColumn metadata Tag: 120110
  - 4
    - One or many DataSet Hi, I am working with WB.NET and SQL Server. I have some trouble grasping what a DataSet in VB.NET is. Do the DataSet mirror the whole database used as the data store for one application or is it recommended to use multiple Dataset for this database (one application). Example: My DataSet have 3 tables: Table1, Table2 and Table3. Table2 has 2 columns, CustomerID and OrderID Table1 has 1 column CustomerID Table3 has 1 column OrderID. For GridList1 I need to define the CustomerID as the PrimaryKey in Table2 and link it to Table1's CustomerID For GridList2 I need to define the OrderID as The PrimaryKey in Table2 and link it to Table3's OrderID. Then I have one Table (Table2) with two different PrimaryKeys depending on witch Grid I want to display my data in. Do this work or do I have to make two different DataSets, one for Table1 and Table 2 and one for Table2 and Table3? My tables have quite a lot of data. When I fill my Datasets with data from the database, can I choose witch table in the DataSet I want to fill or do I have to fill the whole DataSet at once? Is it best practice to keep the amount of DataSets small but with many tables or it is best practice to have many DataSets but a small amount of tables in each one of them? Thanks in advance :o) Tag: get DataColumn metadata Tag: 120104
  - 5
    - SqlDataReader Ok this one has me stumped. Everything looks right, but for whatever reason the DataReader keeps telling me not set to an instance of an object. When I step through the code, it runs the line SqlDataReader mySqlReader; However I can see in the watch value that the mySqlReader is set to an undefined value When it gets to the mySqlReader = sc.ExecuteReader() command I get the not set error Anyone have any thoughts? Code below <code> int [] results; results = new int[5]; SqlDataReader mySqlReader; sc.Connection = sqlConn; sc.CommandTimeout = 450; try { sqlConn.Open(); mySqlReader = sc.ExecuteReader(); while(mySqlReader.Read()) </code> Tag: get DataColumn metadata Tag: 120100
  - 6
    - Retrieving Excel Schema Information Hello, I am retreiving Excel Schema Information from a Excel Sheet. I am using GetOleDBSchemaTable for it. I see a strange behaviour sometimes that when I retrieve the Column names from a particular Excel file, It returns the column names which are there as well as some extra columns like "F1,F2.,..." Can anyone please tell me if this is a bug or I am doing something wrong. Any help is appreciated. Regards, Rohit Tag: get DataColumn metadata Tag: 120088
  - 7
    - How do I specify a 1 to many relationship? I want to specify a relationship between two data tables in a dataset. (Both data tables come from executing stored procedures in SQL Server.) When I tried to add the relationship through the Relations property of the dataset, I got the following error message: "The columns don't currently have unique values." I believe the problem is there is a one-to-many relationship between the parent data table and the child data table. So my question is how do I specify that when I create the relationship? Is there a property of the DataColumn object that I have to specify? Right now I'm trying to link the two by common columns (client number and case number); should I specify some other column in the child's DataColumn which would be unique for each client/case? (I didn't do this in this case, because the example I saw in David Sceppa's book "Microsoft ADO.NET" didn't do this. I had thought that I had to specify only those columns from both data tables which would be linked between the parent and child DataColumns.) I am using ADO.NET 1.1. Rod Tag: get DataColumn metadata Tag: 120081
  - 8
    - ADO.Net Best Practices Validation Tool Does anyone know of a tool that can look at C# code and make sure that all open datareaders are closed as well as validate other ADO.Net best practice principles? I have a team of 10 developers and we recently had a bug in our code that claims that an existing connection can't be used because it is associated with an open datareader. Unfortunately, looking through all the code is blinding me and so I wondered if there was a tool that could do it for me. Any and all help is appreciated. -- Rodney Tag: get DataColumn metadata Tag: 120077
  - 9
    - DataTable.Select Hi! I am using the following code in my vb.net application.. its working fine on windows 2000 professional and XP Professional, but its not giving me appropriate result when running on a Windows 2003 Server. Dim tblFileList As DataTable Dim drCORows As DataRow() Dim strExFileName As String strExFileName = ConfigurationSettings.AppSettings("ExOthForm") tblFileList = dsFileList.Tables(0) strSort = "FormSequence ASC" drCORows = tblFileList.Select("CopyTo='CO' AND FilePath NOT IN (" + strExFileName + ")", strSort) Please help Thanks, Baren Tag: get DataColumn metadata Tag: 120074
  - 10
    - Perform JOIN in dataset? I would like to pull data from two different databases into one dataset (using a DataAdapter with different commands and connections, and filling that one set with the different results). With this data, I need to perform a JOIN to get the records from those two databases that have a common id (WHERE table1.id = table2.id). How can I do this? I couldn't do a JOIN with my original query that was used in the SelectCommand portion of the DataAdapter because the OracleCommand only takes one connection (to one database). I don't want to get too fancy, so if I can keep it simple it would be better for me. I'm also using C# and not VB. Thanks! Christine Tag: get DataColumn metadata Tag: 120053
  - 11
    - Using Diffgrams for multiple insert/update/delete Hi, I want to do multiple insert/update/delete of multiple rows using a single stored procedure using diffgram. Can I please get a small sample/link for achieving this. I just need the the stucture of the stored proc as I have the diffgram generated successfully. Advanced thanks. -- Samba! Tag: get DataColumn metadata Tag: 120051
  - 12
    - Reading Excel using OLEDB When I read excel file sheet as table through ADO.NET using OLEDB it does not return all data. I know that it scans 8 rows for deciding about datatype and it can be changed to 16 rows. I changed it to 16 rows but still does not work. I know what is happening, it finds the column name which obviously string type on 8th column in my file and probably it guess the columns as string type.. After that I have all numeric data for that column. It does not return all these data Is there any work around ? Tag: get DataColumn metadata Tag: 120050
  - 13
    - When to use new DataSet? When to perform DataSet resident joins? Whenever I create a new database query, I have to answer a couple of questions that are even harder to ignore if you are using visual studio: (1) Should I create a new table in an existing dataset or a new dataset? Is there peril in having one table per dataset and having lots of data sets? (2) Should I use a generic late binding dataset or have visual studio create a custom early binding dataset class for me? (3) Should I implement my one to many relationship in the SQL with an INNER JOIN clause or should I create two datatables and join them in the dataset using the visual studio dataset GUI designer using drag and drop? (4) If I have a many to many relationship, is it possible to implement this in the dataset GUI designer instead of in the SQL with INNER JOIN statements? What would be the advantage? (5) Am I going to supply the dataset with add, delete and update objects? What is the advantage of assigning these Command objects to the Dataset instead of just calling them directly as needed if they are needed? (5a) When you are configuring a new data adapter, you get the oppertunity to let visual studio automatically generate the SQL statements to INSERT, DELETE and UPDATE and assign their respective command objects to the dataset. I've done it and it even worked, but I did not feel like I understood it. How does visual studio know how to automatically generate the SQL update, add and delete SQL statements when you have a complex join statement anyway? (5b) When is visual studio not able to automatically generate the UPDATE, DELETE, and INSERT SQL statements and their respective command objects? I received a message from Visual Studio recently complaining that I was not using enough PK to automatically generate the UPDATE, DELETE and INSERT SQL statements. This was OK since I could not imagine how Visual Studio was going to know how to implement these statements anyway. Was it because I was joining some fields that were secondary indices? I've successfully used the dataset GUI designer to connect the foreign key field to the PK of another table. (I'm not talking about the query designer here -- this is connecting fields in the dataset after they have been read out of the database tables). It worked. I was impressed but I was not (still not) clear on the advantage. When I tried to do the same thing with a many to many I could not figure out how to do it. So I'd love some help on the perils and merits to each approach! Thanks, Siegfried Tag: get DataColumn metadata Tag: 120046
  - 14
    - ADO .NET ODP Equivalent of DBMS_SESSION.SET_CONTEXT Is there a way using the ODP for .NET (Oracle 9i, .NET 1.1) to set the context for calling a stored procedure, using .NET code? For example, I want to do the equivalent of the following: DBMS_SESSION.SET_CONTEXT('rowlevelaudit_ctx', 'userid', 'Chris'); in VB .NET so that during subsequent calls, I can do the follwoing: SELECT SYS_CONTEXT ('rowlevelaudit_ctx', 'userid') INTO v_username FROM DUAL; The greater goal here is to have row level auditing by user, while still having ony a single login to the database. I want to avoid having to pass in each individual's userid to every single stored procedure. Any help would be greatly appreciated, Chris Tag: get DataColumn metadata Tag: 120041
  - 15
    - Relational Database Samples in C# Can anyone point me to samples of Relational Databases (MSAccess or SQL Server) that show how to update and add records to relational tables? Any help would be greatly appreciated. Thanks. Tag: get DataColumn metadata Tag: 120038
  - 16
    - FYI: Sudden timeouts when executing SqlCommand I had a problem recently where a SqlCommand would work for weeks at a time, and then start timing out. My SqlCommands always invoke stored procedures, and the stored procedures always return the smallest amount of data possible (usually only one row or using output parameters). I had no idea what was causing the problem. The code hadn't changed. I wracked my brain looking at the code, stepping through it, checking the objects, viewing stuff in the debugger, blah blah blah. Everything seemed peachy. Then I pointed to a duplicate database on a different server. The code executed without a hitch. I compared the stored procedures and everything matched up. There was no problem there. After another few weeks of searching for the cause of this mysterious error, we pinned it down to one thing: A monstrously huge transaction file. Somehow, the SqlAgent on the development server had been shut down for about a month (prolly by a wayward developer), and the backups weren't running. Consequently the transaction log had reached a size in excess of 15GB. Not surprisingly, the drive was down to 50MB free. We reenabled the backup, ran it, and truncated the logs; everything ran just beautifully after that. If your SqlCommands start inexplicably timing out, check your transaction log, and check the free space on the drive. It may not be the cause of YOUR problem, but you'll be able to rule it out. Hope this helps! Tag: get DataColumn metadata Tag: 120037
  - 17
    - no value given for one or more required parameters - error Hi all, Why am I getting a 'no value given for one or more required parameters' error when I try to fill the dataset. Below is the code I'm using and I'm getting the error on the DASOURCE.FILL(DSSOURCE, "FIELDS"); part of the code. objSourceConn is fine. Can anyone help? Thank you. string selectFromSource; selectFromSource = "SELECT * FROM Fields WHERE ['Label name'] = '" + drDistinctSource["Label name"].ToString() + "'"; OleDbDataAdapter daSource = new OleDbDataAdapter(selectFromSource, objSourceConn); DataSet dsSource = new DataSet(); daSource.Fill(dsSource, "Fields"); -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200510/1 Tag: get DataColumn metadata Tag: 120034
  - 18
    - Connect a TableAdapter to an already existing DataTable hejdig. I have a DataTable from before and now want to connect it to a database. I havn't found a way, through VS.Net2005rc1, to create a TableAdapter and connect it to an already existing DataTable. Is there a way (besides hacking the XML)? TIA /OF Tag: get DataColumn metadata Tag: 120030
  - 19
    - UpdateCommand doesn't work fine. Hello, I've implemented a control that works with a database oracle, with dataset and dataadapter. It works fine, inserts, delete, and update works fine when the sentence is like "update table set a=:1,b:1", but when the update is "update table set a=:1,b=:2 where c=:1", it says "Concurrency Infraction: Update Command affected 0 register" or "ORA-01722: Incorrect number". It makes the sentence dynamically the code is: If txt.isPrimaryKey Or txt.ConcurrencyField Then If Not primerw Then where &= " AND " Else primerw = False End If where &= txt.FieldName & " =:" & txt.FieldName Else If Not primerc Then strupdate &= "," Else primerc = False End If strupdate &= txt.FieldName & "=:" & txt.FieldName End If If param Is Nothing Then ReDim param(0) Else ReDim Preserve param(param.Length) End If param(nparams) = New OracleParameter param(nparams).ParameterName = ":" & txt.FieldName param(nparams).OracleDbType = txt.FieldType param(nparams).SourceColumn = txt.FieldName If txt.isPrimaryKey Or txt.ConcurrencyField Then param(nparams).SourceVersion = DataRowVersion.Original Else param(nparams).SourceVersion = DataRowVersion.Current End If nparams += 1 Thanks in advance Silvia. Tag: get DataColumn metadata Tag: 120026
  - 20
    - DataTable.Select method in .NET 2.0 Hi, I have a problem with DataTable.Select method in Microsoft .NET 2.0. I have a ListBox control, with DataSource pointing to a DataTable "RolePermissions". ListBox has SelectionMode property set to "Multiple". I would like to enable User to Delete Multiple items with just one click. I am almost sure, that this worked in .NET 1.1. I have a DataTable with column "permissionID" of type uniqueidentifier Here is my Code: // We will concatenate selected Guid's into one string StringBuilder sbItemsSelected = new StringBuilder(); // for every Item .... foreach (ListItem item in lbRolePermissions.Items) { // ... which is selected... if (item.Selected == true) { // ...we add selected ID, and space after it sbItemsSelected.Append("'"); sbItemsSelected.Append(item.Value); sbItemsSelected.Append("' "); } } /// We now have list of ID's, separated with spaces (with space at he end) string selectedItems = sbItemsSelected.ToString(); // remove last space selectedItems = selectedItems.Trim(); // insert commas instead of spaces selectedItems = selectedItems.Replace(" ", ","); // get the table, to handle data DataTable dtRolePermissions = Session["RolePermissions"] as DataTable; /// select rows, which are of interest to us ******************************************************************** DataRow[] drsRolePermissions = dtRolePermissions.Select("permissionID IN (" + selectedItems + ")"); ********************************************************************* /// delete them foreach (DataRow drRolePer in drsRolePermissions) { drRolePer.Delete(); } /// save new Table in Session variable Session["RolePermissions"] = dtRolePermissions; // bind the Table again lbRolePermissions.DataSource = dtRolePermissions; lbRolePermissions.DataBind(); The line inside "stars" is line, in which an exception occurs, the exception Message is: "Cannot perform '=' operation on System.Guid and System.String." I thought, that exception can occur because the Items in listBox are filtered with an expression, but that would be stupid of Microsoft not to let such operations. I am confused.... this really worked in .NET 1.1 I used it a lot in previous applications.... Is there some explanation to this fact ? ( apart from saying, that this is a bug ;-) ) Thank You in advance for help, maciu Tag: get DataColumn metadata Tag: 120024
  - 21
    - Detecting detail datagrid rows changing Hi All I need to detect when the row count in a detail grid changes as the user navigates on the Master datagrid I need to change one of the column widths on the detail grid to allow for the scrollbar appearing and disappearing. I can't seem to find an event which fires when the detail grid changes I am using VB 2003 and use a dataset to bind the 2 grids with a relation Regards Steve Tag: get DataColumn metadata Tag: 120023
  - 22
    - Writing back data (Update) Hello, I have created a connection of a Siebel Database (or to be more specific Sybase SQL Anyware), and I am able to retrieve data and view it contents (Tables, Views Stored Procs) using odbc from ADO.NET. The problem I am having is trying to Update a table. It does not appear that I have permission or there is some other type of commands I should be using besides SQL statements. I really need information on writing data back to this database. Any information would be greatly appreciated. If necessary you will be able to VPN onto the computer to accomplish this. I would also be interested in a referral or consultant. Siebel has been no help, especially since Oracle has agreed to purchase them. Thrid party drivers are ok to, providing they do not require individual lisences for each computer that is using it. Thanks, Chuck Tag: get DataColumn metadata Tag: 120021
  - 23
    - How to know a column whether indexed or not in sql2k Tag: get DataColumn metadata Tag: 120011
  - 24
    - Odd Behavior Copying VarBinary Data I'm copying the content from one DataTable to another. Both tables have a SQL column of type VarBinary. The data is actually copied this way. DataRow[Column] = DataRow[Column]; Would this method cause a problem with VarBinary data types? The problem I'm seeing is that the data seems to be duplicated. So, if the both rows contained the value "1 2 3" after the copy I'm seeing a value of "1 1 2 2 3 3" in the row where the data was copied to. Any thoughts ideas are appreciated. Tag: get DataColumn metadata Tag: 120005
  - 25
    - DataTable Adapters and Oracle Provider (2005) Using the GUI tools to have a Table Adapter's Fill& GetData proc point to a SPROC in the database (or a function) works well if the proc is a Function that has no parameters, OR if it is a proc and you are using SQL Server. In my case, I want to use functions inside Oracle, and want to pass parameters. First I tried using SPROC. Note I'm not trying to return a cursor here - my goal is simply to get the SPROC to run. CREATE OR REPLACE PROCEDURE getScreenByApparatus (parmapparatusID in varchar) IS myCursor doe_pkg.TPI_Cursor; BEGIN open myCursor for Select scr.screenID, scr.screenType,scr.Description, scr.userid from screen scr, screenprocessstep sps where sps.apparatusID = 1 -- parmApparatusID and sps.screenID = scr.screenID; end getScreenByApparatus; --- End SQL --- In the DataSet designer, I set the SCREENTableAdapter (attached to the SCREEN object I dragged over) to use these values: Command Text: getScreenbyApparatus CommandType: StoredPrcocedure ExecuteMode: Reader Parameters: Parameter1: DbType: AnsiString Direction: Input ProviderType: VarChar I Right click Preview Data, select the table and procedure, and fill in the Parameter (either Hi There, "Hi There" or "hiThere") I then get back a dialog: Could not be previewed: ORA-6550: line 1 column 7: PLS-00306. wrong number or types of arguments in call to 'GETSCREENBYAPPARATUS' ORA-6550:line 1, column 7 PL/SQL: Statement ignored. (I know I have a select statement that's pointing into a cursor, and I've tried this with an OUT cursor as the second parameter, OUT cursors as return values, but will skip that discussion until I get the basics working). This all works smashingly well in SQL Server. What does it all mean? However, when I create the For example, my Oracle -- Senior Developer Transform Pharmaceuticals Tag: get DataColumn metadata Tag: 120003
- Next
  - 1
    - using webrequest Hi I have a requirement to submit form data to a url after collecting the data from the database. For example I need to send firstname and lastname through a post method I can call on the fly. This is the traditional route below , I want to emulate this submit action from a vb.class using request.method="post" <FORM action="http://somesite.com/prog/adduser" method="post"> First name: <INPUT type="text" name="firstname"><BR> Last name: <INPUT type="text" name="lastname"><BR> email: <INPUT type="text" name="email"><BR> <INPUT type="submit" value="Send"> <INPUT type="reset"> </FORM> clear as mud? Anyone have any tips ? Tag: get DataColumn metadata Tag: 120001
  - 2
    - Intermittent SP error - "has too many arguments" Hello, I have an audit table into which I insert information about the use of the application. This works sometimes and other times fails. I cannot find any reason for it failing. It is always given the information. Here are the errors I receive; --------------------------- Exception Message: Procedure or function usp_UseAudit_ins has too many arguments specified. --------------------------- --------------------------- Exception Source: .Net SqlClient Data Provider --------------------------- --------------------------- Exception StackTrace: at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at CLIP.UseageAudit.InsertAuditRecord(String selection, String version) --------------------------- The call to the method \\ InsertAuditRecord("ManageJobs", _version) // The Method \\ Friend Sub InsertAuditRecord(ByVal selection As String, ByVal version As String) Call DAL.InsertUseAudit() Dim row As DataRow = _dataSet1.Tables("tblUseAudit").NewRow row("pkUseAuditId") = Guid.NewGuid.ToString row("ClipUser") = SystemInformation.UserName.ToString row("version") = version row("DateTime") = Now row("Selection") = selection _dataSet1.Tables("tblUseAudit").Rows.Add(row) Dim dsDataChanges As New CLIP.dsTables dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables) If (Not (dsDataChanges) Is Nothing) Then DAL.daUseAudit.Update(dsDataChanges, "tblUseAudit") _dataSet1.Merge(dsDataChanges) _dataSet1.AcceptChanges() // The DataAccess (DAL) \\ Public Sub InsertUseAudit() With cmdUseAudit_Ins .CommandType = CommandType.StoredProcedure .CommandText = "usp_UseAudit_ins" .Connection = sqlConn With cmdUseAudit_Ins.Parameters ' No return required - Does not work even with this incuded '.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, '4, ParameterDirection.ReturnValue, False, CType(0, Byte), 'CType(0, Byte), "", DataRowVersion.Current, Nothing)) .Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36, "pkUseAuditId")) .Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50, "ClipUser")) .Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version")) .Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4, "DateTime")) .Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50, "Selection")) End With End With End Sub // Parts of the DataSet \\ Friend tblUseAudit As New DataTable Me.tblUseAudit = Me.Tables.Add("tblUseAudit") ' tblUseAudit Dim pkUseAuditId As DataColumn = Me.tblUseAudit.Columns.Add("pkUseAuditId", GetType(String)) pkUseAuditId.MaxLength = 36 pkUseAuditId.AllowDBNull = False Dim ClipUser As DataColumn = Me.tblUseAudit.Columns.Add("ClipUser", GetType(String)) ClipUser.MaxLength = 50 ClipUser.AllowDBNull = False Dim version As DataColumn = Me.tblUseAudit.Columns.Add("version", GetType(String)) version.MaxLength = 50 version.AllowDBNull = False Dim DateTime As DataColumn = Me.tblUseAudit.Columns.Add("DateTime", GetType(DateTime)) DateTime.AllowDBNull = False Dim Selection As DataColumn = Me.tblUseAudit.Columns.Add("Selection", GetType(String)) Selection.MaxLength = 50 Selection.AllowDBNull = False pkUseAuditId.ReadOnly = True tblUseAudit.PrimaryKey = New DataColumn() {tblUseAudit.Columns("pkUseAuditId")} The Table \\ CREATE TABLE [tblUseAudit] ( [pkUseAuditId] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ClipUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateTime] [smalldatetime] NOT NULL , [Selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO // The Stored Procedure \\ CREATE PROCEDURE dbo.usp_UseAudit_ins( @pkUseAuditId varchar(36), @ClipUser varchar(50), @version varchar(50), @DateTime smalldatetime, @Selection varchar(50) ) AS SET NOCOUNT OFF; INSERT INTO tblUseAudit( pkUseAuditId, ClipUser, version, DateTime, Selection ) VALUES ( @pkUseAuditId, @ClipUser, @version, @DateTime, @Selection ) /** no return required - doesn't work even with this included. ; SELECT pkUseAuditId, ClipUser, version, DateTime, Selection FROM tblUseAudit WHERE (pkUseAuditId = @pkUseAuditId) **/ GO // What could cause this error? Thank you, dbuchanan Tag: get DataColumn metadata Tag: 119991
  - 3
    - Connection String Problem Hi, I am trying to encrypt my connection string in the app.config file. I am using Base64 encryption, I just want to hide it from casual users. I keep getting file not found error. However when I just use the connection string in plain text I can connect fine. The only difference between the two is the @ sign is used in front of the connection string (plain) when I view them in the command window (see below). Any ideas? > ? this.connectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" > ? this.connectionString @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" Tag: get DataColumn metadata Tag: 119984
  - 4
    - retrieve a column value from a row that might be DBNull I have a dataset that I want to update. I pull the row out that I want to update. There is a value in one of the columns that must be analized to determine if other values need to change. This works great as long as there is something in that column. If that DB column is empty, I get this error... "System.Data.StrongTypingException: Cannot get value because it is DBNull. ---> System.InvalidCastException: Cast from type 'DBNull' to type 'Decimal' is not valid. I have tried all sorts of things. I am wanting to get like, row.CCNum THis is great unless CCNumis empty. I've tried the method isDBNull(row.CCNum), but I still get the same error. How do I return a value from a DB and not error out if its empty? I have multiple columns to update, so I don't want to use an execute reader. I would like to use the 'row' type methods to get this done. Tag: get DataColumn metadata Tag: 119979
  - 5
    - Multiple Connections for a Dataset Hi, I have a method in a data access class (all in C#) where I'm trying to put information from different connections into a single dataset to return to the calling class. Any ideas on how I can do this? I only know how to pull data from different tables using ONE connection. I have data in a completely different connection that I need to pull from. This is what I currently have: Connection.Open(); myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand( query, base.Connection ); myDataSet = new DataSet(); myDataAdapter.Fill( myDataSet ); return myDataSet; This fills my dataset using the query and only one connection. I don't want to have to pass connection strings to the method that does this. Any ideas would be greatly appreciated! Christine Tag: get DataColumn metadata Tag: 119977
  - 6
    - Filtering question with related tables Hi guys, I've been struggling with this one for a while. I have a typed dataset. It has two tables inside of it; Resources and EmployeeMaster. Both EmployeeMaster and Resources have a column called "EmpID", an int, which is also both of their primary keys. This is the relationship between the two, and is present in the dataset. EmployeeMaster has detailed data about employees such as full name, address, email, etc. Resources is a table that I created that is used to determine which employees are using a program I am writing right now, and what their security level is. That column is of type int called "UserType". So this is all set up, my dataset is populating nicely with every row from both tables, everything seems to be set. Now, what I want to do is populate a listbox on a C# Windows.Forms app with employee names (stored only in EmployeeMaster) of only those employees who have a UserType (stored only in Resources) of 5. The equivalent SQL for this is simple. "SELECT emp.LastName FROM EmployeeMaster emp, Resources r WHERE r.UserType = 5" I can't figure out how to get the equivalent of this to work in my typed DataSet so I can databind it to my listbox. I looked at DataViews, but couldn't find any decent examples of the RowFilter that was filtering on a child table. I did find one page that said there's a "[child]" keyword that should be put in there, but I couldn't figure that out. I also looked at DataViewManagers, but hit a dead end on that one as well. Can one of you gurus give me a little guidance, please? I feel silly asking what's probably a really simple question, but I've scoured the net for hours and haven't found anything. Thank you VERY much to any and all help! Andy K Tag: get DataColumn metadata Tag: 119973
  - 7
    - creating an xsd through xsd designer - xml format Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea *** Sent via Developersdex http://www.developersdex.com *** Tag: get DataColumn metadata Tag: 119969
  - 8
    - How to force a specific format on xml generated by xsd Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: get DataColumn metadata Tag: 119968
  - 9
    - Newbie with a Problem Hi There, Hope Somebody dosn`t mind Helping a Newbie? I have the Follwoing Code: Private Sub frmSystemUsers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'File Datasets Try dsSystemUsers.EnforceConstraints = False Try Me.odcSystemUsers.Open() odaSystemUsers.Fill(dsSystemUsers) cmSystemUsers = DirectCast(BindingContext(dvSystemUsers), CurrencyManager) mlLoading = True Catch fillException As System.Exception Throw fillException Finally Me.odcSystemUsers.Close() End Try Catch eLoad As System.Exception System.Windows.Forms.MessageBox.Show(eLoad.Message) Finally mlLoading = False End Try 'End of Fill Datasets End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click cmSystemUsers.AddNew() txtFirstname.Enabled = True txtSurname.Enabled = True txtUsername.Enabled = True txtPassword.Enabled = True End Sub Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click If txtFirstname.Text = "" Then ValidDataMessage = "First Name Required" txtFirstname.Focus() ElseIf txtSurname.Text = "" Then ValidDataMessage = "Surname Required" txtSurname.Focus() ElseIf txtUsername.Text = "" Then ValidDataMessage = "Username Required" txtUsername.Focus() ElseIf txtPassword.Text = "" Then ValidDataMessage = "Password Required" txtPassword.Focus() If ValidDataMessage = "" Then If MessageBox.Show("This Will Save All Changes - Are You Sure?", "Warning!", MessageBoxButtons.OKCancel) = DialogResult.OK Then cmSystemUsers.EndCurrentEdit() If dsSystemUsers.HasChanges Then odaSystemUsers.Update(dsSystemUsers) dsSystemUsers.Clear() odaSystemUsers.Fill(dsSystemUsers) End If Else End If MessageBox.Show(ValidDataMessage, "Data Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End If End If End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim DeleteResult As DialogResult = MessageBox.Show("Delete " & txtUsername.Text & " ?", "Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If DeleteResult = DialogResult.Yes Then Me.dvSystemUsers(cmSystemUsers.Position).Delete() End If End Sub Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click txtFirstname.Enabled = True txtSurname.Enabled = True txtUsername.Enabled = True txtPassword.Enabled = True End Sub Private Sub btnCancelChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelChanges.Click If MessageBox.Show("This Will Cancel All Changes - Are You Sure?", "Warning!", MessageBoxButtons.OKCancel) = DialogResult.OK Then cmSystemUsers.CancelCurrentEdit() End If txtFirstname.Enabled = False txtSurname.Enabled = False txtUsername.Enabled = False txtPassword.Enabled = False End Sub End Class First Question - Am I writing my code the correct way for a Database Application, or am I going about it totally the Wrong Way? Second Question is - Why I try and click my Update Button, it dosn`t Update the Data to my Database. It Adds a Row but with just Blank Data Third Question - When I delete a Row, It deletes it on Screen but dosn`t Update My Database Again Sorry for been sooooooo Stupid but like I said Im a newbie thats confused, I`ve search the Net but got even more confused because it seems there is so many ways of doing things. Hope omebody dosn`t mind Helping out? Many Thanks NSC Tag: get DataColumn metadata Tag: 119965
  - 10
    - Is there an XMLDataAdapter? I have two, dynamic XML files that contain data. My customer insists on using XML for a number of quite valid reasons. Being new (read naive) to XML, I thought there might be some form of SELECT statement that could be used with XML files. A number of books have referred me to the .NET SDK. However, I can't seem to find what I need. Is there a data adapter for XML? If so, where might it be documented? If not, must I program my own queries against the contents of various dataset tables (read by <data-set>.ReadXML)? I must stay within the Microsoft provided .NET framework and not purchase software from another vender. I also must assume that the user will have neither SQL nor Access available. TIA, Gus Tag: get DataColumn metadata Tag: 119956
  - 11
    - how to know the table is exist in the database Tag: get DataColumn metadata Tag: 119942
  - 12
    - Connect to a password protected MS Access DB After I set the password for MS Access DB , i modify the connection string from connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=System.Environment.SystemDirectory\" & "MyDBName.MDB" to connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=System.Environment.SystemDirectory\" & "MyDBName.MDB" & ";User Id=admin;Password= " & "MyPassWord" & ";" Afterward I open the Access DB, it return error "cannot start your application. the workgroup information file is missing or opened exclusively by another user." Any idea? thanks. Peter Tag: get DataColumn metadata Tag: 119937
  - 13
    - ANN: New VistaDB 2.1 release supports Visual Studio .NET 2005 VistaDB 2.1 embedded SQL database has been released. Free major update features over 60 improvements including improved .NET Provider support for Visual Studio .NET 2005, .NET 2.0 and .NET Compact Framework. 2.1 now includes a free 2-User VistaDB Server that extends the 500KB embedded engine to provide true client/server SQL database support. Embedded and server feature royalty free distribution. Read more: http://www.vistadb.net/Press_051024_VistaDB21Released.asp -- Anthony Carrabino www.vistadb.net Tag: get DataColumn metadata Tag: 119927
  - 14
    - Update Multiple table using Dataset Hi, I have an issue in updating the Primary Key in the Parent table. I have set of three tables (Database SQL2000, VB.NET), where A is related to B and B is related to C. I have written a stored procedure which returns all the records in these tables and fill up a single DataSet. All these table have primary key which is an Identity Column. The dataset then populates the three grids in the client application. The users can add/update/delete the values in all the three tables and there is single save button to do the updates. I am code I have created DataRelation between table A,B and C. Also when I add a new add to the child table, I set the parent row as well. All these is working fine. I am using the example as described in the CodeProject article http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883 But my problem here is when I add a row in the Parent table say A and then add new rows in the child table B. The new generated ID in the Parent table is not propagated to the rows in the child table. I am using RowUpdated event to capture get the latest ID returned from the stored procedure as output parameter. I use the latest ID to update the datatable. When I debug the application, I can retrieve the new ID in the RowUpdated event and set it back to the datarow: e.Row.Item("ColumnName") = newID. e.Row.AcceptChanges() But these changes are not seen in the dataset. It still has same old ID but in the DEBUG mode the e.row has the new value. Moreover, its an issue because the event arguments are passed as Byval and not as ByRef. i.e. I am not getting the actual reference to the DataRow. Has anybody faced this problems before? Please help... Tag: get DataColumn metadata Tag: 119923
  - 15
    - Fill table in dataset with query and then add columns? I'm filling up a dataset table with a database query. Then I'm binding the dataset to an ASP.Net asp:DataGrid. However, some derived columns have values that are too complicated to fill out in the SELECT statement in the query. Can one add additional columns to a dataset after initially creating a dataset with a database query? What I want to do is to fill up the dataset with a query, create additional columns (unless I can/should do that in the SELECT list?), loop thru the dataset and on each row do some logic to decide how to fill out the derived columns. The derived columns can all be text. I do not need to do updates on this table back into the database. Is this a reasonable approach? Tag: get DataColumn metadata Tag: 119919
  - 16
    - Fill related tables in dataset with one query? Thought this would be simple, but apparently its not. I have a strongly-typed dataset with tables that have relations set in the DataSet designer. I've been stymied when attempting to fill this dataset using a join query. Without TableMappings, nothing is filled in the dataset. I can't use table mappings, however, because it won't allow me to split one result set among four different tables. I can create a tablemapping for each table in the dataset, but the "source table" is the same in each one--the result set. No two table mappings can have the same source table, so it fails. If I was grabbing all data, it wouldn't be such a problem, as I could fill each table in turn. However, I'm performing a search at the same time, which means only relevant data is returned. Pulling it all down and then sorting through everything is not feasable, since the database could hypothetically contain millions of rows. What should I do? Use a DataReader and fill the dataset manually? TIA. Tag: get DataColumn metadata Tag: 119916
  - 17
    - Do DataTable's in ASP.NET 2.0 support "blob" datatypes? I have potentially large PDF legal files (up to 10MB) that will be passed to an imaging system via a webservice. Does anyone know which datatype I should use for this? IOW, is there something akin to a "blob", since this is binary data. Tag: get DataColumn metadata Tag: 119912
  - 18
    - Copy a table using ADO.Net OK, I think the answer is no, but I need to ask. I have a table structure defined in my Access database (SQL Server is not available), I would like to take that table and create a new table with a unique name. Can I do this using ADO.NET? Thanks, Dave Tag: get DataColumn metadata Tag: 119902
  - 19
    - Newbie with Database Connection Problem Hi, Wonder if Somebody could help me? I`ve been searching all over for a solution but am totally lost. I have a Class called Connection as below: Public Class Connection Private Shared mConnectionsstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Private Shared mLocation As String Private Shared connArray As ArrayList Public Shared Sub setString(ByVal ind As Integer) Dim connArray As New ArrayList connArray.Add("J:\Database\MasterDB.mdb") connArray.Add("J:\Database\TestDB.mdb") mLocation = connArray(ind).ToString End Sub Public Shared ReadOnly Property [String]() As String Get Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mLocation & ";" End Get End Property End Class On my Main Form I have my Database Connection Setup as: Dim UP As New OleDb.OleDbCommand() Dim Con As New OleDb.OleDbConnection() Connection.setString(Me.cboSelectDB.SelectedIndex) Con.ConnectionString = Connection.String UP.Connection = Con UP.CommandType = CommandType.Text UP.CommandText = "Select * From Passwords Where Users_Name=? and Users_Password=?" End Sub But when I run my App it Errors on line "mLocation = connArray(ind).ToString" with the following error: "Additional information: Index was out of range. Must be non-negative and less than the size of the collection" Anybody point me in the right direction? Many Thanks in Advance Regards Not_So_Clever Tag: get DataColumn metadata Tag: 119899
  - 20
    - Adding deleted rows back to a DataTable I'm trying to add rows marked as deleted back into their parent DataRowCollection by using DataRowCollection.Add. The behavior I was expecting was having the DataRowState switch back to it's last value before the delete, i.e. Unchanged->Deleted->Unchanged, Modified->Deleted->Modified, etc. Instead I get an ArgumentException with the message "This row already belongs to this table." Obviously the row does belong to the table, however it is not currently part of it's row collection. How would I go about implementing this? I know I can use RejectChanges but that will revert the state back to the last Original, which will undo any field changes as well. All I want to do is undo the last Delete action. I was considering adding a duplicate new row and loading the deleted row's values into it, but that might result in constraint violations when running the related stored procedures against the data source. Thanks in advance. Tag: get DataColumn metadata Tag: 119893
  - 21
    - Hierarchical search of tree flattened to dataset--advise pls I have a search problem that I'm seeking some advice in. I'm dealing with the need to search node names in a tree structure that is flattened into a database table (I have no say over the design, just over how to search). The tree consists of nodes that represent rooms, cabinets in each room, drawers in each cabinet, etc. The flattened representation is as what you would expect--each data row has a column representing the room, cabinet, drawer, etc. When I search this data, I need to do it in a hierarchical fashion--I look at the first room, then the first cabinet in that room, then the first drawer in that cabinet, etc. If I search the dataset linearly, I end up showing the same hit multiple times (if I hit on the room name, once I finish showing hits on that row, I encounter the room again the next row). I could, once a hit is found, move vertically down the column in the dataset, erasing all names that match, but that isn't easily done without performing multiple checks to make sure I'm still in the same branch of the tree. Any hints on a better way to perform this search would be appreciated. I could convert the data to xml and use xpath, or create a normalized dataset and fill it appropriately, but I'm not sure this will ease the search later. Thanks. Tag: get DataColumn metadata Tag: 119890
  - 22
    - SQL Server Raiserror and .NET SqlDataAdapter Running SQL Server 2000 SP4 and .NET Framework 1.1.43322 SP1 I have a trigger that does some work and then, when it decides there is a problem does: if @ErrMsg is not null BEGIN RAISERROR (50001,16,1, 'AssetConfig', 'AssetKey/Site', @ErrMsg) ROLLBACK transaction In my Windows Forms application I have try { SqlTransaction sqlTrans = â?¦.BeginTransaction(); Update several tables, including the one with the trigger sqlTrans.Commit(); } catch (SqlException e) { sqlTrans.Rollback(); } When the trigger raises the error, I do not get the @ErrMsg because the SqlDataAdapter does not trap the error. Instead, I get System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTransaction.Rollback() at the Rollback statement. This is because the Commit threw this SqlException Message # 3902 "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." What I WANT is to have the SqlDataAdapter.Update throw the error that I have specified, just as it would do if, for example, a primary or foreign key had been violated. Then the catch block would do the rollback and handle the error. I tried taking the Rollback out of the trigger, but then I got no message, no error, and the database was updated with bad data. What is the secret to making SQL Server Raiserror work with the Framework to return a meaningful error? Tag: get DataColumn metadata Tag: 119889
  - 23
    - Data Grid - Style Hi All! How can i add styles like combo box, checkbox in my datagrid control TIA Viswanathan S Tag: get DataColumn metadata Tag: 119884
  - 24
    - Datagrid - format Hi All! I want to display the currency symbol in the datagrid for all the rows of the salary (currency) field. How to do this? 'This is my sample code Dim cm As New OleDbCommand Dim da1 As New OleDbDataAdapter Dim ds1 As New DataSet Try cm.CommandText = "select distinct d.dept_name as deptname ,sum (e.salary) as salary from department d ,employee e where d.dept_id=e.dept_id group by d.dept_name" cm.Connection = Con da1.SelectCommand = cm da1.Fill(ds1, "employee") dg.DataSource = ds1.Tables("employee") Catch ex As Exception MsgBox(ex.Message) End Try TIA Viswanathan S Tag: get DataColumn metadata Tag: 119883
  - 25
    - build SQL text from SqlCommand Hi, is it possible to retrieve exact sql text which will be executed from a prepared SqlCommand or DBCommand? I need this for logging all activity. Something like this would be helpfull: Command.CommandText = "ExecProc" Command.Paramaters.Add..... SQL = Command.GetInternalSql 'or Command.GetType.InvokeMember ("GetInternalSql") Tag: get DataColumn metadata Tag: 119874

hello all,
i am looking for a way to get metadata of a DataColumn (in c#).

i want to write a switch-case statement(if it possible) that check the
Column DataType as declared at the database (adChar, adVarChar,adDate,
adDBDate, adDBTimeStamp,adDecimal, adSingle, adDouble, adNumeric, ..., ....)
and then to get for each one it's 'DefinedSize' and 'NumericScale' values.

in ADO Recordset it was very simple,
what is the best & right way to do it in ADO.net (C#)?
thank's
stan

Top

Re: get DataColumn metadata by William

William
Thu Oct 27 16:05:12 CDT 2005

In 2.0 you can use the Connection.GetSchema method.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"stanley" <stanley@discussions.microsoft.com> wrote in message
news:7624C7B6-FBCC-4719-AACD-6A15DFD422A2@microsoft.com...
> hello all,
> i am looking for a way to get metadata of a DataColumn (in c#).
>
> i want to write a switch-case statement(if it possible) that check the
> Column DataType as declared at the database (adChar, adVarChar,adDate,
> adDBDate, adDBTimeStamp,adDecimal, adSingle, adDouble, adNumeric, ...,
> ....)
> and then to get for each one it's 'DefinedSize' and 'NumericScale' values.
>
> in ADO Recordset it was very simple,
> what is the best & right way to do it in ADO.net (C#)?
> thank's
> stan
>

Top