ADO Connection after DB backups

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
    - vs2003 with vs2005 Hi Is it possible to install and use vs2003/net1.1 and vs2005/net2 side by side on the same drive? Only from experience please. Thanks Regards Tag: ADO Connection after DB backups Tag: 118206
  - 2
    - How do I bind an MSAccess CrossTab to a DataGrid I have an MSAccess database that I have used to create a cross tab querry. When I try and bind it to a datagrid unsing an OleDbDataAdapter or OleDbDataReader the data grid only displays two of the many column headers and none of the data. I am not haveing trouble binding to other queries in the same database. I did note that in the Visual Studio .Net Server explorer if I make a connection to the database it lists the Crosstab querry as a stored procedure not a view. I have also tried using an OleDbCommand of CommandType StoredProcedure but still no luck. Any help would be appreciated. Tag: ADO Connection after DB backups Tag: 118189
  - 3
    - Textbox value in the edittemplate of a datalist doesn't report the changes Good Morning, I've a problem with a textfiled inside a datalist, in particular it's value it is not updated with the changes, I mean sNomeCampo has still the value coming from the dB and not that changed by the user, May you help me ??? Antonio D'Ottavio www.etantonio.it/en Here's the code : void Update_Command(Object sender, DataListCommandEventArgs e) { String sNomeCampo = ((TextBox)e.Item.FindControl("NomeCampo")).Text; } <ASP:DataList id="MyDataCampi" runat="server" HorizontalAlign="Center" RepeatDirection="Horizontal" RepeatColumns="1" OnEditCommand="Edit_Command" OnUpdateCommand="Update_Command" OnDeleteCommand="Delete_Command" OnCancelCommand="Cancel_Command" OnItemDataBound="BindComboes" > <ItemTemplate> <tr style="background-color:CCFF99"> <td width=10% align="center"> <asp:LinkButton id="EditButton" Text="Edit" CommandName="Edit" runat="server"/> <input id="HiddenIDCampo" type="hidden" value='<%# DataBinder.Eval(Container.DataItem, "IDCampo") %>' runat="server" /> </td> <td> <%# DataBinder.Eval(Container.DataItem, "NomeCampo")%> </td> </tr> </ItemTemplate> <EditItemTemplate> <asp:LinkButton id="UpdateButton" Text="Update" CommandName="Update" runat="server"/> <td> <asp:TextBox id="NomeCampo" Text='<%# DataBinder.Eval(Container.DataItem, "NomeCampo") %>' runat="server"/> </td> </EditItemTemplate> </ASP:DataList> Tag: ADO Connection after DB backups Tag: 118181
  - 4
    - OracleClient Namespace+BLOB can some one help me out here. I am trying to retrieve a ppicture from a DB using OracleClient Namespace. But cant seem to get it right. Please help me out here. -- Sent via .NET Newsgroups http://www.dotnetnewsgroups.com Tag: ADO Connection after DB backups Tag: 118180
  - 5
    - Binding multiple datasets to one datagrid Hello, I have an asp datagrid where i'd like to display four colums, each one showing datas coming from a different dataset. Each dataset has a common PK, so i'd like to make a join on this PK, and fill a result dataset to bind it to my asp datagrid. I can't find any samples showing this kind of technique. Any ideas ? Thanks in advance, J.Philippe Tag: ADO Connection after DB backups Tag: 118178
  - 6
    - Enterpriseservices namespace and Timeout Hi all I am using System.enterpriseservices in a class library. I am calling a stored procedure within this class procedure. This procedure is taking a long time to execute and after some time I get the follwoing error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." It is working fine with less records. If I change the timeout from MTS option tab then it is working fine . I know the solution here is to increase the timeout value. But I have no access to the productin server where the web application woudl be finally deployed. I need to know how to increase the MTS Transaction through code for a single class and to again reset it back. Thanks pradeep_TP Tag: ADO Connection after DB backups Tag: 118171
  - 7
    - One SqlDataAdapter, One DataSet with multiple tables. possible? Hi, I have a data adapter and trying to connect it to multiple tables. I am not sure if it is possible and if so, I can't find what I am doing wrong. I am using HTML controls because the original page was created with another software, but I changed all the controls to "Run as Server Control". The loading works fine, but the updates don't. How can I attach two SQL statements to one data adapter? Or do I have to use two data adapters? Option Explicit On Option Strict On Imports System.Data.SqlClient Public Class _62031 Inherits System.Web.UI.Page ' Variables to hold local copies of the global data objects Dim lv_PageDataAdapter As SqlDataAdapter Dim lv_PageConnection As SqlConnection Dim lv_PageDataSet As DataSet Dim lv_TableName1 As String = "INCLUS" Dim lv_TableName2 As String = "EXCLUS" Dim lv_id1 As Integer Dim lv_id2 As Integer Dim Radio(1) As HtmlControls.HtmlInputRadioButton ' A holder for radio buttons used to get indexes Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ' Make local copies of the data objects to make it easier to manipulate lv_id1 = CType(Session.Contents("gv_id1"), Integer) lv_id2 = CType(Session.Contents("gv_id2"), Integer) lv_PageConnection = CType(Session.Contents("gv_Connection"), SqlConnection) lv_PageDataAdapter = CType(Session.Contents("gv_DataAdapter"), SqlDataAdapter) lv_PageDataSet = CType(Session.Contents("gv_DataSet"), DataSet) If Not Page.IsPostBack Then ' Make sure we have an open connection If lv_PageConnection.State <> ConnectionState.Open Then lv_PageConnection.Open() lv_PageDataAdapter.SelectCommand = _ New SqlCommand("SELECT id1, id2, i1 FROM " & _ lv_TableName1 & " WHERE id1=" & lv_id1 & " AND id2=" & lv_id2, _ lv_PageConnection) lv_PageDataAdapter.Fill(lv_PageDataSet.Tables.Add(lv_TableName1)) ID1.Value = lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("id1").ToString ID2.Value = lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("id2").ToString i1.Value = lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("i1").ToString) ' Exclusions lv_TableName2 = "EXCLUS" lv_PageDataAdapter.SelectCommand = _ New SqlCommand("SELECT id1, id2, e1 FROM " & _ lv_TableName2 & " WHERE id1=" & lv_id1 & " AND id2=" & lv_id2, _ lv_PageConnection) lv_PageDataAdapter.Fill(lv_PageDataSet.Tables.Add(lv_TableName2)) e1.Value = lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("e1").ToString) End If End Sub Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("i1") = i1 lv_PageDataSet.Tables(lv_TableName1).Rows(0).Item("e1") = e1 Try sbUpdateData(lv_PageDataSet) lv_PageDataSet.AcceptChanges() Catch ex As SqlException End Try End Sub Private Sub sbUpdateData(ByVal pardv As DataSet) Dim lv_CommandBuilder As New SqlCommandBuilder(lv_PageDataAdapter) If lv_PageConnection.State <> ConnectionState.Open Then lv_PageConnection.Open() lv_PageDataAdapter.UpdateCommand = lv_CommandBuilder.GetUpdateCommand lv_PageDataAdapter.Update(pardv.Tables(lv_TableName1)) End Sub End Class Tag: ADO Connection after DB backups Tag: 118162
  - 8
    - Concurrency Problem I am running a VB.NET app on my desktop with Access 2003 as the DB. The table I am working with has a PK made up of 2 columns. I am currently the only user. I built my own UpdateCommand for the data adapter. The command uses values for the PK columns in the WHERE clause ad then uses "Set" for some (but not all) other columns. Whenever I try to do an update I get a concurrency violation? How can that be? Since this application is strictly a single user application, is there a way to simply turn off all concurrency checking? Wayne Tag: ADO Connection after DB backups Tag: 118161
  - 9
    - Run-time error '430' Visual 6.0 Hola mundo tengo la siguinnte conexion a una base de datos Acces 97, funciona bien en windows 98 y 2000 pero al instalar la aplicacion en WindwsXP SP2 sale el sgiuiente erorr Run-time error '430': Class does not suppot Automation. Estoy usando mdac2.7 Public db As New ADODB.Connection Public strcnn As String strcnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & App.Path & "\Basedatos\xxx.mdb" db.ConnectionString = strcnn db.Open db.CursorLocation = adUseClient Tag: ADO Connection after DB backups Tag: 118157
  - 10
    - No value given for one or more required parameters on relation update Here's a variation on the old chestnut, but I can't get a handle on it. I have a database that has a many to many relation. Table A has two columns AID and AName, table B has two columns BID and BName and table AB has three columns ABID, AID and BID for relating tables A and B. (I am using Access, but don't use the autogenerated ID, although I let it operate.) I create a relation which contains ABID, AID, BID and BName. If I edit BName in this relation I get the above error when I try to update the databases, but I am only updating AB with the IDs, not the name info and yet I am getting the error: System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at ConsoleApplication1.Program.Main(String[] args) in C:\Documents and Settings\Gordon\My Documents\Visual Studio 2005\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 111 0 A and 0 B and 0 AB records updated - from my line of code Line 111 contains the: i = daAB.Update(ds, "AB"); line I have noted by cycling through the datarows of the AB table in the dataset that the one that is changed is getting its status changed to modified, which is fair enough. I suspect that since the dataadapter.update is seeing that change then it wants to update the AB table in the access table, even though it doesn't need to. Any pointers would be gratefully appreciated, as well as any thoughts on the update code, as I found this pretty complex to get my head around - possibly where there are errors? I have stripped my code as much as possible, but this is what generates the error: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Text; using System.Windows.Forms; namespace WindowsApplication2 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { string myDB = "AorB.mdb"; OleDbConnection conn = null; DataSet ds; OleDbDataAdapter daA; OleDbDataAdapter daB; OleDbDataAdapter daAB; OleDbDataAdapter daBA; DataRelation drAB; DataRelation drBA; //create a connection string to the access database conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=;Password=; Data Source=" + myDB); //clear the dataset ds = null; ds = new DataSet(); //set up the link daA = new OleDbDataAdapter("SELECT * FROM A ORDER BY AName", conn); daB = new OleDbDataAdapter("SELECT * FROM B ORDER BY BName", conn); //fill the dataset with data daA.Fill(ds, "A"); daB.Fill(ds, "B"); //Create Data Relation from A to B, daAB = new OleDbDataAdapter("SELECT AB.ABID, AB.AID, AB.BID, B.BName FROM B INNER JOIN AB ON B.BID = AB.BID ORDER BY B.BName", conn); daAB.Fill(ds, "AB"); drAB = new DataRelation("AToB", ds.Tables["A"].Columns["AID"], ds.Tables["AB"].Columns["AID"], true); ds.Relations.Add(drAB); //daBA = new OleDbDataAdapter("SELECT AB.ABID, AB.BID, AB.AID, A.AName FROM A INNER JOIN AB ON A.AID = AB.AID ORDER BY A.AName", conn); //daBA.Fill(ds, "BA"); //drBA = new DataRelation("BToA", // ds.Tables["B"].Columns["BID"], // ds.Tables["BA"].Columns["BID"], true); //ds.Relations.Add(drBA); ds.EnforceConstraints = true; //Change the BName in the first row of the AB table DataRow[] dr = ds.Tables["AB"].Select(); DataRow r = dr[0]; r["BName"] = "Help"; //update database string updateSQLA = "Update A set [AID]=?, [AName]=? where [AID]=?"; // //the @ is to make sure that it is treated as is, no confusion with C# keywords daA.UpdateCommand = new OleDbCommand(updateSQLA, conn); daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID"); daA.UpdateCommand.Parameters.Add("@AName", OleDbType.VarWChar, 50, "AName"); OleDbParameter AID1 = daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID"); AID1.SourceVersion = DataRowVersion.Original; string updateSQLB = "Update B set [BID]=?, [BName]=? where [BID]=?"; // daB.UpdateCommand = new OleDbCommand(updateSQLB, conn); daB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID"); daB.UpdateCommand.Parameters.Add("@BName", OleDbType.VarWChar, 50, "BName"); OleDbParameter BID1 = daB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID"); BID1.SourceVersion = DataRowVersion.Original; //now need to update AB table in real database string updateSQLAB = "Update AB set [ABID]=?, [AID]=?, [BID]=? where [AID]=? and [BID]=?"; // daAB.UpdateCommand = new OleDbCommand(updateSQLAB, conn); daAB.UpdateCommand.Parameters.Add("@ABID", OleDbType.Integer, 10, "ABID"); daAB.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID"); daAB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID"); //OleDbParameter ABID1 = daA.UpdateCommand.Parameters.Add("@ABID", OleDbType.Integer, 10, "ABID"); //ABID1.SourceVersion = DataRowVersion.Original; OleDbParameter ABID2 = daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID"); ABID2.SourceVersion = DataRowVersion.Original; OleDbParameter ABID3 = daA.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID"); ABID3.SourceVersion = DataRowVersion.Original; int g = 0, h = 0, i = 0; try //AB first (important) { i = daAB.Update(ds, "AB"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { g = daA.Update(ds, "A"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } try { h = daB.Update(ds, "B"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //for testing MessageBox.Show(g.ToString() + " A and " + h.ToString() + " B and " + i.ToString() + " AB records updated"); } } } Tag: ADO Connection after DB backups Tag: 118154
  - 11
    - How to read and execute script file I create a script file (.sql) with DTS. How can I read it into Asp.net and execute it? Tag: ADO Connection after DB backups Tag: 118149
  - 12
    - Displaying datagrid on second form I have a form which has a listbox, 5 textboxes and a datagrid, what I'm trying to do is when a user double clicks a row on the datagrid this then displays another datagrid on the second form with the information from the Items Table I have already set up a data relation to link the Customers and Orders Table on the first form through the Customer ID and have tried to link the Orders Table and Items Table through the Order ID to display the info in the second datagrid but doesn't work, getting an error on the relation declaration line My coding is below any advice would be appreciated on this Thanks Imports System.Data.OleDb Imports System.IO Public Class Form1 Inherits System.Windows.Forms.Form Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System. EventArgs) Handles MyBase.Load 'Declares and instantiates a command object Dim SqlCustomers As String = "SELECT * FROM customers" 'Sets the connection string Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\ The Doughnut Shop.mdb" 'Declares and instantiates a new OleDbconnection object Dim myconnection As OleDbConnection = New OleDbConnection(connString) 'Sets the connection string property myconnection.ConnectionString = connString 'Declares and instantiates a data adapter Dim dacustomers As OleDbDataAdapter = New OleDbDataAdapter(SqlCustomers, myconnection) 'Declares and instantiates a new data set Dim ds As DataSet = New DataSet 'Fills the data adapter with the customers table dacustomers.Fill(ds, "customers") 'Declares and instantiates a command object Dim sqlorders As String = "SELECT * FROM Orders " 'Declares and instantiates the datadapter Dim daorders As OleDbDataAdapter = New OleDbDataAdapter(sqlorders, myconnection) 'Fills the data adapter with the orders table daorders.Fill(ds, "orders") 'Declares and instantiates a new data relation linking together the customers and the orders table Dim relation As DataRelation = New DataRelation("CustomersOrders", ds.Tables ("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID") ) 'Adds the relation to the collection ds.Relations.Add(relation) ListBox1.DisplayMember = "CustomerID" ListBox1.DataSource = New DataView(ds.Tables("customers")) 'Binds the customers table details to the text boxes TextBox1.Text = ds.Tables("customers").Rows(0).Item("FirstName") TextBox2.Text = ds.Tables("customers").Rows(0).Item("LastName") TextBox3.Text = ds.Tables("customers").Rows(0).Item("City") TextBox4.Text = ds.Tables("customers").Rows(0).Item("State") TextBox5.Text = ds.Tables("customers").Rows(0).Item("ZipCode") End Sub Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged 'Declares a data row view Dim drv As DataRowView = CType(ListBox1.SelectedItem, DataRowView) 'Declares a data row Dim childRows() As DataRow = drv.Row.GetChildRows("CustomersOrders") TextBox1.Text = drv("FirstName") TextBox2.Text = drv("LastName") TextBox3.Text = drv("City") TextBox4.Text = drv("State") TextBox5.Text = drv("ZipCode") 'Sets the data grid data source property DataGrid1.DataSource = drv.CreateChildView("CustomersOrders") End Sub Private Sub DataGrid1_DoubleClick(ByVal sender As Object, ByVal e As System. EventArgs) Handles DataGrid1.DoubleClick 'Declares and instantiates a new form Dim myForm As New Form2 'Displays the second form myForm.ShowDialog() End Sub End Class Imports System.Data.OleDb Imports System.IO Public Class Form2 Inherits System.Windows.Forms.Form Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System. EventArgs) Handles Button1.Click End End Sub Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System. EventArgs) Handles MyBase.Load Dim sqlOrderItems As String = "SELECT * FROM Items" Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\ The Doughnut Shop.mdb" Dim myconnection As OleDbConnection = New OleDbConnection(connString) myconnection.ConnectionString = connString Dim daOrderItems As OleDbDataAdapter = New OleDbDataAdapter(sqlOrderItems, myconnection) Dim ds As DataSet = New DataSet daOrderItems.Fill(ds, "Items") Dim relation As DataRelation = New DataRelation("OrdersItems", ds.Tables ("Orders").Columns("OrderID"), ds.Tables("Items").Columns("OrderID")) ds.Relations.Add(relation) Dim drv As DataRowView Dim childRows() As DataRow = drv.Row.GetChildRows("OrdersItems") DataGrid1.DataSource = drv.CreateChildView("OrdersItems") End Sub End Class Tag: ADO Connection after DB backups Tag: 118147
  - 13
    - CommandBuilder Problem I am using ADOX to create an Access table and populate it with one row of data. The ADOX code sets two columns as primary keys (I can view the table in Access and see that they are set). When I try to create the UpdateCommand I get an error that it fails because the Select does not include a primay key field (the select is "Select * From tablea"). The relavent code is shown below. Any ideas on what I am doing wrong are appreciated. ======================================== Dim cmdBldr As New OleDb.OleDbCommandBuilder(da1) da1.InsertCommand = cmdBldr.GetInsertCommand da1.UpdateCommand = cmdBldr.GetUpdateCommand da1.DeleteCommand = cmdBldr.GetDeleteCommand ================================================ Wayne Tag: ADO Connection after DB backups Tag: 118146
  - 14
    - Call stored procedure in ASP.NET 2.0 I would like to know if anyone knows how to execute a stored procedure from ASP.NET 2.0. I'm using the NorthWind database and I'm trying to execute the "CustOrderHist" stored procedure. The error I get is "Incorrect syntax near 'CustOrderHist'. " Public Function GetCustomerOrderHistory(ByVal customerid As String) As SqlDataReader Dim conn As New SqlConnection(conString) Dim cmd As New SqlCommand("CustOrderHist", conn) cmd.Parameters.AddWithValue("@CustomerID", customerid) conn.Open() Dim dtr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Return dtr End Function Tag: ADO Connection after DB backups Tag: 118138
  - 15
    - Confused About Connections What is the difference between an OleDBConnection and an ADODB.Connection? I am working in a VB Express 2005 application and I want to have a global connection object throughout but when creating a table in an Acess DB using ADOX the OleDBConnection won't work and I have to create an ADODB.Connection. I am confused (as usual!) Wayne Tag: ADO Connection after DB backups Tag: 118128
  - 16
    - Passing output parameters to SQL Server stored procedures through an SqlCommand object's Parameters collection Hi, I'm currently working writing a Windows service in C# which needs, among other things, to interface with Microsoft Great Plains 8.0, specifically to add General Ledger entries. In order to add a new General Ledger entry to a Great Plains database, it's necessary to supply a journal entry number - Microsoft provide a stored procedure which calculates the most recent journal entry number used, increments this value by one, and returns this to the caller in the form of an output parameter. The top of the procedure is as follows: CREATE PROCEDURE dbo.glGetNextJournalEntry @l_tINCheckWORKFiles tinyint = NULL, @I_iSQLSessionID int = NULL, @IO_iOUTJournalEntry int = NULL output, @O_tOUTOK tinyint = NULL output, @O_iErrorState int = NULL output AS IF((@l_tINCheckWORKFiles IS NULL) OR (@IO_iOUTJournalEntry IS NULL) OR (@I_iSQLSessionID IS NULL)) BEGIN SELECT @O_iErrorState = 20159, @IO_iOUTJournalEntry = 0, @O_tOUTOK = 0 RETURN END <snipped rest of proc> @IO_iOUTJournalEntry returns the next journal entry number @O_tOUTOK returns 0 or 1 depending on whether the procedure succeeds or fails @O_iErrorState contains 0 if the procedure succeeds, or the error state if it fails I'm calling the procedure as follows: SqlConnection mobjSqlConn; SqlCommand mobjSqlCommand; SqlParameter mobjSqlParameter; ArrayList maobjSqlParameters = new ArrayList(); SqlDataReader mobjDR = null; maobjSqlParameters.Clear(); mobjSqlParameter = new SqlParameter("@l_tINCheckWORKFiles", SqlDbType.TinyInt); mobjSqlParameter.IsNullable = true; mobjSqlParameter.Value = 0; maobjSqlParameters.Add(mobjSqlParameter); mobjSqlParameter = new SqlParameter("@I_iSQLSessionID", SqlDbType.Int); mobjSqlParameter.IsNullable = true; mobjSqlParameter.Value = 0; maobjSqlParameters.Add(mobjSqlParameter); mobjSqlParameter = new SqlParameter("@IO_iOUTJournalEntry", SqlDbType.Int); mobjSqlParameter.IsNullable = true; mobjSqlParameter.Direction = ParameterDirection.Output; mobjSqlParameter.Value = 0; maobjSqlParameters.Add(mobjSqlParameter); mobjSqlParameter = new SqlParameter("@O_tOUTOK", SqlDbType.TinyInt); mobjSqlParameter.IsNullable = true; mobjSqlParameter.Direction = ParameterDirection.Output; mobjSqlParameter.Value = 0; maobjSqlParameters.Add(mobjSqlParameter); mobjSqlParameter = new SqlParameter("@O_iErrorState", SqlDbType.Int); mobjSqlParameter.IsNullable = true; mobjSqlParameter.Direction = ParameterDirection.Output; mobjSqlParameter.Value = 0; maobjSqlParameters.Add(mobjSqlParameter); mobjSqlConn = new SqlConnection(CGlobal.gstrSQLConnectionString); mobjSqlCommand = new SqlCommand("glGetNextJournalEntry", mobjSqlConn); mobjSqlCommand.CommandType = CommandType.StoredProcedure; foreach(SqlParameter objSqlParameter in maobjSqlParameters) { mobjSqlCommand.Parameters.Add(objSqlParameter); } mobjSqlCommand.Connection.Open(); mobjDR = mobjSqlCommand.ExecuteReader(CommandBehavior.CloseConnection); It all works as far as here, i.e. there are no errors and mobjDR contains an SqlDataReader, obviously with no records. However, the procedure does not return the next journal entry number. The values of the three output parameters are as follows: @IO_iOUTJournalEntry - 0 @O_tOUTOK - 0 @O_iErrorState - 20159 Clearly, the procedure is not getting past the first validation (see above). So I enabled SQL debugging in the probject and set a breakpoint at the top of the procedure - turns out that all three output parameters have lost their initial values by the time they get passed into the procedure i.e. they are all being passed in as NULL. Therefore, I tried to send a hardcoded SQL string to the procedure instead i.e. string strSQL = ""; strSQL += "DECLARE @IO_iOUTJournalEntry int, @O_tOUTOK tinyint, @O_iErrorState int "; strSQL += "SELECT @IO_iOUTJournalEntry = 0, @O_tOUTOK = 0, @O_iErrorState = 0 "; strSQL += "EXEC glGetNextJournalEntry 0, 0, @IO_iOUTJournalEntry out, @O_tOUTOK out, @O_iErrorState out "; strSQL += "SELECT @IO_iOUTJournalEntry"; mobjSqlConn = new SqlConnection(pstrSqlConnectionString); mobjSqlCommand = new SqlCommand(pstrSQL, mobjSqlConn); mobjSqlCommand.Connection.Open(); mobjDR = mobjSqlCommand.ExecuteReader(CommandBehavior.CloseConnection); mobjDR.Read(); intNextJournalEntry = mobjDR.GetInt32(0); mobjDR.Close(); This worked perfectly. Can anyone please tell me what I'm doing wrong? Is there something specific about passing output parameters to SQL Server stored procedures in the SqlCommand's Parameters collection which makes them lose their initial values? Any assistance gratefully received. Regards, Mark Rae Tag: ADO Connection after DB backups Tag: 118115
  - 17
    - Cursors vs Data Table Hi. I have an asp.net application where I have encrypted customer information (all columns encrypted) in a table, and need to pull each row, decrypt the email address and see if it matches a person logging in. I'm using sql server 2000. I could use sql cursors, and go through each row and do my processing, or I could fill a data table on the client and then do my processing. Which way is more efficient in terms of connection bandwidth and other practical matters??? Thanks, Phil Boston, MA Tag: ADO Connection after DB backups Tag: 118113
  - 18
    - Do a Find on a Dataset With Multiple Column Primary Key How do I code a Find method for a dataset that has a multiple column primary key. I tried the following but it won't accept the syntax of the Find. The prinmary key is a combination of the name and uclass columns. =========== code =========== Public Function FindUnitRow(ByVal name As String, ByVal uclass As String) FindUnitRow = ds.Tables(0).Rows.Find(name, uclass) If FindUnitRow Is Nothing Then MsgBox("Entry not found for: " & name & " - " & uclass, MsgBoxStyle.Critical, "Unit Record Not Found") End If End Function ======================================= Wayne Tag: ADO Connection after DB backups Tag: 118110
  - 19
    - Problem Creating Dataset I am using VB 2005 Express and trying to create a dataset. I did the Add New Item for the Dataset and then defined some columns. In the IDE I have a warning "Warning 1 The 'urn:schemas-microsoft-com:xml-msdatasource:DataSetUISetting' element is not declared. " which points to the DataSetUISetting phrase in the xsc file (see below) =========== xsc file contents ================= <?xml version="1.0" encoding="Windows-1252"?> <DataSetUISetting Version="1.00" xmlns="urn:schemas-microsoft-com:xml-msdatasource"> <TableUISettings> </TableUISettings> </DataSetUISetting> =================================== I suspect I have a missing reference but I cannot figure out what it wants. Any ideas? Wayne Tag: ADO Connection after DB backups Tag: 118103
  - 20
    - When to use ExecuteNonQuery Do I use ExecuteNonQuery only when I insert, delete, and update the database? Thanks Ross Tag: ADO Connection after DB backups Tag: 118098
  - 21
    - Morphing UPDATEs I was wrong. When the CommandBuilder is hooked to the DataAdapter, the UPDATE statements generated DO morph from row-to-row. This way only the changed columns are SET. This is a good feature, but given the other limitations of the CB, I still can't recommend its use. When you use hand-written UPDATE commands (set the DataAdapter UpdateCommand), the SQL is invariant. Except for input parameters, the SQL does not change from row to row. I apologize for any confusion. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com/blog/billva www.betav.com www.sqlreportingservices.net Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Tag: ADO Connection after DB backups Tag: 118095
  - 22
    - Stored Procedure Are stored procedures are precompiled in SQL Server. John. Tag: ADO Connection after DB backups Tag: 118091
  - 23
    - There is already an open DataReader associated with this Connection which must be closed first I'm starting to worry a bit now. We're getting the above error when two users hit the same database/page on an ASP.NET application using ADO.NET, talking to a SQL 7 server. The error is perfectly repeatable :-( But this should help! The error is occurring inside ExecuteReader which uses a DataReader internally. Here are some things that I'm pretty sure it's *NOT*: It's not because our data readers are not being closed - they are. If they weren't being closed, then the same error would occur in single user operation. It doesn't - it's only when two users (threads) are running at once. It's not connection pooling as we've turned that off using Pooling=False in the SqlConnection connection string. I've verified it really is off by watching connections open & close manually in SQL Enterprise manager. The two threads are not (AFAIK) sharing the same connection object. The SqlConnection object is not in a shared or static variable - it's stored as a private variable within one of our classes (called MSSQL) in a variable called m_Connection. This m_Connection variable is created each time we open a connection using m_Connection = New SqlConnection(ConnString), opened, used (ExecuteScalar) closed and then destroyed using m_Connection = nothing. The instance of MSSQL is stored in the session cache but I've verified that the two users/threads are indeed using their own instance of MSSQL and therefore their own instance of m_Connection. The two instances of m_Connection will have the same connection string (SQL login used, not Windows authentication). There are no exceptions/error conditions occuring elsewhere apart from this final error. I don't think it's the connection/data reader closing - I've triple checked all open/close pairs and the close is always within a Try...Catch with the connection closed in the Finally section. The error never occurs in our own use of data readers, only from within ExecuteScalar. Hmm, I guess I could write my own version of ExecuteScaler... So, pretty flummoxed at the moment. I willing to try any suggestions! Cheers, Rob. Tag: ADO Connection after DB backups Tag: 118089
  - 24
    - How do a get a sql serverSscale value from a numeric datatype Windows Forms Application Sql Server backend Controls are bound to a DataSet I want to restrict the amount of numbers to the right of the decimal a user can enter into a control that is bound to a sql server numeric, .net system.decimal. If the numeric's scale is 2 then the user can only enter 2 numbers to the right of the decimal. If the numeric's scale is 3 then the user can only enter 3 numbers to the right of the decimal, etc.... I want this to be dynamic, the control 'knows to do this'. How can I get the value of the scale through ADO.NET? Tag: ADO Connection after DB backups Tag: 118087
  - 25
    - Error "exporting data" in Server Explorer Greetings! In the VS.Net IDE, in the Server Explorer, you can right-click on a table and choose "Export Data" to send the table data to a .dat file which can be used in scripts to recreate the database. When I attempt to use this I get this error: "The bulk copy initialization failed. Possibly the connection was not BCP enabled." Does anyone know how to fix or get around this? Thanks, Mike Tag: ADO Connection after DB backups Tag: 118086
- Next
  - 1
    - copy row from one datatable to another I have read posts and tried many things and I am not getting how to do this. I have a datagrid bound to a dataset (the source). When a row is selected on the datagrid, I need to duplicate that row into another dataset (the destination). The destination dataset will be bound to a a repeater that is located below the datagrid. I cloned the source dataset to the destination and have datatables referenced for both datasets. I have primary keys defined for each datatable and the schemas are identical. The datagriditem only contains a portion of the columns from the source dataset. The repeater will display more of the columns from the destination dataset. I can get the primary key for the selected datagriditem, but that is as far as I can get. How do I do copy a selected row to the destination dataset? I am using asp.net 1.1 with code-behind of vb.net. If needed, I can post the code that I have so far. Thanks, Alex Tag: ADO Connection after DB backups Tag: 118078
  - 2
    - Strange problem. Need help! I have a really wired thing going on here and I can't seem to figure out what is wrong. I have a managed C++ application that is using ADO.NET on an Access database. Here is the steps that I am taking that are causing a problem: 1. I open a OleDBConnection to the database 2. I create an OLeDBDataAdapter to read in a table. 3. I read the table into a DataSet with the data adapter in step 2. 4. I modify several rows in the DataSet with some updated values (basically setting a Yes/No column to 1). 5. I update the database using the data adapter. 6. I close the connection. 7. At this point, I check the database with Access and confirm that the changes have been made. 8. Next, in another portion of code, I open a new connection to the same database. 9. I read several tables into a DataSet, including the table modified in step 4. I am using a where clause on the command object to only get the rows that have the column (used in step 4) set to 1. 10. At this point, I check the dataset contents, and all but 1 of the rows is read back, even though the missing row should have been included with the query. 11. Now comes the weird part, If I clear the dataset, and read it again using the same command string, then all of my data is read the way I expect. I hope someone can decipher all of this and make some sense out of it and tell me what is wrong. I am getting really frustrated here because I know that this is a simple operation. I am fairly familiar with ADO.NET and this seems like it should just work and it is messing with my mind. Is there some bug in the Jet 4 DB engine or in .NET 1.1 that would be causing this? -- ----------------------------------- Ken Varn Senior Software Engineer Diebold Inc. EmailID = varnk Domain = Diebold.com ----------------------------------- Tag: ADO Connection after DB backups Tag: 118073
  - 3
    - Representing Recursive Relatiobships Say I have master table of inventory parts (PartID, PartNr, PartName, ...) I also have a detail table for bill of materials (BOMID, ParentPartID, BOMPartID, ...) The two tables are in a one to many relatiobship linked by PartID<>ParentPartID. BOMPartID in the detail table also references a record in the master table. This relationship can go several levels - i.e. each part in a bill of materials record can in turn have parts in it's bill of material etc. For example, Part A can have part B and part C in it's BOM, Part B may also have parts D and E in it's BOM, part E might have part F in it's BOM. I would love to be able to display the detail table in a grid in a way that the user can see in a tree-like fashion all the bill of materials needed for a particular part. Is there an elegant way to create these recursive relationships in ADO.NET? --Bruce Tag: ADO Connection after DB backups Tag: 118071
  - 4
    - Dataset queries I need to return values from multiple tables in a dataset at the same time. Is there a way to create inner join query to a dataset that has several tables? BR Johanna Turku Tag: ADO Connection after DB backups Tag: 118065
  - 5
    - Schemas in ADO.Net 2.0 Hello: Im trying to build a generic library with tools that can be connected to any database. I want to list the tables on the database. Now I'm using the code: DataTable resultTables = cs.GetSchema("Tables"). But it returns different tables for the different databases For example, in SqlServer (TABLE_NAME, TABLE_TYPE, TABLE_CATALOG, TABLE_SCHEMA), but in Oracle (TABLE_NAME, TYPE, OWNER). I remember that in oledeb, schemas were uniform (take a look to OleDbConnection.GetOleDbSchemaTable () methd. But it seems that in ADO.Net 2.0 they are not defined and any provider can return anything. Is there in ADO.Net 2.0 any way to ask for some kind of more uniform schemas? The class System.Data.Common.SchemaTableColumn, What is it for? Thanks. Tag: ADO Connection after DB backups Tag: 118063
  - 6
    - Dear Mr Gates, A suggestion to make the CLR Ubiquitous Check out the article titled : Dear Mr Gates, A suggestion to make the CLR Ubiquitous at http://dreams2text.blogspot.com/ Tag: ADO Connection after DB backups Tag: 118060
  - 7
    - Odd characters (swedish) in a Gridview hyperlinkfield Hello! I am using Visual Studio 2005 and tries to get a functional search engine for my file server. I have a web service that returns (to a web site) a table with the paths of the files found. In my web site I am presenting these in a gridview as hyperlinkfields so that the user could click these links and open the files. Everything works just fine for normal shortcuts, including files with spaces and some characters as comma and dash, but in Sweden (and Denmark) we have a few other characters that gets the links not to work. Some of them are å ä and ö. If I present it as a bound text field everything seems good and I see for instace å but when watching the link by mouse hover I see that the å becomes %c3%a5. The space works fine and becomes %20. What could be wrong, and what should I do? Should I do something with the table or with the hyperlink field? Regards Magnus Tag: ADO Connection after DB backups Tag: 118054
  - 8
    - Windows service and oledb Created a vb.net windows service which connect to a database by using OleDb. Using a connectionstring pointing to a local sqlserver database everything works fine, but when connecting to a database on another server the connection.open method executes without errors but the connection stays closed. When using same code in a winform or webform app things goes as expected. Is there some kind of restriction to windows services to use resources from another server? Roel Tag: ADO Connection after DB backups Tag: 118053
  - 9
    - I can't use DataTable, type expected Hi, I can't use DataTable! I try to write ( in form load): Dim myDataTable As DataTable = New DataTable("ParentTable") I then get "Type expected" for the DataTable (in Task List) I have included "Imports System.Data" (Tried Imports System.DataTable, and other Imports) I have Visual Basic .net 2002 (ver 7.0 I think). Is it to old, is there something else I have to do? Is there an other way to show the user a table with content from a xml file!? Best Regard -bodil- Tag: ADO Connection after DB backups Tag: 118048
  - 10
    - How can we detach /attach database in program? Can we detach /attach database in program? Tag: ADO Connection after DB backups Tag: 118042
  - 11
    - DataSet.GetChanges() -> StackOverflow in ShouldWriteRowAsUpdate() The console application below demonstrates the problem. Is there a fix? using System; using System.Data; namespace ConAppOverflow { class Class1 { [STAThread] static void Main(string[] args) { // Create a dataset. DataSet ds = new DataSet("ds"); DataTable dt; DataRelation dr; // Create two tables with [very] similar schema // ID is the key // CAT is an internal foreign key // Ref is an external foreign key // Text is text dt = new DataTable("DT1"); dt.Columns.Add("ID", System.Type.GetType("System.Int32")); dt.Columns.Add("CAT", System.Type.GetType("System.Int32")); dt.Columns.Add("REF", System.Type.GetType("System.Int32")); dt.Columns.Add("Text", System.Type.GetType("System.String")); ds.Tables.Add(dt); //Add relation for internal foreign key dr = new DataRelation("DT1CAT2ID", dt.Columns["ID"], dt.Columns["CAT"]); ds.Relations.Add(dr); // Add some data dt.Rows.Add(new object[] {1,1,10, ""}); dt.Rows.Add(new object[] {2,1,20, ""}); dt.Rows.Add(new object[] {3,1,30, ""}); dt = new DataTable("DT2"); dt.Columns.Add("ID", System.Type.GetType("System.Int32")); dt.Columns.Add("CAT", System.Type.GetType("System.Int32")); dt.Columns.Add("REF", System.Type.GetType("System.Int32")); dt.Columns.Add("Text", System.Type.GetType("System.String")); ds.Tables.Add(dt); //Add relation for internal foreign key dr = new DataRelation("DT2CAT2ID", dt.Columns["ID"], dt.Columns["CAT"]); ds.Relations.Add(dr); // Add some data dt.Rows.Add(new object[] {10,10,1, ""}); dt.Rows.Add(new object[] {20,10,2, ""}); dt.Rows.Add(new object[] {30,10,3, ""}); // And accept it ds.Tables["DT1"].AcceptChanges(); ds.Tables["DT2"].AcceptChanges(); // now, edit a row and watch the stack overflow... DataRow drow = ds.Tables[0].Rows[0]; drow.BeginEdit(); drow["Text"] = "*" + drow["Text"]; drow.EndEdit(); if (ds.HasChanges((DataRowState.Modified))) { DataSet xds = ds.GetChanges(DataRowState.Modified); ds.AcceptChanges(); } } } } Tag: ADO Connection after DB backups Tag: 118040
  - 12
    - Binding Data I have a windows form with 5 fields. They are bound as follows: drvDetail = (DataRowView) cm.Current; vueDetail = drvDetail.DataView; this.BindingContext[vueDetail].Position = cm.Position; txtSON.DataBindings.Add("Text", vueDetail, "service-order-number"); txtTaskID.DataBindings.Add("Text", vueDetail, "taskid"); txtServiceDesc.DataBindings.Add("Text", vueDetail, "task-desc"); txtStartDate.DataBindings.Add("Text", vueDetail, "start-date"); txtStartTime.DataBindings.Add("Text", vueDetail, "start-time"); txtCompDate.DataBindings.Add("Text", vueDetail, "complete-date"); txtCompTime.DataBindings.Add("Text", vueDetail, "complete-time"); txtServiceCost.DataBindings.Add("Text", vueDetail, "task-cost"); The problem is I fill in the field data as follows: LoadData(); cbServices.DataSource = dvServices; cbServices.DisplayMember = "service-description"; cbServices.ValueMember = "service-nbr"; The data appears in the text box ok, but when I save it the fields are null. If I key in the data its works just fine, what am I doing wrong?? Please Help!!! -- Norm Bohana Tag: ADO Connection after DB backups Tag: 118035
  - 13
    - Using DAAB Hi, I am trying to use MS's DAAB in my project. I referenced Enterprise.Common, Enterprise.DataAccess, Enterprise.DataAccess.SqlHelper, Enterprise.ExceptionManagement in my project then in my class's I would put a using statement for each one. But when I go to create a certain object from lets say DataAccess I get no intellisense on the object that I am trying to create. Am I missing something that needs to be available or referencing? I have the dll's in a certain folder outside of my working directory, that shouldn't be a problem should it? JJ Tag: ADO Connection after DB backups Tag: 118031
  - 14
    - Data Adapter update I have a connection to an iSeries table that I pull a customer number, name, and status into a dataset. I have two data grids on the form one has the dataset from the iSeries as its source and the other has the dataset from the Access database table as it's source. The Access table only has the fields that are in the iSeries dataset. The connections, data adapter, and datasets were all created through the designer. I have tried to merge the two datasets and then call the update of the access data adapter and I have tried just using the fill method on both datasets from the iSeries data adapter. The data grids show identical information correctly but the table will not update. The table name is CUMA8 on both the iSeries and the Access database. The filed names are identical. The code is simple: cnCustomer.Open() daCustomer.Fill(dsUrmCustomer) daCustomer.Fill(dsAccessTagType) cnAccessTagType.Open() daAccessTagType.Update(dsAccessTagType) cnCustomer.Close() cnAccessTagType.Close() I know this should be easy but I can't seem to make it work. Tag: ADO Connection after DB backups Tag: 118025
  - 15
    - execute a dts package from vb.net Is it possible with vb.net & ado.net to execute a local dts package stored on a sql 2k server? Can anyone point me to an article / howto that shows an example of this? Thanks in Advance. Tag: ADO Connection after DB backups Tag: 118006
  - 16
    - How to insert data from strongly typed dataset into database (jbx01) Hi, I have a strongly typed DataSet with 10+ tables, filled with data from a XMLDocument, and SQLServer database matching the dataset schema. How do I insert that data into the database with minimum coding effort using ADO.NET and the .NET framework? Thanks (jbx01) Tag: ADO Connection after DB backups Tag: 118005
  - 17
    - DafaultView traversing, C# Hi. I have a DataSet with two tables, and I setup a view (using defaultView) on one of the tables within the DataSet. All with strong typing. Here the 3 lines: ppDS.HPerson.DefaultView.RowFilter = "fullname like 'test%'"; int row_count = ppDS.HPerson.DefaultView.Count; foreach (ParamPersonDataSet.HPersonRow dr in ppDS.HPerson.DefaultView.Table.Rows) The row_count gives me the correct result, but the traversing with foreach, gives all rows, so How should the line with foreach look? Thanks, Flemming Tag: ADO Connection after DB backups Tag: 117996
  - 18
    - Typed Dataset as description of XML database? I have read how one can define tables, indices, relations etc. in .Net code in a way that allows you to run a off-line XML based ADO.NET, which is excactly what I need. I have also read about Typed Dataset and how this definition is stored in an XSD file. I was wondering if these two technologies can be used together or put in other words: Can I use the GUI tools in VS.Net to define the layout of my tables in order to design my off-line XML database? Thank you in advance. All inputs on the issue is welcome. Best regards, Soeren Tag: ADO Connection after DB backups Tag: 117995
  - 19
    - Database choice to use in C# Hi, I'm in the design phase of a little database program in C#. But I don't know what database to use. The DB should be free, easy to set up, and easy to program up against in C#. I'm using VisualStudio.NET 2003, wich I have a Certified Partner licence for. I have never used ADO before, and I'm somewhat of a newbie in C# also. :) What DB is recommendable? Are there any good guides regarding this subject? Best regards Mads Tag: ADO Connection after DB backups Tag: 117991
  - 20
    - Creating tables in a JET database In ado.net I know I can execute a command and create a table using the appropriate SQL syntax (ie. CREATE Table. . .) This does not give much control or structure over how the table is created. From previous days of DAO I know there is a tabledefs collection, containing tables, which on turn contain a fields collection and so on. Reading a few posts, it seems that to do this in ADO.Net I need an interop to adox, and everything should be ok. I have the interop, and found the following sample code. (ms-help://MS.MSDNQTR.2005JAN.1033/ado270/htm/admsctablecreationexample.htm) On Error GoTo CreateTableError Dim tbl As New Table Dim cat As New ADOX.Catalog ' Open the Catalog. cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _ "Data Source='Northwind.mdb';" tbl.Name = "MyTable" tbl.Columns.Append "Column1", adInteger tbl.Columns.Append "Column2", adInteger tbl.Columns.Append "Column3", adVarWChar, 50 cat.Tables.Append tbl Debug.Print "Table 'MyTable' is added." 'Delete the table as this is a demonstration. cat.Tables.Delete tbl.Name Debug.Print "Table 'MyTable' is deleted." 'Clean up Set cat.ActiveConnection = Nothing Set cat = Nothing Set tbl = Nothing Running this code gives a wierd error on the cat.Tables.Append line. "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." Abbrieviating the code to the following also shows the problem cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _ "Data Source='Northwind.mdb';" Dim x As ADOX.Table For Each x In cat.Tables Console.WriteLine(x.Name) Next I've read quite a few articles on this, and none seem to apply (i.e. Delphi default values etc.) Does anyone know what's causing this or more importantly how to fix?? Tag: ADO Connection after DB backups Tag: 117990
  - 21
    - stored Procedure question In y C# project i'm trying microsoft solution to rotate rows as colums http://support.microsoft.com/kb/175574/EN-US SELECT YEAR, Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR = Q.YEAR),0), Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR = Q.YEAR),0), Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR = Q.YEAR),0), Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR = Q.YEAR),0) FROM QTRSALES Q GROUP BY YEAR I can build the Subqueries dynamiclly from code by pass the (Column Alias & Quarter value) from pre-query : SELECT Q_NAME FROM INDECATORS; and use foreach loop on retrieved rows and replace Q1& Q2... I want to convert this to one stored procedure so i can pass a YEAR to get AMOUNT value, is this possible and How?? help plz Tag: ADO Connection after DB backups Tag: 117988
  - 22
    - Time Consuming with large scale data by using OracleDataAdapter.Fill Dear, I'm using Microsoft .NET Provider for Oralce, but it's time consuming on large scale record by using OracleDataAdapter. Code Snippet: - DataSet dataset = new DataSet(); OracleConnection conn = new OracleConnection(); OracleCommand myCommand = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); conn.ConnectionString = dbConnectionString; myCommand.Connection = conn; conn.Open(); myCommand.ExecuteNonQuery(); adapter.SelectCommand = new OracleCommand(query, conn); adapter.Fill(dataset); conn.Close(); During traced the program on "adapter.Fill(dataset);", it needs 10 mins for 35,152 records. But the execution time on sql*plus is around 23 secs for the same query. Is it any mistakes on the program or any suggestion for this. Thanks for your kindly attention. Note: use with VS2003, .NET FW 1.1, Oracle9i Tag: ADO Connection after DB backups Tag: 117980
  - 23
    - Error when databinding This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C5BF1C.451E4B90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable When I run my application, which uses databinding in a DataList, I = recieve the following error: Server Error in '/' Application.=20 -------------------------------------------------------------------------= ------- Public member 'title' on type 'DataRowView' not found.=20 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.=20 Exception Details: System.MissingMemberException: Public member 'title' = on type 'DataRowView' not found. Source Error:=20 Line 65: <ItemStyle Font-Names=3D"Arial,Helvetica,Sans-Serif" = BackColor=3D"Silver"></ItemStyle> Line 66: <ItemTemplate> Line 67: <asp:HyperLink id=3DlnkTitle1 runat=3D"server" = NavigateUrl=3D'<%# "viewpoem.aspx?poem=3D" & = Server.UrlEncode(Container.DataItem.title) %>' Text=3D'<%# = DataBinder.Eval(Container, "DataItem.title") %>' Width=3D"400px" = Font-Size=3D"Medium"> Line 68: </asp:HyperLink> Line 69: <asp:Label id=3DlblRating1 runat=3D"server" Text=3D"<%# = FormatNumber(CDbl(Container.DataItem.totalpoints)/CDbl(Container.DataItem= .timesrated),1,TriState.True) %>" Width=3D"40px" Font-Size=3D"Medium">=20 Source File: c:\inetpub\wwwroot\poetry\poemratings.aspx Line: 67=20 Stack Trace:=20 [MissingMemberException: Public member 'title' on type 'DataRowView' not = found.] Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, = Type objType, String name, Object[] args, String[] paramnames, Boolean[] = CopyBack) +885 ASP.poemratings_aspx.__DataBind__control12(Object sender, EventArgs = e) in c:\inetpub\wwwroot\poetry\poemratings.aspx:67 System.Web.UI.Control.OnDataBinding(EventArgs e) +66 System.Web.UI.Control.DataBind() +26 System.Web.UI.Control.DataBind() +86 System.Web.UI.WebControls.DataList.CreateItem(Int32 itemIndex, = ListItemType itemType, Boolean dataBind, Object dataItem) +127 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean = useDataSource) +686 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49 System.Web.UI.WebControls.BaseDataList.DataBind() +23 localhost.poetry.poemratings.SortPoems(String sortby) in = C:\Inetpub\wwwroot\poetry\poemratings.aspx.vb:59 localhost.poetry.poemratings.Page_Load(Object sender, EventArgs e) in = C:\Inetpub\wwwroot\poetry\poemratings.aspx.vb:41 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +750 =20 -------------------------------------------------------------------------= ------- Version Information: Microsoft .NET Framework Version:1.1.4322.2032; = ASP.NET Version:1.1.4322.2032=20 I cannot figure out why this is happening. The code I use to do the = binding, which I call from Page_Load using Me.SortPoems("title") is as = follows: Private Sub SortPoems(ByVal sortby As String) Dim ratedpoems As New DataSet Dim sqltext As String =3D "SELECT * FROM poemratings ORDER BY " Select Case sortby Case "title" sqltext &=3D "title" Case "rating" sqltext &=3D "(totalpoints/timesrated),timesrated,title" Case "timesrated" sqltext &=3D "timesrated,(totalpoints/timesrated),title" End Select Dim dataadapterSelect As New = System.Data.OleDb.OleDbDataAdapter(sqltext, Global.GetDBConnection()) dataadapterSelect.Fill(ratedpoems) datRatings.DataSource =3D ratedpoems datRatings.DataBind() End Sub I have tried several experiments (such as removing the databinding = expressions that use the field, but the same thing happens with the = other fields). Why is ASP.NET telling me the field is not there? Any = help would be appreciated. Thanks. --=20 Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ ------=_NextPart_000_0032_01C5BF1C.451E4B90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2722" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>When I run my application, which uses = databinding=20 in a DataList, I recieve the following error:</FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN> </DIV> <DIV> <H1>Server Error in '/' Application.=20 <HR width=3D"100%" color=3Dsilver SIZE=3D1> </H1> <H2><I>Public member 'title' on type 'DataRowView' not found.</I>=20 </H2></SPAN><FONT=20 face=3D"Arial, Helvetica, Geneva, SunSans-Regular, sans-serif = "><B>Description:=20 </B>An unhandled exception occurred during the execution of the current = web=20 request. Please review the stack trace for more information about the = error and=20 where it originated in the code. <BR><BR><B>Exception Details:=20 </B>System.MissingMemberException: Public member 'title' on type = 'DataRowView'=20 not found.<BR><BR><B>Source Error:</B> <BR><BR> <TABLE width=3D"100%" bgColor=3D#ffffcc> <TBODY> <TR> <TD><CODE><PRE>Line 65: <ItemStyle = Font-Names=3D"Arial,Helvetica,Sans-Serif" = BackColor=3D"Silver"></ItemStyle> Line 66: <ItemTemplate> <FONT color=3Dred>Line 67: <asp:HyperLink id=3DlnkTitle1 = runat=3D"server" NavigateUrl=3D'<%# "viewpoem.aspx?poem=3D" &amp; = Server.UrlEncode(Container.DataItem.title) %>' Text=3D'<%# = DataBinder.Eval(Container, "DataItem.title") %>' Width=3D"400px" = Font-Size=3D"Medium"> </FONT>Line 68: </asp:HyperLink> Line 69: <asp:Label id=3DlblRating1 runat=3D"server" = Text=3D"<%# = FormatNumber(CDbl(Container.DataItem.totalpoints)/CDbl(Container.DataItem= .timesrated),1,TriState.True) %>" Width=3D"40px" = Font-Size=3D"Medium"></PRE></CODE></TD></TR></TBODY></TABLE><BR><B><FO= NT=20 face=3DVerdana>Source File:=20 </FONT></B>c:\inetpub\wwwroot\poetry\poemratings.aspx<B><FONT = face=3DVerdana>=20    Line: </FONT></B>67 <BR><BR><B><FONT face=3DVerdana>Stack=20 Trace:</FONT></B> <BR><BR> <TABLE width=3D"100%" bgColor=3D#ffffcc> <TBODY> <TR> <TD><CODE><PRE>[MissingMemberException: Public member 'title' on = type 'DataRowView' not found.] Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, = Type objType, String name, Object[] args, String[] paramnames, Boolean[] = CopyBack) +885 ASP.poemratings_aspx.__DataBind__control12(Object sender, EventArgs = e) in c:\inetpub\wwwroot\poetry\poemratings.aspx:67 System.Web.UI.Control.OnDataBinding(EventArgs e) +66 System.Web.UI.Control.DataBind() +26 System.Web.UI.Control.DataBind() +86 System.Web.UI.WebControls.DataList.CreateItem(Int32 itemIndex, = ListItemType itemType, Boolean dataBind, Object dataItem) +127 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean = useDataSource) +686 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49 System.Web.UI.WebControls.BaseDataList.DataBind() +23 localhost.poetry.poemratings.SortPoems(String sortby) in = C:\Inetpub\wwwroot\poetry\poemratings.aspx.vb:59 localhost.poetry.poemratings.Page_Load(Object sender, EventArgs e) in = C:\Inetpub\wwwroot\poetry\poemratings.aspx.vb:41 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +750 </PRE></CODE></TD></TR></TBODY></TABLE><BR> <HR width=3D"100%" color=3Dsilver SIZE=3D1> <B><FONT face=3DVerdana>Version Information:</FONT></B> Microsoft = .NET=20 Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032 = </FONT></FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial=20 size=3D2></FONT> </DIV><FONT face=3DArial size=3D2></FONT> <DIV><FONT face=3DArial size=3D2>I cannot figure out why this is = happening. The code=20 I use to do the binding, which I call from Page_Load using=20 Me.SortPoems("title") is as follows:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Private Sub SortPoems(ByVal sortby As=20 String)<BR>    Dim ratedpoems As New=20 DataSet<BR>    Dim sqltext As String =3D "SELECT * FROM = poemratings=20 ORDER BY "</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    Select Case=20 sortby<BR>        Case=20 "title"<BR>          &n= bsp;=20 sqltext &=3D "title"<BR>        = Case=20 "rating"<BR>          &= nbsp;=20 sqltext &=3D=20 "(totalpoints/timesrated),timesrated,title"<BR>    &n= bsp;  =20 Case=20 "timesrated"<BR>         &nb= sp; =20 sqltext &=3D = "timesrated,(totalpoints/timesrated),title"<BR>   =20 End Select<BR>    Dim dataadapterSelect As New=20 System.Data.OleDb.OleDbDataAdapter(sqltext,=20 Global.GetDBConnection())<BR>   =20 dataadapterSelect.Fill(ratedpoems)<BR>    = datRatings.DataSource =3D=20 ratedpoems<BR>    datRatings.DataBind()<BR>End = Sub</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial = size=3D2></FONT><FONT=20 face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have tried several experiments (such = as removing=20 the databinding expressions that use the field, but the same thing = happens with=20 the other fields). Why is ASP.NET telling me the field is not there? Any = help=20 would be appreciated. Thanks.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>-- <BR>Nathan Sokalski<BR></FONT><A=20 href=3D"mailto:njsokalski@hotmail.com"><FONT face=3DArial=20 size=3D2>njsokalski@hotmail.com</FONT></A><BR><A=20 href=3D"http://www.nathansokalski.com/"><FONT face=3DArial=20 size=3D2>http://www.nathansokalski.com/</FONT></A></DIV></BODY></HTML> ------=_NextPart_000_0032_01C5BF1C.451E4B90-- Tag: ADO Connection after DB backups Tag: 117975
  - 24
    - Accessing the current DataRow of the DataSource from ItemDataBound I want to access the DataRow used in DataBinding from the ItemDataBound event. In my case, the reason for doing this is to determine whether I need to make a word singular or plural. How can I do this? Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: ADO Connection after DB backups Tag: 117969
  - 25
    - Event Bubbling From The HeaderTemplate I have three LinkButtons in the HeaderTemplate of my DataList (I use them to let the user determine what to sort the list by). I am assuming that the event will be bubbled to the ItemCommand event (since that is the default and I did not supply a CommandName). However, I need to determine which of the three LinkButtons triggered the event. How can I do this? Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: ADO Connection after DB backups Tag: 117967

We stop a separate server with a SQL Server instance for backups at night
the restart after backups are completed. The ADO connection on our web
server (Win Svr 2k w/IIS) cannot reconnect to the database server unless the
web machine is rebooted. We have tried to restart IIS but it did not work.
Has anyone seen this problem before? Any thoughts on where to start?

R. DeWaal

Top

Re: ADO Connection after DB backups by Sahil

Sahil
Mon Sep 26 13:31:06 CDT 2005

Rich,

This is a known issue with .NET 1.1. Essentially there are a few open
connections left in the connection pool when you reboot your database
server. But when you reboot the server, the web server (and it's connection
pool), has no idea that the connection pool is now corrupt. .NET 2.0 now has
a new static method called "ClearConnectionPool" (or something like that),
on SqlConnection which tells the web server to clear out the pool and start
afresh (clearing only marks the existing pool entries as corrupt, so it
cannot be used as an alternate to disposing or closing connections).

Now how do you do this in .NET 1.1.

a) I had written some code using reflection to do the same, which I can't
find right now LOL. If I do, I'll post it on my blog, but no guarantees of
it working. (Also I've gotta leave for the summit tommorow, so may not have
time tonight .. but I will post it eventually .. maybe in a week's time).
b) Don't stop your SQL Server, instead backup from a replicated copy (that
you can stop). <-- Better solution.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

"Rich DeWaal" <rdewaal@rjdnet.net> wrote in message
news:eoYQHrqwFHA.2312@TK2MSFTNGP14.phx.gbl...
> We stop a separate server with a SQL Server instance for backups at night
> the restart after backups are completed. The ADO connection on our web
> server (Win Svr 2k w/IIS) cannot reconnect to the database server unless
the
> web machine is rebooted. We have tried to restart IIS but it did not
work.
> Has anyone seen this problem before? Any thoughts on where to start?
>
> R. DeWaal
>
>

Top