using access as database

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
    - haii...adding bunch of records haii... i neeed to know how to add a bunch of records to a database table from c#.net coding.. please sugest me .... Tag: using access as database Tag: 133293
  - 2
    - Connection String of SQL 2005 We upgrade our database from 2000 to 2005. So I use the new connection string in our newly developed web app. "Provider=SQLNCLI;Server=server_ip;Database=db_name;UID=username;PWD=password;" I got this error: The 'SQLNCLI' provider is not registered on the local machine I want to know, if I use OLEDB in my asp.net, is it necessary to use the Native Client drive? Tag: using access as database Tag: 133292
  - 3
    - CommandBuilder ConflictOption usage I'd like to use the command builder to create update queries but my database schema uses datetime columns for the last time modified instead of a timestamp. Unfortunately ConflictOption.CompareRowVersion seems to only work if a timestamp exists. The update command seems to be rewritten by the dataadapter update method. I added an event handler for the RowUpdating event and the update command is different inside the handler. I'm guessing the update command is created dynamically for each row. Looks like I'll need to fire up reflector. Tag: using access as database Tag: 133285
  - 4
    - Possible to Select * Into #temp From tblx and read #temp with ado. Hello, I have a complex query on sqlsrv2000 that runs better if I use an intermediate #temp table. I have tried using a dataAdapter, Command Object to da.SelectCommand.CommandText = "Select * Into #temp From tblx" da.SelectCommand.ExecuteNonQuery But I get an error. The goal is to then use that same #temp table in a subsquent query. I am thinking that since I am still on the same connection - the #temp table would persist. But that does not seem to be the case. Any suggestions appreciated if this is doable and how - or if I am limited to using a stored Proc are table on the disk for something like this. Thanks, Rich Tag: using access as database Tag: 133284
  - 5
    - Calling Oracle proc from C# - must I pass in a cursor? I want to create a simple procedure in Oracle that returns a resultset (a select query). In all the examples I've seen, you have to pass in a "ref cursor" OUT param into the proc from C#. Is there a simpler way to do this? IWO can I not pass anything in, and just declare the cursor in the proc? Or have no cursor, like with SQL Server? Seems pointless to pass in an empty cursor param each time. Thanks, Burt Tag: using access as database Tag: 133281
  - 6
    - getting data from exchange server How can I get email data by email address from Exchange Server into a vb.net web application? The exchange server is on the same network as the web server. I basically need to be able to display what emails were sent to/received from based upon an email address, with the date, subject and message. For instance, if there were emails from clienta@clienta.com, I want to be able to get any emails back that were sent to clienta@clienta.com and who sent them, and any emails received from clienta@clienta.com and who received them. Can you help on this? Thanks. Tag: using access as database Tag: 133280
  - 7
    - OleDbConnection + MySQL hanging. Hi, I'm trying to use a web service on my webhotel that has support for ASP.NET 2.0 and MySQL 4.0.18-nt. However, whenever I try to use OleDbConnection, my SOAP call just hangs for a while, then returns with "WebException unhandled - Operation timed out.". I've pinpointed it all the way to happening when I call conn.Open() on my OleDbConnection object. Now I tried to make an ODBC driver for my db too, but it gave the same result. Everything else seems work fine with my web service, but it's not much fun without the database support.. Web Service Code: [WebMethod] public string CheckDb() { OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); OleDbCommand command = new OleDbCommand("SELECT * FROM test", conn); command.ExecuteNonQuery(); conn.Close(); return "okay"; } catch (Exception x) { return x.ToString(); } } Just to add, I've tried with both valid and invalid connection strings, tried excluding my username / password to get it to fail, but the result is always the same. Anyone have any experience with these sorts of hangs - and what the problem could be? Thanks, - Rasmus. Tag: using access as database Tag: 133275
  - 8
    - MS ACCESS and ADO.NET 2.0 Hi, I try to use new DataSource wizard to create a connection to MS Access 2003 with VB.NET 2003. Unfortunetly after I created the solution when I try to use getdata button I get and error that MS Access DB not found. I cretaed folder in under my project and call it as DB and mdb file name was DDB.mdb. First run was I get above error and second time when I try to cionstruct again it tells me to its going to add the mdb file under my solution and I am still getting error. Does anyone know how to construct this or what should I do to change in my config file to accept new string that point correct folder with correct files? Thank you. Rgds, Niyazi -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200609/1 Tag: using access as database Tag: 133271
  - 9
    - ConnectionString manual and secure in C# 2.0 Hello, I am a newbie in C#2.0, as I have read, the application automatically generates a configuration files to store the connectionstring in (.XML file). So after compiling, everyone can see this sensitive infomation (username, password). Pls tell me how to make it "hidden" from user and how to manage the connectionstring promatically (set server name, databse name, username) Thanks in advance! -- Contact: Bauman Moscow State Technical University Russia, Moscow 105005 Tel: +7915-426-6272 ICQ: 160-223-078 YM: Chipheo2k Tag: using access as database Tag: 133269
  - 10
    - DataTable Add Row is slow (especially with empty table) After several frustrating hours, I found a solution to a problem that I've been having with DataTable.Add running very slow when trying to add a bunch of records. I found similar questions posted but no good answers, so I thought I'd post this in hopes of helping someone else. Problem: DataTable.Add is very slow. It may be relatively fast when the DataTable already has some rows in it, but is terribly slow when the DataTable is empty. First, unbind any controls that are bound to the DataTable or dependent DataViews. For example: myDataGrid.SetDataBinding(Nothing, Nothing). Next, before you start your batch Add process, call myDataTable.BeginLoadData(). This turns off notifications that the DataTable normally does automatically when you Add. After you are done adding rows, call myDataTable.EndLoadData(). This turns them back on. myDataGrid.SetDataBinding(Nothing, Nothing) myDataTable.BeginLoadData() ' Add a bunch of rows here... myDataTable.EndLoadData() Good luck. Tag: using access as database Tag: 133267
  - 11
    - combining fields VS2003 C# Hi, I have a bound text box in which I would like to display a first name/last name concatination. These reside in different fields in the database. Rather than do this from the underlying database, or doing it in code, which would reduce the effectiveness of the binding, is there anyway I can combine them on the dataset/datatable that is being bound to? Thanks for any ideas on this. Tag: using access as database Tag: 133266
  - 12
    - Passing parameter for Stored Procedure to Data Adapter/Data Set Hey all, I'm trying to pass a parameter into my Stored procedure to be accessed by my data adapter into a dataset db2.AddParameter("@intClientID", cmbClientList.SelectedValue.ToString()); DataSet rsDataset = db2.Fill("spListMappingDetails", CommandType.StoredProcedure); However, when i try to run it, it gives me an error {"Procedure 'spListMappingDetails' expects parameter '@intClientId', which was not supplied."} Any help is appreciated. I used a executescalar and executequery function and it has no problem. thanks in advance Tag: using access as database Tag: 133265
  - 13
    - Compare datasets with hash values or possibly another method? I have two datasets with the same exact structure that I would like to compare. I would like to know if there is any difference in the data between them. I don't need to know what the differences are, only that there is a difference. From what I have read about hash values, I thought that I could obtain the hash values of the datasets and compare those. If the hash values were different, I would know that there is a difference in the data in the datasets. I tried this approach, but I found that even when I changed a string in one of the fields in one dataset, the hash values calculated were the same. I am not sure if I am missing something here??? This is the code I performed on both datasets to get the hash values... ' Create a new instance of memory stream Dim ms1 As New MemoryStream Dim formatter As New BinaryFormatter 'Serialize dataset to memory stream formatter.Serialize(ms1, dsCurrent) 'Compute hash value for stream Dim hashvalue1 As Byte() = CType(CryptoConfig.CreateFromName("MD5"), HashAlgorithm).ComputeHash(ms1) Then I changed the hash byte array to a hex string... Dim i As Integer Dim sOutput1 As New StringBuilder(hashvalue1.Length) For i = 0 To hashvalue1.Length - 1 sOutput1.Append(hashvalue1(i).ToString("X2")) Next Dim strHashValue1 As String = sOutput1.ToString I then compared the hex strings. Is there another way other than hash values that I could use to compare the data in the datasets? I tried dataset.merge and found that when merging the second dataset into the first, the rowstate of the rows that were different between the two datasets did not change to 'modified', but remained at 'unchanged'. Any help would be greatly appreciated!! Thanks! From http://www.developmentnow.com/g/7_0_0_0_0_0/dotnet-framework-adonet.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com Tag: using access as database Tag: 133263
  - 14
    - 2.0 Auto Id in typed datasets Hi, I'm still strugling with the following situation. I've got two table which are related by a foreign key contraint. These table reside in a typed dataset. I working on a webbased app, so a disconnected environment. Now, when I add a row to the parent table and add rows to the childtable (think of customer and orders). This means that I have to use the autogen. id of the parenttable and use this Id as de FK for the childtable. Then when updating the database, I have to retrieve the new Id from de customer table from de db and use this id to update the FK in the childtable before I can add the rows to the db. In 1.1 for this I used the RowUpdating and RowUpdated event. The above example is very basic, in my situation I had to update multiple depending tables. This forced me to use the RowUpdating and RowUpdated methods. I als used the dataadapter.update to update the typed dataset. I can not rely on the Id generated by the typed dataset, because an other person can also add a customer and orders at the same time. To uniquely create an Id, I set the prim.key in the parent to a seed of -1 starting from -1. The neg. numbers are replaced with the new db Id as described in the example above. Now, do I have to use the same construction in 2.0 or is it better to use tableadapters instead? If yes, how can I use them in such a way the same functionality is there as in my 1.1 app? Tag: using access as database Tag: 133261
  - 15
    - objectdatasource questions Can anyone tell me is there an easy way to get a value into an objectdatasource select parameter from Application Variables? Obviously its easy from cookies, session etc... but what about Application? Or is there an easy way to get a value into an objectdatasource parameter from the web.config? Kind of like how we get the connection string from web.config into a datasource object, like: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:spg_portalsConnectionString %>" Can I do: <SelectParameters> <asp:Parameter Name="dept_id" DefaultValue="<%$ AppSettings:dept %> </SelectParameters> Tag: using access as database Tag: 133252
  - 16
    - TableAdapter specifing 'IN' in the where clause for list of id's Hi, I have a table that contains an interger primary key. I want to query the table for rows that have values equal to a list of ID's that I specify. The sql that I would use would be something like the following: SELECT FunctionalGroupId, ReportId FROM FunctionalGroupReportXRef WHERE (FunctionalGroupId IN (@Ids)) When I specify this query in the TableAdapter wizard the wizard typecasts @id to an interger. Thus I can only specify one id in my query rather then a list of values. I would have expected @Ids to be a string thus allowing me to specify a list of id's. How can I get around this issue? Any help would be appreciated. Thanks!!! Tag: using access as database Tag: 133249
  - 17
    - DataGridView or something else from what I read & tried, DataGridView was meant for Window Forms, not for ASP/Web pages... correct? While I can use it to display data, I can't seem to figure out how to edit the cells... I'm not looking to put edit buttons on each row... I'll go back to regular window forms before I create such an interface.. So is it true, in ASP.Net using the DataGridView, I'm not going to be able to use that control to edit data? Is there such a control I can use in ASP.Net? Tag: using access as database Tag: 133244
  - 18
    - how useful DbCommon namespace? I am working on an application where the end user will select the type of datastorage to use (SQL, Oracle, Access, even Excel for some readonly data, and maybe more providers ) The application reads/writes data and even must be able to create tables in databases (or even create databases) as needed. I am trying to use the new DbCommon namespace in ADO 2.0 to keep the database code as generic as possible. However, as we all know, there are many nuances between the common data providers that make this difficult. For example date formats in queries are different (JET, at least before Access 2003, requires '#' delimiters), cultural settings can work differently with Access/Excel JET (m/d/y or d/m/y). Syntax for creating tables are also different (nvarchar(1000) crashes in JET because text fields in JET are limited to 255 characters so you must use MEMO). So, while I can write a lot of DB independent code, I need specifics in many cases. While working to this end, I can't even find a simple way to query a connection to see who it's driver is!! I've used if (conn is System.Data.SqlClient.SqlConnection) ,is there a better way? I've also written query formatters for date parameters. Who out there has a little more experience to share? Some tricks, pitfalls, links to good provider comparision imformation, anything would be helpful! Tag: using access as database Tag: 133240
  - 19
    - locking recoreds hi i know in the VS 2005 when we use data access and binding there is no locking for data as before cuz its disconected but i wonder is this the standard now ? i mean when i make a query i will always be sure that maybe some recoreds out side my query will be modified by another user and i will not get it at the time i made my query and what about making same query at same time from different users ? -- Ammar S. Mitoori IT Head QIMCO Co. Tel : +9744831199 Mobile : +9745378400 Fax : +9744831643 Tag: using access as database Tag: 133224
  - 20
    - multidimensional array and data table help In vb6, I used getrows to load data into a multi-dimensional array. Since ADO.NET doesn't have getrows, I've succesfully loaded the same data into a datatable. How do I reference the data in the data table as a multi-dimensional array for (row and column), or load the data into an array from the data table? Or is there a better way to do this? Thanks Tag: using access as database Tag: 133220
  - 21
    - SQL Server 2005 Express connection error? When I run the code below in Visual Web Developer 2005 Express, I'm getting an "Unable to find the requested .Net Framework Data Provider. It may not be installed." error. I've been researching a fix for the past two days without any luck. I've tried completely uninstalling both VWD and SQL Server 2005 Express and reinstalling. I've checked for capitalization and typos (code compiles fine). I've also looked at the machine.config file, although I really don't know what I'm looking for to be honest. There are lines in there for "System.Data.SQLClient," although they don't use the same capitalization. Anyone out there seen this issue before and have a fix? If not, can someone enlighten me as to how to troubleshoot it? I'm just beginning in C# and SQL Server, so any info would be greatly appreciated. Anyway, here's the code that's throwing the error. It's the "DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);" line. public static DbCommand CreateCommand() { //Obtain the database provider name string dataProviderName = MyProjectConfiguration.DbProviderName; //Obtain the database connection string string connectionString = MyProjectConfiguration.DbConnectionString; //Create a new data provider factory DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName); //Obtain a database specific connection object DbConnection conn = factory.CreateConnection(); //Set the connection string conn.ConnectionString = connectionString; //Create a database specific command object DbCommand comm = conn.CreateCommand(); //Set the command type to stored procedure comm.CommandType = CommandType.StoredProcedure; //Return the initialized command object return comm; } -Josh Nikle Tag: using access as database Tag: 133219
  - 22
    - ASP I have a database ASP program that works off a centralized server. Now the client wants me to put it on laptops using SQL Server 2005 Express. I can do this but do not know of any way to protect my AP code from being viewed and stolen. Is there any easy or not too time consuming way to do this? Tag: using access as database Tag: 133216
  - 23
    - How to Remove SPAN tag from the Web Custom controls i'm creating a WebUserControl that overrides the RenderContents method, on that i generate the HTML to the writer. here the problem is that on the html code of the page i've one more tag that i doesn't whant <span>! What can i do to remove this tag from the renderes results? Overrided Method: Protected Overrides Sub RenderContents(ByVal output As HtmlTextWriter) MainRender() ' Generates the Content output.Write(m_strOutputa) End Sub The MainRender method on Overrided Sub RenderContents generates the folowing HTML code: <table id="MenuOP1" class="s" width="100px"> <tr> <td>x</td> <td>x</td> <td>x</td> </tr> </table> Unfortunaly i get one exta span tag in the html source of page: ... <span id="MenuOP1" style="display:inline-block;width:100px;"><table id="MenuOP1" class="s" width="100px"> <tr> <td>x</td> <td>x</td> <td>x</td> </tr> </table> </span> ... How to remove the span tag Tag: using access as database Tag: 133213
  - 24
    - Using a DataTable instead of a DataReader I've gotten quite comfortable using a DataReader to grab a single value from a table as in the following: Public Shared Function IsUserAdmin(ByVal strNUID As String) As Long Dim dr As SqlDataReader Dim conMembers As SqlConnection = MembershipDB.GetMembershipConnection() Dim cmd As New SqlCommand("SELECT [Level] FROM dbo.user_settings WHERE [Address] = @NUID", conMembers) cmd.CommandType = CommandType.Text cmd.Parameters.Add("@NUID", SqlDbType.NVarChar, 50).Value = strNUID conMembers.Open() dr = cmd.ExecuteReader(CommandBehavior.SingleRow) 'check if the clinical value is 1 If dr.Read Then 'if it is then the user admin privileges 'find out the level Return dr("Level") Else 'if no user is found - it means the user doesn't have any admin rights Return 0 End If conMembers.Close() conMembers.Dispose() conMembers = Nothing End Function I would like to practice using DataTables but I need a little help getting started- could someone tranlate this same functionality (above) for a DataTable? Tag: using access as database Tag: 133204
  - 25
    - Backing up master Re SQL Server 2005 Express Edition When I attempt to back-up the master file, I get told it is being used by another application. So the file cannot be copied for back-up. This is even the case after a complete shut-down of the PC. What is the solution to this? Tag: using access as database Tag: 133190
- Next
  - 1
    - Edit and update multiple rows at a single click Dear How can I select multiple checkboxes and update it to database at a single click on update button.The GridView allows to edit only one row at a time so for each row I have to select edit and than select checkbox and then click on update. How can I select multiples checkboxes and update them at one click. Regards, Rakesh Tag: using access as database Tag: 133182
  - 2
    - DataColumn.DataSetDateTime Property - What is it good for? Hello everybody, When I saw this new DataColumn.DataSetDateTime property for the first time I thought myself: wow, what a good idea! Perhaps ADO.Net seamlessly converts UTC to local and vice versa, so I will be able to store dates as UTC but display as local! All that just specifying DataSetDateTime property value. But it does not seem to work quite that way. DataSet.Merge() operation requires compatibility and does not perform any translations. So, how is it supposed to be used? What is the hidden advantage? Could someone share his/her experience? I already read docs, but the description is rather scant. Tomasz Tag: using access as database Tag: 133181
  - 3
    - How to use DataSet for MS-SQL and Oracle All, Is it possible to use the same (strongly typed) DataSet object with an Oracle table adapter (or data adapter) and a MS-SQL table adapter? It would be nice to use the same dataset object so the calling code can use only one dataset and not use one for each database type. Thanks in advance. Tag: using access as database Tag: 133180
  - 4
    - How t I have a talbe, there is a smallDateTime field in it. I want to update this filed with a today's date. I want to make s sql like "Update myTable set dateField = {0}" I can I replace the {0} in the sql? Tag: using access as database Tag: 133178
  - 5
    - How t I have a talbe, there is a smallDateTime field in it. I want to update this filed with a today's date. I want to make s sql like "Update myTable set dateField = {0}" I can I replace the {0} in the sql? Tag: using access as database Tag: 133177
  - 6
    - How t I have a talbe, there is a smallDateTime field in it. I want to update this filed with a today's date. I want to make s sql like "Update myTable set dateField = {0}" I can I replace the {0} in the sql? Tag: using access as database Tag: 133176
  - 7
    - Using Informix Client SDK I get this error ( The parameter data type of DBNull is invalid ) We have the following environment .Net Framework 2.0 - Informix Client SDK 2.90 TC6 - Informix 9.40 FC8 The following error started appearing after we upgraded to TC6. It never happened in TC4/TC5. System.ArgumentException: The parameter data type of DBNull is invalid.. at IBM.Data.Informix.TypeMap.FromSystemType(Type dataType, Int32 iLength) at IBM.Data.Informix.IfxParameter.GetParameterValue() at IBM.Data.Informix.IfxParameter.Bind(IntPtr stmt, IfxCommand parent, Int16 ordinal, CNativeBuffer buffer, CNativeBuffer intbuffer) at IBM.Data.Informix.IfxCommand.ExecuteReaderObject(CommandBehavior behavior, String method) at IBM.Data.Informix.IfxCommand.ExecuteReader(CommandBehavior behavior) at IBM.Data.Informix.IfxCommand.System.Data.IDbCommand.ExecuteReader() Please help Tag: using access as database Tag: 133174
  - 8
    - Getting reusable API to "discover" the DB connection it needs at runtime We're working on an application that has a 'common' project that hosts a variety of 'general desktop app' APIs - user preferences, the non-UI code the app's services uses, etc. As part of that logic, it makes use of another new project which hosts the 'licensing' system. This licensing project contains an ORM wrapper around the licensing database, as well as the licensing/permissions APIs and logic. In this app, the database connections will come from the 'common' project (because they're specified by the user's preferences). However, the licensing project needs that connection also so that the its implementations of the licensing APIs can access the current licensing database. We don't want the licensing project to deal with WHERE it goes for a connection because other apps down the road will use this same licensing libary. For example, this app uses a preference setting while a web app would specify these in a config file. So the question is how to design it so that the licensing system gets the connection details it needs at runtime using logic that will work down the road for other types of apps. We've considered having the licensing system provide an IConnectionProvider interface. Any app that's going to use the licensing system would provide an implementation of that. class DesktopConnectionProvider : IConnectionProvider { public SqlConnection GetConnection() { ... } } Then the code in the licensing system would need some place to go to get a connection public class ConnectionManager { public static IConnectionProvider ConnectionProvider; } At startup, the app would "initialize" or "register" its connection provider implementation with the licensing system. Licensing.ConnectionManager.ConnectionProvider = new DesktopConnectionProvider(); ... I know this would get the job done and work just fine. However, I'm not sure I care for the need to initialize or register something explicitly at startup. I'm wondering if there's a more elegant approach that would give the licensing system more "runtime discoverability" of the connection details it will need. I'm curious what thoughts or ideas others might have. Thanks. Tag: using access as database Tag: 133166
  - 9
    - BeginEdit is ignored Hi, Im pretty new to ADO.net and I ran into the following problem: I try to add a row to a DataSet that has NotNull-Constraints on its columns. When doing: DataRow row = ds.Tables["docdescriptors"].NewRow(); DocTypeDB dt = new DocTypeDB(row); NpgsqlCommand command = new NpgsqlCommand("select nextval('docdescriptor_id_seq')", connection); row["id"] = (int)(Int64)command.ExecuteScalar(); ds.Tables["docdescriptors"].Rows.Add(row); I get the a ConstraintException because I did not fill all columns. That's ok so far. But following the documentaion I added a BeginEdit DataRow row = ds.Tables["docdescriptors"].NewRow(); row.BeginEdit(); DocTypeDB dt = new DocTypeDB(row); NpgsqlCommand command = new NpgsqlCommand("select nextval('docdescriptor_id_seq')", connection); row["id"] = (int)(Int64)command.ExecuteScalar(); ds.Tables["docdescriptors"].Rows.Add(row); But I still get the Exception I try to add the Row to the table. Best Thomas Tag: using access as database Tag: 133164
  - 10
    - Access / DAO - What's the Future We are using Access as the "repository" for a C++ MFC application. It's single user. The schema is complex (100 tables) and can be a few hundred MBytes in size. We are starting to reach the limits: occassionl "Query too Complex" and "Max Lock Files Exceeded". Although some of these we have tackled and got around through better design. We recently moved to Visual Studio 2005 and of course no longer have the benifit of DAO wizards for creating RecordSets and refreshing them when the schema changes. This too we have got around for now - just using old classes as a model and creating them by hand. We are about to start the next phase of our product and it will stress the database even more. The questions: o Would you recommend that we change to SQL Server Express? o What are our migration options? Do we have to convert everything to ADO right away? I assume we can't use DAO with SQL Server. If we could we migrate slowly - using ADO (or ODBC) for new classes. o Or should we use ODBC? Thanks TrevorS. Tag: using access as database Tag: 133163
  - 11
    - String[9]: the Size property has an invalid size of 0??? I'm attempting to insert data to a table via an ExecuteNonQuery call on a SqlCommand sproc object, but I keep getting the error displayed in the subject line and can't, for the life of me, figure out what the ExecuteNonQuery call doesn't like. If I attempt to use the sproc from within SQL Server Management Studio, the insert performs flawlessly. The sproc is as follows: ALTER PROC [dbo].[AddDrawing] @DeptID int, @DrawingCode smallint, @Description varchar(60), @Category char(4), @ManHours decimal(5, 2), @CaddRatio decimal(5, 2), @CompRatio decimal(5, 2), @LastUser varchar(20), @NewDrawingID int output, @NewTimeStamp timestamp output AS INSERT INTO ProjectDrawings (DeptID, DrawingCode, Description, Category, ManHours, CaddRatio, CompRatio, LastUser) VALUES (@DeptID, @DrawingCode, @Description, @Category, @ManHours, @CaddRatio, @CompRatio, @LastUser) SELECT @NewDrawingID = DrawingID, @NewTimeStamp = LastChanged FROM ProjectDrawings RETURN I am adding to the Parameters collection for the SqlCommand object as follows: cm.Parameters.AddWithValue("@DeptID", _deptID); cm.Parameters.AddWithValue("@DrawingCode", _drawingCode); cm.Parameters.AddWithValue("@Description", _description); cm.Parameters.AddWithValue("@Category", _category); cm.Parameters.AddWithValue("@ManHours", _manHours); cm.Parameters.AddWithValue("@CaddRatio", _caddRatio); cm.Parameters.AddWithValue("@CompRatio", _compRatio); cm.Parameters.AddWithValue("@LastUser", _lastUser); cm.Parameters.AddWithValue("@NewDrawingID", _drawingID); cm.Parameters["@NewDrawingID"].Direction = ParameterDirection.Output; cm.Parameters.AddWithValue("@NewTimeStamp", _timeStamp); cm.Parameters["@NewTimeStamp"].Direction = ParameterDirection.Output; I use essentially the same routine for inserting values into various other tables in my database, but this is the only one that is giving me a hard time. What can I do to track down and correct whatever it is that's causing me to get the error message? Any thanks will be greatly appreciated. Allen Tag: using access as database Tag: 133152
  - 12
    - MS Access and Typed Dataset problem Hi, I am attempting to use a typed dataset with an Access database. I have the data showing in a grid and that works prefectly but when I try to update, it does nothing. I tracked the problem down to the fact that the DataAdapter wizard does not generate UPDATE and DELETE statements. I don't have any permissions at all on the database and I can edit and delete the records within Access. If I upsize the same database to SQL Server, it works perfectly. TIA Tag: using access as database Tag: 133140
  - 13
    - Most efficient way to process this set of records??? Hello All, I'd like to know the most efficient way to process a set of records. I've built a composite record that is to contain columns from 6 other "Master" tables. I need to update the composite record with the values of those columns and perform some calculations along the way. I coded a vb.net (2005) using Access (I know it should be SQL Server, but not allowed by client) to accomplish the task, but it takes an extremely long time. The code was procedural as: Get composite record Set up Master-Table-1-Select-Parameters Get Master-Table-1-Columns and place in Composite Set up Master-Table-2-Select-Parameters Get Master-Table-2-Columns and place in Composite ....and so on with the other 4 Master tables..... Update the datatable Accept Changes Go get another composite record and process until end of composites It is further complicated by volume: Composite table - 162,000 records Master-Table-1 - 18,000 (1) Master-Table-2 - 77,500 (1) Master-Table-3 - 5,500 (2) Master-Table-4 - 15,000 (1) Master-Table-5 - 130,000 (1) Master-Table-6 - 1,742,000 (2) (not a typo) (The number in parens is the number of records I have to retrieve for EACH composite record.) I tried to create a datarelation between a Master-Table and the Detail-Composite (in code) but ran into problems, which I posted a message to "microsoft.public.dotnet.languages.vb". (See "DataRelation Woes!!! Need Help (column argument cannot be null)") So my questions are: By properly setting up datarelations, does that substantially increase performance over the way I originally coded it? (My guess is that I approached this in the worst way possible.) ....OR better yet, Can anyone provide the process/logic to improve the performance to get this task accomplished?? Thanks, Hexman Tag: using access as database Tag: 133130
  - 14
    - Iterating an ADODB RecordSet using System.Reflection Everyone, I have to be able to retrieve data from ADODB RecordSet, but without referencing the ADODB interop. I do get a RecordSet object, but when I try to use a DataAdapter to populate a Dataset: myDataAdapter.Fill(myDataSet, myRecordSetObject, "MyTableName"); I get the exception: Row handles must be released before new ones can be created. Any simple ideas how I can do this? It woudl only be a few records, in read-only access. Many thanks, Jules Tag: using access as database Tag: 133128
  - 15
    - Whats going on with the DAAB and UpdateDataSet? OK, I've been staring and debugging for hours. Surely, someone else has run into this problem. I have a typed dataset that contains 3 tables with relationships between them. I need to insert the records for the first table to get back the identity column for the subsequent inserts. However, after I call UpdateDataSet the first time (and get back my identity and everything cascades to the child tables just fine), the dataset returns false for HasChanges() - meaning my subsequent inserts fail. How do I fix this problem? This is the code snippet that I have wrapped nicely in a try/catch block with transactions. I have traced into the DAAB code and it looks like it properly calls update using a dataadapter on the table only, and I cannot see anywhere that AcceptChanges is called on the dataset. All help greatly appreciated. { // this works just fine rowsAffected = db.UpdateDataSet( ds, TABLE_Company, insertCommandCompany, updateCommandCompany, deleteCommandCompany, transaction); // this thinks no changes and returns 0 rowsAffected rowsAffected = db.UpdateDataSet( ds, TABLE_EntityType, insertCommandCompanyEntityType, updateCommandCompanyEntityType, deleteCommandCompanyEntityType, transaction) // this thinks no changes and returns 0 rowsAffected rowsAffected = db.UpdateDataSet( ds, TABLE_Branch, insertCommandBranch, updateCommandBranch, deleteCommandBranch, transaction); } Tag: using access as database Tag: 133127
  - 16
    - ADO.NET 2.0 Love it or die from it!!! Hi. When I move to VS.NET 2003 I find very challenging than since march 2005 I had big problem doing a report with IBM AS400 and luckily I finished. I get 5 days of to study new ADO.NET 2.0 with VS.NET 2005 Professional edition. It was expensive but I find I can do many things much easier than previous one. First I create a database using SQL Server 2000 (I guess I am chicken to move on SQL Server 2005) and had only 3 tables as Customer, Products and Orders. I use the wizard to create and see how easy and fast to create a input windows form and it was very good and very fast. Not only I get details form also I place a data grid and love to see the â??BindingNavigatorâ??. Now when I try to make another form for Orders tables I get many difficulties so I guess I have to love the new ADO.NET or I might going to kill myself! Last 2 days I couldnâ??t sleep and I was searching many books and articles on the net to find some good example for my problem to solve it. The orders form has 2 specific columns that show the Customer ID and Product ID. The data grid view was okay but I was stuck on the â??Detailsâ?? section. Only Data sources tab I choose both column to show as combo Box. And it is okay when I view but when I click the â??BindingNavigatorâ?? ADD btn I couldnâ??t mange to fill customer name and product name. What I need was to when I click the add button on the â??BindingNavigatorâ?? I need to fill the 2 combo boxes with Customer name and Product name. And then when I click the SAVE btn on the â??BindingNavigatorâ?? I want to save my orders but the Customer Name and Product Name must be somehow converted to integer so I can place them in my SQL tables. In Order tables in my SQL server has set to accept integer value for Customer ID and Product ID. So how to mange to get this work? Or does anyone know good article about this problem that can share with me? Thank you very much for reading my mail and I hope I can get quick response to my post. Rgds, GC Tag: using access as database Tag: 133126
  - 17
    - ArrayList and FilterRow Hi, I have an interesting problem. With the code below, the loop takes ages to run: For intA = 0 To aryJobs.Count - 1 Dim strJobx As String = aryJobs(intA).ToString strJobx = "JobNumb = '" & strJobx & "'" adsWork.Tables(0).DefaultView.RowFilter = strJobx ..... Next intA ..... If I change it to this, it is super fast: For intA = 0 To aryJobs.Count - 1 Dim strJobx As String strJobx = "JobNumb = '00001" adsWork.Tables(0).DefaultView.RowFilter = strJobx ..... Next intA ...... Yet the first item in the ArrayList(0) is "00001" so why is it way slower when I try to pass an ArrayList rather than the number 00001 as seen above? I have also tried an array of string with the same slow result. I have left the code out where the ... are as the perfomance problem is in the above code. Is this problem because setting the number direct is like a constant where getting it from an arraylist is really slow? The reason I have to do this is because i am upgrading data where there is a 'jobnumb' field in an access table but there could be several records for it under 1 job number, however the source is flat file and one job could have a different extension (say one part might be 00001-02A and another part 00001-02 and so on and many variations on that) so I create a datatable that I add temp fields too so I can get the job number to be the same so I can filter the records and I built an arraylist of unique jobnumbs then I can import the records to a new relational setup so there is only 1 jobnumb with relations to other tables. I tied making a temp table but no difference. TIA Tag: using access as database Tag: 133116
  - 18
    - Access DB Update Problem- string updateSQL; updateSQL = "UPDATE TestDetails SET AnsweredWordID = :AnsweredWordID WHERE RID=:RID"; OleDbCommand command = new OleDbCommand(); command.CommandText = updateSQL; OleDbParameter param1 = new OleDbParameter(":AnsweredWordID", OleDbType.Integer, 0, "AnsweredWordID"); command.Parameters.Add(param1); OleDbParameter param2 = new OleDbParameter(":RID", OleDbType.Integer); param2.SourceVersion = DataRowVersion.Current; param2.SourceColumn = "RID"; command.Parameters.Add(param2); m_DataTableTestDetails.Rows[m_Position]["AnsweredWordID"] = 99; OleDbDataAdapter adapter = new OleDbDataAdapter(); OpenConnection(); adapter.UpdateCommand = command; adapter.UpdateCommand.Connection = m_Connection; adapter.Update(m_DataTableTestDetails); CloseConnection(); There is an exception when adapter.Update(m_DataTableTestDetails); line. The exception is ":RID No value given for one or more required parameters". I need help pls. Thanks alot. Tag: using access as database Tag: 133114
  - 19
    - Questions about generated stored procedures by VS 2005 (TableAdapter). Hi, When I let a TableAdapter generate stored procedure for "SELECT * FROM Supplier WHERE SupplierID = @SupplierID", it generates (some parts removed): Select: SET NOCOUNT ON; SELECT * FROM Supplier WHERE SupplierID = @SupplierID Update: SET NOCOUNT OFF; UPDATE [Supplier] SET <...> WHERE <...>; SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID); Insert: SET NOCOUNT OFF; INSERT INTO [Supplier] VALUES <...>; SELECT SupplierID, <...> FROM Supplier WHERE (SupplierID = @SupplierID); Delete: SET NOCOUNT OFF; DELETE FROM [Supplier] WHERE <...>; My questions: (1) Why does Visual Studio generate a SELECT after the INSERT & UPDATE statements? (2) Why is NOCOUNT set to ON in the select? Kind regards, Steven - - - Tag: using access as database Tag: 133111
  - 20
    - Creating a database I am new to stored procedures. Is the code below a stored procedure? It doesn't have "CREATE PROCEDURE" in the code. How should I incorporate the code below in a C#.NET application? Thank you, Adrian. USE master GO CREATE DATABASE Products2 ON ( NAME = prods2_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods2.mdf' ) GO Tag: using access as database Tag: 133101
  - 21
    - Problem with NewRow in .NET 2.0 I am trying to add new rows to a DataTable in .NET 2.0 using the code below. The problem I have is that all new rows have all of the column values set to System.DBNull.Value instead of the values I assign. This code works fine in .NET 1.1. I have just migrated the code to .NET 2.0 and this issue has appeared. If I change the code a bit an use a string array instead of .NewRow(), everything works as expected. What is wrong with NewRow? Function getRoles Dim dsRole As New DataSet dsRole.Tables.Add("ROLE") With dsRole.Tables("ROLE") .Columns.Add("Role", System.Type.GetType("System.String"), "") .Columns.Add("Name", System.Type.GetType("System.String"), "") .Columns.Add("Description", System.Type.GetType("System.String"), "") .Columns.Add("Context", System.Type.GetType("System.String"), "") Dim oRow = .NewRow() oRow("Role") = "CCB" oRow("Name") = "Change Control Board" oRow("Description") = "Person(s) who approve a request" oRow("Context") = "ORGANIZATION" .Rows.Add(oRow) oRow = .NewRow() oRow("Role") = "SA" oRow("Name") = "System Administrator" oRow("Description") = "Manages the technical aspects of the request system" oRow("Context") = "GLOBAL" .Rows.Add(oRow) End With getRoles = dsRole.Tables(0) End Function -- Bill Tag: using access as database Tag: 133097
  - 22
    - GetSchema( "Columns" ) results in ??? I am moving a project over from VS2003 to VS2005. It is an adhoc data mining tool that lets you explore tabular data from just about any source. In it I use SqlConnection, OracleConnection, OledbConnection, and OdbcConnection as needed. For each type I had special code to get the tables and a table's columns where the columns data types are very important to me. For Sql and Oracle I executed queries, for Oledb I used the GetOleDbSchemaTable(), and for Odbc I used calls to an ADODB (old ado) Interop assembly to the GetSchema() call of OleDb using the MSDASQL provider to pass through to ODBC. They all work but are a lot of work to maintain. I was looking forward to getting rid of all the specialized code and the interop assembly by using the new GetSchema() function. But I have found that even though it exists for each connection, it doesn't return results that are consistent across connection types, and worse it isn't even consistent within Odbc for sure, maybe Oledb too. Calling GetSchema( "Columns" ) results in a table with the DATA_TYPE column containing text for Sql and integers of Odbc. The Sql ones are obvious and easy to deal with, but the Odbc ones are NOT values that come even close to the OdbcType enumeration's values. For instance, connecting to MS-Access using ODBC, DATA_TYPE contains -9 for a Text column, but OdbcType.Text == 18. And on top of that, using OdbcConnection to connect to dBase or Paradox using the Jet driver, a call to GetSchema( "Columns" ) returns an EMPTY dataset! So, does anyone know: 1) Am I crazy to expect a common function to return common results? 2) How to interpret the DATA_TYPE column from GetSchema( "Columns" ) for Odbc. 3) Why do I get no results from GetSchema( "Columns" ) for some drivers/providers when a connection will return a valid data reader? 4) Is there a better way to get the column names and types for any given connection in a consistent way? Tag: using access as database Tag: 133092
  - 23
    - Stored Procedures Query Is it possible to use stored procedures with SQL Server 2005 express edition? 1. In "Object Explorer" under "Databases" there is no pocket called "Stored Procedures". Is such a pocket available anywhere else? 2. Alternatively via C# Visual Studio (Standard Edition), if under "Project" I select "C#", there is no "Database", and from there I cannot select ""SQL Server Project" So how - in Visual Studio - can I create a stored procedure? Many thanks, Adrian. Tag: using access as database Tag: 133085
  - 24
    - Stored procedures Is it possible to use stored procedures with SQL Server 2005 express edition? 1. In "Object Explorer" under "Databases" there is no pocket called "Stored Procedures". Is such a pocket available anywhere else? 2. Alternatively via C# Visual Studio (Standard Edition), if under "Project" I select "C#", there is no "Database", and from there I cannot select ""SQL Server Project" So how - in Visual Studio - can I create a stored procedure? Many thanks, Adrian. Tag: using access as database Tag: 133084
  - 25
    - DateTime Conversion Folks, I have created a DataTable containing a column, FilterOn, using the DateTime type: col = tbl.Columns.Add("FilterOn", GetType(DateTime)) While attempting to store a value of #12:00:00 AM# I observed that what actually gets stored is the "Default Date" of 1/1/0001, without the time. Apparently this happens because the .NET Framework and COM assume a default date when only a time is specified. There are frequent occasions when I only want to store 12:00:00 AM in the field, without any date. Is there a propery or method, etc that I can use to make this happen? This table is being used to insert records into an Access database. The Access database has gobs of these type dates. Thanks, Vint Tag: using access as database Tag: 133083

