Problem adding records with SQLDataadapter to SQL Server database

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - something about stored procedure not returning the result I have succesfully passed parameters to the stored procedure... but i keep getting no rows. I notice that if I generate a dataset with the wizard it creates a 'ReturnValue' parameter Do I need to be using this @ReturnValue parameter somehow? If I preview the dataadapter, I get results. But when I use the following code, I get no results: (further below is the stored procedure) SqlConnection1.Open() 'daMembers_SQL.SelectCommand.CommandType = CommandType.Text 'daMembers_SQL.SelectCommand.CommandText = strMemberSQL If Len(Session("ssMRN")) <> 0 Then daMembers_SQL.SelectCommand.Parameters("@MRN").Value = Session("ssMRN") End If If Len(Session("ssNAME")) <> 0 Then daMembers_SQL.SelectCommand.Parameters("@MemNAME").Value = Session("ssNAME") End If If Len(Session("ssDOB")) <> 0 Then daMembers_SQL.SelectCommand.Parameters("@DOB").Value = Session("ssDOB") End If If Len(Session("ssSSN")) <> 0 Then daMembers_SQL.SelectCommand.Parameters("@SSN").Value = Session("ssSSN") End If If Len(Session("ssSEX")) <> 0 Then daMembers_SQL.SelectCommand.Parameters("@SEX").Value = Session("ssSEX") End If daMembers_SQL.Fill(dsMembers, "qMemberSelect") 'Stored procedure ================================ CREATE PROCEDURE qMemberSelect @MRN int = NULL, @MemNAME nvarchar(40) = NULL, @DOB datetime = NULL, @SSN nvarchar(9) = NULL, @SEX nvarchar(1) = NULL, @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = 'SELECT [MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT] FROM dbo.MEMBERSHIP AS Y WHERE 1=1' if @MRN IS NOT NULL SELECT @sql = @sql + ' AND MRN = @xMRN' if @MemNAME IS NOT NULL SELECT @sql = @sql + ' AND MemName like @xMemNAME + ''%''' if @DOB IS NOT NULL SELECT @sql = @sql + ' AND DOB = @xDOB' if @SSN IS NOT NULL SELECT @sql = @sql + ' AND SSN = @xSSN' if @SEX IS NOT NULL SELECT @sql = @sql + ' AND SEX = @xSEX' if @debug = 1 PRINT @sql SELECT @sql = @sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT], [THRU-DT] HAVING len([THRU-DT])= 0 Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From dbo.Membership As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN From dbo.Membership As Z Where len(Z.[THRU-DT])=0)) ORDER BY MemNAME, [FROM-DT] DESC' SELECT @paramlist = '@xMRN int, @xMemName nvarchar(40), @xDOB datetime, @xSSN nvarchar(9), @xSEX nvarchar(1)' EXEC sp_executesql @sql, @paramlist, @MRN, @MemNAME, @DOB, @SSN, @SEX Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122996
  - 2
    - ADO.NET Update SQL View I have created a view in SQL server 2000 that joins two one-to-one tables. This view is updateable from SQL enerprise manager as all of the keys are present to perform updates. In VB6, I could simply create an ADO Recordset that queries this view, make the recordset optimistic, and updates would be performed automatically when the .Update method was invoked. However, in ADO.NET this appears to be impossible. I have created a stored procedure that queries this view and bound the resulting dataset to a grid. MY issue is I cannot make this dataset updateable. Obviously, the command builder will not generate the Update statement because there is a table join. I have tried creating a stored procedure fot the update but am receiving an error - which gives me no indication as to what the actual problem is. I cannot create two adaptors because I need to bind he dataset to a single grid. What is the solution? It seems as though ADO.NET is a huge step backward from ADO. Any help would be greatly appreciated Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122984
  - 3
    - too many arguments specified... I have an ASP.NET app that searches for members using 5 parameters. I finally wrote a nifty Stored procedure to account for all or anything in between - parameters - that could be used out of 5. And the stored procedure works fine while using Query Analyzer in SQL Server... but now in trying to implement it using session variables because the textboxes for the parameters are on a different page: I get a "too many arguments specified" error when trying to implement this code in VB.NET/ADO.NET - how can I make sure it works? (this is the code I have on the Results page which is a DataGrid of the result from whatever the use selected on the search page) daMembers_SQL.SelectCommand.Parameters.Add("@MRN", SqlDbType.Int).Value = Session("ssMRN") daMembers_SQL.SelectCommand.Parameters.Add("@MemNAME", SqlDbType.NVarChar, 40).Value = Session("ssNAME") daMembers_SQL.SelectCommand.Parameters.Add("@DOB", SqlDbType.DateTime).Value = IIf(Len(Session("ssDOB")) = 0, DBNull.Value, Session("ssDOB")) daMembers_SQL.SelectCommand.Parameters.Add("@SSN", SqlDbType.NVarChar, 9).Value = Session("ssSSN") daMembers_SQL.SelectCommand.Parameters.Add("@SEX", SqlDbType.NVarChar, 1).Value = Session("ssSEX") 'daMembers_SQL.SelectCommand.Parameters.Add("@Debug", SqlDbType.Bit).Value = 0 daMembers_SQL.Fill(dsMembers, "dbo.qMemberSelect") Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122976
  - 4
    - Email message using MS Sql Server All, What is the simplest way to send an email message through the MS SQL Server? There was a method to send email messages using a standard C# way via SMTP server, but the required port was blocked on our server for the security reasons. So there is the only one method that I see to send my messages. The idea was to send these messages about some critical events, exceptions, etc., happening with our web application. The mechanism was already written and working but I need to switch to the MS SQL Server to get it working again. Can anybody give any idea, or code, or links? I know that this feature should be built into the MS SQL Server. Thanks, Just D. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122975
  - 5
    - Selecting, Inserting and Updating Relational Data (SQLServer/ADO.NET) Dear Group, I have a heirarchical set of database tables, say - "order" and "order_type" and want to display a series of orders in a grid control, and in place of the order_type foreign key identifier, I would like a dropdown combo box (lookup from the "order_type" table) to change the type of the order. I also need an update command button, a delete row button and also an insert new row button. I'm sure this is a very common design pattern, although I can't find any good tutorials anywhere. Can someone point me in the right direction, as I want to get the design pattern right from the very beginning. Also a couple of specific questions: 1) If I use a typed dataset with a SQL query to return the rows, and databind this to the grid control - how can I override/load the order_type column to load a combobox lookup dropdown? 2) If I use a typed dataset with a SQL query and the relations set, bound to the grid control - how can I change the WHERE clause dynamically at runtime? Since I'm building the dataset at designtime, I can't see how I can restrict the amount of data queried in memory (example: WHERE customer_name = "bob"). I can do all of the above in ADODc, and with careful use of recordsets; but I'm sure ADO.NET would be faster for this job! I'm using ADO.NET 2.0, VB2005 (Professional Edtn) and SQL Server Express. Many thanks for your help! Mike Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122974
  - 6
    - SQL Server processes and ADO.NET Hello, I have a web application accessing SQL Server 2K through ADO.NET The application is working fine, but while monitoring the processes in SQL Server Enterprise Manager, I noticed each user connecting to the application creating a NEW process in "awaiting command" state. I would like to know if this is normal, do "awating command" processes represent connections left in an open state? Thank you Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122968
  - 7
    - How to initialice a default value for a column in a typed DataSet Hi all... I have a typed dataset that corresponds to a SQL Server database model. I have in a table a datetime column that cannot be null, but it has a default value of getdate(). How can I set the default value for the corresponding DataSet table? I tried using getdate() or DateTime.Today for the column DefaultValue property but without success.. Any help will be greatly appreciated Thanks Jaime Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122966
  - 8
    - dataset.writexml different output depending on overload param type Why when using Dataset.WriteXml("C:\somefile.xml") is there a xml declaration added to the top of the xml document created. However, when using Dataset.WriteXml(StreamWriter) there is no xml declaration? The same dataset is used in both WriteXml. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122964
  - 9
    - Where are the IsNull methods on my DataSet Usually, when I create a typed DataSet using the Visual Studio designer, IsColumnNameNull methods are automatically generated for each column in my tables. However, this time these methods were not created. I can not figure out how to get them. Any advice? Thanks. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122963
  - 10
    - Trouble with DefaultValue on Typed DataSet Using the Visual Studio designer, I can easily create a typed dataset. It is possible to set default values for table columns by setting the default in the designer. I can do this easily for boolean and integer types, but if I have a string field and I want to set the default to "", I can not find a way to do this. If I go into the auto generated cs file and make the change, it gets wiped out if I ever make any other changes in the designer. Is there a way to set the DefaultValue of a string column to be empty string for typed datasets created using the Visual Studio designer? Thank you. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122960
  - 11
    - parameter question Is it possible to use a parameter in conjuntion with an IN statement? For example, if I have the following SQL, select * from table where key in ('1','2','3') How can I make the values in the IN be a param? If I do the following. select * from table where key in (@invals) I can't make a parameter like so, param = new sqlparameter("@invals","'1','2','3'") It simply returns no results. Is there a way to make a parameter that will work? Thanks Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122959
  - 12
    - Remoting/Web service/sqlClient performance Hi, In our project we are using sqlClient to communicate with the database server(SQL Server 2005). It's running great. Our project is doing lots of sql queries. We are planning to put our code in the web server or a remote server and use remoting to do the query. I want to know, is it going to effect the performance if we do like this. What other problems we might face doing like this. We need some experts comments and suggestion before we perform this task. Thanks Kim Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122956
  - 13
    - Deleting rows in dataset How can i delete a row in dataset based on the column value, i have four columns in a row and i want to delete the row based on the column1 value. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122951
  - 14
    - Access queryDef as stored procedure in the 'Access Cookbook' by Getz, Litwin and Baron, it shows how to get a qryAlternativeAlbums query as an OleDbDataReader: 'Construct an OleDbCommand to execute the query Dim AltRock as OleDbCommand = _ New OleDbCommand("qryAlternativeAlbums", cnx) 'Odd as it may seem, you need to set the CommandType 'to CommandType.StoredProcedure. cmdAltRock.CommandType = CommandType.StoredProcedure 'Run the query and place the rows in an OledbDataReader. Dim drAltRock as OleDbDataReader drAltRock = cmdAltRock.ExecuteReader 'Bind the OleDbDataReader to the DataGrid dgrAltRock.DataSource = drAltRock dgrAltRock.DataBind() As an Access developer, this was exciting news, as I thought I presently had no way to use joins to Union queries, etc... Anyhow, I was wondering if I can use the DataAdapter in the same fashion? Please help me get any kinks out if this is possible. And if not, how can I parameterize a DataReader - is this possible? Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122946
  - 15
    - Updating - let me count the ways There are several ways of updating a database through ADO.Net 2. In my case, I'm interested in using queries to update my Access data. I'm not sure what all the pros and cons are for each method. Below I've listed what I know, I'd welcome some discussion. And some correction on the 'terms' if required For my examples, I have a table called Weeks. This table has a boolean field, PastWeek. I wish to set this to true for all weeks prior to a supplied week number. Which one do you use? Whats the fastest to run? Thanks Vayse __________________________________________________________________________ 1) TableAdapter Where one opens the xsd file, right clicks on the table adapter, and clicks on Add Query. For my example, I create a query called SetPastWeeks, as follows UPDATE Weeks SET PastWeek = True WHERE (WeekID<= ?) To use this in my code, I would do Me.WeeksTableAdapter.SetPastWeeks(lNewWeekID) Pros: Easy to setup Easy to test - you can run the query in the xsd window. (what is the correct term for xsd window?) Its easy to use Cons: I guess its slower, but I don't know for sure. __________________________________________________________________________ 2) Commands and Dymanic SQL Using connection As New OleDbConnection(stConnection) connection.Open() Dim stSQL as string = "UPDATE Weeks SET PastWeek = True WHERE WeekID<= " & lNewWeekID Dim command As New OleDbCommand(stSQL, Connection) Dim lRows = command.ExecuteNonQuery() End Using Pros/ Cons: I'm not sure! __________________________________________________________________________ 3) Commands and Access Query That is, create the query in Access, then run it via your code. Now that I think about it, I'm not sure how to pass a parameter to such a query! Anyway, running it should be the same at the Dymanic SQL. I don't know what advantages this has over method 2, but it can make your code trickier to debug. __________________________________________________________________________ 4) DataAdapter To keep the code short, I won't add the parameters to the command. Dim dbConn As New OleDbConnection(stConnection) dbConn.Open() Dim stSQL As String = "SELECT * FROM Weeks" Dim daWeeks As New OleDbDataAdapter(stSQL, dbConn) stSQL = "UPDATE Weeks SET PastWeek = True WHERE WeekID <= 9" Dim command As New OleDbCommand(stSQL, dbConn) daWeeks.UpdateCommand = command daWeeks.UpdateCommand.ExecuteNonQuery() Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122939
  - 16
    - Newbie Client-Server select Query Question It might be a very basic question, but I don't the answer. Please clear my boubt. From the client system when we execute "select * from Employee" where does the execution takes place. Does it execute it at server or at client. Do we need to have a high power client to do any queries. Please Help, Andy Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122936
  - 17
    - Can't open two DataReaders to the same connection I don't know if I am outdated or is my grouse still valid. I am trying to connect two concurrent DataReader objects to the same connection, but the attempt fails with the error "Another DataReader is already open, it needs to be closed.. bla..bla.." Using good old ADO I could connect as many Recordsets I wanted through one connection, so how come ADO.Net is not allowing me to connect two DataReaders? I think I read somewhere that this can be done through .Net Framework 2.0. Is this true? How stable is .Net 2.0? Are there any big issues that I should know about? My application will be distributed as a packaged software, so it should be as stable as possible. Please do send me your ideas. Thanks Cyril Gupta Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122924
  - 18
    - Specify alternative base class for strongly-typed DataSet Our application, currently in .NET 1.1, uses strongly-typed DataSets as business objects. These have been created using a third-party generator (from AdoGuy.com) which reproduced the standard Microsoft STD code, but added a couple of extra things to make deriving from the generated DataSet/Table/Rows work properly. The generator was open-source, and I modified it to allow me to specify different base classes for the DataSet/Table/Rows, so I could add common functionality there-in. So now we want to move the project into 2.0, and the original motive for the alternative generator no longer exists, because STDs are declared as partial, so business logic can be added in a separate file. But I now have a lot of code in my alternative base classes, which would have to be duplicated in each and every DataSet/Table/Row. We've got half a dozen DataSets, with an average of eight tables in each, and I expect to create another half a dozen over the next year, so that'd be a lot of work. (Of course, when C# 3.0 appears, I could just use an extension method, but I can't really wait another two-to-three years...) What I'd like to do is find a way of changing the base classes in the generated code as easily as possible. Does anyone know of a third-party DataSet generator that will let me do this? Alternatively, how feasible would it be to write a VS2005 plug-in to change the base classes in the generated code, preferably automagically after the MS generator runs when the XSD is saved, but manually if necessary? I've also considered writing a plug-in to generate an additional partial class with the extra code in, but that would inflate the classes quite a lot. Or are there any other suggestions for a good way to achieve this? Any thoughts would be much appreciated. Oh, and Happy New Year. Mark Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122923
  - 19
    - SqlServer Express, Firebird & stored procedure question I want to toy with an embeded database for shareware application. I would like to know better SqlServer as well. However, correct me if I'm wrong, but it seems that as much as I would like to play with SqlServerExpress it seems higly unfit to this kind of application for the following reasons: - +20Mb to the install plus strange question to answer (for the clueless user) - Even though SqlServer express is installed your consumer application could fail if the service is, for some reason, off. - Even though SqlServer express is up and running you might not have connect access right to it, or right to create a new (file based) database (if need to), or maybe the server name is not what you expect and it can't be guess (or could 127.0.0.1 be used?) Because of these problem I was thinking to use Firebird (real, embeded, file based, database engine). Is the Procedural SQl language of Firebird the same as SqlServer express? Would it grows my SqlServer understanding to play with Firebird? Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122919
  - 20
    - Problem OLEDB Oracle with .Net 1.0 - Exeption when i have date fie Hi, We have one application that return the exception below, when retrieve any date fields: "Specified argument was out of the range of valid values. Parameter name: Hour, Minute, and Second parameters describe an unrepresentable DateTime." If change from OLEDB to Oracle Provider the exception disappear, however we canÂ´t change this application in this time. This application use .NET 1.0, Oracle 9.2.0.7.0 and OLEDB Provider. Could anyone help us show any solution or article for this problem? (Therefore i need show in my job) Thanks Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122918
  - 21
    - data access block for ADo.NET 2.0 Does anyone know when the data access block for ADO.NET 2.0 will be released? (or has it - and i cant find it?) guy Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122917
  - 22
    - Newbee: Help on child-parent relation and binding to Forms Hello, i'm a newbee to ADO.Net. I need to create a Form that contains values of a Childtable 'artikel' and the corresponding value of the field 'lieferant.firma' which connects through a FL-constraint to the table 'lieferant'. I tried to bind the realtion lieferantartiekel as the datamember to the Combobox. Doesn't work. Is there any hint how to solve this problem ? Thanks a lot. Thomas Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122911
  - 23
    - Deleting a bound DataGridView row? I have a DataGridView which is bound to a dataset. When I delete rows from the DataGridView, should it delete the same rows from the dataset also? I have tried the following code, but apparently, the dataset isn't updated. Did I miss (understood) something? for(int i = 0; i < dataGridView.SelectedRows.Count; i++) { DataGridViewRow row = dataGridView.SelectedRows[i]; dataGridView.Rows.Remove(row); } Debug.WriteLine(myTypedDataset.myTypedDataTable.Rows.Count.ToString()); // Here, the count is the same as before the deletion, but when // accessing the dataTable rows, a // DeletedRowInaccessibleException occurs on the deleted rows Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122910
  - 24
    - Connect to Foxpro Data (*.DBF) in VB.Net Dear All We are currently write a program to "Transfer" Data from old system. How can i connect to a set of DBF files by What is the connection string to connect the DBF? I have try this one but error. connstr = "Provider = VFPOLEDB;Data Source=" & "C:\ESS2001\DATA\" I have the following question? 1. Does [OleDBConnection] Object support DBF Files? or i should use [ODBCConnection] ? 2. Should I it a must to have a "*.dbc" file ? 3. I need to use ADO instead of ADO.Net in VB.Net ? Thank you for your help in advance! -- EricLun Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122902
  - 25
    - Use ADO.NET to keep data between client and server in sync Hi, my server has a database table. I read this table on the server into a dataset and my client retrieves it through a webservice. No changes on the client and server happen. What's the best way to keep the data in sync? I want to avoid that I transfer the entire dataset all the time. Is there feature of ADO.NET which I can use to find changes and send just them? Thanks for any hint Anthony -- Anthony Malt Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122900
- Next
  - 1
    - Random 'Invalid Row Handle' This is a multi-part message in MIME format. ------=_NextPart_000_0203_01C61032.6F29FDF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I am facing a weird problem using the DataReader. This is what my code looks like. --------------- Dim dbCommand As OleDbCommand Dim dReader As OleDbDataReader dbCommand =3D New OleDbCommand("SELECT * FROM CmpDet where = cmpDet_CompanyID =3D " & CurrentCompany.CompanyID, DBCompany) dReader =3D dbCommand.ExecuteReader 'Retrieve info from the database dReader.Read() 'Read the record 'Load the company details Try CurrentCompany.Address =3D dReader.Item("cmpDet_MailAddress") CurrentCompany.CompanyName =3D dReader.Item("cmpDet_CompanyName") CurrentCompany.City =3D dReader.Item("cmpDet_CityName") CurrentCompany.State =3D dReader.Item("cmpDet_State") Catch ex As Exception MsgBox(ex.Message) Stop End Try 'Empty the objects dReader.Close() dbCommand.Dispose() dReader =3D Nothing dbCommand =3D Nothing --------------------- My problem is that on the dReader.item("cmpDet_MailAddress") line (When = I try to read the contents of the dReader object). I sometimes get an = error 'Invalid Row Handler'=20 This error does not occur all of the time, only some of it. Could it be = because the records that the dReader retrieves were added to the db in a = line just above this code block? I am using a dbCommand object with an = "INSERT INTO" query to add the contents to the DB so I expect that there = should be no problems retrieving data. This error only occurs some of the time, and at other times it runs = fine. Any clues why this could be happening? Thanks Cyril Gupta ------=_NextPart_000_0203_01C61032.6F29FDF0 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.2802" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DVerdana size=3D2>Hello,</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana size=3D2>I am facing a weird problem using the = DataReader.</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana size=3D2>This is what my code looks = like.</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana size=3D2>---------------<BR></FONT><FONT = face=3DVerdana=20 size=3D2>Dim dbCommand As OleDbCommand<BR>Dim dReader As=20 OleDbDataReader</FONT></DIV> <DIV> <P><FONT face=3DVerdana size=3D2>dbCommand =3D New OleDbCommand("SELECT = * FROM CmpDet=20 where cmpDet_CompanyID =3D " & CurrentCompany.CompanyID, = DBCompany)<BR>dReader=20 =3D dbCommand.ExecuteReader 'Retrieve info from the = database<BR>dReader.Read()=20 'Read the record</FONT></P> <P><FONT face=3DVerdana size=3D2>'Load the company=20 details<BR>Try<BR> CurrentCompany.Address =3D=20 dReader.Item("cmpDet_MailAddress")<BR> CurrentCompany.CompanyName = =3D=20 dReader.Item("cmpDet_CompanyName")<BR> CurrentCompany.City =3D=20 dReader.Item("cmpDet_CityName")<BR> CurrentCompany.State =3D=20 dReader.Item("cmpDet_State")<BR>Catch ex As=20 Exception<BR> MsgBox(ex.Message)<BR> Stop<BR>End = Try</FONT></P> <P><FONT face=3DVerdana size=3D2>'Empty the objects</FONT></P> <P><FONT face=3DVerdana = size=3D2>dReader.Close()<BR>dbCommand.Dispose()<BR>dReader =3D=20 Nothing<BR>dbCommand =3D Nothing</FONT></P> <P><FONT size=3D2><FONT = face=3DVerdana>---------------------</FONT></FONT></P> <P><FONT face=3DVerdana size=3D2>My problem is that on the=20 dReader.item("cmpDet_MailAddress") line (When I try to read the contents = of the=20 dReader object). I sometimes get an error 'Invalid Row Handler' = </FONT></P> <P><FONT face=3DVerdana size=3D2>This error does not occur all of the = time, only=20 some of it. Could it be because the records that the dReader retrieves = were=20 added to the db in a line just above this code block? I am using a = dbCommand=20 object with an "INSERT INTO" query to add the contents to the DB so I = expect=20 that there should be no problems retrieving data.</FONT></P> <P><FONT face=3DVerdana size=3D2>This error only occurs some of the = time, and at=20 other times it runs fine.</FONT></P> <P><FONT face=3DVerdana size=3D2>Any clues why this could be = happening?</FONT></P> <P><FONT face=3DVerdana size=3D2>Thanks<BR>Cyril=20 Gupta</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_0203_01C61032.6F29FDF0-- Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122887
  - 2
    - SQL Server Starting Points Greetings - Several of you were helpful in disuading me from using Jet in a prior post. I'm developing an application that needs a "person manager" attached to it, with typical interfaces like "add person" "edit person" "get list of people" plus some custom items. A few questions. I'm experienced in relational database design, access and .net, but not at all in sql server: 1. How steep is the learning curve on SQL Server? 2. Any suggestions for good materials on getting started (most of the documentation looks geared to those who are proficient with prior versions) 3. Any suggestions for how I can find a third party to get me started on this -- most of the solution providers listed online seem to be looking to do large turnkey packages. I'd be looking more for some part time development and mentoring consulting to get this launched and then back in my hands. Any ideas where I can find this (I'm just outside of NYC, fwiw). Thanks for any pointers/suggestions! kc Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122883
  - 3
    - Copy just some rows from a datatable to another datatable. I have a datatable with a field in it that tells me where the data belongs (let's say the values are 'A' and 'B'). I want to split the table into two other tables where in one there are only the 'A' records and the other one has only the 'B' records. What's the easiest way to do it? I looked at a dataview but it has no copy, it has copyto. TIA - Jeff. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122870
  - 4
    - DataSet.Clear required before loading new data? Is a DataSet.Clear required before new data is brought into the dataset? I.E. daMyDataset.Fill(dsMyDataset) Can I simply refill the dataset with new data intended to totally replace the old data, or must I do a .Clear first? Thanks Rick Lederman Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122869
  - 5
    - cool list of threads for clearing Software Interviews .NET Interview Questions http://blogs.crsw.com/mark/articles/2542.aspx http://www.geocities.com/dotnetinterviews/ http://blog.daveranck.com/archive/2005/01/20/3553.aspx http://www.dotnetspider.com/technology/exams/ExamsCategory.aspx?CategoryId=3 http://techinterviews.com/ http://www.certfaq.com/employment/e011.html SQL Server Interview questions http://vyaskn.tripod.com/microsoft_sql_server_articles.htm Java Interview questions http://www.allapplabs.com/interview_questions/java_interview_questions.htm Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122845
  - 6
    - 2nd layer of filtering? I have a paramaterized command for a data adapter SELECT MRN, Name, Sex, DOB, SSN FROM dbo.Membership WHERE (MRN = @MRN or @MRN is null) AND (Name like @Name or @Name is null) AND (Sex = @Sex or @Sex is null) etc. The name of that result set is from a stored procedure called: qMembershipSelect for 1.4 million records, this ensures that the data will come up quickly because all I'm doing is filtering between 1-4 parameters. But after the first select is done, I'd like to apply additional filtering to that result set that weeds out other stuff like duplicate records and selects a true Current member: SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT] FROM qMembershipSelect AS Y GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT] HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN From qMembershipSelect As Z Where Z.[THRU-DT] Is Null)) ORDER BY MemNAME, [FROM-DT] DESC; Obviously this 2nd layer of filtering wouldn't be so tedious to a smaller dataset as it would to 1.4 million records at the start. Are you able to do this in ADO.NET with a DataTable?... if not, how do you go about it? In DAO, what I would do is write the First Select Statement qdf.SQL = "Select...." That would become qMembershipSelect and then refer to the rewritten query as if it were a table. it would be nice if qMembershipSelect could be referred to with this complexity with another dataadapter? I don't see in ADO.NET being able to write complex SQL statements that refer to a DataTable (equivalent to a query in Access)? Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122842
  - 7
    - Dataadapter produces error: Syntax error in INSERT INTO statement The code below loads an Access table with one row. It then adds a row and displays all this in a datagridview. So far so good. But when I use the dataadapter's update command to update the database table, I receive the following error: Syntax error in INSERT INTO statement The Insert statement as displayed by the debug,write line is: INSERT INTO Main (Id, Interval, Tutor1, Tutor2) VALUES (?, ?, ?, ?), which looks fine to me. Where is the problem? The Commandbuilder should have no problems with this simple setup. The code: Dim ds2 As New DataSet Dim da2 As New Data.OleDb.OleDbDataAdapter Dim dc As Data.OleDb.OleDbConnection = New OleDb.OleDbConnection dc.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test2.mdb" dc.Open() Dim StrSQL As String = "SELECT * from Main" Dim objCommand As New Data.OleDb.OleDbCommand(StrSQL, dc) da2.SelectCommand = objCommand Dim CB2 As Data.OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da2) Debug.Write("CB: " & CB2.GetInsertCommand.CommandText) 'What it writes looks fine to me da2.Fill(ds2, "Main") Dim NewRow As DataRow = ds2.Tables(0).NewRow NewRow.Item("ID") = 254 NewRow.Item("Interval") = 22 NewRow.Item("Tutor1") = "Joe" NewRow.Item("Tutor2") = "Mary" ds2.Tables(0).Rows.Add(NewRow) Me.DataGridView1.DataSource = ds2.Tables(0) MsgBox("Got this far") da2.Update(ds2, "Main") 'Here is where the error occurs. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122841
  - 8
    - Code reuse and DataSet generation from XML schema Hello, I have several strongly typed DataSet classes that encapsulate the same table. My challenge is that I can't reuse the wrapper code for this table generated by Visual Studio, because although wrapper classes represent identical structures, they have different identities. If I have two strongly typed DataSets (DataSet1 and DataSet2) and both of them wrap a table MyTable, VisualStudio duplicate the code in .cs files, i.e. I get DataSet1.MyTableRow class and DataSet2.MyTableRow, so it is not possible to have a common method DoSomethingWithMyTableRow(MyTableRow row) - I have two different identities for MyTable row structures. Does anyone know an approach for code reuse in such cases? Thanks in advance Vagif Abilov Oslo Norway Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122839
  - 9
    - Problem connecting to sql server Hi, I am using XP Pro SP2, VS.NET 2003 & IIS 5. I used to develop an ASP.NET application on my PC on my local IIS, connecting to a LAN server which hosts my MSSQL database. I have no problem running the web app locally. After I reformat and reinstall XP, I could not run the app anymore. The app could not find the SQL server. But I can connect to the server via Enterprise Manager installed locally and browse the tables. And I can even ping the server. I have tried using both IP and servername but to no avail. However, my machine can connect when I tried using normal windows application, using the same connection string. I think I am missing something bcos I used to have the same problem last time. But I forgot what is needed to be done on my machine to make it work. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122825
  - 10
    - use sql mdf from application folder Hi, I need to deploy my simple windows application to client's pc so that the database it requires is packaged along with it in the application folder. I have done it through the setup project but after installation it throws exception of being unable to acess the database. Please note that the client does not need to have SQL server installed. I have MS SQL Server Express and Developer Edition both installed on my m/c. I am using SQLEXPRESS for database connection strin in my project. My question is, does the client need to have SQL server installed on their system? TIA. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122821
  - 11
    - I'd like to programatically move through a table I'd like to programatically move through a table (call it a recordset in ADO ) and disply fields in a textbox. I've gotten as far as the code below (which just loads a listbox) I assume I need to work with the dataadaptor , but have been unable to find info on the web as to how to do it - the hope is to be able to work , record by record,; something like the .movenext capacity in the recordset objects of DAO and ADO. myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\t.mdb") myConnection.Open() 'myCommand = New OleDbCommand("select * from main", myConnection) Dim myDataAdapter As New OleDb.OleDbDataAdapter( _ "Select * from main", myConnection) Dim myDataSet As New DataSet() myDataAdapter.Fill(myDataSet, "main") 'Dim myReader As OleDb.OleDbDataReader Dim DataViewManager1 As DataViewManager = myDataSet.DefaultViewManager ComboBox1.DataSource = DataViewManager1 ComboBox1.DisplayMember = "main.First" Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122820
  - 12
    - Problem using diffgram I'm use diffgarm xml (for update) which is sent with a schema with sqlxmlcommand whith sqlxml version 3 sp3. This mechanizem used ALL the parameters in the before xml as the parameters used in the where clause used by update commands to the data base. 1. Is there a way to limit the parameters used by the where clause only to spesific fields!!!? When I tried to send a diffgram which only contains some of the nodes deffined in the schema, the mechanizm automaticly fills the missing nodes with nulls and an update will use them with where clause with the value null change these nodes which were missing in before clause to null. 2. Is there a way only to send and change the sent data? Thanks, G. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122816
  - 13
    - Managing "Row ID" for Table Inserts Using c# and Visual Studio 2003, I've begun to wonder whether I'm adding rows to Oracle tables correctly. The tables often have "row ID" columns and, before adding a row, I pull the "max" ID value from a table, add 1 to it, and then use that number as the new "row ID" (the base table then gets updated, so the dataset and table are not long out of sync). I'm sure this approach is wrong, since, among other issues, the "max" ID returned could be wrong if, for instance, a recently-added row was deleted. I don't know, however, how else to do this. Should I instead use stored procedures? How can I add rows to my dataset tables and know what "row ID" the underlying table will expect during the row insert (especially with other users potentially hitting the database)? Thanks for advice. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122815
  - 14
    - SqlCeResultSet, why not in ADO.NET? I just discovered the SqlCeResultSet in the compact framework. It is more like ADO or DAO than anything else that I have seen as far as coding in a VB application goes. It appears to be very fast and efficient too! Is there any plan to place the resultset in ADO.NET? It just seems FAR easier to implement with fewer lines of code than creating a dataset. Rick Lederman www.soaringsoftware.com Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122812
  - 15
    - History of Data Access Hi, I'm a novice programmer. When reading stuff on ADO.Net I often face terms like OLE DB, ODBC. Is there anyone who knows the web links that can provide me the concepts like these (ODBC,OLE DB and other concepts related to Data Access) that can be understood easily by a novice like me. If anyone, please give me those links. Thanks in advance, Regards, Swat Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122790
  - 16
    - Generate DataSet in 2.0? I'm beginning to think Microsoft has spent the last few years revising .NET so as to break every single programming construct I've developed. Sheesh! Today, I'm trying to figure out where the Generate Dataset context menu option went in VS2005. I used to build quick and dirty config files -- which I could access through a Dataset -- by creating an xml file with dummy data, creating a schema from that xml file, converting the schema to a Dataset, and then selecting Generate Dataset. This created a "hidden" file containing an autogenerated class which I could then read my xml config data into via ReadXml(). Unfortunately, the Generate Dataset menu option has disappeared. Or been moved. And I can't find it. So how do I create a Dataset based on an existing xml file? - Mark Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122788
  - 17
    - Philosophical question: how often to submit updates to database A concept I've waffled on from time-to-time with ADO.Net: how often to submit updates back to the database. For example, I have some CF apps where it's quite obvious the *when* part of submitting the update -- soon as you come off of the record. For other desktop apps, it's not quite so obvious. For example, I have a CRM app where there are dozens of fields for one customer record, as well as many different datatables for sales, appointments, etc.. So, I wonder: submit the update every time a single change is made or submit it only when a different customer is selected? The downside to the update for every single change is the obvious network/server overhead; the downside for waiting on a customer change is what if the app gets closed/crashes, etc? I'm not looking for a *right* answer here, just some opinions. Thoughts, strategies, ideas from others would be appreciated. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122775
  - 18
    - Jet vs. Sql Server Express Greetings - I'm working on a new application and need to select a database for it. I was wondering if anyone had any views on tradeoffs between going with Jet/Access vs. SQL Server 2005 Express. The database is going to sit on a server. I'm planning to implement .net remoting from my client objects to a wrapper around the database, so the database will be transparent to the distributed client apps. I'll also access the database for ad hoc reporting and data manipulation. I'm experienced with Access but haven't used SQL Server, so I would tend to lean toward using Access/Jet. However, it seems most of the attention is on SQL Server in everything I see about VS 2005. Is Jet still getting attention? What are the pros/cons of SQL Server vs. Jet? Thanks for any help kc Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122772
  - 19
    - how do i connect to a remote database i have a website that i use to file all my clients information, the go online and they type the information. i'm using an microsoft access database. Now, i want to create a windows application that i can run whenever a want to transfer the new information that is on my website database to my local sql server database. I just want to know how do i make a connection to the database onlie using visual studio .net 2005 or if you have another option please let me know. Thanks. Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122771
  - 20
    - exception on : ra = myCommand.ExecuteNonQuery() (VB Exoress 20005) I get an exception on this line : ra = myCommand.ExecuteNonQuery() {"ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."} in this code: Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim ra As Integer myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\t.mdb") myCommand = New OleDbCommand("delete from main where last = 'freud'", myConnection) ra = myCommand.ExecuteNonQuery() MessageBox.Show("Records Deleted" & ra) myConnection.Close() whereas this code works fine : myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\t.mdb") myCommand = New OleDbCommand("select * from main", myConnection) Dim myReader As OleDb.OleDbDataReader myConnection.Open() myReader = myCommand.ExecuteReader Do Until myReader.Read = False MessageBox.Show(myReader("last")) Loop myReader.Close() myConnection.Close() Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122758
  - 21
    - Books on ADO.NET 2.0? I would like to get a good book on ADO.NET 2.0. I searched Amazon and found 2 books that are rated well: - Pro ADO.NET 2.0 (Expert's Voice) - Programming Microsoft ADO.NET 2.0 Applications: Advanced Topics However, I am looking to learn about the "new" way of working w/ data... TableAdapter, BindingSource, etc. From the descriptions of the books, it kind of seems like these topics are not covered well, or at all. So my question is, what is a good ADO.NET 2.0 book that covers TableAdapters, BindingSources, etc.? Thanks! John Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122754
  - 22
    - dataset columns with system.dbnull Hi everyone(happy new year!!), Well, here we go... I have a problem with datsets, ado.net as i understand ..I have a sp that in query analizer retrieve 13 columns and the last 3 like the others have data, but when i see the dataset which gets the data the last columns are system.dbnull, and here after several tests, i am begging your help guys...did anyone have this problem???? i ll be thankfull really for any help u can post... Thanks in advance... Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122752
  - 23
    - Creating Schema In ADO.Net, can I create "create table ( ... )", "alter table ..." stuff, and execute it to create my schema for MS-SQL2000? Jack Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122751
  - 24
    - How to use backgroupworker to load dataset? As the existing application is running very slow when start application, How to use backgroupworker to load dataset? Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122750
  - 25
    - SqlExpress & C# problem in an ASP.NET web site I'm trying to create a SqlServer express database file on the fly in a web application.. (mhh.... maybe I should try to get something running in a desktop application first?) Anyway I want to connect to a database file and, if it doesn't exist, create the DB on the fly. Actuall I'm trying to create the connection like that: I'm trying to create a connection like that: === public static SqlConnection GetConnection(string filename) { string connString = @"Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|{0}.mdf;Integrated Security=True;User Instance=True"; connString = string.Format(connString, filename); SqlConnection sqlc = new SqlConnection(filename); // <= exception here return sqlc; } === It fails with "Format of the initialization string does not conform to specification starting at index 0." mmh.. is it because the file doesn't exists? How am I to connect to SqlServer to run the SqlSever creation script if my file doesn't exist? Should I embed an empty database as a resource in my control library if I want to automatically create the database? Or is there an other way? (I have the creation Sql script ready, I just wonder how to run it).... Tag: Problem adding records with SQLDataadapter to SQL Server database Tag: 122741

Hi,

I'm building an application, to edit data on a SQL Server database.
Database has a table Jobs with primary key (JobID) with identity seed = 1
and identity increment = 1

I'm using SQLConnection and SqlDataAdapters, DataSet, datagrid and DataViews
Dataset represents the SQL database.

When i add a record to the DataSet(Jobs), there is a Primary key generated.
So for example the datagrid on my form shows the following:

JobID JobName JobDescription
1 test1 This is the first job record
2 test2 This is the second job record
3 test3 This is the third job record

When i click add i add a new record to the dataset.
The primairy key is generated (not by my code) as JobID = 4.
But my JobID on SQL server (because i've added several records and deleted
them) is 15. When i click save my SQLDataAdapter is updating the SQL server
and adds the record (through stored procedures) without supplying a JOBID.
When the save is done i have 2 extra records. One is a record wich only
exists in the dataset with JobID = 4 and one new record from the SQL server
with JobID = 15

This is not expected behaivior... Anyone have some ideas?

Help would be greatly appriciated, thanks in advance

Rob Smeets

Top

Re: Problem adding records with SQLDataadapter to SQL Server database by Miha

Miha
Wed Jan 04 08:10:30 CST 2006

Hi Rob,

First thing to do is to use negative values for both seed and increment.
So the new records won't interfere with old ones.
As per duplicates, this is (annoyingly) correc: see if this helps you
http://support.microsoft.com/kb/313540/en-us

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Rob Smeets" <RobSmeets@discussions.microsoft.com> wrote in message
news:7826CD22-63BC-4BDD-A864-C5D1C22217CC@microsoft.com...
> Hi,
>
> I'm building an application, to edit data on a SQL Server database.
> Database has a table Jobs with primary key (JobID) with identity seed = 1
> and identity increment = 1
>
> I'm using SQLConnection and SqlDataAdapters, DataSet, datagrid and
> DataViews
> Dataset represents the SQL database.
>
> When i add a record to the DataSet(Jobs), there is a Primary key
> generated.
> So for example the datagrid on my form shows the following:
>
>
> JobID JobName JobDescription
> 1 test1 This is the first job record
> 2 test2 This is the second job record
> 3 test3 This is the third job record
>
>
> When i click add i add a new record to the dataset.
> The primairy key is generated (not by my code) as JobID = 4.
> But my JobID on SQL server (because i've added several records and deleted
> them) is 15. When i click save my SQLDataAdapter is updating the SQL
> server
> and adds the record (through stored procedures) without supplying a
> JOBID.
> When the save is done i have 2 extra records. One is a record wich only
> exists in the dataset with JobID = 4 and one new record from the SQL
> server
> with JobID = 15
>
> This is not expected behaivior... Anyone have some ideas?
>
> Help would be greatly appriciated, thanks in advance
>
> Rob Smeets

Top