Problem accessing SQL Server using Windows Authentication

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
 - Optimistic Concurrency - DataRow.Column Error Property Not Set I'm implementing an optimistic concurrency model within a VB.net application which makes use of a SQL Server 2000 database. I'm able to trap concurrency errors caused by an Update stored procedure, and can easily work with the DataRows which have errors, but cannot figure out why the ColumnError properties are not set. Using the DataRow.GetColumnsInError or DataRow.GetColumnError(column) methods return ziltch. The DataRow simply has a "concurrency error, the update effected 0 records" error. I test for concurrency issues by including all editable fields within the WHERE clause of the UPDATE stored procedure. I'm also using a DataAdapter to call the appropriate Select, Insert, Update, and Delete stored procedures. My DataAdaptor is set to continue updates when errors are encountered. I guess that I don't understand which object is responsible for setting the column error property, and when it's supposed to happen. Any help would be appreciated. I can work around this by comparing row version values, but knowing that there is an easier way is eating away at me. - CPatrick_530 Tag: Problem accessing SQL Server using Windows Authentication Tag: 114988
 - 2
 - Error when updating database using the dataadapter.update method I get an Error when i try to update a row in a table using the dataadapter.update method. The table in this case is only two columns, the 1st col is the key (bucket_status) and the 2nd column is a string (Comments) and can be null. this is a parent table to another child table, my data base contains 7 tables that are all related. when a change is done to the data in the datagrid, i extract the changed records from the dataset, then i pass the new dataset (with the changed rows) to the dataadapter.update method. When i try to change the value of the comments column, i get the following error: "[DB2/LINUX390] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610" The SQL code for the dataadapter was auto generated and it looks like this: UPDATE dbname.Bucket_tbl SET BUCKET_STATUS = ?, COMMENTS = ? WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS IS NULL) my code is fairly simple: daBucketStatus.Update(dsChanges) This is a DB2 database and i am using an OleDb connection. * inserts and selects work ok, problem is with the update and delete methods. I can run the generated SQL code in the command window with the values substitued for the parms and it works ok, however, the update method always returns the above error?!! Please help! Thanks Riad Amro Tag: Problem accessing SQL Server using Windows Authentication Tag: 114984
 - 3
 - MySql ado.net connector problem My host doesn't offer SqlServer, so I'm taking a swing at MySql and so far I'm getting a FileNotFoundException in my asp.net app when I try to instantiate a MySqlConnection object. I have added a reference to the dll and including the namespace in my using directive. Here is the stack trace: [FileNotFoundException: The system cannot find the file specified.] [FileNotFoundException: File or assembly name 'MySql.Data, Version=1.0.4.20163, Culture=neutral, PublicKeyToken=c5687fc88969c44d', or one of its dependencies, was not found.] TheHudsonDrags.BackEnd_Components.CDAL..ctor() in e:\files\web sites\thehudsondrags\web_root\backend_components\dal.cs:23 TheHudsonDrags.Controls.News.Page_Load(Object sender, EventArgs e) in e:\files\web sites\thehudsondrags\web_root\controls\news.ascx.cs:22 System.Web.UI.Control.OnLoad(EventArgs e) +102 System.Web.UI.Control.LoadRecursive() +45 System.Web.UI.Control.LoadRecursive() +130 System.Web.UI.Control.LoadRecursive() +130 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952 I really don't see the problem. I have added the reference, so how does it not know where it is? Confusing. Any help appreciated, Steve Tag: Problem accessing SQL Server using Windows Authentication Tag: 114983
 - 4
 - dataset Performence Issue We've heard reports that the Dataset object in the .NET 1.1 frameworks tends to slow down dramatically after it reaches a certain size, around 70MB or so, regardless of the amount of RAM installed on the machine. We haven't experienced this ourselves yet, but can see the day when our Dataset will comprise of hundreds of megabytes of data. Questions: 1 Is this a well-known issue? 2 Is this a general performance problem, or just a problem with certain methods? The Merge method comes to mind, which we use quite a bit in our code indirectly. 3 Is there any way to work around this limitation? 4 Does the NET 2.0 framework address this issue? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114981
 - 5
 - Strange Error when adding to access mdb file ok.... this is a strange error and it only happens in 2 instances 1) it happens when the program is first compiled. a restart of the program solves the problem 2) and when its out of order .... eg ( 12 Mad dog, 34 may and bbb) where bbb is displayed twice. this bbb is displayed twice in both instances. here is the code: Debug.WriteLine("miAddLCProject_Click (BEFORE) : " + this.dsProjects.Tables["projects"].Rows.Count.ToString()); System.Data.DataRow newRow = this.dsProjects.Tables["projects"].NewRow(); newRow["projectName"] = curProject.ProjectName.ToString(); this.dsProjects.Tables["projects"].Rows.Add(newRow); this.daProjects.Update(this.dsProjects, "projects"); this.daProjects.Fill(this.dsProjects, "projects"); Debug.WriteLine("miAddLCProject_Click (AFTER) : " + this.dsProjects.Tables["projects"].Rows.Count.ToString()); and when the error occurs, the debug lines output say 2 and 4 any ideas what could be causing this error.... Brent Tag: Problem accessing SQL Server using Windows Authentication Tag: 114977
 - 6
 - DataReader.Read() Ends program Hi all, I have an odd problem, which I traced back to a line of code. I have a record set coming back into a DataReader (OdbcDataReader in my case). I can .Read() once, but when the loop starts again and the .Read() is executed a second time, the application just ends. There are NO exceptions, logs or anything, its as if the .Read method called Application.Exit(). Any ideas? Thanks Andy Tag: Problem accessing SQL Server using Windows Authentication Tag: 114974
 - 7
 - URGENT: Oracle: String buffer too small I have a typed dataset which contains data I want to send to the database. I created an OracleDataAdapter and have set the correspond OracleCommands. In oracle there are the required procedures. When I call 'Update', I always get the error 'String buffer too small' . I already set the size of all parameters that correspond to a VARCHAR2 variable, to eg.50. But that was not the solution. Can anybody give me some more help or tips that could solve the problem? Some more info; Oracle 9.2.0.6 on the server Oracle 9.2.0.4.1 on the client. Using ODP.NET Thanks, Kris Tag: Problem accessing SQL Server using Windows Authentication Tag: 114963
 - 8
 - Merge and Get Changes returns nothing I have a simple case of two datasets each containing a table with the same schema. When I merge the two datasets, the empty tables in daataset1 get filled with the rows from dataset2, all fine so far. When I try and get the changes of the tables in dataset1 no changes are returned. Any ideas? ''merge the server table with the local table ds_Server.Merge(ds_Local) ds_Server.Table(0-x) all have rows now, but ds_Server.Table(0-x).GetChanges returns nothing. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114957
 - 9
 - Oracle 8i, OleDB driver and Batch Queries Hello Is it possible to execute Batch Queries with Oracle and the driver OleDB. I don't managed to do it. My ConnectionString : ..value="Provider=OraOLEDB.Oracle; Data Source=MYBASE; User ID=USER; Password=PASSWORD" Here is my code, --- This part is running, there is only one request. Dim mySQL As String = "SELECT CHAMP1? CHAMP2 FROM MATABLE" _ & "WHERE MATABLE.IDE = ?" Dim myOleDbCmd as New OleDbCommand (mySQL,myOleDbConn) myOleDbCmd.Parameters.Add(New OleDb.OleDbParameter("ide", intIde)) Dim myReader As OleDbDataReader = myOleDbCmd.ExecuteReader() But if i want to try that, Dim mySQL As String = "SELECT CHAMP1? CHAMP2 FROM MATABLE" _ & "WHERE MATABLE.IDE = ?;SELECT COUNT(*) FROM MATABLE" I get error ORA-0911 invalid Caracter. The problem seem to be the caracter ; Thanks a lot for your help fabrice Tag: Problem accessing SQL Server using Windows Authentication Tag: 114956
 - 10
 - reading text file using OLEDB into dataset Hi I'm using OLEDB to fill a dataset from CSV file. it works great - too great. the dataset recognizes some text fields as integers. for example the date 02/08/05 is transformed to 20805. I want the dataset to read all data as strings. I can't use schema files because the CSV files are generated at runtime with different names and column number, and I can't manipulate the files I read. any ideas? Thanks Richard Tag: Problem accessing SQL Server using Windows Authentication Tag: 114954
 - 11
 - move all data access to component(s)? I'm thinking about moving all my data access to components that will return a series of record sets, data readers, scaler. The pages will create an instance of a record set to be filled by functions. Updates will be functions with datarows or recordsets as parameters. Am i going too far? Will this slow down that app or make it unnecessarly big? i like some advice on this (I still new at this, but i'm getting there) thanks kes Tag: Problem accessing SQL Server using Windows Authentication Tag: 114948
 - 12
 - Copy ms access tables from one DB to an other using ado.net in vb. Hi everyone, I need to know how to copy a ms access table from database A to database B using ado.net in vb.net. What classes of the OLEDB namespaces will allow me to do this? Can you please provide a code sample? Thanks for your help. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114944
 - 13
 - display data from Oracle database with another code page Hi! I have database with ISO-8859-2 code page set (POLISH_POLAND.EE8ISO8859P2) I write a WinForms application which uses those data (System.Data.OracleClient) How can I force my application to display national chracters in proper way (i want use windows-1250 code page)? Pawel Tag: Problem accessing SQL Server using Windows Authentication Tag: 114942
 - 14
 - Problem in Context connection of CLR Trigger Hi, I'm using June CTP Visual Studio 2005. I have Created CLR Object. In my CLR Method i have opened a connection like below public partial class Triggers { //In my CLR Method i have Opened the connection like below [Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger", Target="Triplets",Event="FOR INSERT")] public static void CLRTrigger() { Triggers objTriggers = new Triggers(); objTriggers.Connection(true); ... ..... Test(); // Here i'm calling one more method Test(); .... .... objTriggers.Connection(false); } In this test method i'm Executing the command. Public void Test() { .... SqlCommand cmd = new SqlCommand(); string cmdText = "select dirname from Docs where id=45"; cmd.CommandText = cmdText; cmd.Connection = con; try { sdr = cmd.ExecuteReader(); } catch (Exception exp) { WriteLog("R E ::: "+exp.Message); } ...... } //Connection open and Close method public void Connection(bool status) { if (status) { con = new SqlConnection("context connection = true"); con.Open(); } else { con.Close(); } } } I'm getting the error of Connection already in use. I have properly opened the connection in begin of method and Closed in end of method. Inside the execution of method if i called some method means then it can also use the same connection. Then Why its giving this error. Please anybody knows solution to my problem let me know to solve it. Thanks, Vinoth vinoth@gsdindia.com Tag: Problem accessing SQL Server using Windows Authentication Tag: 114940
 - 15
 - sqltransaction and updates to multiple databases I'm trying to use sqltransaction for updates across multiple databases but can't quite figure out how to do it. Since the sqlconnection will be connected to a single database, it would appear that I would need multiple sqltransactions, which would keep from having true rollback and commit for the updates. A vb.net example of what I'm trying to do would be as follows. This is simply trying to copy transactions from a live database to a historical database then remove the transaction from the live database. I know there are other ways to do this but it gives an example of my problem: Dim sqlConnection1, sqlConnection2 As New SqlConnection() sqlConnection1.ConnectionString = "Persist Security Info=False; Integrated Security=SSPI;database=livedatabase; server=mySQLServer;Connect Timeout=30" sqlConnection2.ConnectionString = "Persist Security Info=False; Integrated Security=SSPI;database=historydatabase; server=mySQLServer;Connect Timeout=30" Dim SqlTrans1, SqlTrans2 As SqlTransaction 'Copy to history and remove from current SqlTrans1 = sqlConnection1.BeginTransaction SqlTrans2 = sqlConnection2.BeginTransaction Try insert commands to history database go here (commands have .transaction property set to SqlTrans2) Try delete commands from live database go here (commands have .transaction property set to SqlTrans1) Catch e As Exception SqlTrans1.Rollback() SqlTrans2.Rollback() End Try Catch e As Exception SqlTrans2.Rollback() End Try 'commit routine Try SqlTrans2.Commit() Try SqlTrans1.Commit() Catch e As Exception SqlTrans1.Rollback() End Try Catch e As Exception SqlTrans1.Rollback() SqllTrans2.Rollback() End Try The problem with the above fragment is that if the Commit to the remove from the live database (SqlTrans1.Commit) fails, I've already performed the commit to the write to the history database (SqlTrans2.Commit) so I'm not able to roll the history database back to the previous state. Am I failing to understand something about the behavior of sqltransaction? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114919
 - 16
 - NEW CHANCE TO WIN MONEY This is a multi-part message in MIME format --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/html Content-Transfer-Encoding: Binary <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <meta name="GENERATOR" content="Microsoft FrontPage Express 2.0"> <title>Ceci n'</title> </head> <body bgcolor="#000000">   <div align="center"><center> <table border="0" bgcolor="#FFFFFF"> <tr> <td>READY TO BE RICH...</td> </tr> </table> </center></div>   <div align="center"><center> <table border="0" bgcolor="#FFFFFF"> <tr> <td> </td> <td>Dear Friends: Greetings: I am a retired attorney. A few years ago a man came to me with a letter. He asked me to verify the fact that this was legal to do. I told him I would review it and get back to him. When I first read the letter my client brought me, I thought it was some "off-the-wall" idea to make money. A week and a half later we met in my office to discuss the issue. I told him the letter he originally brought me was not 100% legal. My client then asked me to alter it to make it perfectly legal. I asked him to make one small change in the letter. I was still curious about the letter, so he explained to me how it works. I thought it seemed like a long shot, so I decided against participating. But before my client left, I asked him to keep me updated on his results. About two months later, he called me to tell me he had received over $800,000 in cash.I didn't believe him, so he asked me to try this idea and find out for myself. I thought about it for a couple of days and decided I really didn't have anything to lose, so I asked him for a copy of the letters. I followed the instructions exactly, mailed 200 copies, and sure enough, the money started coming in! It arrived slowly at first, but coming. I kept a precise record of the earnings, and in the end, it totaled $978,493! I could hardly believe it. I met with my friend for lunch to find out exactly how it worked. He told me there are quite a few similar letters around, but this one is different because there are six names at the end of the letter, not five like some others. This fact alone results in your name being in far more returns. The other fact was the help I gave him, making sure the whole thing was legal, since no one wants to take the risk of doing something illegal. By now you are surely curious to know what small changes to make. If you sent a letter like this one out, in order to be completely legal, you must actually sell something in order to receive a dollar in return. So when you send a dollar to each of the names on the list, you must include these words, "PLEASE PUT ME ON YOUR MAILING LIST" and include your name and address. This is the key to the program. The item you will receive for the dollar you sent to the six people below is the letter. At the time I first tried this idea, I was earning a good living as a lawyer. But everyone in the legal profession will tell you there is a lot of stress that comes with the job. I told myself if things worked out, I would retired from my practice and play golf. I decided to try the letter again, but this time I sent 500 copies. Three months later , I had totaled $2,341,178! Here are a few reasons a person might give for not trying this program: Some people think they can never make a lot of money with anything this simple. Some are afraid they will be ridiculed for trying Some dream of large sums of money, but do nothing to actually achieve it. Some are just plain lazy. Some are afraid of losing their investment. They think this program is designed to beat them out of a few dollars. The system works if you will just try it. But you must follow the simple instructions exactly, and in less than three months, you will receive $800,000 GUARANTEED! Keep what you are doing to yourself for awhile. Many will tell you it won`t work and will try to talk you out of your dreams. Let them know of your success after it works. LETTERS FROM PARTICIPANTS IN THIS PROGRAM: :My name is David Rhodes. In 1992 my car was repossessed and bill collectors were housing my. I was laid off and my unemployment ran out. In October of 1992, I received a letter telling me how to earn $800,000 anytime I wanted. Of course, I was skeptical. But because I was so desperate and virtually had nothing to lose, I gave it a try. In January 1993, my family and I went on a 10-day cruise. The next month I bought a brand new Mercedes with cash! I am currently building a home in Virginia and I will never have to work again. This money program really works perfectly every time. I have never failed to receive less than $500,000. This is a legitimate, money-making opportunity. It does not require you to sell anything or to come in contact with people. And , best of all, you only leave the house to mail the letters. If you have always believed that someday you would get the lucky break, then simply follow the instructions and make dreams come true. Larry McMahon, Norfolk, VA Six months ago, I received this letter and ignored it. Five more came within a period of time and I ignored them also. I was tempted, but I was convinced that they were just a Hoax. After three weeks of deliberating, I decided to give it a try ( not expecting much ). Two weeks went by and nothing happened. The fourth week was unbelievable! I cant say I received $800,000 but I received $400,000. For the first time in years, I am debt free. I am doing this again, only this time starting with 500 post. I strongly recommend that you follow the instructions exactly as outlined in this letter. INSTRUCTIONS 1. Immediately send $1.00 to each of the six people on the list at the end of this letter. Wrap the dollar bill in a note saying " Please add me to your mailing list" and include your name and address. 2. Copy this letter. You do not have to type it 200 times. Simply place your cursor at the top of the page, hold it and drag it all the way down to the end of the letter. Then click on "edit" and select "copy". Now open up a notepad file on your computer and put the cursor at the top of the page in the notepad, click on 'edit' and then select 'paste' it will copy the letter for you onto your computer. 3. Remove the name next to the #1 on the list and move the rest of the names up one position (#2 becomes #1, #3 becomes #2, etc.....) Then place your name in the #6 position. Then save it, make sure it is saved as a txt. file. Cover money by paper fully by which it can not been seen in light , because postal employees do some cheat some times. 4. When you have completed the instructions, take this letter and then go to (Google,Yahoo,...) and type in (Making Money Message board,or post message,...)and start posting your copy to 200 message boards,or more this is only the minimum, you can post as much as you like...The more copies you send the better the results. Keep a copy of this letter so you can use it a second time. Post it out again in six months, but Post it with the addresses you receive with each dollar. It will work better the second time. NOTE: This service is 100% legal - (Refer to title 18 section 1302 of the U.S. Postal & lottery laws) How does it work? When you send out 200 Posts, it is estimated that at least 15 people will respond and send you a $1.00. ($15.00) Those 15 will Post 200 Posts each and 225 people send you $1.00 ($225.00) Those 225 people Post 200 Posts each and 3,375 people send you $1.00 ($3,375.00) Those 3,375 post 200 posts each and 759,375 people send you $1.00 ($759,375.00) At this point your name drops off the list, but so far you have received $813,615.00. Here is the list to Send 1.G.H.L.N Thorntree Rd1600 Muncie,IN 47304-9710 USA 2. K. Hastie 459 Pinetree Cres. Cambridge ON N3H 4x4 CANADA 3. M.Motut.Anora Dr.2377 Abbotsford, BC v2s 5p3 CANADA 4. M. Baun, 8451 Cabin Creek Drv. Apt. G, Indianapolis IN 46237 5. Mr Angel VILLAR PEREZ Calle Castellon nº2 - 4F 47012 VALLADOLID SPAIN 6. Kyle Pate P.O.Box 2046 - Great Falls, MT59403-2046 USA </td> </tr> </table> </center></div> </body> </html> --=_NextPart_2rfkindysadvnqw3nerasdf-- Tag: Problem accessing SQL Server using Windows Authentication Tag: 114917
 - 17
 - Store and retrieve an array of double into image field(sql server) I have a single dimensional array of double values which I want to store into an image field of sql server database. I couldn't find any sample code to do that, the sample code everywhere talks about reading and writing to a file. I want to know how to store and retrieve an array of double from the image field using vb.net. I would appreciate any inputs on this. Thanks Tag: Problem accessing SQL Server using Windows Authentication Tag: 114916
 - 18
 - How to do a simple update command with ado.net? I am trying to execute a simple SQL Update command on an Access database. I have an OleDBConnection (gDatabaseConn) that has successfully been executing select commands, but I cannot get an update command to work. Here is the code in question: Dim cmdUpdate As New OleDb.OleDbCommand("", gDatabaseConn) sSQL = "UPDATE NoteTable SET AcctID = '" & Account.sAcctID sSQL = sSQL & "' WHERE AcctNo = '" & Account.sAccountNo & "'" cmdUpdate.CommandText = sSQL cmdUpdate.ExecuteNonQuery() The call to ExecuteNonQuery throws an exception with the message "No value given for one or more required parameters". Both of the variables used in the string have values. Is ADO.NET capable of running a simple SQL command like this, or do I need to investigate OleDBParameter objects? This way would be preferrable, since I am not using DataTables or DataRows. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114912
 - 19
 - New column added to old table causes ORA-00904 I added a new column to an old table, and any time I try to add it to a select, I get an ORA-00904 error. I'm using the MS OracleDataProvider for DotNet, and Oracle 9.2.0.4.0. Experimenting has proven that any new column on any table produces the same error. I have renamed the column, dropped and re-added, and tried various simple names and a variety of datatypes. Has anyone else ever seen this problem? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114911
 - 20
 - dataset sort I want to use two SQL statements: Dim strSQLLCMR As String = "SELECT DTG, POO FROM LCMR WHERE (Confirmed = true and Friendly = False) ORDER BY DTG" Dim strSQLQ36 As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed = true and Friendly = False) ORDER BY DTG" to fill one table in a dataset. Then I want to sort that dataset by the field DTG. Is this possible? The reason for this is that I then want to loop through the table and determine records that have a Date/Time group (DTG) within 5 minutes of each other. Any suggestions welcome. Thanks, Thomas -- Posted via NewsDemon.com - Premium Uncensored Newsgroup Service ------->>>>>>http://www.NewsDemon.com<<<<<<------ Unlimited Access, Anonymous Accounts, Uncensored Broadband Access Tag: Problem accessing SQL Server using Windows Authentication Tag: 114889
 - 21
 - Problems with array<Byte>^ Hi everyone! I was playing a little with VC++ 2005 Express for evaluation purposes (and out of curiosity) when I stumbled over some problems. I have a set of binary data in a postgresql-table. I managed to insert the data via the npgsql-data-provider. I can also read string and numeric values, but when it comes to reading the data out of the db and writing it to disk I fail. I tried the following (I did this on another computer and type from mind here so don't bother typos plz): String^ statement = "SELECT data FROM jpegimages WHERE \"ID\" = " + Convert::ToString(newObject->GetID()) + ";"; NpgsqlCommand^ command = gcnew NpgsqlCommand(statement, this->conn); NpgsqlDataReader^ reader = command->ExecuteReader(); // I know there is only one row... reader->Read(); if (reader[0] != IntPrt::Zero) { array<Byte>^ bytes = (array<Byte>^)(reader[0]); bufferedWriter->Write(bytes); ... The code compiles without errors but when it is run I get "Cast not valid" Exceptions at the "array<Bytes.."-line. There isn't much info in the help about stuff like this. The docs for the postgres-data-provider are written against c# (which I didn't intent to use in this special case) where one can use a simple "byte[]"-construct. Any help is highly appreicated. regards... ...Dennis Tag: Problem accessing SQL Server using Windows Authentication Tag: 114885
 - 22
 - ADO.net book, resources Hello All, I am new to ADO.net and C#, I have been trying to understand both but having problems. Can anyone please guide me to a good ADO.net book using C# that I can buy, I have checked books on Amazon but have not been able to make a decision as to which one should I buy. In addition a good online resource that I should use to work with ADO.net would be great, for now although there are lot of resources, but have not been able to find one that I can get most of the information from, generally have to search for each thing on Google and then spend hours to find answer to something simple as declaring a null column in the prepared statement. Thanks a lot. Imran. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114813
 - 23
 - parameter not supplied error , using parameters.add Hello All, I have a table with few fields that allow null, I use the following code to add entries strQuery = "insert into tblChannelItems (nChannelID,sItemTitle,sItemLink,sItemDesc,sItemAuthor,sItemCategory,sItemComments,sItemEnclosure,sItemPubDate) values (@nChannelID,@sItemTitle,@sItemLink,@sItemDesc,@sItemAuthor,@sItemCategory,@sItemComments,@sItemEnclosure,@sItemPubDate);"; strQuery = strQuery + " SELECT nItemID from tblChannelItems where nItemID=SCOPE_IDENTITY();"; SqlCommand objCmd = new SqlCommand(strQuery, this.conn); SqlParameter nChannelID = objCmd.Parameters.Add("@nChannelID", SqlDbType.Int); SqlParameter sItemTitle = objCmd.Parameters.Add("@sItemTitle", SqlDbType.NVarChar); SqlParameter sItemLink = objCmd.Parameters.Add("@sItemLink", SqlDbType.NVarChar); SqlParameter sItemDesc = objCmd.Parameters.Add("@sItemDesc", SqlDbType.Text); SqlParameter sItemAuthor = objCmd.Parameters.Add("@sItemAuthor", SqlDbType.NVarChar); SqlParameter sItemCategory = objCmd.Parameters.Add("@sItemCategory", SqlDbType.NVarChar); SqlParameter sItemComments = objCmd.Parameters.Add("@sItemComments", SqlDbType.NVarChar); SqlParameter sItemEnclosure = objCmd.Parameters.Add("@sItemEnclosure", SqlDbType.NVarChar); SqlParameter sItemPubDate = objCmd.Parameters.Add("@sItemPubDate", SqlDbType.DateTime); nChannelID.Value = objStItem.nChannelID; sItemTitle.Value = objStItem.sItemTitle; sItemLink.Value = objStItem.sItemLink; sItemDesc.Value = objStItem.sItemDesc; sItemAuthor.Value = objStItem.sItemAuthor; sItemCategory.Value = objStItem.sItemCategory; sItemComments.Value = objStItem.sItemComments; sItemEnclosure.Value = objStItem.sItemEnclosure; sItemPubDate.Value = objStItem.sItemPubDate; nItemID = (int)objCmd.ExecuteScalar(); but I get the error of {"Prepared statement '(@nChannelID int,@sItemTitle nvarchar(33),@sItemLink nvarchar(50' expects parameter @sItemAuthor, which was not supplied."} Seems although the author column is optional but still somehow since I dont specify the parameters as optional in my prepared statement so I get this error, can anyone please guide me how I can set these parameters as optional , so I dont get this error? Imran. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114793
 - 24
 - Controlling a primary key column Hello: I have an application with a data base. i insert information by the application in the data base, and i have to control that the user doesn't give a value for the primary key column that already exists. I would want to know what happens in that case, when you try to insert a new row in the table with a value for the primary key that already exists, an exception is raised? nothing happens? it happens an updated? Thanks for the help. Bye. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114768
 - 25
 - ADODB.DLL - Error Object Reference not set to an instance of an object Hello, I have the following problem on certain servers, when trying to access ADO objects I get the following error message 'Object Reference not set to an instance of an object', on certain servers ADODB.DLL is in the GAC and on other servers its in the application folder. On certain server it works when installed in the application folder, and on ther servers it does not, if I install it in the GAC then it works, why does this happen? I've seen the following artcles KB321688 KB810098 Please can some explain why it works on some servers when not installed in the GAC. Thanks, Tag: Problem accessing SQL Server using Windows Authentication Tag: 114691
- Next
 - 1
 - Bulk insert Hi, I need to insert nearly more than 6000 records in a single shot. For that I need to get the data from tree view (windows app) control, Im reading through recursive mannner to get parent & childs and im forming an xml file then sending to backend. here to compose the xml file from tree view control it is taking 25 secs time and at back end I returned and Stored procedure for bulk insert. Create PROC insertversion ( @VersionData ntext ,@Update bit ) AS DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc OUTPUT,@VersionData IF @Update = 1 BEGIN INSERT INTO Version_Map (version_id, question_id, parent_question_id, disp_sequence_nbr, created_by, created_date, updated_by, updated_date) SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1) WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO int, created_by varchar(20), created_date datetime, updated_by varchar(20), updated_date datetime) XMLVersion END ELSE BEGIN INSERT INTO Version_Map (version_id, question_id, parent_question_id, disp_sequence_nbr, created_by, created_date) SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1) WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO int, created_by varchar(20), created_date datetime) XMLVersion END EXEC sp_xml_removedocument @hDoc GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Here Im using the OpenXml method... for bulk insert this is taking 50 secs.. Total insert opration it is taking 84 secs to do this operation... but Im looking a solution to this operation with in 10 secs.. plz help me .... thanks .... Tag: Problem accessing SQL Server using Windows Authentication Tag: 114681
 - 2
 - Incorrect syntax near the keyword 'DEFAULT' Hello Experts! I am attempting to use the OleDbCommand.ExecuteScaler() function within my ASP.NET C# web page to perform a simple validation, but receive the following error: "Incorrect syntax near the keyword 'DEFAULT'" The form has 2 fields on it, called tb_username and tb_password. (see code snippet below). The SQL Server table is called _users and has a number of columns, 2 of which I am reading: username and password. I've searched the net for any clues on this but so far none of the items I've found were relavent to what I was doing. Can anyone shed any light on this? Many thanks! === here is my code ==== private bool ValidateLogin() { // set the connection string oleDbConnection_login.ConnectionString = C_Constants.g_sDSN; // add parameters to the query oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWChar,25,tb_username.Text); oleDbCommand_login.Parameters.Add("@pass",System.Data.OleDb.OleDbType.VarWChar,25,tb_password.Text); oleDbCommand_login.CommandText = "Select username from _users whereusername=? AND password=?"; // open the connection oleDbConnection_login.Open(); // execute the query oleDbCommand_login.Connection = oleDbConnection_login; object objResults = oleDbCommand_login.ExecuteScalar(); <--- errormessage occurs here // close the connection oleDbConnection_login.Close(); if (objResults==null) { return false; } return true;} Tag: Problem accessing SQL Server using Windows Authentication Tag: 114675
 - 3
 - Distinct Values from a Datatable guys, Has anyone tried this before? i have a dataset and rather than doing an addtional trip to the server with a DISTINCT sql query - i'd like to just loop thru the existing dataset in memory. i assign that dataset (ds) to a datatable, declare a datarow and then use the select property to get the distinct employee id. i keep getting "Syntax error: Missing operand after 'EmplId' operator." and i've pretty much been out on the web looking for a solution to this. any help is appreciated. snippet of the code is below. thanks rik Dim oTable As DataTable = ds.Tables(0) Dim myRow As DataRow For Each myRow In oTable.Select("DISTINCT EmplId") cboEmplList2.Items.Add(myRow.Item(0)) Next ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Problem accessing SQL Server using Windows Authentication Tag: 114666
 - 4
 - Saving a dataview to a new dataset in C# Hi, I am hoping I can get some help with a small problem I have run into using C#. U have an XML file that I load into a Dataset and then display this in a Datagrid. No problems doing this at all. I then use a Dataview to filter this view using a keyword and I have no problems with this either. What I would like to do is to save this Dataview to a new Dataset so I can save the reslts to a new file but here is where I am having the problem. A Dataview is typically not written back to a Dataset so I'm not sure what would be the best way to do this. I found a code snippet after looking for a day but I have errors with it and I do not know why. I have created my main Dataset called 'ds' and after filtering it and displaying it I clone 'ds' to a new Dataset and then try to import the rows back into my new data set. The code I am using to do this is posted below.... //*************************************************** DataSet ds2 = ds.Clone(); for(int iTable = 0; iTable < ds.Tables.Count; iTable++) { for(int iRow = 0; iRow < ds.Tables[iTable].DefaultView.Count; Row++) { ds2.Tables[iTable].ImportRow(ds.Tables[iTable].DefaultView[iRow].Row); } } ds2.WriteXml(@"c:\data.xml"); //************************************************* My problem comes in on the this line... for(int iRow = 0; iRow < ds.Tables[iTable].DefaultView.Count; iRow++) The error that is displayed is as follows.... "An unhandled exception of type 'System.Data.InvalidConstraintException' occurred in system.data.dll Additional information: ForeignKeyConstraint Computer_ComputerHardware requires the child key values (3) to exist in the parent table." On this part of the code three matches are found & displayed so I'm not sure if it is important. The error occurs on the 'iRow++' part pf the statement but I don't know why. I can't set breakpoints in C# nor can I see where the error is because I can't step through the code. Any idea of this would happen? I'm really stumped on this, below is a snippet of my XML document so you know what I am using in this code. Thanks for any help, Dennis //***************************************** <Office_Inventory> <Computer> <RoomNumber>G2017</RoomNumber> <ComputerNumber>G201701</ComputerNumber> <InServiceDate>2003-03-15</InServiceDate> <ComputerHardware> <Printer>HP DeskJet 500</Printer> <Scanner>HP Scan 100</Scanner> <CD-DVD>40X CD-ROM</CD-DVD> <Monitor>Acer 67L</Monitor> </ComputerHardware> <ComputerSoftware> <OS>Windows 2000</OS> <Office>Office 2003 Professional</Office> <Programming>Visual Studio 2003 Profressional</Programming> <Utilities>Norton AntiVirus 2005</Utilities> </ComputerSoftware> </Computer> </Office_Inventory> Tag: Problem accessing SQL Server using Windows Authentication Tag: 114664
 - 5
 - Use datareader to get a value for UserNo Hi, I'm relatively new to ADO, so forgive me if I am going about this in the wrong way. I have a login.aspx page, with a textbox 'usernameTxtBx' I would like to extract a value from a single table in an SQL database. The database has two columns of interest usrNo and usrName, they are in the same table. I would like to set the value from usrNo as an int where the value in the usrName column matches that entered in the usernameTxtBx. I have attempted to use a datareader in c#. Here's the relevent code: // Declare conn from Web.Config SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]); conn.Open(); // Set username as a string string Username = usernameTxtBx.Text; //find usrNo SqlCommand cmd = new SqlCommand("SELECT usrNo FROM tblUser WHERE usrName = 'Username'", conn); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); int UserNo = (int)dr["UserNo"]; conn.Close(); This gives the following error: Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IndexOutOfRangeException: UserNo In debugging the value of UserNo appears to get set to 0 regardless of the login used, where i would expect the value to equal 1 or 3 or similar integer. Any ideas what i'm doing wrong? am I even on the right track?? Thanks Tag: Problem accessing SQL Server using Windows Authentication Tag: 114662
 - 6
 - Clearing dataset tables where they have relations I'm using a Public dataset in the module for all data throughout the project. For most forms, this is great, no need to instantiate a new object, just clear the datatables and reload as needed. However, an issue arises where I have data relations in a form. Reloading the data on those forms requires clearing the relation and the datatables. What I've done in the past is reset the dataset and clear the relations. This also works fine -- except for the obvious, that being that it kills off any data in the dataset. So, I've tried to just clear the tables and then clear the dataset relations. This causes an exception on the 2nd load of data ("The row doesn't belong to the same DataSet as this relation."). How do I resolve this -- without creating a new dataset for each form where I have a data relation? ********************************************************* TECHNIQUE A (this works well, except clears the dataset entirely) ds.Reset() ds.Relations.Clear() TECHNIQUE B (this causes an exception on 2nd load of employee data) If ds.Tables.Contains("dtEmployees") Then ds.Tables("dtEmployees").Clear() End If If ds.Tables.Contains("dtEmpCategoryMatches") Then ds.Tables("dtEmpCategoryMatches").Clear() End If ds.Relations.Clear() Tag: Problem accessing SQL Server using Windows Authentication Tag: 114655
 - 7
 - Can rows be ordered in a datatable? I have a DataTable that is bound to a DataGrid that displays a number of selections from a product menu: Item1 quantity price totalAmount Item2 quantity price totalAmount TAX - - - - - - - - - - totalAmount TOTAL - - - - - - - - totalAmount Each time the user makes a selection, the DataTable is updated and re-bound to the DataGrid. The problem is I need to keep the TAX and TOTAL rows as the last rows in the DataTable so they appear at the bottom of the DataGrid. But the only way I know how to do this is to delete the rows every time the user makes a selection from the product menu, then recalculate the order, and lastly reinsert the rows in the DataTable (otherwise the user's most recently selected product is the last row in the DataTable and appears at the bottom of the DataGrid). Is there a way to re-order the rows rather than deleting and re-inserting every time? Is there some way to tell the data table that the Total row should be the last row in the table, and the Tax row second-to-last? That way I could just overwrite the value in those rows rather than deleting and re-inserting after each time the users selects another item. Thanks in advance. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114653
 - 8
 - Check to ensure server and db available When I load the startup form, I want to make a check to ensure the named server AND database (SQL2k) is available before trying to load any data. Any recommendations? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114646
 - 9
 - Sort Dataview by relationships Is it possible to sort a child table in a dataview on its own ? for example if there are two tables: tblParent & tblChild The form is bound by its dataview. Now there is a tab on the frmMain which lists all the records in tblChild The main form is sorted by a field in tblParent, further to that can I sort the records in the tab by another field in tblChild ? Any input would be appreciated. Thanks Tag: Problem accessing SQL Server using Windows Authentication Tag: 114642
 - 10
 - How to create and schedule full-text catalogue programatically Hi, I have writen a database installation application that simply creates tables inside a database. I also need to create and schedule full-text indexing on some of my tables. I am using SQL server 2000 and and VB.NET 2003. Thank you, _dino_ Tag: Problem accessing SQL Server using Windows Authentication Tag: 114639
 - 11
 - How do I connect to a FoxPro 6 .dbf file using OleDb? Does anyone know how to simply connect to a FoxPro 6 .dbf file using ADO.NET's OleDb provider so I can query the data? Mainly I need to know what the connection string is so I can execute a query. Thanks, Tony Tag: Problem accessing SQL Server using Windows Authentication Tag: 114638
 - 12
 - Stupid bugs in ODP.NET 9.2.0.4.01 1. Execute this SQL DELETE FROM ... WHERE ROWNUM=1 RETURNING a,b INTO :pa,:pb with ExecuteNonQuery(), here a and b are number columns, and the two output parameters' datatype is set to DbType.Int64, you are ok where there is row in database, otherwise your app crashs. 2. Guess what the DbType of this parameter is: OracleParameter p = new OracleParameter("name",0L); it is DbType.AnsiString other than DbType.Int64. Oracle did not release any patch for it for so long time. How could it be a company? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114636
 - 13
 - Stupidly named field selection I am trying to work with and IBM AS400 OLE DB data provider and when using the wizard, I am going along selecting my fields and setting a WHERE clause. There is a field with a "#" at the end of it. As soon as I type this WHERE clause, I have an error message about the syntax and I know that it has to do with the "#". I have enclosed the field name with quotes, single quotes and nothing seems to work. I have been using the ODBC which does work fine, however I am a beginner at ASP .Net (I usually do Windows apps) and I have a problem with pulling data from the AS400 using ODBC. Help, what can I do? Brad Tag: Problem accessing SQL Server using Windows Authentication Tag: 114634
 - 14
 - Capturing return value from SQL Server's stored procedure Using Microsoft.Practices.EnterpriseLibrary.Data, how do I capture the return value from executing a stored procedure on SQL Server 2000 (i.e., how could I tell the Stored Procedure did not return 0 [indicating failure] or that a transaction the stored procedure begun had been rolled back): No exceptions were thrown from the following when -1 was returned and transaction rolled back: Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "uspCreator"; DBCommandWrapper creatorCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand); db.ExecuteNonQuery(creatorCommandWrapper); Tag: Problem accessing SQL Server using Windows Authentication Tag: 114627
 - 15
 - Dataset constraints A question please. Lets say I have a grid with its datasource as a dataset with three columns in it; UserID (an auto generated ID), LastName and FirstName. Additionally, lets say I want to restrict the user from entering two records with the same last name and first name. I have created a constraint for LastName FirstName in the dataset but I would like a different message than the default message I get when the constraint is violated. Is there a way for me to do this? Tag: Problem accessing SQL Server using Windows Authentication Tag: 114622
 - 16
 - Interesting Problem regarding Parameters Backgroud: My company is currently migrating to .Net from PHP for a number of reasons, however we have one slight problem we would very much like to solve before moving to .Net (ok, many but this one is a toughie). Problem: On our current PHP platform we have our own database abstraction class which inherits from the PEAR DB abstraction class. One of the major reasons why we did this is because we wanted to add automatic querie logging to our database connection object. Here is a brief snippit of code to demonstarte the functionality we currently have: //Creating DB connection objects $connection = mis_DB::connect( 'database-server', 'database' ); $connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE ); $connection->setLogMask( MISDB_LOG_ALL ); This will automatically log any queries thrown at at, for example (I'm using prepare and execute syntax for a reason that will be come clear in just a minute): /* Disclaimer, such a DB does not exist on my company DB server, but it should! */ $query = "SELECT * FROM beer.beer_styles WHERE style = ?"; $handle = $connection->prepare( $query ); $result_object = $connection->execute( $handle, array( "Imperial Stout" ) ); What our current database class will do (after executing the query) is logg the following message to the /log_path/filename.log file: Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM beer.beer_styles WHERE style = 'Imerial Stout' What we would like is to have some thing similar with our new .Net DB class, however the problem I'm running into is I don't know how to get the query that was actually executed on the server, what I mean by that is: //Assum proper connection string OleDbConnection Connection = new OleDbConnection( ConnectionString ); OleDbCommand Command = new OleDbCommand( Connection ); Connection.Open(); Command.CommandText = @"SELECT * FROM beer.beer_styles WHERE style = @style"; Command.Paramaters.Add( "@style", "Imperial Stout" ); OleDbDataReader Reader = Command.ExecuteReader(); Now that the query has executed, how to I pull the value: "SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'" out of the database class v. "SELECT * FROM beer.beer_styles WHERE style = @style" Thanks for any help you can provide! -------------------- Carlo Razzeto carlo.razzeto@mtginfo.com Tag: Problem accessing SQL Server using Windows Authentication Tag: 114617
 - 17
 - Interesting Problem regarding Parameters Backgroud: My company is currently migrating to .Net from PHP for a number of reasons, however we have one slight problem we would very much like to solve before moving to .Net (ok, many but this one is a toughie). Problem: On our current PHP platform we have our own database abstraction class which inherits from the PEAR DB abstraction class. One of the major reasons why we did this is because we wanted to add automatic querie logging to our database connection object. Here is a brief snippit of code to demonstarte the functionality we currently have: //Creating DB connection objects $connection = mis_DB::connect( 'database-server', 'database' ); $connection->addLog( 'file', '/log_path/filename.log', PEAR_LOG_MODE ); $connection->setLogMask( MISDB_LOG_ALL ); This will automatically log any queries thrown at at, for example (I'm using prepare and execute syntax for a reason that will be come clear in just a minute): /* Disclaimer, such a DB does not exist on my company DB server, but it should! */ $query = "SELECT * FROM beer.beer_styles WHERE style = ?"; $handle = $connection->prepare( $query ); $result_object = $connection->execute( $handle, array( "Imperial Stout" ) ); What our current database class will do (after executing the query) is logg the following message to the /log_path/filename.log file: Friday, July 29, 2005 8:41:10 AM [Informational] SELECT * FROM beer.beer_styles WHERE style = 'Imerial Stout' What we would like is to have some thing similar with our new .Net DB class, however the problem I'm running into is I don't know how to get the query that was actually executed on the server, what I mean by that is: //Assume I have a proper connection string OleDbConnection Connection = new OleDbConnection( ConnectionString ); OleDbCommand Command = new OleDbCommand( "", Connection ); Connection.Open(); //Set the query with parmater then execute reader Command.CommandText = "SELECT * FROM beer.beer_styles WHERE style = @style"; Command.Paramaters.Add( "@style", "Imperial Stout" ); OleDbDataReader Reader = Command.ExecuteReader(); How do I get the query that actually executed on our DB server, i.e. "SELECT * FROM beer.beer_styles WHERE style = 'Imperial Stout'", not "SELECT * FROM beer.beer_styles WHERE style = @style". Thanks for the help! ---------------------- Carlo Razzeto carlo.razzeto@mtginfo.com Tag: Problem accessing SQL Server using Windows Authentication Tag: 114616
 - 18
 - CAPTION FOR UPLOAD CONTROL Has anybody been able to figure out how to change the wording of the word "Browse" in a file upload control? It seems to be built in the browser and it doesn't seem like we can change it. anybody knows? THANKS! Tag: Problem accessing SQL Server using Windows Authentication Tag: 114612
 - 19
 - Hoping some one here can help me, or at least point me in the right direction I know this dosen't really fit the category, but most of you ADO.NET guys do some reporting at some point in time. If you know a better place for me to put this let me know. I have a Crystal Report that is external to my application. It uses an XML file (also outside my application) as it's datasource. I need to be able to change the XML datasource and pass this to the report (THE XML file it's reporting on) I can't seem to find the anwser. I don't want to pull the XML file into a dataset and then push it to the report, I would prefer to use the Crystal Pull method. Thanks again! Justin Tag: Problem accessing SQL Server using Windows Authentication Tag: 114608
 - 20
 - ado.net 2.0 and sql server 7 Hello, we are migrating our "old" .net 1.1 application to 2.0 and have noticed that opening connections to a sql server 7 now is very very slow, but this doesn't happen with sql server 2000. Is there any workaround to speed up this a bit. Our application is now unusable with sql 7. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114603
 - 21
 - ODBC Driver not displaying? Hello, I'm trying to install an ODBC driver, specifically IBM's Client Access OBDC Driver (32 Bit) which is installed as part of Client Access for the AS/400. The ODBC driver is installed okay, but if I launch odbcad32.exe (managing odbc data sources), I do not see the driver. I cannot choose the driver when creating a new dsn because it just is not listed. I have checked the registry a HKLM\Software\ODBC and I see that there are entries for the driver, and they appear to be correct. Why can't I see this driver listed so I can use it to create a DSN? My system is WinXp Pro, SP1, .Net 1.1, MDAC 2.8 Thanks for the help - Wayne P. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114598
 - 22
 - TableAdapter problems w VS.NET 2005 Hi everyone, When I use the TableAdapter wizard with a SQL select that contains a linked server table (like Select * from SF...Account) , I get an error message when generating the SQL insert. A dialog box appears with the msg : The specified identifier was in an invalid format". The select command generates ok but the insert, update and delete commands do not. I'm using the June CTP of VS.NET 2005 Thanks. -- Bill Emerson Tag: Problem accessing SQL Server using Windows Authentication Tag: 114596
 - 23
 - Writing updates from a wide DataTable I have a DataTable with a large number of colums - 50 or more. I'm following the recommendations to use stored procedures to apply updates to the database, and using the Enterprise Llibrary. So I end up with a stored procedure with 50+ parameters, and an Update statement that updates the same number of columns. In a typical use case only a few columns will actually be updated, but my DAL generally doesn't know which - it just gets a DataSet with some changes. Everything works fine, but I feel uncomfortable with this solution. I don't like rewriting all those columns I haven't changed. I don't like shipping all this unchanged data around. Should I feel uncomfortable? Are there better ways of handling this kind of situation? Thanks... -------- Nigel Norris Tag: Problem accessing SQL Server using Windows Authentication Tag: 114582
 - 24
 - = Null problem Hi Am using ADO.NET connection from my ASP.NET application to connect to MS SQL SERVER 2000. I have a Stored procedure which works fine when I directly connect to the Database using Native drivers, the same is not working properly when I use ADO.NET connection. When I debugged the Stored procedure I found that the following script was not working with ADO.NET emp_name = Null and the same was working fine when I changed the sql script to emp_name IS Null. I have few hundreds of stored procedure and It might not be possible for me to alter the Stored Procedure. 1.Any body have faced the same problem? 2.Is there an alternative solution to overcome from this problem ? 3.Is this fixed in any service packs from Microsoft ? 4. Or there is no other option rather than change the sql script in Stored Procedure to emp_name IS NULL ? Any help would be greatly appreciated Thank you Mahesh.V Tag: Problem accessing SQL Server using Windows Authentication Tag: 114571
 - 25
 - Debug skips DataAdapter.Update code. Why?? Hi, I'm attempting to input data into two related tables using stored procedures. I found some good example code, which i have followed, checked and double checked, but for some reason the update is not happening. Below is the code, it's pretty lengthy so to save you time i'll say now that it runs through fine in debug until near the very end, the patientDataAdapter.Update(dsAddPatient, "Patient"); line just above the 'catch' section. It runs the previous line then jumps this and subsequent lines going straight to 'catch'. The code: private void addPatientDataToDB() { try { // Create the DataSet object DataSet dsAddPatient = new DataSet(); // Connect to database SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]); conn.Open(); // Create the DataTable "Patient" in the Dataset and the DataAdapter SqlDataAdapter patientDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM tblPatient", conn)); patientDataAdapter.InsertCommand = new SqlCommand("proc_InsertPatient", conn); SqlCommand cmdInsert = patientDataAdapter.InsertCommand; cmdInsert.CommandType = CommandType.StoredProcedure; cmdInsert.Parameters.Add(new SqlParameter("@patientNo", SqlDbType.Int)); cmdInsert.Parameters["@patientNo"].Direction = ParameterDirection.Output; cmdInsert.Parameters["@patientNo"].SourceColumn = "patientNo"; cmdInsert.Parameters.Add(new SqlParameter("@pntUnitID", SqlDbType.NVarChar,15,"pntUnitID")); cmdInsert.Parameters.Add(new SqlParameter("@pntTitle", SqlDbType.NVarChar,4,"pntTitle")); cmdInsert.Parameters.Add(new SqlParameter("@pntFName", SqlDbType.NVarChar,20,"pntFName")); cmdInsert.Parameters.Add(new SqlParameter("@pntLName", SqlDbType.NVarChar,30,"pntLName")); cmdInsert.Parameters.Add(new SqlParameter("@pntDOB", SqlDbType.DateTime,8,"pntDOB")); cmdInsert.Parameters.Add(new SqlParameter("@pntSex", SqlDbType.NVarChar,1,"pntSex")); cmdInsert.Parameters.Add(new SqlParameter("@pntAddress1", SqlDbType.NVarChar,150,"pntAddress1")); cmdInsert.Parameters.Add(new SqlParameter("@pntAddress2", SqlDbType.NVarChar,150,"pntAddress2")); cmdInsert.Parameters.Add(new SqlParameter("@pntAddress3", SqlDbType.NVarChar,150,"pntAddress3")); cmdInsert.Parameters.Add(new SqlParameter("@pntcountryNo", SqlDbType.Int,4,"pntCountryNo")); cmdInsert.Parameters.Add(new SqlParameter("@pntPostcode", SqlDbType.NVarChar,10,"pntPostcode")); cmdInsert.Parameters.Add(new SqlParameter("@pntHPnone", SqlDbType.NVarChar,14,"pntHPnone")); cmdInsert.Parameters.Add(new SqlParameter("@pntWPhone", SqlDbType.NVarChar,14,"pntWPhone")); cmdInsert.Parameters.Add(new SqlParameter("@pntMobPhone", SqlDbType.NVarChar,14,"pntMobPhone")); cmdInsert.Parameters.Add(new SqlParameter("@pntEmail", SqlDbType.NVarChar,50,"pntEmail")); cmdInsert.Parameters.Add(new SqlParameter("@pntStage", SqlDbType.NVarChar,5,"pntStage")); cmdInsert.Parameters.Add(new SqlParameter("@pntT", SqlDbType.TinyInt,1,"pntT")); cmdInsert.Parameters.Add(new SqlParameter("@pntN", SqlDbType.TinyInt,1,"pntN")); cmdInsert.Parameters.Add(new SqlParameter("@pntM", SqlDbType.TinyInt,1,"pntM")); cmdInsert.Parameters.Add(new SqlParameter("@pntPreviousTreatments", SqlDbType.Char,1000,"pntPreviousTreatments")); cmdInsert.Parameters.Add(new SqlParameter("@pntFurtherNotes", SqlDbType.Char,1000,"pntFurtherNotes")); patientDataAdapter.FillSchema(dsAddPatient, SchemaType.Source); DataTable pTable = dsAddPatient.Tables["Table"]; pTable.TableName = "Patient"; // Create the DataTable "Histology" in the // Dataset and the DataAdapter SqlDataAdapter histologyDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM tblSample", conn)); histologyDataAdapter.InsertCommand = new SqlCommand("proc_InsertHistology", conn); cmdInsert = histologyDataAdapter.InsertCommand; cmdInsert.CommandType = CommandType.StoredProcedure; cmdInsert.Parameters.Add(new SqlParameter("@patientNo", SqlDbType.Int,4)); cmdInsert.Parameters["@patientNo"].SourceColumn = "patientNo"; cmdInsert.Parameters.Add(new SqlParameter("@lesHistology", SqlDbType.NVarChar,50)); cmdInsert.Parameters["@lesHistology"].SourceColumn = "lesHistology"; cmdInsert.Parameters.Add(new SqlParameter("@lesNull", SqlDbType.NVarChar,10)); cmdInsert.Parameters["@lesNull"].SourceColumn = "lesNull"; histologyDataAdapter.FillSchema(dsAddPatient, SchemaType.Source); pTable = dsAddPatient.Tables["Table"]; pTable.TableName = "Histology"; // Create the relationship between the two tables dsAddPatient.Relations.Add(new DataRelation("ParentChild", dsAddPatient.Tables["Patient"].Columns["patientNo"], dsAddPatient.Tables["Histology"].Columns["patientNo"])); // Insert the Data DataRow patientRow = dsAddPatient.Tables["Patient"].NewRow(); patientRow["pntUnitID"] = patientCodeTxtBx.Text; patientRow["pntTitle"] = titleCboBx.SelectedValue; patientRow["pntFName"] = fNameTxtBx.Text; patientRow["pntLName"] = lNameTxtBx.Text; patientRow["pntSex"] = sexCboBx.SelectedValue; patientRow["pntDOB"] = DOBTxtBx.Text; patientRow["pntAddress1"] = address1TxtBx.Text; patientRow["pntAddress2"] = address2TxtBx.Text; patientRow["pntAddress3"] = address3TxtBx.Text; patientRow["pntcountryNo"] = countryCboBx.SelectedValue; patientRow["pntPostcode"] = postcodeTxtBx.Text; patientRow["pntHPhone"] = phoneTxtBx.Text; patientRow["pntWPhone"] = workPhoneTxtBx.Text; patientRow["pntMobPhone"] = mobileTxtBx.Text; patientRow["pntEmail"] = emailTxtBx.Text; patientRow["pntStage"] = stageCboBx.SelectedValue; patientRow["pntT"] = tTxtBx.Text; patientRow["pntN"] = nTxtBx.Text; patientRow["pntM"] = mTxtBx.Text; patientRow["pntPreviousTreatments"] = previousTreatmentsTxtBx.Text; patientRow["pntFurtherNotes"] = notesTxtBx.Text; dsAddPatient.Tables["Patient"].Rows.Add(patientRow); DataRow histologyRow = dsAddPatient.Tables["Histology"].NewRow(); histologyRow["lesHistology"] = histologyCboBx.SelectedValue; histologyRow.SetParentRow(patientRow); dsAddPatient.Tables["Histology"].Rows.Add(histologyRow); patientDataAdapter.Update(dsAddPatient, "Patient"); histologyDataAdapter.Update(dsAddPatient, "Histology"); messageLbl.Text = "Data successfully added to database"; } catch (Exception ex) { messageLbl.Text = ex + "Unable to connect to the database"; } finally { // Close the connection DataAccess.DBConnection.CloseDBConnection(); } } Anyone with any ideas why it might be jumping the patientDataAdapter.Update(dsAddPatient, "Patient"); and histologyDataAdapter.Update(dsAddPatient, "Histology"); statements? Thanks. Tag: Problem accessing SQL Server using Windows Authentication Tag: 114569

Hi,

I'm facing an while issue accessing SQL Server using Windows Authentication.
There is one webpart (Sharepoint) which gets connected to a database and
displays some data. There is one service account allocated - with Windows
Authentication enabled. Now when the user loads the webpage, wepart tries to
access using the connection string with Windows authentication. Webpart is
trying to access the SQL Server DB with the logged in user's windows account
- which does not have access to the DB. Is it possible to access the Database
for the logged in user using another Windows Authenticated service account.
Is it possible to execute one block of code with another service acccount
(another windows credentials).

Thanks in Adavance
Suhas

Top

Re: Problem accessing SQL Server using Windows Authentication by Frans

Frans
Wed Aug 03 02:43:40 CDT 2005

Suhas Vengilat wrote:

> Hi,
>
> I'm facing an while issue accessing SQL Server using Windows
> Authentication. There is one webpart (Sharepoint) which gets
> connected to a database and displays some data. There is one service
> account allocated - with Windows Authentication enabled. Now when the
> user loads the webpage, wepart tries to access using the connection
> string with Windows authentication. Webpart is trying to access the
> SQL Server DB with the logged in user's windows account - which does
> not have access to the DB. Is it possible to access the Database for
> the logged in user using another Windows Authenticated service
> account. Is it possible to execute one block of code with another
> service acccount (another windows credentials).

This is configurable in IIS. In your website's properties, you can
click on 'document security', in there click on edit next to
authentication and access control. Make sure 'integrated security' is
not selected/checked.

After you've configured your website correctly, the user defined for
the website, typically ASPNET, will be used to connect to the db. If
you want to use another user, use impersonation, OR use sqlserver
specific users.

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Top