Hey everyone,
I want an advice. I want to build a site as an internet application using
database etc.
Since I need to use database , I'm interested in something not expensive for
my own needs.
I thought about using sqlServer, but I know that it is very expensive.
Though I thought to use Access.

- Did anyone use Access in an Asp.net application ?
- Is it comfortable and runs ok ?
- Does Microsoft continue to develope and support this tool ?
- Which Access do I need for my purpose ? the simple one that comes with
office 2003 ?

Thanks a lot for any answer !!!

Top

RE: using access as database by KerryMoorman

KerryMoorman
Wed Sep 27 07:10:01 CDT 2006

moshi,

One option is to use SQL Server 2005 Express Edition, a free download from
Microsoft. I don't believe there are any license restrictions that would
prevent you from using the product in a production environment, but you would
need to check this out.

Other options would be to use an OSS product like MySQL or PostGreSQL.

I also think that both Oracle and IBM have free versions of thier database
products that you could use.

I would only use Access if this were a classroom/learning project. I would
avoid Access for a production application.

Kerry Moorman

"moshi" wrote:

> Hey everyone,
> I want an advice. I want to build a site as an internet application using
> database etc.
> Since I need to use database , I'm interested in something not expensive for
> my own needs.
> I thought about using sqlServer, but I know that it is very expensive.
> Though I thought to use Access.
>
> - Did anyone use Access in an Asp.net application ?
> - Is it comfortable and runs ok ?
> - Does Microsoft continue to develope and support this tool ?
> - Which Access do I need for my purpose ? the simple one that comes with
> office 2003 ?
>
> Thanks a lot for any answer !!!

Top