ADO Connection question

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
    - add STOP button, to cancel the query process I have form that executes heavy Stored Procedure on sql 2000. I want to add STOP button, to cancel the process. I try to abort the thread. But this action not stops the process Immediately. It?s take a long time until query cancel. In the Query Analyzer I can kill the process at once by disconnecting the window, it?s cause also to cancel the query. How I can do something like from .NET code? I try close Connection from Main thread, and nothing happened? Tag: ADO Connection question Tag: 100547
  - 2
    - How do I "Fake" a Bookmark? I have a 'for each DataRow in DataTable.Rows loop and I need a way to jump back to a privious row and continue the loop. I haven't been able to come up with any clever ways of faking this since Bookmarks don't exist anymore:-( Thnx, Grayson Tag: ADO Connection question Tag: 100544
  - 3
    - DataSet I need to convert a VB6 Disconnected Recordset project to C#. The VB6 app uses ADO to create a recordset, add fields, add data. It then Binds to a grid. The VB6 App uses the client side cursor. When ever I move through the Recordset using (MoveNext,MovePrevious,Find) th Grids cursor position mirrors the recordset row. I can't seem to duplicate this in C#. I can create a Dataset, add fields, add data and use the "Find" method and bind the datagrid BUT the grids cursor doesn't track. Also I can't find the Navigate functions in the Dataset(MoveNext,MovePrevious,EOF,BOF) I really need those. is there a "Cursor" in the Dataset? I am somewhat confused, I'm still thinking in the older ADO world... Can someone point me in the right direction? Thanks... -Lou Tag: ADO Connection question Tag: 100536
  - 4
    - Throw DbConcurrencyException? I am using SQL OleDb provider (not SqlManaged provider). I have an ADO.NET class that uses DataAdapter.Update to pass changes to the database. I have a "concurrency" column in the database and in my DataTable. The MSSQL (2000) stored procedure only does the update of the "concurrency" column matches, if not it returns a result set. What is a good strategy for detecting, at the SP or DataAdapter level, if the update failed so I can throw a DBConcurrencyException? What is a good strategy for force-throwing a DBConcurrencyException from the DB? Thanks. Tag: ADO Connection question Tag: 100535
  - 5
    - Data Type Error Reading from database Thanks Memnoch, I have got round with that error and now something else has cropped up. My guess is that its something to do with data conversion. I am getting this error, In my database, the field LoginName is set as Vachar type. It looks as though there is a problem reading this field. could you please enlighting me on what I have done wrong and what i could do to correct this error. Thanks for you Help. ''----------------------Error------------------------- The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable. Source Error: Line 56: authorisedusernameList.Items.Add( dataReader.GetString( 0 ) ); Source File: c:\inetpub\wwwroot\websupportproject\login.aspx.cs Line: 56 Stack Trace: [InvalidCastException: The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable.] System.Data.OleDb.DBBindings.get_ValueString() System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal) WebsupportProject.Login.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\websupportproject\login.aspx.cs:56 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain() '---------------end of error------------------------------ Thank you for your help so far. Tag: ADO Connection question Tag: 100526
  - 6
    - connectionstring Hi, I am new to dotNET. I want to connect to MSAccess DB from ASP.NET I want to seperate connectionstring and open the connection just as an object and properties. I am not able to do that. How can i do? In every page i want to connect to DB and no need to specify connectionstring in every page. And, how can i set the connectionstring in web.config file? <appSettings> <add key="appconnection" value="Provider=....;data source=????> Here we must specify physical path. But with web always must specify virtual path. How can i set server.mappath here? </appSettings> Tag: ADO Connection question Tag: 100516
  - 7
    - Interrogating mySQL datatypes with ADO.NET Hi, Interrogating the DataType property of a DataColumn object where the underlying column in a mySQL table is CHAR, VARCHAR or TEXT returns System.String for each. Is there a method to differentiate these underlying datatypes and, if so, to interrogate the column length so that it will return me, say, VARCHAR(20) instead of just System.String? Any assistance gratefully received. Best regards, Mark Rae Tag: ADO Connection question Tag: 100513
  - 8
    - RUSH YOUR RESUMES -------------------------------------------------------------------------------- 1. Software Professionals: B.Tech/MCA from reputed institute/university with 4-6 years of software experience with excellent conceptual understanding and extensive usage of: Ø Microsoft Technology - ASP, .net, HTML, COM, XML, VSS and SQL Server. Ø Java/J2EE Technology - Java, J2EE, EJB, Swing, Servlets, JSP, Struts, XML, XSL style sheets, JDBC, PL/SQL. -------------------------------------------------------------------------------- 2. Project Managers/Software Architects/Database Architect: B. Tech/MCA with Software Project Management experience of 8-12 years. Should have handled projects in Microsoft Technologies like ASP, .Net, etc. Sound knowledge of SDLC & quality processes and ability to manage a team of software engineers, meeting the delivery schedules with quality. For Software Architect / DBA, extensive experience either in Software architecture (Microsoft technology - ASP, .net) or SQL server design and architecture are needed. -------------------------------------------------------------------------------- 3. Testing Professionals: B.Tech/MCA with strong testing skills, preparing test cases and test plans, reporting & tracking the Bugs using PVCS Tracker. Candidates with 3-5 yrs experience in Functional testing, Integration testing, System testing & Acceptance testing along with 2 yrs software development experience are more preferred. Exposure in automated test tools like Mercury WinRunner is desirable. Candidates with 8-10 years experience will be considered for senior position and such candidates should have experience in managing / leading a team. -------------------------------------------------------------------------------- 4. Back Office Associate: B.Com / M.Com / MBA with 1- 2 yrs experience and sound understanding of the Stock Market. Comfortable using MS office tools. Experience in depository, brokerage and/or stock holding industry with exposure to back office operations will be an added advantage. -------------------------------------------------------------------------------- 5. CFA Professionals: Maximum 3 years total work experience, of which minimum 1 year experience with a securities firm. Thorough knowledge of Capital Markets, Advisory Business, and Custodian Operations. Will be a Business Analyst / Domain Expert / Financial Consultant to the technical team, responsible for the accuracy of the functionality and end-to-end testing of an industry leading system for financial advisors. Knowledge about US capital markets will be an added advantage. Should have excellent analytical and communication skills and to be a go-getter. -------------------------------------------------------------------------------- princematthewkoshy@yahoo.com TRIVANDRUM Kerala INDIA Tag: ADO Connection question Tag: 100512
  - 9
    - logon failed on asp.net and crystal report Hi, I have made a asp.net application which is having a asp.net page showing crystal report.net on it(using crystal report viewer), and MS Access as back end.But when i try to view in a browser it is showing logon failed. I donn't have any password set in my mdb file. Regards Reny --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.806 / Virus Database: 548 - Release Date: 05/12/04 Tag: ADO Connection question Tag: 100510
  - 10
    - How I can give to my user ability to kill or cancel transaction Sql 2000 with dot.net 1.1 How I can give to my user ability to kill or cancel transaction Immediately, and rollback? I try to kill the thread. But this not cause the transaction to be Cancel immediately. First this takes a lot of time. Secondly I not be sure if this make rollback. Tag: ADO Connection question Tag: 100508
  - 11
    - Get XML from typed dataset Hi, I was using Typed Dataset in VB.NET to generate XML file. I difined XSD file( named customer_order_info) to include three tables, let's say customers, orders and order_details. I also defined relationship between customers and orders, orders and order_details.( customerid is PK in customers, orderid is PK in orders, orderid and productid are PK in order_details). In my VB.NET code, I declared a variable as the XSD(customer_order_info), declared a adapter to populate it(some common routines to get dataset). the select command is a stored procedure which will retuen 3 recordsets. I also defined table mappings when I populated the dataset. After I got the dataset, I call ds.getXML to get xml file. I was able to get the file with correct content, but the format is wrong. For eample, the format should be as follows: <customer customerid=â??aaaâ?? name=â??billâ?? phone=â??123345456â?? > <order orderid=â??1011â?? customerid=â??aaaâ?? shipname=â??Du monde entireâ??> <order_detail orderid=â??1011â?? productid=â??tool1â?? quantity=â??12â??/> <order_detail orderid=â??1011â?? productid=â??tool2â?? quantity=â??20â??/> <order_detail orderid=â??1011â?? productid=â??tool3â?? quantity=â??30â??/> </order> <order orderid=â??1012â?? customerid=â??aaaâ?? shipname=â??Du monde entireâ??> <order_detail orderid=â??1012â?? productid=â??tool4â?? quantity=â??12â??/> <order_detail orderid=â??1012â?? productid=â??tool2â?? quantity=â??20â??/> <order_detail orderid=â??1012â?? productid=â??tool5â?? quantity=â??30â??/> </order> </customer> But I got : <customer customerid=â??aaaâ?? name=â??billâ?? phone=â??123345456â?? > <order orderid=â??1011â?? customerid=â??aaaâ?? shipname=â??Du monde entireâ??/> <order orderid=â??1012â?? customerid=â??aaaâ?? shipname=â??Du monde entireâ??/> <order_detail orderid=â??1011â?? productid=â??tool1â?? quantity=â??12â??/> <order_detail orderid=â??1011â?? productid=â??tool2â?? quantity=â??20â??/> <order_detail orderid=â??1011â?? productid=â??tool3â?? quantity=â??30â??/> <order_detail orderid=â??1012â?? productid=â??tool4â?? quantity=â??12â??/> <order_detail orderid=â??1012â?? productid=â??tool2â?? quantity=â??20â??/> <order_detail orderid=â??1012â?? productid=â??tool5â?? quantity=â??30â??/> </customer> Could anyone help me out? Thanks. Tag: ADO Connection question Tag: 100500
  - 12
    - How to excute a MS Access Stored procedure? Hi, there I have create a MS Access Stored procedure using the code below and no error occured. '--------------------------------------------------------------------------- --- Dim cmd As OleDbCommand cmd = Conn.CreateCommand cmd.CommandType = CommandType.Text Try cmd.CommandText = _ "CREATE PROC udpGetSampleIDByName" & vbCrLf & _ "(@zSampleName VarChar(64))" & vbCrLf & _ "AS" & vbCrLf & _ "Select zSampleID FROM TSamples " & _ "Where zSampleName = @zSampleName " cmd.ExecuteNonQuery() Catch e As Exception MessageBox.Show(e.ToString, "Error Creating Stored Procedure") Finally cmd.Dispose() End Try '--------------------------------------------------------------------------- ------ And now I want to call the Stored procedure with OleDBCommand . After I excute the codes below I got an error message: "The syntax of the input string is invalid!" '--------------------------------------------------------------------------- ------ 'connNoTran is an opened oledbconnection Dim oCmd As New OleDbCommand With oCmd .Connection = connNoTran .CommandType = CommandType.StoredProcedure .CommandText = "udpGetSampleIDByName" .Parameters.Add("", OleDbType.SmallInt) .Parameters(0).Value = sName End With Try oCmd.ExecuteNonQuery() Catch e As Exception MessageBox.Show(e.ToString) End Try '--------------------------------------------------------------------------- ------ Anyone can help me? Peter Tag: ADO Connection question Tag: 100497
  - 13
    - Please Help Me - object reference not set to an instance hello all, I am new to C# and ASP, I am trying to build and applicationwith a login in page. I followed an example from a book to create his page but I am havin problem when i run the program. I know there has been a thread like this in this forum but the suggestion I have tried does not correct this problem. please have a look at the code belo and help me.need help urgently. you help is much appreciated. When i run the run I get this error in the browser, Object reference not set to an instance of an object" The error is on the line coloured red - dataReader = oleDbDataAdapter1.SelectCommand.ExecuteReader(); '------code from program--------- using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Web.Security; using System.Data.OleDb; namespace WebsupportProject { /// <summary> /// Summary description for WebForm1. /// </summary> /// allow authorised users to login public class Login : System.Web.UI.Page { protected System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1; protected System.Data.OleDb.OleDbCommand oleDbSelectCommand1; protected System.Data.OleDb.OleDbCommand oleDbInsertCommand1; protected System.Data.OleDb.OleDbCommand oleDbUpdateCommand1; protected System.Data.OleDb.OleDbCommand oleDbDeleteCommand1; protected System.Data.OleDb.OleDbConnection oleDbConnection1; protected System.Data.OleDb.OleDbDataReader dataReader; //web form labels protected System.Web.UI.WebControls.Label lbpassword; protected System.Web.UI.WebControls.Label lbauthorisedusername; protected System.Web.UI.WebControls.Label lbprompt; protected System.Web.UI.WebControls.DropDownList authorisedusernameList; protected System.Web.UI.WebControls.Button submitButton; protected System.Web.UI.WebControls.RequiredFieldValidator requiredPasswordValidator; protected System.Web.UI.WebControls.CustomValidator invalidPasswordValidator; protected System.Web.UI.WebControls.TextBox passwordTxtBox; protected System.Web.UI.WebControls.Label promptlabel; protected System.Web.UI.WebControls.DropDownList authourisedusernameList; protected System.Web.UI.WebControls.Label passwordLabel; protected System.Web.UI.WebControls.TextBox passwordTextBox; //Page Loader //Reading the list of user names from authoriseduser table private void Page_Load(object sender, System.EventArgs e) { if ( !IsPostBack ) { //open Database oleDbConnection1.Open(); //execute query dataReader = oleDbDataAdapter1.SelectCommand.ExecuteReader(); ' line of error // add first authorisedusername to the list while ( dataReader.Read() ) authorisedusernameList.Items.Add( dataReader.GetString( 0 ) ); // close database connection oleDbConnection1.Close(); } // Put user code to initialize the page here } // end of Page_Load private void invalidPasswordValidator_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args ) { //open database connection oleDbConnection1.Open(); oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM AuthorisedUsers WHERE LoginName = '" + Request.Form[ "authorisedusernameList" ].ToString() + "'"; dataReader = oleDbDataAdapter1.SelectCommand.ExecuteReader(); dataReader.Read(); //if password is correct create authentication ticketfor user if ( args.Value == dataReader.GetString( 1 ) ) { FormsAuthentication.SetAuthCookie( Request.Form[ "authorisedusernameList" ], false); Session.Add( "name", Request.Form[ "authorisedusernameList" ].ToString()); Response.Redirect( "LoggedCall.apsx" ); } else args.IsValid=false; //close database connection oleDbConnection1.Close(); }// end method invalidPasswordValidator_ServerValidate #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand(); this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); // // oleDbConnection1 // this.oleDbConnection1.ConnectionString = @"Integrated Security=SSPI;Packet Size=4096;Data Source=""ERYCKOP-002"";Tag with column collation when possible=False;Initial Catalog=Project;Use Procedure for Prepare=1;Auto Translate=True;Persist Security Info=False;Provider=""SQLOLEDB.1"";Workstation ID=""ERYCKOP-002"";Use Encryption for Data=False"; this.Load += new System.EventHandler(this.Page_Load); } #endregion }//end class login } ----end of code------ any help is much needed. Thank you Tag: ADO Connection question Tag: 100492
  - 14
    - Excel-like appearance in the datagrid I'd like to give my users the same "empty grid" look of an Excel sheet (at least an empty row to start with). I've thought to mimic this appearance by setting up an empty datatable bound to the grid (columns will be predefined) and then using AddNew to add the first empty row to the table. Is that the usual approach or can anyone recommend something better? Tag: ADO Connection question Tag: 100491
  - 15
    - Same question again - tables fro 2DBs Sorry, but I asked the previous question poorly. I have two SQL Server databases (on the same server) and I want to reference tables from both DBs in the SQL. I know that manually written SQL can reference tables in multiple databases by prepending the database name when necessary. However, we use the DataAdapter Configuration Wizard and Querybuilder for productivity reasons but it seems that this wizard will only allow connection to a single database withing a single data adapter. Is there a way to be able to include tables from multiple databases in the Querybuilder wizard? Thanks, T Tag: ADO Connection question Tag: 100489
  - 16
    - 2 DBs Ref by 1 DataAdapter? I have two SQL Server databases and I want to reference tables from both DBs in the SQL. How can I have a connection object that points to both DBs? Thanks, T Tag: ADO Connection question Tag: 100485
  - 17
    - Can't save new row to table Can't save new row to table I'm trying to save a new row using the following code: It's a schema adapted almost exactly from one given me by Kevin Yu some time ago. One difference is the inclusion of the ID field. If I don't include it I get an error message. I also have an addhandler onCAIrowupdated. The msgbox in that gives the right answer for the table's ID = 0, since it would be the first entry. However, right after the msgbox I get an error. Message: You cannot add or change a record because a related record is required in table 'PointsOfView'. The line number indicated is intModified = daCAI.Update(tblCAI) Of the six fields in ComceptAssignmentInstances, four have 1 to many relationships with with table: TopicID, PointOfVew, ReportType, and ConceptID. This is an Access database. I carefully examined the relationship table, and PointOfView is identical with all the others. So I put in the following message boxes Both showed values 0, because I hadn't changed them, and the error message showed. The second time I changed the comboxes selected items and the save was successful. Two problems. The first is the known but of having to first move the selected value of of comboboxes to 1 and then back to 0. But where and how can I do it once I click the save button? The second problem is that the ID value in the saved table was -1. Since ID is an autoincrementing long integer, why didn't it have value 0? thanks, dennist685 Private Sub btnSaveC1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveC1.Click Try daCAI.Fill(DsCAI1, "ConceptAssignmentInstances") Dim tblCAI As New dsCAI.ConceptAssignmentInstancesDataTable Dim rowCAI As dsCAI.ConceptAssignmentInstancesRow rowCAI = tblCAI.NewConceptAssignmentInstancesRow rowCAI.ID = -1 rowCAI.TopicID = iTopicID rowCAI.BeginText = txtConceptText.SelectionStart rowCAI.ConceptID = lstConcepts1.SelectedValue rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue rowCAI.PointOfView = Me.cboPOVCncpt.SelectedValue 'rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue MsgBox("report type " & rowCAI.ReportType.ToString) MsgBox("point of view " & rowCAI.PointOfView.ToString) tblCAI.AddConceptAssignmentInstancesRow(rowCAI) daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO ConceptAssignmentInstances(ID, TopicID, BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?,?)", cnCAI) 'daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO ConceptAssignmentInstances( TopicID, BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?)", cnCAI) daCAI.InsertCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID") daCAI.InsertCommand.Parameters.Add("@TopicID", OleDb.OleDbType.Integer, 4, "TopicID") daCAI.InsertCommand.Parameters.Add("@BeginText", OleDb.OleDbType.Integer, 4, "BeginText") daCAI.InsertCommand.Parameters.Add("@ConceptID", OleDb.OleDbType.Integer, 4, "ConceptID") daCAI.InsertCommand.Parameters.Add("@ReportType", OleDb.OleDbType.Integer, 4, "ReportType") daCAI.InsertCommand.Parameters.Add("@PointOfView", OleDb.OleDbType.Integer, 4, "PointOfView") AddHandler daCAI.RowUpdated, AddressOf OnCAIRowUpDated Dim intModified As Integer intModified = daCAI.Update(tblCAI) Dim sOutput As String sOutput = "Modified " & intModified & " ConceptAssignmentInstances" Dim s As String = ControlChars.CrLf Catch ex As OleDbException If ex.Errors(0).SQLState = "3022" Then Else Dim errorMessages As String errorMessages += "Message: " & ex.Errors(0).Message & ControlChars.CrLf _ & "NativeError: " & ex.Errors(0).NativeError & ControlChars.CrLf _ & "Source: " & ex.Errors(0).Source & ControlChars.CrLf _ & "SQLState: " & ex.Errors(0).SQLState & ControlChars.CrLf _ & "StackTrace: " & ex.stacktrace & "The form will be closed" MsgBox(errorMessages) Me.Close() End If Catch ex As DBConcurrencyException MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error) Me.Close() Catch ex As Exception MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error) Me.Close() End Try Me.cnCAI.Close() End Sub Tag: ADO Connection question Tag: 100479
  - 18
    - Large Datasets Hello All, Does anyone have any suggestions on working with large datasets (10GB - 100GB)? Best initialization? Transfer from dataset to database? Anything would be helpful, articles, code examples, discussions, etc Thanks In Advance Tag: ADO Connection question Tag: 100474
  - 19
    - Connection String Machine Independent. Hi there, I've been researching on the diferent newsgroups to find out how to do this, but no luck so far. I'm developing a client/server windows application with VB.NET, MSDE is my backend database. This is sample connection string to connect to my server: data source=MACHINE_NAME\MSDE; initial catalog=DatabaseName; user ID=sa;password=sa Now, when I distribute this application, the Server will have to be installed in one machine in the LAN, now the connection string it's hardcoded right now to look for a specific machine name and the instance name. So, how can I build, if it's possible, the connection string machine independent? Thanks very much, Manny Tag: ADO Connection question Tag: 100467
  - 20
    - ADO .NET and Excel ... Hi Frankly speaking, what ever one says, Microsoft technologies will always remain problem-matic.. specially with versions... they don have seamless integration. Deployment is always always an issue. But I still love .NET!! Anyway folks, my problem goes like this... I am reading and updating excel sheet using ADO .NET. Everything is working fine on machine, but on server it is all messing up... I connect to sheet using the string called... Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;' But whenver I try to execute insert command, following error occurs... Operation must use an updateable query.Microsoft JET Database Engine at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at MyPledge.ClientPaymentData.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\Web1\Data.aspx.vb:line 74 I will highly appreciate if someone help me... !!! eff_kay ------------------- IMPORTANT: No trees were killed during the sending of this message but a large number of electrons were terribly inconvienced. Tag: ADO Connection question Tag: 100465
  - 21
    - How to resolve "General Network Error"? I'm getting the following message trying to update a SQL Server table: "General network error. Check your network documentation." I'm using stored procedures via a SQL data connection from within a .Net windows application. The application producing the error, reads a table from the server, then proforms a series of updates, deletions and insertions locally to a DataSet. After all processing is completed, the data is written back to the server using the Update(dataset, "table_name") method on the DataAdapter. The Update method is called 3 times, once for all updated rows, once for all inserted rows and once for all deleted rows. Currently, I read and update 3 tables in seperate datasets. 2 tables work and the 3rd does not. How can I get resolved this issue? Tag: ADO Connection question Tag: 100464
  - 22
    - Getting SQL Error - "General network error" I'm getting the following message trying to update a SQL Server table: "General network error. Check your network documentation." I'm using stored procedures via a SQL data connection from within a .Net windows application. The application producing the error, reads a table from the server, then proforms a series of updates, deletions and insertions locally to a DataSet. After all processing is completed, the data is written back to the server using the Update(dataset, "table_name") method on the DataAdapter. The Update method is called 3 times, once for all updated rows, once for all inserted rows and once for all deleted rows. Currently, I read and update 3 tables in seperate datasets. 2 tables work and the 3rd does not. How can I get resolved this issue? Tag: ADO Connection question Tag: 100463
  - 23
    - create table based on existing tables in a dataset, how ? hi all i create a dataset with table a, b and c in it. Is it possible for me to generate extra table (e.g. d) into this dataset based on table a and table b (which already exist in this dataset) ? note that creation of table d, include a LEFT JOIN between table a and b. pls give me some suggestion / example on how to do it. cheers, Tag: ADO Connection question Tag: 100457
  - 24
    - Cannot open OracleConnection with Oracle 9.2 in ASP.NET Hi I'm using System.Data.OracleClient with VisualStudio.NET 2003 and trying to connect to Oracle 9.2 database. Oracle client software for 9.2 has been installed and I can perfectly make a connection in a Windows Forms application. But on the same machine, I cannot make a connection in an ASP.NET web application. The Exception is raised when I try to open the connection as follows: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. What is happening? Tag: ADO Connection question Tag: 100456
  - 25
    - ado.net exception from raiserror In a couple of stored procedures (SQL Server 2000) im doing: raiserror('aaa',16,1) return 1 My understanding was that this should throw an exception in my C# code. But sometimes it doesn't (I have to manually check the return value parameter). Can anyone explain why? Is there a difference in behaviour between SqlCommand.ExecuteNonQuery and ExecuteReader (or SqlDataAdapter.Fill) in this regard? The situation where I've noticed no exception is being thrown is when I'm calling adapter.Fill(), and the sproc has already returned a resultset (but later on in the sproc code it detects an error). Maybe the presence of a resultset prevents an exception being thrown? Thanks, John Tag: ADO Connection question Tag: 100448
