Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()

SQL Server Data Access

Index ‹ SQL Server
- Archive
  - ISV
  - VFP
  - Game
  - Windows Vista
  - Windows Forms
  - Dotnet
  - SharePoint
  - Architecture
  - VC++
  - SQL Server
  - Visual Studio
  - Visual C#
  - Windows Live
  - VS 2008
  - VB
  - Office
  - IE
  - VS Express
  - Smart Device

- Previous
  - 1
    - How to deploy an report and connection string from some xml file What is an expression based connection and how can I pass my connection string (or components of it) in as parameters. Please let it clear a little bit Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 2
    - Help sorting a Recursive Query It works! But can you explain to me how you came up with this. I would like to understand it as well. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 3
    - "Select * from Table" is processed much faster than "Select MyField from Table" ???? I have never seen code something like this before. Try to re-write the logic to bring the resultsets bring using other techniques like left joins avoiding not exists and also using inner joins to avoid exists. I am not saying exists is bad but this many exists for one query, I think there are better ways to do it. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 4
    - Usage Based Optimization - Problem The following newsgroup thread might be worth looking at: http://groups.google.co.uk/group/microsoft.public.sqlserver.olap/browse_frm/thread/221161efe1f2172f/b315943016bdd2a8 lnk=st&q=%22Usage+based+optimization%22+.mdb&rnum=2&hl=en#b315943016bdd2a8 You can find the Query Log Access database here: C:\Program Files\Microsoft Analysis Services\Bin\msmdqlog.mdb On a standard install of AS2K. HTH, Chris Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 5
    - DTS problem on 64-bit installation Is there solution of the DTS problem for Vista 64-bit The one below doesn’t work for 64-bit installation. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 6
    - Can I change from Integrated Security? When creating my database, I initially set it up to use Integrated Security (Windows Login), however I am having a hard time logging into the database on a consistent basis using this method. Can I now change to a specified user id and password How would I do that Would I lose any data in my database Does the user id and password need to be the same user id and password used when logging into SQL Server Thank you. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 7
    - Remove SP1 or install SP2 Dec. CTP Actually it is possible to uninstall SQL Server 2005 service packs - just use Add or Remove Programs in the Control Panel on the server, ensuring you have the 'show updates' check box enabled. Regards, Gary. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 8
    - Insert Identity value with SqlCeResultSet Hi I have a table with an identity column. This is set to increase by 1 and start on 1, and is the first column of the table. When I insert a record into the resultset, I get an error stating that it can't be DbNull. Th record is created from a resultset with the query "Select * from table", so the id should be included But as I understand it, it should be increasing automatically, right Becuase that's the behaviour of the normal sql Insert command. I have seen an article that just left the id column in the record alone before inserting it, but it didn't help me. How can I sidestep this issue Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 9
    - Add Reference to Excel in Scripting tool in SSIS 2005 How can I add a reference to the Microsoft excel 2003 in the script tool in SSIS 2005 I need to automate Excel for formatting. Thank you. Dan Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 10
    - How to change the target table name in SSIS package(not designed from scratch but was migrated) What tasks are in the package Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 11
    - Inconsistency between Data Source Designer and underlying code I have 2 data sources that have recently been updated from SQL Express to full versions of 2005. The connection strings have been changed, and the changes appear in the code, but the data source designer still shows the SQLExpress portion of the connection string. This seems to be fouling up SSIS packages that are using these data sources. Has anyone else encountered this If so, what can I do to fix this issue Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 12
    - SQL Server 2005 Express Is there no way in SQL Server Management Studio 2005 Express (SSMSE) to migrate the data only to an already provisioned database on another server Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 13
    - Increase Transaction Log size Hi, How can I increase the transaction log size of my sql server 2005 database i can execute the shrink and backup log which can shrink the .ldf file to 1MB. However, since my dts involves a lot of inserts and updates, the my transaction log grows to 700MB in the middle of my dts execution. How can i increase the transaction log size maximum capacity to probably 1.5GB just so it can accommodate my full dts execution BTW, this is my commands to shrink the log file: DBCC SHRINKFILE ( <dbname>_log , 1 ) BACKUP LOG <dbname> WITH NO_LOG DBCC SHRINKFILE ( <dbname>_log , 1 ) cherriesh Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 14
    - top 10 accounts for every day with summary hi, i am new to mdx and stuck on a problem on analysis services 2005. there is an orders cube with calendar and accounts dimension. the report i want to do is something like this date account quantity top10percentage 01.01.2007 All 1000 85 bnm 230 23 abc 150 15 bbc 140 14 aaa 100 10 wer 60 6 rrr 40 4 xyz 35 3,5 xtv 35 3,5 tyu 30 3 tbh 30 3 02.01.2007 All 1230 75 abc 150 15 bnm 150 15 bbc 140 14 aaa 100 10 txp 60 6 rrr 40 4 xyz 30 3 xtv 30 3 tyu 25 2,5 tbh 25 2,5 i have written a calculated member for the percentage of the account for that day's total order amount. but when i do a filter on accounts dimension to get top 10 records for that day in excel 2007 the total value of the percentage for that day remains 100. not the sum of the top 10 records percentage values. i am looking a way to write a report like this and appreciate any suggestions. and also any workaround to solve this excel behaviour. thanks in advance Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 15
    - Improving Performance Hi Tech_Andrew, I guess you do not have any indexes on your table, can you post some samples of the types of queries that are being executed against the data From this it might be possible to suggest some improvements in retrieving data. For example if lots of queries want to know what happened on a certain server between such and such and date a couple of indexes should have the whole thing executing more efficiently. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 16
    - Copy objects with DTS Import/Export Wizard? That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000 Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
