How call crosstab query in Access

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
    - DataSet Losing Data when Column Name Contains Colon Hi, Somebody else has posted something in regards to this before, but that was back in 2002. You can see his post at: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=0f0c01c233f2%24988a7c30%24a4e62ecf%40tkmsftngxa06&rnum=4&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26q%3Ddataset%2Bcolon%2B.net Microsoft has also made mention of this same problem. You can see Microsoft's FIX at: http://support.microsoft.com/default.aspx?scid=kb;en-us;818962 The FIX was posted in late 2003, but I have not seen any resolution to the problem. The problem must still exist since I am experiencing it today (2004). So, my problem is this: I have a dataset that gets populated by calling multiple stored procedures. The dataset is successfully populated. The stored procedure returns column names that contain colons (":"). When I do WriteXml(fileName, WriteSchema), I the dataset is saved successfully. When I load the file back, using ReadXml(fileName, ReadSchema), the dataset (its datatables, rather) have appropriate structures. The problem, though, is that the datatables that have column names that contain colons, result in the column being completely populated with DBNULL. I need help finding a work around to this. Any help is greatly appreciated. Keep in mind that I cannot change the stored procedure (in order to return appropriate column names). I hope I was clear as to what my problem is. Again, any help is greatly appreciated. Regards. Jacques Tag: How call crosstab query in Access Tag: 93900
  - 2
    - know the MappingName of a clicked Column in a Parent-Child DataGrid Hi, I have in my VB.NET-application a DataGrid with two DataTables in with a Parent-Child-relationship defined. When the users clicks on a call in the DataGrid, i want to know the MappingName of the Column. This is easy with only one table, but with 2 tables defined I'm not able to know on which Table the User is currently looking (the parent of the child), so I don't know if I should look at the Parent-tablestyle or the Child-tablestyle for my mappingname. does anybody has any suggestions how to know on which Table the user is lookin,g or how to find the MappingName without any need of the TableStyle or soething else? Thanks a lot in advance! Pieter Tag: How call crosstab query in Access Tag: 93895
  - 3
    - NEWBIE - SQL SUBSTR Function in asp.net Greetings, I have a quick question here. Can I use the SQL SUBSTR() function in my SQL statements in an asp.net page? I am getting an error when I try. Can anybody point me to a list of functions that are recognized and valid for SQL statements in asp.net pages? Thank you, -Dave Tag: How call crosstab query in Access Tag: 93890
  - 4
    - did debugging with sql server sp3 ever get fixed? When I installed SP3 on SQL 2000, I lost the ability to debug stored procedures from Visual Studio - single stepping into the stored procedure. I spent a day trying to fix it maybe 8 months ago - and gave up. Are there any tech notes that actually work to restore the ability to debug when SQL Server is on the same machine as Visual Studio? -- Thanks in advance, Les Caudle Tag: How call crosstab query in Access Tag: 93887
  - 5
    - SqlDataReader.GetChar ?? exist or not Hello, If this exists http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetchartopic.asp Why doesn't it show in intellisense in VS.NET 1.1 and throw errors. I have a stored procedure returning a char(1) datatype.... How should I pull this datafield from my DataReader, I know I could use dr.GetSqlString(i) but shouldn't there be something for a char or fixed length? Thanks, Matt Tag: How call crosstab query in Access Tag: 93886
  - 6
    - How to do this with SQL Hi! This is not clearly ADO.NET question, but I need this with VB.NET and ADO.NET anyway. It is possible to make SQL-query like ... SELECT 0 AS IntegerField, '' AS StringField ... ... to get field which really don't exists in database, but how to get this kind of "virtual field" as boolean type (true or false)? I need this as an aid field for user's temporary marking use with real database fields when form is loaded in SELECT SQL-query with ADO.NET. -- Thanks in advance! Mika Tag: How call crosstab query in Access Tag: 93882
  - 7
    - Expression Columns with Dates I was wondering if someone could enlighten me about using date/time variables in column expressions. I am trying to setup a timesheet style form. The datatable that holds the information is filled by code, no database interaction. I'm aware of the DateDiff function in SQL, but I can't get it to work in an expression column. Is there another function that I should be using, or do I have to do a work-around in code? Tag: How call crosstab query in Access Tag: 93881
  - 8
    - How to get distinct list of a column from a DataTable Hi; I am trying to do some calc for a DataTable populated from database. The DataTable has composite key e.g.ID, Date. My question is: What is the best and effective way to get the distinct list of ID from DataTable just like SQL "select distinct ID from table"? Thank you for your help! Yi Tag: How call crosstab query in Access Tag: 93873
  - 9
    - set parameter value fail? Hello, everyone: I have a store procedure just like: select name, id from user where( id in (@id)) When I try to set the parameter (@id) value with multiple id, I always get error, could anyone tell me how to set the value correctly? what I have done is: com1.parameters["@id"].value="1,2,3,"; but the type of this parameter is int. thanks for your help. haiwen Tag: How call crosstab query in Access Tag: 93864
  - 10
    - Connection to dBase file but not using .dbf extentions Hey all, Was wondering if there is another parameter to pass along with the rest of the connection string to have Jet look for a dBase connection using something other than .dbf? Here's my conn string for reference... "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cDataPath & ";Extended Properties=dBASE IV;" We just happen to use the extension .DAT for all of our database files. Thanks in Advance for the help. Andy Tag: How call crosstab query in Access Tag: 93862
  - 11
    - DataSet - Forcing Values in IDENTITY fields There are numerous posts and articles on retrieving the IDENTITY values after inserts from Dataset. However, is there a way to force a value of an identity column through Dataset? The objective is to retrieve bulk rows from a source database and insert into a target database keeping the values of Identity columns intact. Thanks in advance! Tag: How call crosstab query in Access Tag: 93861
  - 12
    - Adding new records when databinding and using data relation win forms / data form wizard Hi, I have a dataset with two tables and a relation between them. I have controls (textboxes, check boxes etc) on a windows form bound to those data tables. Editing existing data and navigating between records works fine. However when I try to add a new record, only the master table is updated to the dataset (and eventually to the database). I struggled with this problem for a while and then decided to check what VS.NET data form wizard would generate in a similar situation. So I did the following actions: 1) added dataform to windows app .Net project 2) connected to Northwind database (SQL server) 3) created a new dataset 4) selected Orders and Order Details tables 5) created a datarelation between Orders and Order Details -tables ( Orders being the parent and Order Details the child). 6) chose all columns to be displayed from both tables 7) selected 'single record in individual controls' The generated form works fine when editing existing data or navigating. However when trying to add a new record, the master portion of the order (ie. individual text boxes) has to be saved prior to saving any Order Details rows. If the master portion isn't saved, "ForeignKeyConstraint 'x' requires the child key values (eg. 11088) to exist in the parent table. Do you want to correct the value?" message is displayed. ['x'= name of the datarelation in step 5]. So it's not possible to fill in all the necessary fields and then just hit 'update' to save the new order. Interestingly the OrderID is populated correctly in the datagrid with or without saving the master portion?! So how should the wizard generated code be amended to allow users to save an order by hitting update button just once? Any thoughts and/or pointers would be appreciated. Thanking in advance! Tag: How call crosstab query in Access Tag: 93860
  - 13
    - Strongly Typed Dataset Table - Indexed Find returns Nothing I have a strongly typed dataset and use the "FindBy.." function to search for a particular record based on the Primary Key. Generally, this works fine, but every once in a while, the find function fails and will not work anymore. In other words, when searching for a particular record, the FindBy function returns nothing even though the record definitely still exists. I have included some items from the Watch List below: _dstMain.FuturesContracts.Rows.Count 428 Integer DirectCast(_dstMain.FuturesContracts.Rows(273),FuturesDataset.FuturesContractsRow).FuturesCode "NG " String DirectCast(_dstMain.FuturesContracts.Rows(273),FuturesDataset.FuturesContractsRow).FuturesContract "U04 " String _dstMain.FuturesContracts.FindByFuturesCodeFuturesContract("NG ", "U04 ") Nothing As you can see, the dataset has 428 rows and row 273 contains the values "NG " for FuturesCode and "U04 " for FuturesContract. The function FindByFuturesCodeFuturesContract returns Nothing even though I am searching for a row that exists. (I also tried searching with the trailing spaces trimmed, but that did not help.) This table is being updated very often, though rows never get deleted or added. I also noted that the table in question seems to have 10 indexes. When the failure occurs, it seems to have 12 indexes, many with errors. I don't know where these extra indexes are coming from but I assume that they are part of the problem. Any help would be greatly appreciated. Thanks. Dave. Tag: How call crosstab query in Access Tag: 93854
  - 14
    - Binding datagrid with datset from DLL I am binding a datagrid to a dataset. The data displays fine, BUT if I do anything that causes the grid to repaint a few times (like scrolling the columns or even just moving the window so that part of the grid is off the screen), then the entire application hangs. I am binding like this: theGrid.DataSource = AppObject1.ds.Tables[0].DefaultView ; where AppObject1 is instantiated from a DLL like this: AppObject1 = (IAppClass)assembly.CreateInstance("MyNameSpace.AppClass1") I tried creating a copy of the dataset in the main program using ds.Copy(). When I bind to this new dataset, everything works without hanging. Is there a problem with binding a dataset that was created in a separate component? How can I workaround the problem without having to copy the entire dataset? Thanks, Yitz Tag: How call crosstab query in Access Tag: 93853
  - 15
    - DataReader questions Can a DataReader contain multiple result sets? I know a dataset can but I haven't seen the same statements regarding a DataReader? Also, how do you loop through multiple tables in a result set, whether its a dataset or data reader? Thanks! Norton Tag: How call crosstab query in Access Tag: 93849
  - 16
    - CacheSize Property Hi, I used to set the value of CacheSize property for Recordset in my VB apps(ADO 2.7). What property now I can use instead of CacheSize in ADO.NET? Any help would be greatly appreciated. Amin Tag: How call crosstab query in Access Tag: 93848
  - 17
    - Microsoft's DNP for Oracle. HELP!! (sorry for the x-post) Hello all, I am currently working on a project that uses MS's .net provider for Oracle. Now, here is where it gets WEIRD!!! for some reason when we run it in VS.NET's IDE it is fine, a previous build deployed (xcopy) onto a test PC is fine. However; when we have recently but a new build on it doesn't work. The only thing (and I ain't buying it) that my work mate says it is down to is that the path of the EXE has brackets in it!!! When we remove the brackets it is fine (something ain't right)!!!! Has anyone had a similar scenario or a explanation for this bizarre behaviour???? Regards Alan Seunarayan Tag: How call crosstab query in Access Tag: 93842
  - 18
    - How to read Constraints (Primary key, FK,etc) over any table. Hi, Iam trying hard to read the constraints over any table using System.Data obects in C#. Can anyone suggest the method for retreivint the constraints on any table . Moreover when I try reading tableschema(as XMLfile) it gives the coumn name and type ,but it do not give me the column length. How I can get the length of coumn? Vinay C Tag: How call crosstab query in Access Tag: 93839
  - 19
    - masterdetail need help ! Hi all I have a form with 2 datagrids (master-detail) and a combobox to create a filter for the masterdatagrid. The master-detail worked fine when i used a dataset like this : drelRelation = New DataRelation _ ("drelMasterDetail", dsMasterDetail.Tables("tblMaster").Columns("ID"), _ dsMasterDetail.Tables("tblDetail").Columns("MasterID")) dsMasterDetail.Relations.Add(drelRelation) dtgMaster.SetDataBinding(dsMasterDetail, "tblMaster") dtgDetail.SetDataBinding(dsMasterDetail, "tblMaster.drelMasterDetail") BUT now I wanna use the combobox for filtering, so I thought I could use the DataView and the RowFilter-property but then i can't get my master-detail part right????? anyone any idea? or is there a better way for this? I'm still a newbie Thanx in Advance Tag: How call crosstab query in Access Tag: 93837
  - 20
    - Crystal Report - Object reference not set to an instance of an obj I can't find anything on this.. anyone else have it? I looked on the crystal site but it lists the problem only for asp. I'm not usin asp, but winforms. I write the reports in CR for .NET and save them as .rpt files. Then i reference them in my app. I built a form usin the viewer, pass in all the crap, build and deploy... I start the app up and hit the form to bring up the report. That part works great. I can see the report just fine, data is there and all, and i can export it or whatever. BUT if i click on the print icon of the viewer it brings up the print options and U click ok, then... Object reference not set to an instance of an object What obj ref? It's the viewers print function... and it has the report.... Any ideas? Tag: How call crosstab query in Access Tag: 93834
  - 21
    - WQL DataProvider for ADO.Net Hi, WQL DataProvider is an ADO.Net data provider, so you can use it just like usual MS SQL DataProvider, but for WQL (WMI + SQL = WQL). This DataProvider supports SELECT, ASSOCIATORS and REFERENCES queries. Doesn't support event queries. http://www.yogageneration.com/~mvkazit/demo/WQLDataProvider/index.html -- Thanks, Tag: How call crosstab query in Access Tag: 93831
  - 22
    - KB article 830118 "Timeout Expired" - Cause? Hello, I have just had a bad experience upgrading a Windows Forms app from vb.net 2002 -> vb.net 2003. I experienced the problem as detailed in KB article 830118. According to the article, this behaviour is by design. Can someone please explain to me what the design goal was, and what is going on "under the hood" to cause this? Will it be resolved in future service packs and/or .net versions? Thanks. Tag: How call crosstab query in Access Tag: 93826
  - 23
    - Renumbering MS db ID? I have a small MS db which I update daily with new info. I've noticed that the ID numbers are increasing sequentially. How can I reset them back to 1 when I purge my data prior to adding in the new stuff? Here is how I'm currently deleting the info from my db (TSUpdate): Private Sub ClearMyData() ' Open Connection and Clear Data OleDbConnection1.Open() Dim MyCommand As New OleDbCommand("DELETE FROM TSUpdate", OleDbConnection1) MyCommand.ExecuteNonQuery() OleDbConnection1.Close() MyCommand.Dispose() End Sub Thanks in advance! Bruce Tag: How call crosstab query in Access Tag: 93824
  - 24
    - number of connection Hi How can I know how many connections is estabilished with database in SQL Server? Thanks Konrad Tag: How call crosstab query in Access Tag: 93820
  - 25
    - Problems in reading excel data Hello All I'm having some problems in reading excel data. The connection string is like this connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=" connectionString += "\"Excel 8.0;HDR={1};IMEX=1;ReadOnly=0;\""; It's not reading data which has an F appended to it (with any Alpohabet as suffix), like in 49844F or 32535X which is in coln called STOCK_NO. Any other numeric data, like 49929 is being read fine. This coln is being read into a column of type string and it's coming in as empty string since the resultant DataSet's xml is looking like this <MASTER> <RECD_DTE>2004-07-06T00:00:00.0000000-05:00</RECD_DTE> <STOCK_NO /> <MANUF_PRICE>42565</MANUF_PRICE> <INV_AMT>29960</INV_AMT> <LOW_PRICE>29661</LOW_PRICE> <SAVINGS>12904</SAVINGS> </MASTER> <MASTER> <RECD_DTE>2004-07-02T00:00:00.0000000-05:00</RECD_DTE> <STOCK_NO>49929</STOCK_NO> <MANUF_PRICE>47140</MANUF_PRICE> <INV_AMT>40212</INV_AMT> <LOW_PRICE>37810</LOW_PRICE> <SAVINGS>9330</SAVINGS> </MASTER> Any help is greatly appreciated. thanks Sunit Tag: How call crosstab query in Access Tag: 93815
