How to compact an Access Database under VB.Net?

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
    - no primary key on union query Hello, I need to find a string value in a dataset table which is the result of a union query that does not have any primary keys. Is there another method or approach other than find and contains that I could use that does not require primary keys or is it possible to "supply" a primary key on the query result? Thanks, Mike Tag: How to compact an Access Database under VB.Net? Tag: 120176
  - 2
    - "General Network Error" and Connection Pooling I have a very simple WinForms app, that uses a sqlDataAdapter retrieve data into a dataset. The dataset is set to be the datasource of a DataGrid. If - while editing rows in my grid - i lose my network connection (or stop + start my sqlserver), i get a "General Network Error" when I call my dataAdapters .update method. (...naturally, after reestablishing network connection, that is...) However, if i disable connection pooling, I get no error. Also, with connection pooling enabled, if I repeatedly call update, eventually it succeeds on the third attempt. Anyone who can smarten me up? J. Jespersen Denmark Tag: How to compact an Access Database under VB.Net? Tag: 120167
  - 3
    - Newbie - Datagrid Binding Problem Hi There Thanks for Everyones Help So Far, I used the Database Form Wizard and i`ve looked at he code from that. Its working Great Now. Now the Only Problem I have is i`ve added a Datagrid to my App Aswell, Makes it easier then clicking next a few hundred times to get to the record I want. Ok I could just use a datagrid but it looks a lot nicer if the Datagrid is there aswell. I`ve marked my Datagrid as Read only and want the user to be able to click on the row they want and for that. When I click on my Row on my datagrid is dosn`t update my Position_Changed thinggy which tells them what record number there one. Here is my Position Changed Code: Private Sub dsSystemUsers_PositionChanged() Me.lblNavLocation.Text = (((Me.BindingContext(dsSystemUsers, "Passwords").Position + 1).ToString + " of ") + Me.BindingContext(dsSystemUsers, "Passwords").Count.ToString) End Sub I take it I need to Add a Line for My Datagrid I just can`t find any info. Anywhere on how to do it:( Anybody point me in the right Direction please? Many Thanks NSC Tag: How to compact an Access Database under VB.Net? Tag: 120160
  - 4
    - Create EnterpriseLibrary.Data.Database object from connection stri Is there any way to create a Microsoft.Practices.EnterpriseLibrary.Data.Database object without reading from a configuration file? I am writing a utility that manages databases in SQL Server. The utility queries SQL Server for the database names. So I don't know the names of the databases beforehand, but I will know the names and connection strings once the server has been queried. However, I have a class that I'd like to work with that requires a Microsoft.Practices.EnterpriseLibrary.Data.Database object. Is there any way to create a Microsoft.Practices.EnterpriseLibrary.Data.Database when I already know the connection string? Thanks in advance Tag: How to compact an Access Database under VB.Net? Tag: 120150
  - 5
    - Dataset with large tables Please someone explain this to me ! From what I understand ADO.NET is disconnected. Ok. I have Windows app with a student table with about 15000 students. In ADO I would open a server side cursor and page thru the table in a custom grid I built. So at most I would retrieve about 20 records or so per page (depending on no of displayable rows). Now with ADO.NET I will have to send the whole 15000 records to the client in a DataSet. How can this be good ? I just don't get how everyone claims server side cursor slow down an app. In ADO I retrieve 20 records at a time in a grid and it's instataneous. In .NET it's slowwwwwww. This is just one example but my database is extremely large (over 2000 SQL server tables). If I have many users opening the student module at the same time, they have to wait and wait to get those 15000 records when I only need an initial set of records to display the first page... I just don't understand how real systems are done in a disconnected manner. Tag: How to compact an Access Database under VB.Net? Tag: 120145
  - 6
    - Question about creating Access tables in VB.Net using ADOX I'm writing a VB.Net program. At startup the program looks for a default Access mdb and if it isn't there, creates it. At least that's the design. My question is this: Using ADOX, is there anyway to declare an AutoIncrement table field? I don't see anything in the DataTypeEnum that seems equivalent. Dim tbl As New Table tbl.Name = "Table1" tbl.Columns.Append("Field1", ??????? ) Tag: How to compact an Access Database under VB.Net? Tag: 120144
  - 7
    - There is no row at position x I am getting the exception "There is no row at position 5" on a dataview that has 10 rows (count = 10) The original dataset, i.e. unfiltered, has 12 rows. when it is filtered, there are 10. i can look in the dataview and see all ten rows. but when i am in the foreach(DataRowViw pr in orderDV) loop i get the error when i try to access a field in the sixth row (position 5). i cannot figure out why i keep getting this exception . does anyone know why? thanks m I have included the code that causes this error: string Filter = "BatchID = " + BatchID.ToString(); // usullay BatchID = 0 DS.Tables[OrderTable].DefaultView.RowFilter = Filter; DataView orderDV = DS.Tables[OrderTable].DefaultView; foreach(DataRowView pr in orderDV) { string Orders_Id = pr["OrderNumber"].ToString(); // why do i get an exception here? //pr["OrderNumber"] {"There is no row at position 5." } System.IndexOutOfRangeException foreach (DataRow cr in pr.Row.GetChildRows("OrderCollection")) { } pr["BatchID"] = 1; // assign new BatchID } Tag: How to compact an Access Database under VB.Net? Tag: 120140
  - 8
    - OLE DB Connect Help Needed Hi everyone, I'm having a problem connecting to a mySQL database using OLEDB with vb.net. open3 = New OleDbConnection open3.ConnectionString = "Provider=SQLOLEDB; Data Source=www.db.edu; Initial Catalog=xxx; User Id=XXX; PWD=XXX;" Doesn't work: "Access Denied or server doesn't exist" Yet: open2 = New OdbcConnection open2.ConnectionString = "Provider=MSDASQL; DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & txtServer.Text & ";" & "PORT=3306; DATABASE=xxx;" & "USER=" & txtUser.Text & ";" & "PASSWORD=" & txtPass.Text & ";" & "OPTION=3" -- and -- open = New MySqlConnection open.ConnectionString = "server=" & txtServer.Text & ";" & "user id=" & txtUser.Text & ";" & "password=" & txtPass.Text & ";" & "database=xxx" both work just fine. They all point to the same database, use the same login and pass, and call the same database. Thanks, matt Tag: How to compact an Access Database under VB.Net? Tag: 120139
  - 9
    - get DataColumn metadata 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 Tag: How to compact an Access Database under VB.Net? Tag: 120134
  - 10
    - 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: How to compact an Access Database under VB.Net? Tag: 120127
  - 11
    - 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: How to compact an Access Database under VB.Net? Tag: 120112
  - 12
    - 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: How to compact an Access Database under VB.Net? Tag: 120110
  - 13
    - 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: How to compact an Access Database under VB.Net? Tag: 120104
  - 14
    - 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: How to compact an Access Database under VB.Net? Tag: 120100
  - 15
    - 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: How to compact an Access Database under VB.Net? Tag: 120088
  - 16
    - 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: How to compact an Access Database under VB.Net? Tag: 120081
  - 17
    - 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: How to compact an Access Database under VB.Net? Tag: 120077
  - 18
    - 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: How to compact an Access Database under VB.Net? Tag: 120074
  - 19
    - 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: How to compact an Access Database under VB.Net? Tag: 120053
  - 20
    - 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: How to compact an Access Database under VB.Net? Tag: 120051
  - 21
    - 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: How to compact an Access Database under VB.Net? Tag: 120050
  - 22
    - 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: How to compact an Access Database under VB.Net? Tag: 120046
  - 23
    - 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: How to compact an Access Database under VB.Net? Tag: 120041
  - 24
    - 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: How to compact an Access Database under VB.Net? Tag: 120038
  - 25
    - 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: How to compact an Access Database under VB.Net? Tag: 120037
- Next
  - 1
    - 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: How to compact an Access Database under VB.Net? Tag: 120034
  - 2
    - 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: How to compact an Access Database under VB.Net? Tag: 120030
  - 3
    - 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: How to compact an Access Database under VB.Net? Tag: 120026
  - 4
    - 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: How to compact an Access Database under VB.Net? Tag: 120024
  - 5
    - 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: How to compact an Access Database under VB.Net? Tag: 120023
  - 6
    - 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: How to compact an Access Database under VB.Net? Tag: 120021
  - 7
    - How to know a column whether indexed or not in sql2k Tag: How to compact an Access Database under VB.Net? Tag: 120011
  - 8
    - 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: How to compact an Access Database under VB.Net? Tag: 120005
  - 9
    - 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: How to compact an Access Database under VB.Net? Tag: 120003
  - 10
    - 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: How to compact an Access Database under VB.Net? Tag: 120001
  - 11
    - 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: How to compact an Access Database under VB.Net? Tag: 119991
  - 12
    - 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: How to compact an Access Database under VB.Net? Tag: 119984
  - 13
    - 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: How to compact an Access Database under VB.Net? Tag: 119979
  - 14
    - 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: How to compact an Access Database under VB.Net? Tag: 119977
  - 15
    - 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: How to compact an Access Database under VB.Net? Tag: 119973
  - 16
    - 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: How to compact an Access Database under VB.Net? Tag: 119969
  - 17
    - 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: How to compact an Access Database under VB.Net? Tag: 119968
  - 18
    - 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: How to compact an Access Database under VB.Net? Tag: 119965
  - 19
    - 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: How to compact an Access Database under VB.Net? Tag: 119956
  - 20
    - how to know the table is exist in the database Tag: How to compact an Access Database under VB.Net? Tag: 119942
  - 21
    - 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: How to compact an Access Database under VB.Net? Tag: 119937
  - 22
    - 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: How to compact an Access Database under VB.Net? Tag: 119927
  - 23
    - 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: How to compact an Access Database under VB.Net? Tag: 119923
  - 24
    - 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: How to compact an Access Database under VB.Net? Tag: 119919
  - 25
    - 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: How to compact an Access Database under VB.Net? Tag: 119916

Hi,

In VB6, and DO, I used to progam this simply by
Compactdatabase(SOurce, Destination)
And also I used : repairdatabase (MyDataBase)
These where standard DAO.DBEngine methods

How do I do that in pure VB.Net?
Thanks in advance

GB6

Top

Re: How to compact an Access Database under VB.Net? by Paul

Paul
Fri Oct 28 14:18:53 CDT 2005

On Fri, 28 Oct 2005 21:06:56 +0200, "Georges Bessis" <georges@bessis.com> wrote:

¤ Hi,
¤
¤ In VB6, and DO, I used to progam this simply by
¤ Compactdatabase(SOurce, Destination)
¤ And also I used : repairdatabase (MyDataBase)
¤ These where standard DAO.DBEngine methods
¤
¤ How do I do that in pure VB.Net?
¤ Thanks in advance

You can either use DAO or JRO. There is no native method because there is no native provider for the
Jet database engine.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306287

Paul
~~~~
Microsoft MVP (Visual Basic)

Top