- Next
  - 1
    - Select Statement In Table (Valid Expressions Error). We are currently implimenting row level security by putting part of the Customer's account information in a DB Table. I have been able to do this on a case by case (individual customer) basis, but I tried to script inserting a whole bunch of customers at once using a script. When I run the script just using print commands, everything looks fine. When I run it to actually process the data, things bomb out. Example Customer Table being sent to me: Name AccountNo ----- ----------- abc A5071 def 57791 ghi 8W551 jkl 07A11 & 13866 mno 015X1, 0725A, 07551 Note there can be more than one leading zero in the accountNo. So I want my new table ("New_Cust_Table") to look like: UserId Pwd SelectStmt ------ ------ ------------------------------- abc1 abc1# And AccountNo LIKE '%A5R7' def1 def1# And AccountNo LIKE '%5779' ghi1 ghi1# And AccountNo LIKE '%8W55' jkl1 jkl1# And (AccountNo Like '%07A11' OR Account LIKE '%13866') ... etc. Ignoring the Cust Names with multiple Account numbers for the moment, my SQL Looks like this: -- DECLARE VARIABLES DECLARE @UserID varchar(20) DECLARE @UserPwd varchar(20) DECLARE @SelectStmt varchar(200) DECLARE NewCustUser CURSOR FOR SELECT Name, AccountNo FROM Cust_Table OPEN NewCustUser FETCH NEXT FROM NewCustUser INTO @UserID @SelectStmt WHILE (@@FETCH_STATUS = 0) BEGIN IF (LEN(@SelectStmt) <= 6) -- ACTUALLY GET MULTIPLE ACCOUNT Nos for ONE Customer Begin Print 'Account is ' +@SelectStmt+' for '+@UserID SET @SelectStmt = 'AND AccountNo LIKE ''%'+@SelectStmt+'''' Print @SelectStmt SET @Statement = N'INSERT INTO [dbo].[New_Cust_Table] VALUES ( '''+@UserID+'1'','''+@UserID+'1#'','''+@SelectStmt+''')' Print @Statement EXEC sp_executesql @Statement END FETCH NEXT FROM NewCustUser INTO @UserID @SelectStmt END CLOSE NewCustUser DEALLOCATE NewCustUser Some of the error message I get: - Incorrect syntax near ''. - The name "A5R7" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. Perhaps there is a CAST, CONVERT or COLLATE is needed I have experimented with those and have not found anything that works just right yet... Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 2
    - Why does using the "in" clause cause an error in my "Having" condition in 2000, but not 2005? Okay, here's the real query: select "Business_Grp" . "BusinessGrpAbbrev" AS "level0key" , case when "Business_Grp" . "GLUnitCode" in ( '012201' , '09C202' , '10C010' ) then 'A' else 'B' end AS "levelkey" , sum ( "Facility_Snapshot" . "TotalCmtmnt" ) AS "Total_Cmtmnt" from "t_mlcdm_d_businessGrp" "Business_Grp" , "t_mlcdm_f_facilitySnapshot" "Facility_Snapshot" where "Business_Grp" . "BusinessGrpKey" = "Facility_Snapshot" . "BusinessGrpKey" group by "Business_Grp" . "BusinessGrpAbbrev" , case when "Business_Grp" . "GLUnitCode" in ( '012201' , '09C202' , '10C010' ) then 'A' else 'B' end having case when ( "Business_Grp" . "GLUnitCode" in ( '012201' , '09C202' , '10C010' )) then 'A' else 'B' end = 'A' Keep in mind that this works correctly in SQL 2005, so the syntax should be valid as is. Replace all the case statements with simple comparisons, and it works in SQL2000 as well. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 3
    - Dose anybody knows how to use MergeJoin component programitcally? I'm building a package programitcally and I can't find any document which shows how to work with MergeJoin component in code. Please help, Thanks Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 4
    - SQL Server 2005 SP2 cannot be installed because the Database Engine setup remains in suspended installation mode SP2 refuses to install for the Database Engine because “the previous upgrade did not complete”, i.e. it detects a suspended installation. The same thing happened to me when I installed SP1. I then remember opening the maintenance dialog and completing the setup, after which SP1 installed just fine. When I try to do the same thing this time, I’m presented with the ”complete the suspended installation” option, which when I run it reports the installation completed successfully. However, in reality the installation still remains in suspended mode for whatever reason. If I reboot and open the maintenance dialog, I’m still presented with the ”complete the suspended installation” option. I’ve even tried to run it more than once, but it doesn’t help. Needless to say, SP2 still refuses to install. I don't know why the installation is in a suspended state. I know every part of SP1 installed successfully in the end, and the computer never crashed during the installation. I've been using it every day since then, without any problems. All other parts of SP2 did install successfully. I created a feedback entry on Microsoft Connect, where you can find the log files created by Windows Installer. The URL is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=259606 Any ideas what I can do to resolve this problem Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 5
    - patindex with nvarchar Your query is working fine on my mac.. I tried with following statement ... Code Snippet Select * From dbo . GetKeywordsFromQuery ( N '"SQL Server" is "very good" AND "very Powerfull" AND ' ) OUTPUT: Word ------------------- SQL Server is very good very Powerfull What I am doubting here is @QUERY parameter. Pls check the datatype. And when you call the SP you should prefix the N. Example: Code Snippet Exec dbo.YourSP @query = N '"SQL Server" is "very good" AND "very Powerfull" AND ' ) Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 6
    - MDX Ranking Question Hi Michael, This is a common requirement, but it's beyond the ordered SQL temp table approach. You could use Rank() dynamically, as shown in the query below. But there might be a performance penalty - see this 2005 thread from the OLAP newsgroup: With Member [Measures].[QuantityRank] as Rank ([Product].[Category]. CurrentMember , [Product].[Category].[Category], [Measures].[Order Quantity]) Member [Measures].[SalesRank] as Rank ([Product].[Category]. CurrentMember , [Product].[Category].[Category], [Measures].[Sales Amount]) Member [Measures].[CostRank] as Rank ([Product].[Category]. CurrentMember , [Product].[Category].[Category], [Measures].[Total Product Cost]) Member [Measures].[OverallActivity] as 1/( (1/[Measures].[QuantityRank])^2 + (1/[Measures].[SalesRank])^2 + (1/[Measures].[CostRank])^2 ) Member [Measures].[ActivityRank] as Rank ([Product].[Category]. CurrentMember , [Product].[Category].[Category], -[Measures].[OverallActivity]) select {[Measures].[Order Quantity], [Measures].[QuantityRank], [Measures].[Sales Amount], [Measures].[SalesRank], [Measures].[Total Product Cost], [Measures].[CostRank], [Measures].[OverallActivity], [Measures].[ActivityRank]} on 0, [Date].[Calendar Year].[Calendar Year] * [Product].[Category].[Category] on 1 from [Adventure Works] Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 7
    - BCP loading failing on timeout error Yes I am using Cognos 7.3 version. Drive Space for temporary isquite high. SQL Server 2005 Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 8
    - allows blank value for integer Parameter Hi have a problem to solve and I hope that this is not a SSRS Bug. I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP. One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway. I starts to define tha parameter: Datatype = integer Allow blank value Available: Non queried Default: Null if I want to Preview the report I have to provide an integer to the parameter's field ... If for instance I set: Default: Not queried = 0 In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!! So I tried this solution: Datatype = integer Allow blank value Allow null value Available: Non queried Default: Null In the preview the checkbox: NULL is checked and I click on the View Report. But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked. Do I forget something during my setting I have to control it programmatically N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!! Thank you for any help! Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 9
    - No one can help me on this prolem?????? Hi Jordi, Thanks for your message. Actually, I had tried this before if I don't misundstand what you sugested. 1. created @Employee at report side and passed the value{n1,n2,...,nk} to it from web app; 2. created @EmployeeName at report isde and set its value come from =parameters!Employee.Value, lable = parameters!Employee. Lable or. 2. created a dataset select EmployeeName from tableName where EmployeeName IN (@Employee), then, created @EmployeeName and set its value come from this dataset when the report was running, I had been able to get the values I wanted {n1,n2,...,nk} just liked what I passed from the web app with checkboxes beside them, but after I checked some of them and clicked "view report", the values in the @EmployeeName always showed the default value I set when created it (e.g., the value came back to 'Null' if the default value was 'Null', or 'n1,n2' if the default value was 'n1,n2') and the result report came out agains the default value. It really comfuse me! Did I do something wrong Any response will be appreciated!!!! Jone Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 10
    - SSIS deployment - redefined < xml version="1.0" > - < DTSConfiguration > - < DTSConfigurationHeading > < DTSConfigurationFileInfo GeneratedBy =" prev \user1 " GeneratedFromPackageName =" Package1 " GeneratedFromPackageID =" {0C5712C4-BFAD-45B6-A75D-E8C1BBCC9D29} " GeneratedDate =" 06/11/2007 15.14.52 " /> </ DTSConfigurationHeading > - < Configuration ConfiguredType =" Property " Path =" \Package.Connections[MyProject].Properties[ConnectionString] " ValueType =" String " > < ConfiguredValue > Data Source=X3SQLSERV;Initial Catalog=DBTest;Provider=SQLNCLI.1;Integrated Security=SSPI; </ ConfiguredValue > </ Configuration > - < Configuration ConfiguredType =" Property " Path =" \Package.Connections[RiskMgmt.mdb].Properties[ConnectionString] " ValueType =" String " > < ConfiguredValue > Data Source=F:\RiskMgmt.mdb;Provider=Microsoft.Jet.OLEDB.4.0; </ ConfiguredValue > </ Configuration > </ DTSConfiguration > that is my example config. file. So, if I add all of my connections as < Configuration ConfiguredType =" Property " Path =" \Package.Connections[MyProject].Properties[ConnectionString] " ValueType =" String " > < ConfiguredValue > Data Source=X3SQLSERV;Initial Catalog=DBTest;Provider=SQLNCLI.1;Integrated Security=SSPI; </ ConfiguredValue > </ Configuration > in one config file, I should be OK It doesn't metter that the attribute GeneratedFromPackageName =" Package1 " references one package of my is project Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 11
    - Merge Replication (Push) over Internet Hi all, I was wondering if someone could suggest a solution to my problem: I have an in-house production database. I wanted to use Merge replication (push) to mirror parts of this data to our Internet server on the web which has a Public IP. Steps I followed 1. Created an entry in “C:\WINNT\system32\drivers\etc\hosts” file with IP address of the Subscriber and just the “Servername” of the Subscriber on publisher machine (local server) 2. Created an Alias to the Subscriber 3. Connected to the remote server thru Mgt Studio 4. Created Merge Replication on the Local Database 5. Created Subscription to the remote server. 6. Started the Replication Agent I receive following msg " The process could not connect to Subscriber 'ISVR'. When I check the View Synchronization Status in Mgt studio it shows "The server 'ISVR' is not a Subscriber" I presume that if we have a Public IP at our end, then the Merge Replication will be thru. Is it possible to replicate without having a Public IP Any suggestions, Thanks. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 12
    - Secure SQL Server remote access from ISV application mrdenny wrote: There is no way to block the local user from connecting while still allowing your application to connect. I believe that it is possible to restrict users to login only through application with the use of application roles ( sp_setapprole ) [1]. Search BOL, internet for more articles on the issue [1] SQL Server 2005 BOL Application Roles http://msdn2.microsoft.com/en-us/library/ms190998.aspx Added later: Check also my answer in http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1612977&SiteID=1&mode=1 Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 13
    - SQL server 2000 move Madhu thanks for the reply. I tried tranfering the logins and it said that it was seccussful but when I go into the users of the master DB they are not there. any ideas of what I'm doing wrong thanks for you help. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 14
    - Using CTE in data paging I select only 20 records at a time. But I need to send the count for entire selection. If I count on the client side I will always get up to 20 . Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 15
    - Aggregating records and displaying Hi, Thanks hunchback for the quick reply. I keep getting the error : Column 'dbo.class.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I tried adding the GroupBy to the inner select without much success. Any clues!! Thanks. Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()
  - 16
    - Cannot connect to SQL Server with PHP Anybody Tag: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()

Ionix

Hi

I have done a SQL statement INSERT for my coding. The program compiled and run the insertion with no error occurred. But after the compilation I found out that my database is not populated at all.

I tried the data insertion using the manual query, and I work perfectly.

So the problem now is that I do not know why my codes did not insert the data into my database. Will like to get some advise. Thanks

My codes are pasted in http://www.pastebin.ca/320102
The Debug.Writeline in the code is in http://www.pastebin.ca/320108

Thanks again.

Re: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()

Figo Fei - MSFT

Hi,

Your thread seems to be more appropriate here and hope you find satisfactory answers.

Thank you for understanding

Re: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()

Jens K. Suessmeyer

Hi,

start SQL Server profiler to see what ishappening behind the scenes and post back the printed results. Include the STMT events as well as exceptions.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Re: Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery()

Ionix

Hi

I think I have found the problem the database connection i set to. The database in my project folder is the one that is appearing on my IDE's server explorer. But the program was actually inserting data to /bin/debug/database.mdf instead. That is the reason why I am unable to see the data in my database.

How can I make my debug binary point to the same working database instance, than to create mulitple copy of the database