- Next
  - 1
    - Better way to build results? (Relative newbie) Hello, Undoubtedly this answer is in front of my nose, but I can't figure it out or find it anywhere. Using VB.NET, Access database. I have successfully built a series of nested queries that return results from three interrelated tables, reading the results into datareaders. I can iterate through the three datareaders and build the results that I want, but I suspect there are more efficient ways to do what I need. The only way I have been able to get this to work is by establishing one connection for the first query, and then in a nested loop keep making connections to satisfy the second query, and within that loop there is another loop that keeps making connections to satisfy the third query. This works fine on my very small test database, but I know users will have much larger ones. I can't use UNION, because the tables have differing numbers of fields. Let's say I have three tables: Neighborhoods, Houses, and Residents (not my real database!). Neighborhood.nID = Houses.nID and Houses.hID = Residents.hID. The trick is that I might not have houses for every neighborhood, and I might not have residents for every house. But I need to get all the neighborhoods, even the ones without houses, and I need to get all of the houses, even the ones without residents. Conversely, every resident has to have a house, every house has to have a neighborhood. So my simplified code is: Establish first connection Get all the neighborhoods Read the first neighborhood Build a node for a TreeView Establish/reestablish second connection Get all the houses in that neighborhood Read the first house Build a node for a TreeView Establish/reestablish third connection Get all the residents in that house Read the first resident Build a node for a TreeView Loop Close third connection when we've found all the residents for that house Loop Close second connection when we've found all the houses for that neighborhood Loop Close first connection when we've found all the neighborhoods Any and all advice gratefully received. Thanks, Chris Tag: ADO Connection question Tag: 100447
  - 2
    - Error: ArgumentOutOfRangeException on stringbuilder Hi experts, I wrote a function to use stringbuilder for a fix length text delimited file for banking stuff. I received an error on strResult.Insert(141, "".PadLeft(21, " ")) Error: System.ArgumentOutOfRangeException Message: "Index was out of range. Must be non-negative and less than the size of the collection. StackTrace: "at System.Text.StringBuilder.Insert(Int32 index, String value, Int32 count) at System.Text.StringBuilder.Insert(Int32 index, String value) at EPay_Enroller.JenniferFunct.GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus, String& strAddressFlag, String& strNameFlag) Any help? ------------------------------------------------------------------------------------------- Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("...................................................") Dim selectCMD As SqlCommand = New SqlCommand("SELECT DDACTNUM, LASTNAME,FRSTNAME,BRTHDATE FROM UPR00102 where EmployID=' " + strEmpID + "'", GPConnection) Dim GPDataAdapter As SqlDataAdapter = New SqlDataAdapter GPDataAdapter.SelectCommand = selectCMD Dim EfundAcct As String Dim LName As String Dim FName As String Dim MName As String Dim DOB As String Dim Loc As String Try GPConnection.Open() Dim DS As DataSet = New DataSet GPDataAdapter.Fill(DS, "AcctInfoTble") EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(0)("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(0)("FASTNAME") 'Position 1 2A A strResult.Insert(0, "A ") 'Position 3 10A Employee Number strResult.Insert(2, strEmpyID.ToString.PadRight(10, " ")) .........codes 'Position 142 21A Filler strResult.Insert(141, "".PadLeft(21, " ")) ......more codes -------------------------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ADO Connection question Tag: 100445
  - 3
    - Adding columns to SQL Server tables dynamically Hi, I'm writing a routine which queries a table in a mySQL database and then adds records from it into a SQL Server 2000 database - pretty easy stuff. Problem is, that the mySQL table may have additional columns every time I query it. I can easily use the Columns collection of both tables to see if there is a new column in the mySQL table which isn't in the SQL Server table, but what's the easiest / most efficient way to add a new column to an existing SQL Server table, given that I know the name and datatype of the column to be added? Would it be to pass some sort of "ALTER TABLE..." SQL into an SQLCommand object, or is there a better way? Any assistance gratefully received. Best regards, Mark Rae Tag: ADO Connection question Tag: 100440
  - 4
    - Dataset Error:IndexOutofRangeException Hi experts, I have a dataadapter and datasets. I debugged it and gave me an error on LName = DS.Tables("AcctInfoTble").Rows(1)("LASTNAME") error: There is no row at position 1. WHAT DID I DO WRONG at the following code? Thanks. ------------------------------------------------------------------------------------------------ Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("workstation id=ADMIN;packet size=4096;user id=MaintenanceHD;data source=AMKSQL2;p" & _ "ersist security info=True;initial catalog=HFC;password=maintainme") Dim selectCMD As SqlCommand = New SqlCommand("SELECT DDACTNUM, LASTNAME,FRSTNAME,BRTHDATE FROM UPR00102 where EmployID=' " + strEmpID + "'", GPConnection) Dim GPDataAdapter As SqlDataAdapter = New SqlDataAdapter GPDataAdapter.SelectCommand = selectCMD Dim EfundAcct As String Dim LName As String Dim FName As String Dim MName As String Dim DOB As String Dim Loc As String Try GPConnection.Open() Dim DS As DataSet = New DataSet GPDataAdapter.Fill(DS, "AcctInfoTble") EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(1)("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(2)("LASTNAME") ......more codes -------------------------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ADO Connection question Tag: 100437
  - 5
    - Incorrect in passing parameters Hi all, I received an error: System.NullReferenceException: Object reference not set to an instance of an object at GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus). When I run the debug, and in the command line I entered ?strEmpID, and it does passed the correct employee ID. However, in the first line of the dataset, EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") it generates error message "Object reference not set to an instance of an object" and stacktrace is "at GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus)" ---------------------------------------------------- Main.vb Main.VB ----------------- some codes........ Dim strCurEmployId As String strCurEmployId = dtrSelectTransactions("EmployID") smCurBatch.WriteLine(GenerateA(strCurEmployId, boxTopCorp.Text, "Open")) some codes............... ------------------------------------------------ Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection(".............................................") Dim GPDataAdapter As New SqlDataAdapter("SELECT LASTNAME,FRSTNAME,MIDLNAME,SOCSCNUM,ADDRESS1,PHONE1,BRTHDATE FROM UPR00102 where EmployID= " + strEmpID, GPConnection) Dim EfundAcct As String Dim LName (30) As Char Dim FName (30) As Char Dim MName (30) As Char GPConnection.Open() GPDataAdapter.Fill(GPDataset, "AcctInfoTble") Dim DS As DataSet = New DataSet EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(1).Item("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(2)("LASTNAME") MName = DS.Tables("AcctInfoTble").Rows(3)("MASTNAME") 'Position 1 2A A strResult.Insert(0, "A ") 'Position 3 10A Employee Number strResult.Insert(9, strEmpID.PadRight(10, " ")) 'Position 13 16N Efund acct number strResult.Insert(12, EfundAcct.PadLeft(16, " ")) GPConnection.Dispose() Return strResult.ToString End Function *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ADO Connection question Tag: 100435
  - 6
    - DataSet, Relation, IsNested and WebService Hi all, If I want to provide a typed dataset from a webservice and if that dataset has relations that are nested (isNested=True), then the relations(keyrefs) are not available in the XSD that is offered. If I set IsNested to false or default, then it is included in the XSD. Is this a bug or by design? Corno Tag: ADO Connection question Tag: 100431
  - 7
    - Can you package a oledb data provider? Hello, Is it possible to package a oledb data provider? I need to be able to access an iSeries machine, but the server that the program is being installed on does not have the ibmda400 provider. The easy way would be to install client express, but unfortunatly that can not happen. Any ideas? Thanks, Brian Tag: ADO Connection question Tag: 100430
  - 8
    - Converting from DataColumn to OleDbType for Analysis Services 2005?? Its been posted before, but I've not found a satisfactory answer. Environment: Dot-Net 2.0 beta, with Analysis Services 2005 beta. DbDataAdapter produces DataColumns from its FillSchema method which give me all sorts of information about the data type of the column. AMO, the API for Analysis Services 2005, requires me to give an OleDbType when I set up a foriegn key in a fact table and when I make an Attribute. (I'm using metadata as a starting point to scan a data warehouse and build the dimensional model programatically. Think of it as a form of Intellicube, but especially taylored to the structure of our data warehouses feeding off our existing metadata.) The thing is. I've used DbDataAdapter to FillSchema() to get the underlying field types in the tables. It should _know_ what data type is there as its just told me. I suppose the problem is that DataTable class being generic can't know anything about specific databases, but it would be nice if there was a method somewhere to take a DataColumn and get the XXXDbType from it. Analysis Services already knows the data type, as it's got a Data Source View. I've presumably already told it once when I defined the schema by passing in the DataSet that defined it - the DataSet made using FillSchema. Or have I? I'm building up a model that presumably hasn't been committed yet, so presumably the problem is that there's no way (even though I've given it a Connection String) for it to go to the database and find out what it needs, and its not willing to wait until commit time to work it out? Can I assume that the DataColumn doesn't have enough information in itself? Therefore the Data Source View is, at that time, incomplete, and I have to add the information myself from an external source? If thats the case - it's not nice. Also, as an aside, why (I'm following Microsoft's sample code) am I using an OleDbDataType with an SQLServer database? Thanks - Richard Tag: ADO Connection question Tag: 100428
  - 9
    - Value in output parameter not accessable I am using the Microsoft Data Application Block to access a database and am trying to execute the following stored procedure: CREATE PROCEDURE WhiteboardPostItem @Id int OUTPUT, @Subject varchar(255), @Message varchar(2000), @PostedBy varchar(100) AS INSERT INTO Whiteboard(Subject, Message, PostedBy, DatePosted) VALUES (@Subject, @Message, @PostedBy, getdate()) SELECT @Id = @@IDENTITY GO This procedure is called from the following code: Public Shared Function PostItem(ByVal Subject As String, ByVal Message As String, ByVal PostedBy As String) As Integer Dim arParams() As SqlParameter = New SqlParameter(3) {} arParams(0) = New SqlParameter("@Id", SqlDbType.Int) arParams(0).Direction = ParameterDirection.Output arParams(1) = New SqlParameter("@Subject", SqlDbType.VarChar, 255) arParams(1).Value = Subject arParams(2) = New SqlParameter("@Message", SqlDbType.VarChar, 2000) arParams(2).Value = Message arParams(3) = New SqlParameter("@PostedBy", SqlDbType.VarChar, 100) arParams(3).Value = PostedBy ' Call to Microsoft Data Application Block SqlHelper.ExecuteNonQuery(ConnectionString(), "WhiteboardPostItem", arParams) Return Convert.ToInt32(arParams(0).Value) End Function However, the output parameter (arParams(0)) is never set to the identity value of the inserted row even though the row does get added. I've checked the stored procedure in query analyser and the return value is properly set but it does not work through this code. The code in the above function is now in the format described in a Microsoft example and it still doesn't work. I'm sure I'm missing something obvious, but can anyone tell me how to resolve this as it's driving me mad! Many thanks Alan Tag: ADO Connection question Tag: 100421
  - 10
    - Viewing large amounts of data We have a data management application writen in Delphi. This app shows all tables in the database, and display all data in a given table in a datagrid. We are using an ODBC connection for this. This functionality is not unlike what is seen in e.g. Enterprise manager. Now even with 400K of rows, this datagrid is "filled" is mere seconds. I am aware that the underlying dataset only retrieves a subset of rows, and dynamically retrieves and unloads rows as needed, thereby giving an illusion of retrieving the entire table. We are now moving to .net and I find myself having a great deal of difficulty getting the same functionality out of ado.net. The ODBC components we are using in our Delphi app is connected whereas ado.net is disconnected. What I basically need is to retrieve say 100 rows at a time, and dynamically unload and retrieve new rows and filling them into a datagrid as the user scrolls back and forth through the datagrid. How do I achieve this? Please dont lecture about filtering and whatnot. This is the functionality we need. -- Thomas Due Posted with XanaNews version 1.16.5.2 "If you can't convince them, confuse them." -- Harry S. Truman Tag: ADO Connection question Tag: 100419
  - 11
    - include rowstate in dataset.getxml Hi Gurus, how can i include rowstate value in dataset.getxml string <datatable> <row1 state="Added"> <col1>v1</col1> </row1> <row2 state="Modified"> <col1>v2</col1> </row2> </datatable> </dataset> i am passign the above string to stored proc(sql200) to perform DML operations any help on this. regards mak Tag: ADO Connection question Tag: 100407
  - 12
    - deleting rows in one-to-many relationship Dear Ng, I have a 1(master table) : n(first child table) : m(second child table) relationship in my sql server and strongly typed dataset. Now I locate the corresponding row in the master table. DataAccessControl.DocumentTemplateRow rowTemplate = tblTemplate.FindBydocumentTemplateId(templateId); Then i request all child rows: DataRow[] rowsToDelete = rowTemplate.GetChildRows("DocumentTemplateDocumentField"); Then I want to delete all child rows in the child tables: foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete) { tblFields.RemoveDocumentFieldRow(row); } dac.updateDataAccessLayer(); The delete rules of both relations (from the master to the first child and from the first to the second child) is set to cascade (in the database and in the dataset). But if I execute the code, just the rows in the second child table are deleted. What do I do wrong? If I use additionally the row.delete() command foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete) { row.Delete(); if (row.RowState == DataRowState.Deleted) tblFields.RemoveDocumentFieldRow(row); } dac.updateDataAccessLayer(); I'll get a DBConcurrencyException! What is wrong? What I want to achieve is that the master record is kept and all child records are deleted. Thanks in advance and best regards, Patrick Braunschweig Tag: ADO Connection question Tag: 100403
  - 13
    - Trouble with OleDbDataAdapter Hi there, I am trying to display information from Active Directory using a DataGrid. Now, I know MS have put out the System.DirectoryServices classes to make this easy. But if you just want to query and display, it seems an easier path is to use the AdsDsoObject provider with OleDb, and get back a DataTable, pour into a DataView, bind to the DataGrid, voila! At least, that's the theory. The main driver is that trying to bind a SearchResultCollection to a DataGrid doesn't yield very satisfactory results, and that's the only useful class in the DirectoryServices namespace that implements ICollection and IEnumerable. The problem is, if I manually construct a DataTable with dummy data, pour into a DataView, bind to DataGrid and display then I see exactly what I would expect. If I setup an OleDbDataAdapter, query the AD, fill a DataSet and serialize to XML I see what I would expect. If I setup an OleDbDataAdapter, query the AD, fill a DataTable, pour into a DataView, bind to the DataGrid and display, I get: System.Web.HttpException: DataGrid with id '' could not automatically generate any columns from the selected data source Here is the source for Page_Load that generates the last result: string ldapString = "<LDAP://DC=acme,DC=com>;(&(objectClass=user)(objectCategory=person));cn,distinguishedName;subtree"; string connstr = "Provider=ADsDSOObject;User ID=acme\\Administrator;Password=notthispassword"; OleDbConnection conn = new OleDbConnection(connstr); OleDbDataAdapter da = new OleDbDataAdapter(ldapString, conn); DataTable dt = new DataTable("Table1"); da.Fill(dt); DataView dv = new DataView(dt); dg1.DataSource = dv; dg1.DataBind(); Now, I'm fairly new to all this, but I've spent a good couple of days butting up against this one. The only checking I haven't really done is using OleDb with a different provider and seeing if it is a general problem. The main reason I haven't done that is that I can see the data there if I use an alternate means of accessing it! So, any ideas? MTIA, Mick Heywood Tag: ADO Connection question Tag: 100402
  - 14
    - Create Stored procedure Hi, there I want to create a Stored procedure for my Access database using the code below. ' Conn is an opened connection to my Access database Dim cmd As OleDbCommand cmd = Conn.CreateCommand cmd.CommandType = CommandType.Text cmd.CommandText = _ "CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _ "@zSampleName VarChar(64)" & vbCrLf & _ "AS " & vbCrLf & _ "Select zSampleID FROM TSamples " & _ "Where zSampleName = @zSampleName " cmd.ExecuteNonQuery() After running, I got a error message: "invalid SQL Syntax£ºneeded symbol AS¡£" It goes well when I create SQL Server stored procedure using SQLCommand . Now I turn to OleDB and the error occurs. Anybody can help me? Peter Tag: ADO Connection question Tag: 100401
  - 15
    - "Cannot create a child list for field MyTable" I'm having a frustrating problem trying to set up a master-detail relationship between 2 datagrids. I've used DataSets extensively before, but only in code and basically only in a disconnected state. Now as I try to set up this simple master-detail thing, I'm having problems. I have about 6 tables in a SQL Server 2000 database. All tables have single field primary keys, and I've created appropriate relationships betwen the tables in SQL Server. In VS 2003, I've created a SQLDataAdapter for each table, that interacts with the table via stored procedures created by the SQLDataAdapter wizard. I then used the "Generate Dataset..." functionality of one of the data adapters to autogenerate dataset that included all 6 tables (i.e. it was based on all 6 data adapters). So far so good. In the DataSet schema editor, I created relationships between the datatables to mimic the relationships in SQL Server. This seems to go ok, and when I "Preview DataSet" from the schema editor, all seems ok - including the relationships. I also have a WinForm that has 2 DataGrids on it. Both DataGrids have the DataSet I created as their DataSource. When I try to list the DataTables in the DataGrid.DataMember property so I can set up the DataGrids for the master-detail, I the the error, "Cannot create a child list for field MyTable", where 'MyTable' is one of my DataTables in the DataSet. If I remove all of the relations from the DataSet, and save the .xsd schema a few times (moving the datatables on the designer between saves so there's something to save), the error will go away - but then I have no relationships. So, I re-create the relationships in the DataSet schema editor, and I start getting the error again. Sometimes I don't get the error until I have more than one relationship, and sometimes by moving the datatables on the schema designer and re-saving the dataset schema a few times, I don't get the error - even when the dataset has 2 relationships. It seems some code is getting corrupted somewhere. I've tried restarting VS, and I've rebooted my machine. I've also deleted the DataSet and created it again from scratch. The same thing happens. When running my project, I can successfully fill my DataSet from the DataAdapters, but I get the same error when I try to set the DataMember property of one of the DataGrids in code. What's going on? Tag: ADO Connection question Tag: 100400
  - 16
    - SQLDMO Code Sometimes Works I have this code on two identical machines, named DEVONE and DEVTWO. Both on the network. It works on DEVONE, finding (local) and DEVONE and populates the combo box with them, but does not find any server(s) on DEVTWO, putting <No Servers Found> in the combo box. Can anyone shed any light. Code below: Private Sub GetSQLServers() Dim i As Integer Dim sqlApp As New SQLDMO.ApplicationClass Dim sqlServers As SQLDMO.NameList = sqlApp.ListAvailableSQLServers() For i = 0 To sqlServers.Count - 1 Dim srv As Object = sqlServers.Item(i + 1) If Not IsNothing(srv) Then Me.cboRelevantServer.Items.Add(srv) Me.cboMainServer.Items.Add(srv) End If Next If Me.cboRelevantServer.Items.Count > 0 Then Me.cboRelevantServer.SelectedIndex = 0 Else Me.cboRelevantServer.Text = "<No Servers Found>" End If End Sub Thanks, Michael Tag: ADO Connection question Tag: 100399
  - 17
    - DataSet and Stringbuilder? Hi expert, I received a sample from bank for the header record in the output file (attached at the bottom). I want to use same method to have a stringbuilder to arrange the fix length positions, and the fields will be retrieved from database and put into the dataset. But I am familiar at how to use dataset and put into .insert stringbuilder. Here is the code that I am working on (the function should return 1 string line at a time. The loop of reading rows will be controled in main form): Can someone show me how to retrieve a column, say LastName and put into strResult.Insert()??? Thanks. Imports System.Data.SqlClient Module Module1 Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("............................") Dim GPDataAdapter As New SqlDataAdapter("SELECT LASTNAME,FRSTNAME,MIDLNAME,PHONE1,BRTHDATE FROM UPR00102 where EmployID="+strEmpID,GPConnection) Try GPConnection.Open() GPDataAdapter.Fill(GPDataset, "AcctInfoTble") Dim DS As DataSet = New DataSet 'position 1 20A strResult.Insert ????????????????? Catch ex As Exception MessageBox.Show(ex.ToString) Finally GPConnection.Dispose() End Try Return strResult.ToString End Function End Module ------------------------------------------------------------------------------------- Module GenerateFile Public Function GenerateFH(ByRef strCompanyName As String, ByRef strTopCorp As String, ByRef strBatchNum As String, ByRef dtFileDate As Date) Dim strResult As New System.Text.StringBuilder strResult.Capacity = 731 'Position 1 2A FH strResult.Insert(0, "FH") 'Position 3 10A Header Date strResult.Insert(2, dtFileDate.Date.Year & "/" & dtFileDate.Date.Month.ToString.PadLeft(2, "0") & "/" & dtFileDate.Date.Day.ToString.PadLeft(2, "0")) 'Position 13 8A Header Time strResult.Insert(12, dtFileDate.TimeOfDay.Hours.ToString.PadLeft(2, "0") & ":" & dtFileDate.TimeOfDay.Minutes.ToString.PadLeft(2, "0") & ":" & dtFileDate.TimeOfDay.Seconds.ToString.PadLeft(2, "0")) 'Position 21 10A Transfer File Version strResult.Insert(20, "4.0".PadRight(10, " ")) 'Position 31 10A Product Type strResult.Insert(30, "EFUNDS".PadRight(10, " ")) ............. Return strResult.ToString End Function End Module --------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ADO Connection question Tag: 100394
  - 18
    - Moving solution to another machine, SQL Failure Hi all, I've developed a .NET 2003/SQL Server 2000 application. When the app fires up, it loads combo boxes (Using SQLDMO) with available servers, databases and tables. It works great. However, when I move the solution to another PC and try to run it, it fails saying "Login failed for user DEVONE\Guest'. DEVONE is the name of the machine where I developed the application, which is running SQL Server 2000 Developer. No where in my code does it reference DEVONE, so somewhere a reference to the previous machine name or SQL Server instance name is being held in the solution. Any ideas? Thanks Michael Tag: ADO Connection question Tag: 100390
  - 19
    - GetChanges false positives, How to avoid? I was curious if anybody know of a way to avoid false positives while using the GetChanges method. In most of my updateable aspx pages I have text/ddl/checkboxes that are populated from the existing data that the user can change, then hit the save/update button. When the save/update button is clicked the dataset is updated from the controls values regaurdless if the values in the controls have changed or not. Such as ds.Table1.Col1 = TextBox1.Text; // set whether text has changed or not. ds.Table1.Col2 = TextBox2.Text; The problem arises when a user hits the save/update button without changing anything on the page. This means that all the values in the dataset get set to exactly what they were before; however, the GetChanges returns rows with no real changes in them. Take the following code. The last GetChanges always returns 1 row even though no values in the row have changed. Dataset1 ds = new Dataset1(); Dataset1.UsersRow r = ds.Users.NewUsersRow(); string s = "asdf"; r.UserName = s; r.UserID = 1; ds.Users.Rows.Add(r); ds.AcceptChanges(); DataTable dt = ds.Users.GetChanges(); if(dt != null) Console.WriteLine("# of changed rows " + dt.Rows.Count); ds.Users[0].UserName = s; // change the value to exactly what it was. dt = ds.Users.GetChanges(); if(dt != null) Console.WriteLine("# of changed rows " + dt.Rows.Count); Any help is appreciated, EntDevGuy Tag: ADO Connection question Tag: 100388
  - 20
    - SchemaTable I'm using the following code to obtain information about any table in a database. Now _schemaTable contains the info about the table and one of its fields is "IsKey" which as far as I know should identify the columns that are the primary keys in the table. Unfortunately "IsKey" is always false for me even though some columns are the primary keys. Why is that? Is there something I'm doing wrong or something that I'm not doing? Some of the other information in the _schemaTable such as "ColumnName" is correct for each of the columns in the table. The following is the code sample: Thanks for help in advance. Tom. System.Data.IDataReader reader; this._Command.CommandText = "SELECT * FROM [" + tableName + "]"; reader = _Command.ExecuteReader(System.Data.CommandBehavior.SingleRow); _schemaTable = reader.GetSchemaTable(); Tag: ADO Connection question Tag: 100385
  - 21
    - Row-wise vs columnwise If I need to iterate through all the field values in a dataset, is there any preference for organising the outer iterations by row or by column . ie should I do (in VB.Net): For i as integer = 1 to table(0).columns.count For j as integer = 1 to table(0).rows.count ......... Next j Next i or swap columns or rows, or is it immaterial? TIA JGD Tag: ADO Connection question Tag: 100383
  - 22
    - SqlException - Help pls Using SQLServer database. UserId defined as numeric and First/LastName fields defined as nvarchar(50). Here is my SQL Statement: SELECT UserId, LastName & ', ' & FirstName AS FullName FROM Users ORDER BY LastName, FirstName; What does this error mean? Server Error in '/Test' Application. ________________________________________ Invalid operator for data type. Operator equals boolean AND, type equals nvarchar. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid operator for data type. Operator equals boolean AND, type equals nvarchar. Source Error: Line 136: End If Line 137: Line 138: Dim dr As SqlDataReader = MySqlHelper.ExecuteReader(CONNECTION_STRING, CommandType.Text, strSql) Line 139: Line 140: Return dr Source File: c:\inetpub\wwwroot\Test\Components\dbUsers.vb Line: 138 Stack Trace: [SqlException: Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText) Test.dbUsers.GetAllUsers(Int32 UserId) in c:\inetpub\wwwroot\Test\Components\dbUsers.vb:138 Test.ManageUsers.FillUsersDdl() in c:\inetpub\wwwroot\Test\Secured\ManageUsers.aspx.vb:139 Test.ManageUsers.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Test\Secured\ManageUsers.aspx.vb:59 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731 ________________________________________ Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 Tag: ADO Connection question Tag: 100382
  - 23
    - How to effiently load many, many SQL records I have a need to effiently load millions of fairly small records into SQL Sever 2000. I have some financial data with many, many observations (one set has 37 million records) and I need to get it into a SQL server. I create a comma separated variable (CSV) file using a VB.NET program acting on the raw data. I have tried importing it into SQL Server using DTS import, but it says that it completes after about 18 million records, with no error. Using Visual Studio 2004 VB, I wrote a windows program that uses a SQL dataadapter to the table I want to insert records in, set the field parameters to the generated SQL insert command and then call ExecuteNoQuery to insert each record. This process works, but is seems to take forever to create a lot of records this way. I am fairly well versed in VS 2003 and VB.NET, but I am new to using these tools to load so many records. I must be doing something wrong or not using the appropriate technique to do this. Any help will be appreciated. Thanks! Bill Patterson UW-Madison School of Business Tag: ADO Connection question Tag: 100378
  - 24
    - Navigating child rows of a relationship Hi there, Here's my problem : I have 2 tables with a one-to-many relationship. I setup the relationship via the dataset component. ParentTable : Parentkey col1 col2 ChildTable : Childkey ParentKey col1 col2 I Have setup the databindings like this : textBox1.DataBindings.Add("Text",ds,"ParentTable.Parentkey"); textBox2.DataBindings.Add("Text",ds,"ParentTable.col1"); textBox3.DataBindings.Add("Text",ds,"ParentTable.col2"); textBox4.DataBindings.Add("Text",ds,"ParentTable.MyRelation.Childkey"); textBox5.DataBindings.Add("Text",ds,"ParentTable.MyRelation.Parentkey"); textBox6.DataBindings.Add("Text",ds,"ParentTable.MyRelation.col1"); textBox7.DataBindings.Add("Text",ds,"ParentTable.MyRelation.col2"); I setup a currency manager to track position and navigate in the dataset like this : myCurrency = (CurrencyManager)this.BindingContext[ds,"ParentTable"]; myCurrency.Position = 0; The events to navigate through the ParentTable rows are as follows : private void navPrevious_Click(object sender, System.EventArgs e) { if(myCurrency.Position != 0) { myCurrency.Position -= 1; } } private void navNext_Click(object sender, System.EventArgs e) { myCurrency.Position += 1; } My question is : since the ParentTable can have one or many chlidren, how can i navigate (Navigate next, previous,etc ) through these rows ? Do i have to setup another currency manager ? I tried this : myCurrency2 = (CurrencyManager)this.BindingContext[ds,"TableParent.MyRelation"]; myCurrency2.Position = 0; This doesn't seem to work if i setup the events like this : private void navPrevious_Click(object sender, System.EventArgs e) { if(myCurrency2.Position != 0) { myCurrency2.Position -= 1; } } private void navNext_Click(object sender, System.EventArgs e) { myCurrency2.Position += 1; } What am i doing wrong ? Is there a solution ? Thanks a lot for the help. Tag: ADO Connection question Tag: 100375
  - 25
    - Connection Pool Hello, Is it ado provider by default do the connection pooling? or it is depending on the database. I know sqlclient provider does the connection pooling but can I use connection pooling with MYSQL? thanks ap Tag: ADO Connection question Tag: 100371

I am having trouble opening an Access DB using ADO from VB. I am part of a
workgroup, the error message is:

Cannot start your application. The workgroup information file is missing or
opened exclusively by another user.

I can open the DB from Access. I have provided my user ID as part of the
login, my workgroup password is blank.

Thanks in advance...

Top

Re: ADO Connection question by Paul

Paul
Tue Dec 07 09:02:48 CST 2004

On Mon, 6 Dec 2004 14:13:04 -0800, "DMurray" <DMurray@discussions.microsoft.com> wrote:

¤ I am having trouble opening an Access DB using ADO from VB. I am part of a
¤ workgroup, the error message is:
¤
¤ Cannot start your application. The workgroup information file is missing or
¤ opened exclusively by another user.
¤
¤ I can open the DB from Access. I have provided my user ID as part of the
¤ login, my workgroup password is blank.

Could you post your connection string?

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

Top