- Next
  - 1
    - ADO.net update problem with VB and MS Access Greetings all, This is a repost. I had to put this down for a while, but now I have to get it running. I am using an Access Database to store question information for a questionnaire application. I am using VB.net as the front end, and ADO.net to retrieve the information from the database. My data is bring displayed in a DataGrid bound to a dataset. All of my calls to the database are to parameter queries. The problem that I am having is with the update command. When I call the update method for my data adapter, and pass a dataset containing just the changed records, only one record shows the change. I know that parameter queries work because data is being returned and update, just not in the quantities that I want. From what I have read, the update method in ADO.NET should be able to process a batch change, but the only specific information I have been able to find concerns SQL Server and not MS Access. The only information I have been able to find regarding the update method and MS Access were for single records. Does ADO.net send each update request separately, waiting for conformations between requests, or does it send all of the changes at once? I know, if you write the queries properly, SQL server can process the data in a lump sum, but I do not believe Access has that ability. Below is a copy of the offending code. Any help on this would be greatly appreciated. Thanks in advance, Edison Private Sub SaveData() Dim lRetVal As Long Dim oQuestions As Questions = New Questions Dim oDS_Changes As DataSet = New DataSet Dim sMsg As String 'Stop current edits 'Me.BindingContext(m_oDS).EndCurrentEdit() 'Grab all changed rows oDS_Changes = m_oDS.GetChanges 'Check to see if any changes have been made If (Not (oDS_Changes) Is Nothing) Then sMsg = "Are you sure you want to save these " & _ oDS_Changes.Tables(0).Rows.Count() & " rows to the database?" _ lRetVal = MsgBox(sMsg, Microsoft.VisualBasic.MsgBoxStyle.Question + _ Microsoft.VisualBasic.MsgBoxStyle.YesNo, "Save Records") Select Case lRetVal Case vbYes Try ' Save all changes oQuestions.SaveData(oDS_Changes) m_oDS.AcceptChanges() Catch updateException As System.Exception Throw updateException Catch e As Exception Throw e End Try Case vbNo 'Do nothing End Select End If End Sub Public Function SaveData(ByVal oDS As DataSet) As String Dim strMsg As String Dim lngRecsAffected As Long Try If (Not (oDS) Is Nothing) Then 'open connection to Database m_oCn.Open() 'attempt to update the data source da.Update(oDS, strClassName) End If Catch updateException As System.Exception Throw updateException Finally 'Close the Database Connection m_oCn.Close() End Try End Function Dim cmdSel As OleDbCommand Dim cmdIns As OleDbCommand Dim cmdUpd As OleDbCommand Dim cmdDel As OleDbCommand Dim strSQL As String 'Setup Select Command strSQL = "QryQuestion" cmdSel = New OleDbCommand(strSQL, m_oCn) cmdSel.CommandType = CommandType.StoredProcedure 'Setup Insert Command strSQL = "addqryQuestion" cmdIns = New OleDbCommand(strSQL, m_oCn) cmdIns.CommandType = CommandType.StoredProcedure cmdIns.Parameters.Add("QuestionText", OleDbType.LongVarWChar, 255, "Question_Text") cmdIns.Parameters.Add("CorrectAnswer", OleDbType.BigInt, 1, "Correct_Answer") cmdIns.Parameters.Add("QuestionNumber", OleDbType.BigInt, 1, "Question_Number") 'Setup Update Command strSQL = "updqryQuestion" cmdUpd = New OleDbCommand(strSQL, m_oCn) cmdUpd.CommandType = CommandType.StoredProcedure cmdUpd.Parameters.Add(New OleDbParameter("@QuestionNumber", OleDbType.BigInt, 50, "Question_Number")) cmdUpd.Parameters.Add(New OleDbParameter("@QuestionText", OleDbType.LongVarWChar, 255, "Question_Text")) cmdUpd.Parameters.Add(New OleDbParameter("@CorrectAnswer", OleDbType.BigInt, 50, "Correct_Answer")) cmdUpd.Parameters.Add(New OleDbParameter("@QuestionID", OleDbType.BigInt, 50, "Question_ID")).SourceVersion = DataRowVersion.Original 'Setup Delete Command strSQL = "delqryQuestion" cmdDel = New OleDbCommand(strSQL, m_oCn) cmdDel.CommandType = CommandType.StoredProcedure cmdDel.Parameters.Add("QuestionID", OleDbType.BigInt, 50, "Question_ID") initalizeDataAdapter(cmdSel, cmdIns, cmdUpd, cmdDel) End Sub Protected Sub initalizeDataAdapter(ByVal cmdSel As OleDbCommand, ByVal cmdIns As OleDbCommand, ByVal cmdUpd As OleDbCommand, ByVal cmdDel As OleDbCommand) 'Create and Setup Data Adapter da = New OleDbDataAdapter 'Setup Select Command da.SelectCommand = cmdSel 'Setup Insert Command da.InsertCommand = cmdIns 'Setup Update Command da.UpdateCommand = cmdUpd 'Setup Delete Command da.DeleteCommand = cmdDel 'Destroy connection object ' m_oCn.Close() End Sub Tag: How call crosstab query in Access Tag: 93812
  - 2
    - sp_setapprole encryption and .NET If I run exec sp_setapprole @rolename='app',@password={Encrypt N'app'},@encrypt='odbc' from MS query analyzer it sets up okay. I have a guest account in the public role in the database I connect to run this. If I am my .Net program and try to run it as follows strConn="Initial Catalog=MyDB;Data Source=MyServer;user id=guest"; SqlConnection oConn=new SqlConnection(strConn); oConn.Open(); SqlCommand oCmd=new System.Data.SqlClient.SqlCommand("exec sp_setapprole @rolename='app',@password={Encrypt N'app'},@encrypt='odbc'",oConn); iResult=(int)oCmd.ExecuteNonQuery(); It throws an error 'Encrypt' is not a recognized ODBC date/time extension option. If I take out the Encrypt part, as shown below, it works fine. SqlCommand oCmd=new System.Data.SqlClient.SqlCommand("exec sp_setapprole @rolename='app',@password='app',@encrypt='odbc'",oConn); Any ideas on the error? Tag: How call crosstab query in Access Tag: 93810
  - 3
    - SqlDataAdapter and Update grams Does anyone know if theres ever been any development effort of writing an extended version of SqlDataAdapter that uses updategrams to update a dataset. I'm curious if the reduced network round trips would be a sizable performance gain. An entire dataset worth of updates/inserts/deletes could be sent in one request, instead of many. If so, please point me at a link, if not please provide feedback on why you think this would or would not be a good idea to do. Thank you. Tag: How call crosstab query in Access Tag: 93808
  - 4
    - CrystalReports.Engine.LogOnException with DataSet Hello! I have a problem: a created a crystal report based on a DataSet in the project (chosen in the first step of the CrystalReports wizard). When I try to access the report (for ex. by the report viewer9 I get crystalDecisions.Engine.LogOnException with additional information: Logon failed. It seems I have to logon to the DataSet!? How can I solve this problem?? Thanks! Tag: How call crosstab query in Access Tag: 93807
  - 5
    - How to Use Openschema ? Hi, I would like to read all the tabs stored in a Excel sheet. How can I do this with Ado.net (vb.net). I suppose I have to use the OpenSchema method, but I can't see how (I used to do it with ado). Thanks for your help Stan Tag: How call crosstab query in Access Tag: 93797
  - 6
    - select top and next Hi I'am using select top 10 clause to load DataSet Ho to get next 10 rows (from 11 to 20) to DataSet? Thanks Konrad Tag: How call crosstab query in Access Tag: 93795
  - 7
    - Know the DataReader associated with a Connection Hi, Is it possible to know the associated DataReader of a Connection? I know that there can only be a single open DataReader linked to a Connection; my question is: how do I get a pointer to that instance (if there is one) just knowing the Connection? Thank you, Peter. Tag: How call crosstab query in Access Tag: 93783
  - 8
    - a simple question I have a simple question here, ConnectToDB(); string queryString = "SELECT Owner,Description FROM Groups WHERE Title = 'BOSS'"; MySqlCommand myCmd = new MySqlCommand(queryString,mycon); try { lblOwner.Text = (string)myCmd.ExecuteScalar(); } finally { myCmd.Dispose(); mycon.Dispose(); } it's only get one(Owner) value back, so what I need to do so I can pull both Owner and Description from database? Thansk in advance. Tag: How call crosstab query in Access Tag: 93780
  - 9
    - Updating from active record Hello All! I have a DataTable connected to Windows Forms controls thru databinding. The DataTable gets its data from the controls if the user moves te cursor, e.g. in a binded list. Is there a simple method to pass the fresh data to the DataTable without moving the cursor in the list? Thanks, Sztyopi Tag: How call crosstab query in Access Tag: 93777
  - 10
    - Dataview Rowfilter misbehaving Hi, I have a dataview with a rowfilter of: "Column <= '~'". The strange thing is that if I run the code using Visual Studio debugger then I get the expected rows. However when I build the code into its assemblys and run it outside of visual studio then I do not get the expected rows. The underlying Datatable and Dataset are set for CaseSensitive=True. Following is a snippet of the code and some console output: CONTACTS(LOAD): UA: w DataviewRowCount: 1 CONTACT_TYPE_PL <= 'w' R True 1 Dim privchar As String If "~()#\/=><+-*%&|^'""[]".IndexOf(Me._synonyms.UserPriv) >= 0 Then privchar = "\" + Me._synonyms.UserPriv Else privchar = Me._synonyms.UserPriv End If ContactDataView = New DataView(Me._stuadrk, _ "CONTACT_TYPE_PL <= '" + privchar + "'", _ "SEQUENCE_NO", _ DataViewRowState.CurrentRows) Console.WriteLine("CONTACTS(LOAD): UA: " + Me._synonyms.UserPriv + " DataviewRowCount: " + ContactDataView.Count.ToString) Console.WriteLine(Chr(9) + ContactDataView.RowFilter) For Each DR As DataRow In Me._stuadrk.Rows Console.WriteLine(Chr(9) + DR.Item("CONTACT_TYPE_PL") + Chr(9) + (CType(DR.Item("CONTACT_TYPE_PL"), String) <= Me._synonyms.UserPriv).ToString + Chr(9) + CType(DR.Item("SEQUENCE_NO"), Integer).ToString) Next This is the console output: CONTACTS(LOAD): UA: ~ DataviewRowCount: 0 CONTACT_TYPE_PL <= '\~' R True 1 As I said if I run this code in Visual Studio it returns the following output: Tag: How call crosstab query in Access Tag: 93776
  - 11
    - Drowning in the connection pool This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C48124.05EA6670 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable First let me state for the record I have searched and tried all = suggestions I have found, and nothing seems to have helped so far.=20 I have 2 threads created to update a UI w/data from the database. The = connection/command/reader object are all declared outside of the try = statement, and then closed/disposed in the finally portion. The = connections are not left open for any length of time greater than what = is needed.=20 I get "Timeout expired. The timeout period elapsed prior to obtaining a = connection from the pool." when.. 1) I am connection to the database remotely via VPN over cable modem -and- 2) using this connection string (note the integrated security) public const string sConnect =3D "persist security info =3D False;packet = size =3D 8192;data source=3DMYSERVER;Integrated Security =3D = true;initial catalog=3DMYDB"; I don't get that error when.. 1) I am NOT trying to connect over VPN -OR- 2) When I use this connection string (NOT using integrated security) public const string sConnect =3D "uid=3Dsa;pwd=3Dxxxxx;packet size =3D = 8192;data source=3DMYSERVER;persist security info =3D False;initial = catalog=3DMYDB"; This issue pretty much started when I tried to switch over to my final = settings of not using the sa account and using Windows security for the = users. Am I missing a setting on the server or client? Turning = pooling off isn't overly viable since it slows to a dead crawl. ------=_NextPart_000_000B_01C48124.05EA6670 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>First let me state for the record I = have searched=20 and tried all suggestions I have found, and nothing seems to have helped = so far.=20 </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have 2 threads created to update a UI = w/data from=20 the database.  The connection/command/reader object are all = declared=20 outside of the try statement, and then closed/disposed in the finally=20 portion.  The connections are not left open for any length of time = greater=20 than what is needed. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I get "Timeout expired.  The = timeout period=20 elapsed prior to obtaining a connection from the pool."</FONT></DIV> <DIV><FONT face=3DArial size=3D2>when..</FONT></DIV> <DIV><FONT face=3DArial size=3D2>1) I am connection to the database = remotely via VPN=20 over cable modem</FONT></DIV> <DIV><FONT face=3DArial size=3D2>-and-</FONT></DIV> <DIV><FONT face=3DArial size=3D2>2) using this connection string (note = the=20 integrated security)</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>public const string sConnect =3D = "persist security=20 info =3D False;packet size =3D 8192;data source=3DMYSERVER;Integrated = Security =3D=20 true;initial catalog=3DMYDB";</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV>I don't get that error when..</DIV> <DIV>1) I am NOT trying to connect over VPN</DIV> <DIV>-OR-</DIV> <DIV>2) When I use this connection string (NOT using integrated = security)</DIV> <DIV> </DIV> <DIV><FONT face=3DArial size=3D2>public const string sConnect =3D=20 "uid=3Dsa;pwd=3Dxxxxx;packet size =3D 8192;data = source=3DMYSERVER;persist security info=20 =3D False;initial catalog=3DMYDB";</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>This issue pretty much started when I = tried to=20 switch over to my final settings of not using the sa account and using = Windows=20 security for the users.  Am  I missing a setting on the server = or=20 client?  Turning pooling off isn't overly viable since it slows to = a dead=20 crawl. </FONT></DIV></BODY></HTML> ------=_NextPart_000_000B_01C48124.05EA6670-- Tag: How call crosstab query in Access Tag: 93772
  - 12
    - Bug on DataRow object´s GetColumnsInError method According to .NET Framework v1.1 Documentation, the DataRow object´s GetColumnsInError method gets an array of columns that have errors. This description made me think, develop and test a .NET Windows Application in a disconnected scenario. This application has a WindowsForm with a SqlDataAdapter object that fills a DataSet object. The resultant relational data is then displayed on a DataGrid control. At this time, the data can be modified. In this situation, various instances of the same .NET Windows Application running simultaneously can generate a System.Data.DBConcurrencyException when the SqlDataAdapter object´s Update method is called. Note that the SqlDataAdapter object is configured to use optimistic concurrency. If this exception occurs, the exception-handling structure´s Catch block can implement code to show, for example, a friendly message to the user detailing the accurate cells that had caused the errors. DataSet, DataTable and DataRow objects have the boolean property HasError that help developers to find rows with errors. The only information missing to show in the friendly message is the columns with these errors. To discriminate only the rows is not enough. To get these columns, the perfect method would be GetColumnsInError of the DataRow object. But concurrency exceptions do not set errors in DataColumns objects. In other words, the GetColumnsInError method do not return any columns. I assume that this behavior is a bug. Is there another way to reach this goal? Thank´s, Otávio F. Ferreira Filho [Microsoft Certified Professional] Tag: How call crosstab query in Access Tag: 93770
  - 13
    - Execute Multiple Scripts from one directory I got tired of having to execute multiple scripts one at a time from my testing server to my production server. So I decided to create a batch routine that utilizes the command line routine OSQL. All you have to do to use it is place the batch file in a directory containing your SQL scripts and execute the batch file. I have placed in the command the argument to print a result file for each sql script. If you need your SQL scripts run in a particular order simply rename them accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql enjoy REM ############################################# REM Author: Joe Ocampo REM Date: 04/17/2004 REM Version: 1.0.1 REM ############################################# REM You must execute the script from the SQL server itself REM and you must use the SA login or the DBO login of the REM target database. REM ############################################# SET login=sa SET password=password SET server=local SET database=pubs for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i %%a -o %%a_result.txt Tag: How call crosstab query in Access Tag: 93767
  - 14
    - Datareader type conversion I have some data stored in a column of Int16 type (for compactness and convenience) in a Jet 4 database. I'd like to retrieve it into a datareader converted to double type and divided by 10 to restore its true value. Is this possible please (using VB.Net)? So what I'd like to do is have a select string along the lines of: SELECT CDbl(Col_A/10) SomeName, Col_B ...etc FROM etc But if indeed this is a permitted process I can't find what the correct syntax should be. Using the sort of example above just gives me a missing operator error. TIA JGD Tag: How call crosstab query in Access Tag: 93765
  - 15
    - Array to Database I'm using OLEDB in .NET to get my data into a database. The data starts off in an 2-D array of singles where the first dimension represents columns in a database table and the second dimension represents rows in the same table. At the moment I have a single connection & transaction and I am loading the data into the database by iterating through the array and creating an INSERT OLEDBCommand for each row in the array and then executing the command. This works but is very slow. Is there a quicker way to get an array such as mine into the database? -- Cheers, elziko Tag: How call crosstab query in Access Tag: 93761
  - 16
    - Adding columns to a datareader Is there a way of adding a derived column to a datareader once the data reader has been populated or do I need to use a dataset/datatable for this? (I need to apply a function to one of the columns in a way that can't be done within a Select expression and so the only way I can see of doing this is - in theory at least - to add a column to the dr and then to populate that added column.) Presumably if I do add a column to either dr or ds/dt I'll need to iterate through every row to create the new value. Or is there a way of applying a function to a column in a single operation? TIA JGD Tag: How call crosstab query in Access Tag: 93753
  - 17
    - [ANN] Firebird ADO.NET Data Provider 1.6.1 released Hello: The Firebird ADO.NET Data Provider 1.6.1 is available for download. Release notes can be found here: http://www.firebirdsql.org/index.php?action=view_item&topic=1092389058 Download information can be found here: http://www.firebirdsql.org/index.php?op=files&id=netprovider Release notes: 1.6.1 ( 2004-08-13 ) ----- - ---- -- -- - * Bug Fixes (Please review the changelog for details) - Fixed bug #1007104 - Fixed bug #995178 - Fixed bug #998002 - Fixed bug #1000160 - Fixed bug #1003519 * Removed the limitation of one DataReader per connection. (Now you can have many DataReader's per connection and only one per command). * Changes on Database schema support: * All source files has been renamed to remove the Schema suffix (FbTablesSchema.cs -> FbTables.cs) The same for the class names. * There are a new 'Restrictions' schema that will return the restriction columns for each schema. * There are new columns in some schemas: XXXX_CATALOG XXXX_SCHEMA That will make the returned schemas more general and compatible with other databases (what will be nice for .net 2.0 as the schema support is in the System.Data.Common.DbConnection class) These new columns are restriction columns as well (the value will be null always) * New schemas. - DataTypes. Returns the data types information. - MetaDataCollections. Returns all the supported schemas, and the number of restrictions of each one - Restrictions. Returns the restriction columns of each schema. * New FbConnection methods. - GetChema() - GetSchema(string) - GetSchema(string[]) * Changes on the names of the FbDbSchemaType elements to remove the underscore. ** Some of the changes in the Schema Results are: ** * Columns Schema. - The COLUMN_DATA_TYPE now holds the datatype name. - Added new IS_ARRAY column. - Removed PROVIDER_TYPE, PRIMARY_KEY and UNIQUE_KEY columns. (The reason is that the query with that columsn gets a lot time to be executed in databases with complex metadata) * Tables schema. - The IS_SYSTEM_TABLE column now will have aloways 0/1 as values. - Not it's no possible to filter by the IS_SYSTEM_TABLE column, instead filter by TABLE_TYPE can be done using: a. TABLE b. VIEW c. SYSTEM TABLE * Domains Schema. - The COLUMN_DATA_TYPE now holds the datatype name. * ProcedureParameters Schema. - The COLUMN_DATA_TYPE now holds the datatype name. * ViewColumnUsage Schema. - The COLUMN_DATA_TYPE now holds the datatype name. * Generators schema. * The IS_SYSTEM_GENERATOR column now will have always 0/1 as values. * Functions schema. * The IS_SYSTEM_FUNCTION column now will have always 0/1 as values. * Triggers schema. * The IS_SYSTEM_TRIGGER column now will have always 0/1 as values. * ForeignKeys schema: * The order of the restriction columns is changed: 1. PK_TABLE_CATALOG 2. PK_TABLE_SCHEMA 3. PK_TABLE_NAME 4. FK_TABLE_CATALOG 5. FK_TABLE_SCHEMA 6. FK_TABLE_NAME CVS Tag: NP_1_6_1 -- Best regards Carlos Guzmán Álvarez Vigo-Spain Tag: How call crosstab query in Access Tag: 93752
  - 18
    - Refreshing dataset in Visual Studio. Hi, I have growed tired of deleting and then recreating a dataset every time I need to refresh it's column information. Is there some way to "refresh" a dataset? If not, this must be one of the most cumbersome issues with ADO.NET I have experienced so far... Thanks in advance for any suggestions, Trygve Tag: How call crosstab query in Access Tag: 93750
  - 19
    - Oracle distributed transaction I'm using the System.Data.OracleClient namaspace and I get he following problem. In the main thread I open a connection to the database, I query the DB retrieving all the available stored procedure, allowing the user through a GUI to pick a stored procedure a execute it in parallele in different thread. Every thread open a new connection to the DB ! But if the stored procedure issue a rollback statement I get an error from oracle: i can't rollback in a distributed transaction ! But I really don't need a distributed transaction ! I would that any thread session had its ownn transaction ! Any suggestion ? Tag: How call crosstab query in Access Tag: 93747
  - 20
    - How To read tab delimited file using ADO, Connect to text driver? Hi, I can connect to text file driver with coma delimited file, how not Tab delimited? if I use the following : conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _ "DBQ=" & sFilePath & ";FMT=TabDelimited;", "", "" it does not work. Thanks Jessie Tag: How call crosstab query in Access Tag: 93742
  - 21
    - ASP v2 & 3-tier or 2-tier Dear All, ASP 2 introduces a lot of controls that bind directly to a database. Obviously, this does not work for everything so it seems to me that one ends up with a mixture of 2-tier and 3-tier model. This in my opinion is quite bad as I really would like to see the same structure for the whole app. Can somebody with experience in asp 2 comment on that? How are you handling this? Regards, Rob Tag: How call crosstab query in Access Tag: 93741
  - 22
    - Excel to Dataset? Hi All, How to import data from Excel to dataset? Code please,if Possible. -- Cheers, Akhil Tag: How call crosstab query in Access Tag: 93735
  - 23
    - How to query a DBF with index by OLEDB? Hi all, Is there any way to connect the dbf with oledb and using .idx index file? Because i find that it is very slow when the dbf is large. Thanks Tag: How call crosstab query in Access Tag: 93733
  - 24
    - DB connection problem I have a SqlServer database which I try to access from the CompactFramework/PocketPC. In my connection string I haven't specifyed the database as I want to query them throught the sp_databases stored procedure. But I have specifyed and administrator user/password. When I try to connect to the server it returns the following SQL error: "not associated with a trusted SQL Server connection" What could it be? All I found on the net was some NTLM authentification problem, but I guess my problem is different, it's not a web browser issue! -- Lloyd Dupont, Software Architect Get Your Data Mobile http://www.ihookdb.com Tag: How call crosstab query in Access Tag: 93723
  - 25
    - weird connection error Hi guys, I have 2 data adapters da1 and da2, 1 connection con1, and one data set ds1. I use the same connection for all this data adapters. when I change from one record to other on the table JOBS under da1 it trigger one event. In this event, I have a code like System.Windows.Forms.BindingManagerBase bc; bc = this.BindingContext[this.ds1, "jobs"]; this.ds1.WorkEmp.Clear(); if (bc.Count>0) { System.Data.DataRow drv; drv = this.ds1.jobs.Rows[bc.Position]; this.daWorkEmp.SelectCommand.Parameters["njob"].Value=Convert.ToInt16(drv["njob"]); try { this.daWorkEmp.Fill(this.ds1.WorkEmp); // ERROR HAPPENS HERE } catch (Exception e3) { MessageBox.Show(e3.ToString()); return ; } } I got this error message "System.invalidOperationException: there is already an open DataReader associated with this connection which must closed first" WorkEmp.SelectCommand is a like SELECT b.firstname, b.lastname, a.empid, a.njob, b.empid AS Expr1 FROM WorkOn a INNER JOIN employee b ON a.empid = b.empid WHERE (a.njob = ?) I'm missing something??? I use the same logic in other modules with other tables and it works. I appreciate any help, thanks Tag: How call crosstab query in Access Tag: 93721

I have a crosstab query that I've created in MS Access.

How do I run the query and stick the results into a datatable using ado.net?
Can I reference the query just like a regular table?

Top

Re: How call crosstab query in Access by Paul

Paul
Wed Aug 18 14:32:44 CDT 2004

On Tue, 17 Aug 2004 16:04:45 -0400, "VB Programmer" <Dont*NoSpam-Please*@jEmail.com> wrote:

¤ I have a crosstab query that I've created in MS Access.
¤
¤ How do I run the query and stick the results into a datatable using ado.net?
¤ Can I reference the query just like a regular table?
¤

I see no reason why you can't call a cross-tab query and dump the results into a DataTable. Where
are you hung up?

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Top