auto-populate SQLCommand.Parameters collection based on stored-procedure

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
    - Need help!So strange with DataRow State.. Hi:everybody I meet a very strange problem. I use strong typed DataSet. After I change the row value,the rowstate always UnChanged. Here is the code: dsOrder.order_master[cmMaster.Position].order_no=10; string s dsOrder.order_master[cmMaster.Position].RowState.ToString(); or dsOrder.order_master[0].order_no=10; string s dsOrder.order_master[0].RowState.ToString(); s is always "UnChanged". But in the morning ,It was working very well. I try to change the tables DefaultView' value, the same thing happend,I got confused, Dose anybody met problems like this? Thanks ! Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 99003
  - 2
    - Dispose vs Close Trying to determine if calling Dispose destroys the connection instead of releasing the connection back to the connection pool where a close does release to the connection pool. I have read documentation that made that statement. Most everyone agrees dispose does call close then cleans up some more. But does Dispose destroy the connection and not release back to the pool (when connection pooling is enabled)? Also, does a data layer have to implement the IDisposable interface when working with connections (closing and releasing a connection) in the component? Our apps do create and pass in a connection when working with transactions but for the selects we use the datareader with the CloseConnection behavior and some datasets with the disconnection taking place. And one more item, we have encrypted connection strings in our web.config file. Is using connection factories better and is there any explaination on how to build the factory? Thanks Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 99000
  - 3
    - Invalid cast from DBNull to Decimal This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4C82C.DD082030 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I must be just looking at this for so long I am going blind. Here is = part of the code: For Each _lRow In _laRow Me.DepYrStraightLine.DepreciationYearID =3D = CType((_lRow.Item("DepreciationYearID")), System.Guid) Me.DepYrStraightLine.DepreciationID =3D = CType((_lRow.Item("DepreciationID")), System.Guid) Me.DepYrStraightLine.DepreciationTypeID =3D = CType((_lRow.Item("DepreciationTypeID")), Integer) Me.DepYrStraightLine.Year1 =3D CType((_lRow.Item("Year1")), Decimal) Me.DepYrStraightLine.Year1BookValue =3D = CType((_lRow.Item("Year1BookValue")), Decimal) Me.DepYrStraightLine.Year2 =3D CType((_lRow.Item("Year2")), Decimal) Me.DepYrStraightLine.Year2BookValue =3D = CType((_lRow.Item("Year2BookValue")), Decimal) The rows (or columns rather) Year1BookValue and Year2BookValue are two = new columns I added to the table. I have gone all the way from the = stored procs and tables and through the code to make sure everything was = identical on the column data type settings. The data type is just like = 'Year1' and 'Year2'. Decimal, default value of (0.0) 27 precision and 15 = scale. When I step through with the debugger and get to any of the new columns, = I get an invalid cast exception that says the cast to DBNull from = Decimal is not allowed. However, the 'Year1'/'Year2 fields have 0D in = them. Any idea what could be causing this problem? Thanks. Norton ------=_NextPart_000_0008_01C4C82C.DD082030 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.2523" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>I must be just looking at this for so = long I am=20 going blind. Here is part of the code:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <P><FONT face=3DArial size=3D2>For Each _lRow In _laRow</FONT></P> <P><FONT face=3DArial size=3D2>Me.DepYrStraightLine.DepreciationYearID = =3D=20 CType((_lRow.Item("DepreciationYearID")), System.Guid)<BR></FONT><FONT=20 face=3DArial size=3D2>Me.DepYrStraightLine.DepreciationID =3D=20 CType((_lRow.Item("DepreciationID")), System.Guid)<BR></FONT><FONT = face=3DArial=20 size=3D2>Me.DepYrStraightLine.DepreciationTypeID =3D=20 CType((_lRow.Item("DepreciationTypeID")), Integer)<BR></FONT><FONT = face=3DArial=20 size=3D2>Me.DepYrStraightLine.Year1 =3D CType((_lRow.Item("Year1")),=20 Decimal)<BR></FONT><FONT face=3DArial = size=3D2>Me.DepYrStraightLine.Year1BookValue =3D=20 CType((_lRow.Item("Year1BookValue")), Decimal)<BR></FONT><FONT = face=3DArial=20 size=3D2>Me.DepYrStraightLine.Year2 =3D CType((_lRow.Item("Year2")),=20 Decimal)<BR></FONT><FONT face=3DArial = size=3D2>Me.DepYrStraightLine.Year2BookValue =3D=20 CType((_lRow.Item("Year2BookValue")), Decimal)</FONT></P> <P><FONT face=3DArial size=3D2>The rows (or columns rather) = Year1BookValue and=20 Year2BookValue are two new columns I added to the table. I have gone all = the way=20 from the stored procs and tables and through the code to make sure = everything=20 was identical on the column data type settings. The data type is just = like=20 'Year1' and 'Year2'. Decimal, default value of (0.0) 27 precision and 15 = scale.</FONT></P> <P><FONT face=3DArial size=3D2>When I step through with the debugger and = get to any=20 of the new columns, I get an invalid cast exception that says the cast = to DBNull=20 from Decimal is not allowed. However, the 'Year1'/'Year2 fields have 0D = in=20 them.</FONT></P> <P><FONT face=3DArial size=3D2>Any idea what could be causing this=20 problem?</FONT></P> <P><FONT face=3DArial size=3D2>Thanks.</FONT></P> <P><FONT face=3DArial size=3D2>Norton</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_0008_01C4C82C.DD082030-- Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98999
  - 4
    - Transfer Dataset / DataTable / Datareader Info to Excel Spreadsheet Is there some type of command that will allow me to transfer records in a datatable/dataset/ or datareader to an Excel Spreadsheet? Or do I have to do some type of manual iteration through a DataReader / DataTable? L Johnson Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98993
  - 5
    - DataView.Find Hello, I am retrieving data from a database "Order By LastName", when I use a dataview to find a Last Name it does not give me the first instance of the Last Name. ie. ADataView.Sort = "LastName" AnInteger=ADataView.find("Smith") BindingContext(ADataView).Position = AnInteger If there are more than one "Smith" in the database it goes to the second one instead of the First one. What I need is for it to go to the first one. Any suggestions will be greatly appreciated. Thanks, Chuck Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98991
  - 6
    - urgent: connection pooling error Hi all, I have a web service that opens a sqlconnection to the db and does some work. Just recently I have begun getting the following error on a frequent basis: Timeout expired.The timeout period elapsed prior to obtaining a connection from the pool.This may have occurred because all pooled connections were in use and max pool size was reached. I have verified that my web service is closing connections. Why is this happening? Can I increase the max pool size? How? thanks, Param Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98987
  - 7
    - insert data help! Hello! I use a DataAdapeter ( and DataSet) to access to my database. I have get all data well (fill the dataset), but when I try to update it, I got nothing! The code is below. Pls help me conn = new SqlConnection(strConn); sda = new SqlDataAdapter(); ds = new DataSet(); ds.DataSetName = "ds"; string tmp = "SELECT * FROM Personal"; SqlCommand selectCmd = new SqlCommand(tmp, conn); sda.SelectCommand = selectCmd; sda.Fill(ds, "Personal"); conn.Close(); SqlCommand insertCmd = new SqlCommand("InsertNewPerson", sda.SelectCommand.Connection); insertCmd.CommandType = CommandType.StoredProcedure; SqlParameter[] parms = new SqlParameter[4]; parms[0] = new SqlParameter("@name", SqlDbType.NVarChar, 50); parms[1] = new SqlParameter("@code", SqlDbType.Char, 4); parms[2] = new SqlParameter("@type", SqlDbType.Char, 1); parms[3] = new SqlParameter("@isAvailable", SqlDbType.TinyInt); for (int i = 0; i < 4; i++) insertCmd.Parameters.Add(parms[i]); insertCmd.Parameters[0].Value = "NewName"; insertCmd.Parameters[1].Value = "0000"; insertCmd.Parameters[2].Value = "M"; insertCmd.Parameters[3].Value = 1; sda.Update(ds,"Personal"); the storedProc: REATE PROCEDURE InsertNewPerson( @name nvarchar(50), @code char(4), @type char(1), @isAvailable tinyint )AS insert into Personal values(@name, @code, @type, @isAvailable) GO Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98986
  - 8
    - Mdi child controls I need to know what control (within a mdi child form) has focus when the user clicks on a toolbar buttom from the mdi parent form, If that's make sense... =) Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98984
  - 9
    - OBJ REF NOT SET TO AN INSTANCE OF AN OBJ guys ive been pouring over this for hours. i'm bascially copying code from another working asp.net program and i just cant see what im missing. the logon procedure certainly finds the obj reference yet when i go to execute the sql query string, i keep getting this message. and im basically doin the exact same thing from the other program, just querying a different table, so why the ERROR?? here's the code if anyone can take the time to figure out what im missing. i've looked at all the variables Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click sql = "select * from cont_payroll where empl_id='0322K' and ppe="15-oct-04'" Try Dim oracon As New OracleConnection("Server=atserver;Uid=rik;Pwd=winter_04") Dim ds a new Dataset oracon.Open() ds.Clear() ds.EnforceConstraints = False Dim myCmd As New OracleCommand myCmd.Connection = oracon myCmd.CommandText = sql myCmd.CommandType = CommandType.Text Dim myDa As New OracleDataAdapter(myCmd) myDa.Fill(ds) If ds.Tables.Count = 0 Then lblLogon.Text = "Nothing found" ElseIf ds.Tables.Count >= 1 Then Dg1.DataSource = ds.Tables(0) Dg1.DataBind() Else End If oracon.Close() Catch ex As Exception lblLogon.Text = ex.Message End Try End Sub End Class ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98983
  - 10
    - Strategy Question: Semi-Structured Data Comparisons All, I'm hoping one of you Xml or Data gurus can offer an opinion. I'm working on an app to basically compare two semi-structured data files (e.g. Excel / CSV) to one another. But I need to compare them as if they were datatables... (ie a simple diff type tool won't work)... More specifically, the process I envision so far is: 1) Read in two files -- if Excel select appropriate sheet (would also require users to structure their worksheets as tables -- ie no mixed data so common with Excel) 2) Determine worksheet -- if Excel 4) Scan for columns 5) Have user determine which is source and which is target (or file being compared) 6) Have user match one or more "key" columns between two files -- eg Source File "ID" column = Target File "UserID" column (allows for arbitray column names) 7) Have user select (similar to above) columns to compare (simple text, integer or boolean equality for now) 8) Run the comparison determining -- a) Rows in one but not the other file (based on the Keys) b) Rows with matching keys but non-matching (changed) comparison columns c) Rows that match key / compare columns Here's what I'm asking: - I know how to do / have written 1 - 7. - I also know how to do 8 above via brute force -- eg going from the source, read row by row, column by column and comparing keys and comparison columns between the two... * What do you recommend as the data structure? Currently using an OleDB generated dataset. Does Xml or a collection make more sense? * If dataset or Xml, is there a better way than brute force? Dataview with Find? Merge and detect differences? Would appreciate any advice from anyone who's been there before. One concern with brute force approach is that these files could contain hundreds to thousands of rows (yes, I wish my fellow employees would do more with databases) -- which, worst case scenario of, say, a thousand rows in each file results in potentially, I believe, 1 Million comparisons.... Any thoughts, comments, suggestions? Conversion of source data to Access, SQL Server, Oracle not an option......tried it already (politics).... thanks in advance, tim Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98975
  - 11
    - Query about closing connections to SQL Server database. I have an application that connects using SqlConnection objects to a SQL Server database. When I first execute the Connection.Open() method, two new connections appear in my database (according to sp_who2, both of them are from the program ".Net SqlClient Data Provider"). Firstly, is it normal for two connections to appear like this? Anyway, straight after opening the database I call Connection.Close(). Neither of the connections in the database close at this point. Is this the expected behaviour? I'm wondering whether this is just due to connection pooling and is nothing to worry about but would like some confirmation. Calling the .Dispose method on the connection has no effect either. The connections don't disappear from the database until I close my application and return to the IDE. My thanks in advance for any guidance anyone can provide. -- (O)enone Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98970
  - 12
    - Oracle Stored Procedure Problems Hello, I am calling an Oracle Stored Procedure. It has a column which is defined as "Float". The Oracle procedure defines a parameter for this field as "Float". In my program I create a parameter which is a Oracle DbType of "Double". When I execute the procedure, it fails with an ORA 06502 and ORA 06512 and these error messages talk of not being able to convert propertly from string to numeric. When I look at the table, I see that my transaction did apply to the database, inspite of the error message, but the Fields which is identified as "float" and I defined a "Double" has the amount that I passed, but the value truncates the last four decimal places - I passed 8 decimal positions but only 4 remained. Am I using the correct datatype in my program to describe a float value? Any ideas as to why I am getting the Errors begin returned? The output paramterters, when I create them in my program, - the "value" portion of thos parameters, do they need be to initialized with "null" in the "Value" property? Thanks in advance for your assistance!! Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98964
  - 13
    - No partial class for a strongly typed row in a s.t. dataset in 2.0 The dataset itself is a partial, that's nice, can attach code to it if I wish. Ditto for the datatable. However, the area where I was really interested in doing it, the strongly typed datarow, no go. Sure, I can continue to wrap the row in a custom class, but it feels more natural to me to be able to extend the strongly typed datarow class directly. Too late to re-think that? Kenneth Russo USCA5 Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98962
  - 14
    - Account Groups I am accessing an Access database with User groups/accounts set up, is there any way for me to determine which groups indvidual accounts are a member of from my .net application. i.e. if my connection string is : Dim STR_CONNECTION As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db.mdb; User ID=Alice; Password=AliceABC" Is there any way to determine what group Alice is in? Thanks, Steven Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98951
  - 15
    - Asp.Net cannot insert record to dbf file? Help I have a class library to insert a new record into a dbf file. I always get exception "Operation must use an updateable query" when the function is called by a Asp.Net function. However, the insersion works well when I call the function using NUnit. The following is the writing dbf code. Any clue? Thanks. Class DAL static public function writedbf() ... Dim dbfConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\dbfs;Extended Properties=dBase IV;User ID=Admin;Password=" Dim InsertCmdStr As String = "Insert Into wolog (WOTYPE, REF_NO, PRIORITY, Unit, DEPT, DEPT_ID, PAC2, RQNAME, RQPHONE, SHOP, ASTO, WODATE, OPTR, LOCATION, COMM1, ESTHR, STATUS, UP, ROOM, FLR) values ('D','D700804','5','90','740','1','2BUS','a','a','SEC','','11/10/2004 2:02:27 PM','login','2 BUS ST a ','a ',0,'N','N','a','')" Dim cmd As New OleDbCommand(InsertCmdStr, New OleDbConnection(dbfConnectionString)) cmd.Connection.Open() Try REF_NO = cmd.ExecuteScalar() Catch err As Exception Throw err Finally If Not cmd.Connection Is Nothing Then cmd.Connection.Close() End If End Try .... Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98928
  - 16
    - Confusing quotation marks from ADO .NET to stored procedures Hi, I am new to database. I am having difficulty with quotaion marl problems when I am passing string variables in ADO .NET to varchar variable in SQL stored procedures. In ADO .NET code, I used: SqlParameter[] parameters = { new SqlParameter("@StartCity", SqlDbType.VarChar, 50), new SqlParameter("@EndCity", SqlDbType.VarChar, 50), new SqlParameter("@TripDate", SqlDbType.DateTime, 8) } parameters[0].Value = strStartCity; // strStartCity = txtStartCity.Text; parameters[1].Value = strEndCity; parameters[2].Value = tripDate; // tripDate is System.DateTime type ..... DataSet trips = RunProcedure("usp_GetTrips", parameters, "Trips"); Then in SQL stored procedure, I used: SELECT * WHERE StartCity LIKE @StartCity AND EndCity LIKE @EndCity AND DateDiff(day, DateAdd(day, -3, @dtTripDate), TripDate)<10 This does not work returning no result. I used SQL Query Analyser debugger to check the stored procedure. It seems the string value like 'Ottawa' is automatically padded with " (double quotation marks) at both ends and these quotation marks are counted as a character. So it does not work. When I used the following in my stored procedure: SELECT * WHERE StartCity LIKE 'Ottawa' AND EndCity LIKE 'Toronto' AND DateDiff(day, DateAdd(day, -3, '11/16/2004'), TripDate)<10 It worked and returned the results. It seems that the strings passed from ADO ,NET code is not interpreted as string literal in stored procedure. What is the problem and what settings do I need to turn on to solve it? Thanks a lot for your time and help. David *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98926
  - 17
    - Re: Does anyone know anything about this BUG? Ok, Ryan. Thanks for your attention. Do you get 0 rows in which one of the result sets? Because it shows 2 result sets. One with the expected result and another one, where the extra brackets were added with the "wrong" result. Just like bellow: -------------------------------------------------------------------------------------------------------------------------------------- C:\Temp>AdoTableSelectTest LESS BRACKETS FILTER RESULTS ( Description = 'Type B' and Number = 7 ) and ( Code = 'B' ) Row Count: 0 Code - Number - Description --------------------------- EXTRA BRACKETS FILTER RESULTS ( ((Description = 'Type B')) and ((Number = 7)) ) and ( Code = 'B' ) Row Count: 2 Code - Number - Description --------------------------- B - 2 - Type B B - 3 - Type B C:\Temp> -------------------------------------------------------------------------------------------------------------------------------------- Note that the values in the Number column in the 2nd result set are 2 & 3 respectively. But the filter clause says "Number = 7"(???). If both result sets are returning 0 rows, then I don't know what it is. It might be that you have a different MDAC or .NET Framework version where this problem has already been fixed. Many thanks, Dalmo "W.G. Ryan eMVP" <WilliamRyan@NoSpam.gmail.com> wrote in message news:eJcEwwfxEHA.1452@TK2MSFTNGP11.phx.gbl... > Dallara: > > I'm not sure I follow you - I copied the code below and ran it - and I'm > getting 0 rows selected. When I change the code block to this : > DataRow Row1 = Table.NewRow(); > > Row1["Code"] = "A"; > > Row1["Description"] = "Type A"; > > Row1["Number"] = 1; > > Table.Rows.Add(Row1); > > I get exactly what I'd expect. > > > -- > W.G. Ryan MVP (Windows Embedded) > > TiBA Solutions > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > "Dallara" <someone@microsoft.com> wrote in message > news:#7rGTIexEHA.2016@TK2MSFTNGP15.phx.gbl... >> Hi >> >> Just found a bug in the System.Data.DataTable.Select() method in ADO.NET. >> To reproduce the bug, just create a C# Console application with the code >> bellow on the start up class and execute it. >> >> What happens is that in some specific situations when you add some extra >> brackets to the filter string, rows that don't match the filter criteria > are >> selected. If you remove the brackets the select works fine. And it's not >> a >> matter of precedence as all conditions are joined by AND. >> >> In the spike bellow, one of the filter conditions is "Number = 7". This >> condition doesn't match any rows in the test table. Even thought, in the >> select where extra brackets are added to the filter, 2 rows that match >> the >> other conditions are improperly selected. These rows have Numbers 2 and 3 >> respectively (not 7). >> >> >> Thanks, >> Dallara // -------------------------------------- // -- SPIKE TO CATCH THE BUG -- // -------------------------------------- using System; using System.Data; namespace AdoTableSelectSpike { class Class1 { [STAThread] static void Main(string[] args) { TestAdoTableSelect(); } protected static void TestAdoTableSelect() { DataTable Table = GetTestTable(); string Condition0 = "Description = 'Type B'"; string Condition1 = "Number = 7"; string Condition2 = "Code = 'B'"; string LessBracketsFilter = String.Format("( {0} and {1} ) and ( {2} )", Condition0, Condition1, Condition2); DataRow[] LessBracketsRows = Table.Select(LessBracketsFilter); WriteResults(LessBracketsRows, LessBracketsFilter, "LESS BRACKETS FILTER RESULTS"); string ExtraBracketsFilter = String.Format("( (({0})) and (({1})) ) and ( {2} )", Condition0, Condition1, Condition2); DataRow[] ExtraBracketsRows = Table.Select(ExtraBracketsFilter); WriteResults(ExtraBracketsRows, ExtraBracketsFilter, "EXTRA BRACKETS FILTER RESULTS"); } protected static DataTable GetTestTable() { DataTable Table = new DataTable("TestTable"); Table.Columns.Add("Code", typeof(System.String)); Table.Columns.Add("Description", typeof(System.String)); Table.Columns.Add("Number", typeof(System.Int32)); DataRow Row1 = Table.NewRow(); Row1["Code"] = "A"; Row1["Description"] = "Type A"; Row1["Number"] = 1; Table.Rows.Add(Row1); DataRow Row2 = Table.NewRow(); Row2["Code"] = "B"; Row2["Description"] = "Type B"; Row2["Number"] = 2; Table.Rows.Add(Row2); DataRow Row3 = Table.NewRow(); Row3["Code"] = "B"; Row3["Description"] = "Type B"; Row3["Number"] = 3; Table.Rows.Add(Row3); return Table; } protected static void WriteResults(DataRow[] Rows, string FilterString, string ResultsCaption) { Console.WriteLine(""); Console.WriteLine(""); Console.WriteLine(ResultsCaption); Console.WriteLine(""); Console.WriteLine(FilterString); Console.WriteLine("Row Count: " + Rows.Length.ToString()); Console.WriteLine("Code - Number - Description"); Console.WriteLine("---------------------------"); foreach (DataRow Row in Rows) { string Line = String.Format( "{0} - {1} - {2}", Row["Code"].ToString(), Row["Number"].ToString(), Row["Description"].ToString()); Console.WriteLine(Line); } Console.WriteLine(""); } } } // -------------------------------------- Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98924
  - 18
    - Stored procedure Debug in .Net 2003 Hi all I am trying to debug a stored procedure using VS.Net 2003. I get this message "Cannot debug stored procedures because the SQL server database is not setup correctly or user doesn't have permission to execute master.sp_sdidebug." Database server is running on Win3K Database SQL 2000 Client desktop: Win XP pro sp1 I am logged in as Administrator This problem only exists when I the debug stored procedure on remote (SQL 2000 running on Win3K machine) database server. However I can debug stored procedure on local database (SQL 2000) that is running on XP pro sp1. Any help would be greatly appreciated. Thanks Dipendra Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98914
  - 19
    - Show the ordinal number of a row in a dataset I want to build a lookup dialog form that uses the row index number to view a particular row. How do I show the row index number a datagrid so a user can select it? Thanks Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98913
  - 20
    - paper size crystal reports problem I have set printer options property (portrait and A4) of Report just before show it to user but still printer prints letter format. From all other applications (notepad, word, excel, ...) default format is A4 but in crystal it is letter but I set for A4. Why? Any ideas? thx -- ----------------------------------------------------------------------------------------------------------------------- Dzemal Tipura (Dzemo) - MCP Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98906
  - 21
    - connections not being reused... I have a Windows Service written in C# .net 1.1 that writes to SQL Server 2000. It has been deployed for some time now with no issues. now recent performance issues with the SQL Server have pointed to an apparent issue with the Windows Service. The service opens and closes all it's connection objects and has try/catch/finally blocks in place. (again this "had" been working properly). Yet now SQL Server is logging several connections from the service that continue to grow in number. And it appears that connections are not being reused from the connection pool. What can I look at as to why connections in the pool would not be reused versus new connections being created? And was there any recent patch that may have effected this? David Bartosik - [MSFT MVP] www.publishermvps.com www.davidbartosik.com Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98902
  - 22
    - Loading dataset with ComplexTypes I need to generate a XML document (which needs to nest the data) from a SQL query that returns several resultsets and I'm trying to the find the least painful way of doing this. I originally just used OPENXML to return an xml document, but this query became way to complicated to expect anyone to maintain. I tried creating a dataset with several datatables with the appropriate parent child relationships, but that didn't do the job because I can't have the parent's Primary Key nested in the child's node. I was able to create a dataset with simple and complex types that does exactly what I need when it's converted to xml using the XMLDataDocument; but now I have the issue of how to load this from SQL Server. From what I can tell there is no way to directly load a datatable with complex types (am I wrong?). So my next plan of attack is to load the data into one dataset that uses several datatables and then convert this into a seperate dataset with one datatable and several complex types. So is there some way to load the data into several datatables within a dataset and then automatically convert this into a different dataset that uses complex types? Am I missing something that is completely obvious? Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98892
  - 23
    - Cannot insert record to dbf file? Help I always get "Operation must use an updateable query" when I try to insert a new record in a dbf file. My connection string is: "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\dbfs;Extended Properties=dBase IV;User ID=Admin;Password=" And Code is Dim dbfConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\dbfs;Extended Properties=dBase IV;User ID=Admin;Password=" Dim InsertCmdStr As String = "Insert Into wolog (WOTYPE, REF_NO, PRIORITY, Unit, DEPT, DEPT_ID, PAC2, RQNAME, RQPHONE, SHOP, ASTO, WODATE, OPTR, LOCATION, COMM1, ESTHR, STATUS, UP, ROOM, FLR) values ('D','D700804','5','90','740','1','2BUS','a','a','SEC','','11/10/2004 2:02:27 PM','login','2 BUS ST a ','a ',0,'N','N','a','')" Dim cmd As New OleDbCommand(InsertCmdStr, New OleDbConnection(dbfConnectionString)) cmd.Connection.Open() Try REF_NO = cmd.ExecuteScalar() Catch err As Exception Throw err Finally If Not cmd.Connection Is Nothing Then cmd.Connection.Close() End If End Try The dbf file has a few indics and one of them are unique index (5 columns) However, I can successfully run the SQL statement in MS Access to append a record in the linked dbf table. Any clue? Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98883
  - 24
    - Having an issue updating a database.. I am using the ODBCDataAdapter and Datasets. I can fill the bound controls and see the data just fine, but if I make a change and then do an Update on the dataadapter, it just returns a 0 and doesn't make any changes. I have all the commands created, which I used the Wizard on the DataAdapter, by the way. Do you have to call BeginEdit? If I do, what do I call it on? CentralDS is my Dataset, and if I do a CentralDS.CentralRow.BeginEdit() I get an error that says Reference to a non-shared member requires an object reference.... Using VB.Net from VS.Net 2003 ... As you can see, complete newbie here to .Net... Thanks Aaron -- --- Aaron Smith Remove -1- to E-Mail me. Spam Sucks. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98877
  - 25
    - Need help with connect string for using Application Roles in SQL Server. I am trying to figure out how to do a connection string for a ADO.NET SqlConnection object on a database that implements Application Roles. I cannot seem to get the connection to work. I am of the impression that an initial connection must be made to the database and then the sp_setapprole stored procedure must be run to get the proper Application Role permission to the database. I cannot get past the Open() method of the connection object to get to a position where I can run the sp_setapprole stored procedure. Is there some specific connection string that I need to use? Please help! -- ----------------------------------- Ken Varn Senior Software Engineer Diebold Inc. EmailID = varnk Domain = Diebold.com ----------------------------------- Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98876
- Next
  - 1
    - LEFT JOIN How do I accomplish the equivalent of a LEFT JOIN in the VS Designer? I want all items from Table1 and any from Table2 that match. With or without matches from Table2, I need all the items from Table1. I thought this was a LEFT JOIN but VS is making it a LEFT OUTER JOIN and returning 10million records (a bit exaggerated). Thanks. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98872
  - 2
    - CROSS JOIN in VS Designer I'm using a SELECT statement like the following: SELECT table1.ItemA, table2.itemB FROM table1, table2 However, I can't tell if the designer is changing that statement to: SELECT table1.ItemA, table2.itemB FROM table1 CROSS JOIN table2 Is this the same? What's a CROSS JOIN? Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98869
  - 3
    - Need info on comparrision between OleDBConnection and SqlConnection objects. I realize that OleDbConnection can connect to more than one type of database engine, but I was wondering if there are any distinct advantages to using SqlConnection vs. OleDbConnection to connect to a SQL Server database. I have heard that performance could be an issue. Is this true? -- ----------------------------------- Ken Varn Senior Software Engineer Diebold Inc. EmailID = varnk Domain = Diebold.com ----------------------------------- Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98866
  - 4
    - Connection Pooling in SQL Server Hi, I know that this bug exists, but it is different behavior ... BUG: Performance Counters for SQL Server .NET Data Provider Are Not Reset http://support.microsoft.com/default.aspx?scid=kb;en-us;314429 I use this method to get the counterValue: Private Function GetCounterValue(ByVal objectName As String, ByVal counterName As String, ByVal instanceName As String, ByVal machineName As String) As Single Try Dim counter As New Diagnostics.PerformanceCounter With counter .CategoryName = objectName .CounterName = counterName .InstanceName = instanceName .MachineName = machineName End With Return counter.NextValue() Catch ex As Exception Return 0 End Try End Function and those are the calls I do: Dim server As String = "tcp:ELADIO" Dim conn As New SqlConnection("server=" + server + ";Database=northwind;Integrated Security=SSPI;") Console.WriteLine("#pooled connections: " + GetCounterValue(".NET CLR Data", "SqlClient: Current # pooled connections", "pooling", "ELADIO").ToString) conn.Open() Console.WriteLine("Conected...") Console.WriteLine("#pooled connections: " + GetCounterValue(".NET CLR Data", "SqlClient: Current # pooled connections", "pooling", "ELADIO").ToString) conn.Close() Console.WriteLine("Closed...") Console.WriteLine("#pooled connections: " + GetCounterValue(".NET CLR Data", "SqlClient: Current # pooled connections", "pooling", "ELADIO").ToString) the results I get are the following: #pooled connections: 0 Conected... #pooled connections: 2 Closed... #pooled connections: 2 from those results I see two points: -AFAIK, while the connection is in use, it can not be pooled; I think that the counter notifies that the connection is in the pool but it's not in really -I get the results multiplied by 2; I only open one connection and the result is 2; I'm sure I only open one instance of the connection and I'm sure aswell that I have only one instance of the application (named "pooling"). Can you help me to clarify any of those points? Thank you, -- Eladio Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98864
  - 5
    - Extract parameters from SQL statement Hei, I'm currently trying to write a program in C# that will allow users to parametrize their queries. For instance, I have a query like this: SELECT * FROM Customers Where Region = @Region AND Gender > @Gender How can I extract the Parameters names without using String manipulation (which is not perfect since sql statements can also contain '@' in LIKE clauses for example) I tried this way Dim comm As New OleDbCommand(SqlStatement, myCon) comm.Prepare() MsgBox(comm.Parameters.Count.ToString()) comm.Dispose() myCon.Close() but this always return me 0 (zero) Do you have any idea on how to solve this generic problem? Regars Philippe Graca Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98863
  - 6
    - import xsd dataset diagram into visio ? is there some way to get a dataset diagram (stored in visual studio as an xsd, but displayed graphically in the IDE) into visio as a drawing? Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98861
  - 7
    - ODP.NET 10.1.3.0 (Beta) Connectivity All, Finding that MS Oracle managed provider has some limitations with Parameter Building and so on, I switched to ODP.NET cos it has better support for DeriveParameters option. Currently I have .Net 1.1 installed and connecting to Oracle 8i Release 3 (8.1.7) using Oracle 8.1.7 client running on Windows 2000 SP4. I have installed ODP.Net and Oracle 10.1 client as a part of ODP.Net setup. Followed Installation instructions and looks like it is OK to install new client with different ORACLE_HOME. Now I have two clients installed on machine 8i and 10.1. Tried to create connection and open the connection using ODP.Net. But I get an exception saying System Error. I also tried to connect to Oracle 8i from client machine using 10.1 client (Network Config Tool), I get message that this server version is not supported. Thats a surprise. If you look at the System Requirement for ODP.Net 10.1.3.0, it says needs Oracle 8i server with Release 8.1.7. But still I am not able to connect to my DB which is of the same configuration as above. Is it because of Multiple Home? I am not sure! Please help! Thanks in advance. Nilesh Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98856
  - 8
    - speed problem I use SQL 2000 for database and i have 2 tables. Table1 : ID (int, identity) FirstName(int,text) LastName(int,text) FatherName(int,text) with 200000 rows Table2 : ID (int, identity) IDTable1(int) with 1500 rows when i search select * from table1 where fathername like '%xxx%' it work more or less good but when i change this statement into select * from table1 where fathername like '%xxx%' and id in (select idtable1 from table2) speed go down. Why? Any sugestions. Will speed improve to switch from text to char where text isn't needed? thx Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98850
  - 9
    - Dataset in Crystal Report Hello everyone, I'm working with a web application now and we're finding a way to improve the speed of viewing a report (Crystal report). We used a dataset instead of connecting the crystal report directly to the SQL Server that we are using. We thought that this time, we will only have one connection to the SQL Server and that is when filling the dataset. However, during our test of the report, we found out that the Stored procedure that we used in the dataset is being executed everytime the report form changes. Does this mean that the dataset refreshes it self everytime the Crystal report uses its data? We thought that we only have to fill the dataset once and that there will be no other connection to the SQL Server that will be created (by the Crystal report, i guess) everytime we changes a report page. Your ideas will be higly appreciated. Thank you. -- Nonoy Makati, Philippines Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98846
  - 10
    - Sort a price column in a dataview The only way i could see to insert prices into a datagrid was to use strings. Heres a fragment... oTable.Columns.Add(New DataColumn("Price", GetType(String))) . . . oRow("Price") = nPrice.ToString("C") . . . oSpecs = New DataView(oTable) oSpecs.Sort = "Price" Now the problem is, the column is obviously being sorted as regards to a string rather than a currency. For instance i end up with it ordered like this... £1,100.50 £400.00 £700.50 £800.70 Is there any way to make the datagrid aware that a column contains currency values? I couldn't find an appropriate datatype. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98845
  - 11
    - How to dispose a dataset completely Hi, Once a dataset is populated from DataAdapter, I want to release the memory acquired by dataSet object. So I call oDataSet.Dispose(). But it doesnot disposes the internal objects like Datatable/dataColumn. Should I have to call Dispose() method for these internal objects explicitely? OR DataSet.Dispose() takes care to clear the internal objects? Thanx, Atul Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98843
  - 12
    - NullReferenceException ClockSysDataSet.ContactTypeRow rowContactType = null; rowContactType.ContactTypeID = (int)this.cBoxContactType.SelectedValue; ClockSysDataSet.CityRow rowCity = null; rowCity.ZIP = 71679; // (int)this.txtZIP.Text; int intSalutationID = (int)this.cBoxSalutation.SelectedValue; this.clockSysDataSet.Contacts.AddContactsRow(null, 1, "Username", "Password", this.txtFirstName.Text, this.txtSecondName.Text, this.txtSurname.Text, this.txtStreet.Text, null); Hi folks, I always get NullRefenceEception at this coe and I don't know how to handle this. Can somebody help me please? --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98839
  - 13
    - Question with udl and connection string does it work? TIA Sal Greets, All Question with udl and connection string does it work? I'm having problems with the code below I keep getting an error "Keyword not supported 'File Name'" I was just following app.dev cbt course and for some reason it doesn't work for me does someone know what I doing wrong Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click With OpenFileDialog1 Dim response As String Dim strConnect As String .Filter = "UDL connection File(*.udl)|*.udl|All Files(*.*)|*. *" .FilterIndex = 1 .Title = " Select a UDL File" If .ShowDialog() = DialogResult.OK Then txtUDLFile.Text = .FileName SqlConnection1.ConnectionString = String.Format("File Name={0};", txtUDLFile.Text) SqlConnection1.Open() If SqlConnection1.State = ConnectionState.Open Then Console.WriteLine("Connection opened successfully") Else Console.WriteLine("Connection could not be established") End If SqlConnection1.Close() SqlConnection1 = Nothing response = Console.ReadLine() End If End With End Sub Here's the udl file contents below [oledb] ; Everything after this line is an OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sppDb;Data Source=(local)\vsdotnet I'm using VS.net 2003 using vb.net TIA Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98834
  - 14
    - Specify in connection string where MSDE data is stored Hi there, I can using ADO .NET in visual C++ to access an MSDE database. My connection string looks like this: "Data Source=(local);Initial Catalog=MyDB;Integrated Security=SSPI" This goes and gets the database from C:\Program files\Microsoft SQL Server\MSSQL\Data. However, my application needs to be able to access databases stored in other directories. I know you can set the data dir for MSDE at installation and I'm guessing you can change it through the registry after that, but is it possible to specify the data location in the connection string? Thanks, Amy Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98830
  - 15
    - How to hide a column in DataGrid ( or DataView)??? Hello! I need create a dataset with all columns, but in DataView (connected with DataGrid) I need hide some column ( So I don't know I must do it in DataView or DataGrid??) , Pls help me! Thanks for all your help! Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98814
  - 16
    - Microsoft Access versus Programmatic Data Access Hi All, I am experiencing an inconsistency in relation to accessing Oracle data via two different routes. I am accessing an Oracle database using a Microsoft Access front end , making use of linked tables , via a DSN. The user account I am using is limited to read only. I can view but not modify the data using Microsoft Access. However when I programmatically access the data (in c#) using various drivers (Microsoft and Oracle) I can access the database but cannot read the data. I am told that the table or view cannot be found.This occurs even if I access the data programmatically using the DSN which works using the Microsoft Access front end. Why the difference when I am using the same account and the same DSN? Any help greatly appreciated. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98810
  - 17
    - Error: Microsoft JET Database Engine I am working on a VB.NET WINFORM project with MS Access as database. The database has 2 tables: 1) PROJID: has the following fields: projid & projnm, projid is the primary key 2) PROJCOST: has the following fields: projid, currtyp, costtyp, yr, annamt. Primary keys are: projid, currtyp, costtyp, yr. In the MS Access Relationships design for this database, I setup a relationship between PROJID & PROJCOST tables related by 'projid' field and the 'Enforce Referential Integrity' box is checked. On one window form, I created a dataview populated with data from table PROJID. Several text box fields on this form are binded to this dataview, user can change data in table PROJID from these text fields. To save data to table, I use DataAdapter.Update command. This is where I keep getting an error saying: "Error: Microsoft JET Database Engine: The record cannot be deleted or changed because table 'projcost' includes related records" This happen even when the primary key field in PROJID is not changed. Please let me know if you have way to solve this problem Thanks Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98809
  - 18
    - Limit in Dataset? Hi, Could somebody tell me whether there is a limit in the no. of records that a Dataset/Dataset tables can handle? I read in a book that Dataset is not a good option if you have more than 200 records. Thanks LJ Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98806
  - 19
    - Executing a select statement multiple times? Hi, What is the efficient way of executing the database sequence multiple times? Suppose for example, I have a DB Sequence like this: â??Select SequenceName.NEXTVAL from TBLName;â?? I want to execute this multiple times and populate a collection. How do I achieve this efficiently? Do I have to use DataReader multiple times? If so, how do I do that? Thereâ??s no support for stored procedures/functions in the DBMS, so I canâ??t go that route. Any code snippets will help. Thanks. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98804
  - 20
    - unique key for only nested element Hello, I want to have a unique Quesiton Id key for each question under section. Not for every question in the Typed DataSet. Is there anyway I can do this...? Thanks, here is my typed Dataset XSD <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="TestSection" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="TestSection" msdata:IsDataSet="true" msdata:Locale="en-GB"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Section" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="Question" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="Option" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:attribute name="Text" type="xs:string" /> <xs:attribute name="Selected" type="xs:string" /> <xs:attribute name="Tooltip" type="xs:string" /> <xs:attribute name="Answer" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="Text" type="xs:string" /> <xs:attribute name="Type" type="xs:string" /> <xs:attribute name="Score" type="xs:string" /> <xs:attribute name="Mandatory" type="xs:string" /> <xs:attribute name="Tooltip" type="xs:string" /> <xs:attribute name="MultiSelect" type="xs:string" /> <xs:attribute name="AllowNoSelection" type="xs:string" /> <xs:attribute name="Multiline" type="xs:string" /> <xs:attribute name="MaxLength" type="xs:string" /> <xs:attribute name="MinLength" type="xs:string" /> <xs:attribute name="InitialValue" type="xs:string" /> <xs:attribute name="Validation" type="xs:string" /> <xs:attribute name="Answer" type="xs:string" /> <xs:attribute name="Id" type="xs:short" use="required" /> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="Enabled" type="xs:boolean" /> <xs:attribute name="ShowEdit" type="xs:boolean" /> <xs:attribute name="NumberOfQuestions" type="xs:string" /> <xs:attribute name="Type" type="xs:string" /> <xs:attribute name="Score" type="xs:string" /> <xs:attribute name="Text" type="xs:string" /> <xs:attribute name="Id" type="xs:short" use="required" /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:key name="TestSectionKey1" msdata:PrimaryKey="true"> <xs:selector xpath=".//Section" /> <xs:field xpath="@Id" /> </xs:key> <xs:key name="QuestionKey" msdata:PrimaryKey="true"> <xs:selector xpath=".//Question" /> <xs:field xpath="@Id" /> </xs:key> </xs:element> </xs:schema> Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98801
  - 21
    - ExecuteNonQuery() - Bach SQL Statement execution? Hi, I am trying to use the ExecuteNonQuery() to execute multiple SQL update statements. Though the SQL is correct, it only executes the first update statement. When I looked through the documentation I found out this, â??You can also use ExecuteNonQuery to execute multiple SQL statements if the underlying ODBC driver supports this functionality.â?? How do I know if my underlying driver supports this? Is there something I can do to change any setting on the ODBC driver? Thanks for any help. Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98800
  - 22
    - howto: use 'non sqlxml' xml returned by sql in dataset/reader or s it won't go into an xmlreader as the source is not a true sqlxml one [the stored proc is returning a bunch of xml snippets inside a <root>] all i want to do is get an xml string that i can pass back any ideas? Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98797
  - 23
    - Sql Transaction Hi all. I use System.Data.SqlClient.SqlTransaction type to execute several sprocs from C#, one by one, using one transaction. It works fine if I use sa account - commits on success, rolls back on errors, blah, blah. But it fails if I use less priveleged account. Sometimes Exception tells me that "<user> login failed", sometimes just generic sql error. What are minimum priveleges user should have to execute sprocs in context of SqlTransaction without giving him too much power? Thanks, Kikoz Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98787
  - 24
    - Parent-child relationship Hi, I'm adjusting the code at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B310350 to my application. I have 1 parent table with two child tables. I'm adjusting it so I can use it with a strong-typed dataset and generated SqlAdapters. The problem I'm facing is, when processing these lines: daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added)); ds.EnforceConstraints = false; daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added)); ds.EnforceConstraints = true; the INSERT in the first line creates a new record in the Orders table, and updates the DataRow in the DataSet with the new identity PK value, exactly like it's supposed to do. However, this line also is supposed to update the PK value on the child DataRows in the "Details" DataTable, which it does, but in the process it changes their DataRowState from "Added" to "Modified". This causes the next three lines to be useless since they only send DataRows with State = Added to the DataAdapter. (DataRows with State = Modified where sent a few lines before this lines). Hence, I only get the Order record inserted on the database, and all the details are skipped, resulting in a "widow" order with no items. What might be causing this behaviour? I thought I solved it changing the DataSet's relation rules to "Default" instead of "Cascade", but after a few tries that worked I'm getting the same behavior again. Just to be clear, what might cause an Update on the parent table to change the child DataRow states from "Added" to "Modified", hence preventing them to be INSERTed on the database? Thanks, Juan Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98785
  - 25
    - Record Count in ADO.NET This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C4C669.DBFB6D10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, When you run a select query how do you return the number of records in = the record set? IE:=20 Try Dim objCommand As New SqlClient.SqlCommand ("Select Field1, Field2 = on from Table", objConn) Dim objReader As SqlClient.SqlDataReader DBConnection.OpenConnection() objReader =3D objCommand.ExecuteReader iRecordsRtn =3D objReader.?=20 '''###Long time since I used ADO but I'm sure you could use = rs.RecordCount Catch err As Exception display error End Try TIA Darren ------=_NextPart_000_0009_01C4C669.DBFB6D10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DArial><FONT size=3D2><FONT face=3DArial=20 size=3D2></FONT></FONT></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>When you run a select query how do you = return the=20 number of records in the record set?</FONT></DIV> <DIV><FONT face=3DArial><FONT size=3D2><FONT face=3DArial=20 size=3D2></FONT></FONT></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>IE: </FONT></DIV> <DIV><FONT face=3DArial><FONT size=3D2><FONT face=3DArial=20 size=3D2></FONT></FONT></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Try</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    Dim objCommand As = New=20 SqlClient.SqlCommand ("Select Field1, Field2 on from Table",=20 objConn)</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    Dim objReader As=20 SqlClient.SqlDataReader</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   =20 DBConnection.OpenConnection()</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>    objReader =3D=20 objCommand.ExecuteReader</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    iRecordsRtn =3D = objReader.?=20 </FONT></DIV> <DIV><FONT face=3DArial size=3D2>'''###Long time since I used ADO but = I'm sure you=20 could use rs.RecordCount</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Catch err As Exception</FONT></DIV> <DIV><FONT face=3DArial size=3D2>    display = error</FONT></DIV> <DIV><FONT face=3DArial size=3D2>End Try</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>TIA</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Darren</FONT></DIV> <P><FONT face=3DArial size=3D2></FONT> </P></BODY></HTML> ------=_NextPart_000_0009_01C4C669.DBFB6D10-- Tag: auto-populate SQLCommand.Parameters collection based on stored-procedure Tag: 98783

if in VB.NET or C# , if I associate a SQLCommand object with a specific
stored-procedure on a specific database on a specific sql-server ....

is there a way to trigger auto-population of the SQLCommand.Parameters
collection ? (System.Data.SQLClient.SQLParameterCollection)

so ... each SQLParameter would have its various properties populated based
on the stored-procedure declaration ?

Top