.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Parameters with DBNull.Value becomes "default" and causes error in SqlServer

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :ASP.Net

Hi, I have a table with a nullable column.

When I try to insert a record using INSERT statement I pass a parameter set to the value DBNull.Value.

Then I get an error from the SqlServer saying that the parameter was not supplied.

Using Sql-profiler i see that the applied parameter is set to the keyword 'default'.

I try to run the query inside Sql Server Management Studio, it looks something like this:

exec sp_executesql N'INSERT INTO [tbl] ([Val1]) VALUES (@Val1_P)', @Val1_P=default

First, why is the server giving the error about parameter not supplied? I have supplied it, and the value is keyword default.

Then I try to change the query to setting @Val1_P=null. This works..

BUT the problem then is, how do I set this from ADO Command object? I have set the parameter to DbNull.Value, and it translate it to 'default'.. What's going on here? I wanted the parameter to be the keyword 'null' not 'default'.

Any suggestions? Thank you in advance!

View Complete Post

More Related Resource Links

set server default error



i try to use the set server default for reporting services in wss3 but when i click on the link i get an error like this:

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:

<HTML dir="ltr">
<HEAD><meta name="GENERATOR" content="Microsoft SharePoint" /><meta name="progid" content="SharePoint.WebPartPage.Document" /><meta HTTP-EQUIV="Content-Type" content="text/html; charset=utf-8" /><meta HTTP-EQUIV="Expires" content="0" /><meta name="ROBOTS" content="NOHTMLINDEX" /><title>


</title><link rel="stylesheet" type="text/css" href="/_layouts/1033/styles/core.css?rev=5msmprmeONfN6lJ3wtbAlA%3D%3D"/>
<script type="text/javascript" language="javascript" src="/_layouts/1033/init.js?rev=qX%2BG3yl

ssrs 2008 R2 - report parameter with no default set throws java script error

We have recently upgraded from SQL Server 2008 to SQL Server 2008 R2....I am experiencing a problem with my report parameters on my SSRS reports. It seems that my report parameters that I do not have a specified 'default' value throws a java script error when I try and run/render the report under R2.  As soon as I put a 'default' value on the report param, everything is fine.  However, that is not the behavior we need for our reports...the user needs to pick their selection. Can anyone shed some light on this, it seems to be an AJAX problem...I need to be able to have report parameters with a list of values, but no 'default' value on the parameter.

Replication stored procedures not being passed parameters- default appears instead

Hi I am setting up transactional replication from Oracle to SQL Server, it is working fine for 9 tables, but for one it fails on updating and deleting with a message like this: Procedure or function 'spu_VGSM_SUBMISSION_CONTACT' expects parameter '@pkc1', which was not supplied. This is correct, in that the sp should be being passed this parameter, but when I do a trace it will show something like: exec "spd_VGSM_SUBMISSION_CONTACT" '12-3456-10','BLOGGS','S', '2010-07-01 12:43:43', 'M123456' default In other words, the old primary key values which should be being passed in are being replaced with "default". I am using the CALL syntax for the update sp. If anyone can suggest why this is I would be very grateful. This is the publication: exec sp_addpublication @publication = N'_Submission_Contact', @description = N'Transactional publication from Oracle Publisher.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'

getting sqlserver error 40 with "roles.AddUserToRole"

Hi,I am using Membership in my asp.net website built using VS-2005.I am able to create a new user using them Membership.CreateUser method or from asp.net Website Configuration but when I run "roles.AddUserToRole" from code-behind or create a new role using ASP.NET WebsiteConfiguration I am getting the following error (in asp.net website configuration).There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)I have followed these steps:-1>Enabled forms authentication in web.config by using <authentication> and <authorization> tags and verified the same set main.html as start page and set frmlogin.aspx as loginurl and upon running the website the user was getting redirected to frmlogin.aspx2>removed localsqlserver from connectionstrings and again

How to add WebMatrix default parameters?

That subject may not do this questions justice.I am trying to limit what I write on all my .cshtml pages.For example, on everypage I am @using myDLL and "connecting" to sql server.I write that out like @using System.myDLL; @{ myDLL dll = new myDLL(); dll.ConnectionString = "blablabla"; } How can I remove the need to do that to every single page? I think I can create a web.config and do some funky stuff... can someone show examples?

[298] SQLServer Error: 9001, The log for database 'tempdb' is not available.

I have SQL server 2000 standard edition running on Windows 2003 server. I am getting this error [298] SQLServer Error: 9001, The log for database 'tempdb' is not available. [SQLSTATE HY000] (ConnIsLoginSysAdmin) I have enough space and tempdb log file set  on unrestricted groeth. I don't know why I am getting this error

JDBC connection to SQLserver failing with ERROR [JDBCExceptionReporter] The connection is closed.

SQL server version: MS 2005 9.00.3050.00 Std edition Development version is on a stand alone server. Production is on an active/active cluster. Full error message below. There is an explanation and solution as noted below. However we do not see this issue in all our environments. Can anyone tell me why we see this issue in our production environment but not on our development environment, especially as the issue seems to arise following low usage times. Explanation / Solution If you are using the v1.2 driver, and you can positively rule out transient network connectivity issues and multithreaded usage, the next most common cause I am aware of is where a connection pool is being used and the pooling manager (e.g. DBCP or C3PO) does not test pooled connections for validity before handing them out to the app. What happens in this case is connections that sit idle in the pool for a very long time get closed by the TCP/IP stack, routing hardware, etc. Then they're already dead when the pooling manager hands them out. The solution is to configure the pooling manager to validate connections before handing them out, typically using a validation query like "SELECT 1". Error Message INFO | jvm 1 | 2010/09/07 07:52:11 | [09.07 07:52:11.396] LOG ERROR BROKO-EY-TBF-REDUCED-ENV-DP> INFO | jvm 1 | 2010/09/07 07:52:11 | - broko.quark.QueueMonitorThread.getNextCommand(QueueM

InfoPath 2010 Not Allowing Default Parameters Anymore?

It appears that InfoPath 2010 no longer allows you to set the default parameter values for secondary WebService data sources before they are automatically called during the form startup. When you set up a secondary data source to a standard web service call that takes parameters, you get prompted for the sample values.  However, when you run the form, these sample values are not sent to the web service, as you would expect. Furthermore, because the secondary datasource is automatically called when the form starts up, you are not able to set the values before the automatic call because the rules processing occurs AFTER the initial call to the web service. I realize that I could do this in code in the Startup routine, but the problem is that we have users who want to stay away from coding and want to use the functionality of InfoPath and it's "easy to use interface" to be able to create forms.   Now I have to tell them that they need to start to learn C#? Am I doing something wrong here?    Or is this a big oversight on this release of InfoPath? Microsoft: If you are listening...could you please comment on this?   I would consider this a bug.

SQLServer Express 2008 R2 - Cannot connect Error: 18456, Severity: 14, State: 11.

I have just installed  running MSSQL Server 2008 R2 (Express), along with Visual Web Developer 2010 Express In SQL Server Configuration Manager I can see the SQL Server Service running (SQLEXPRESS) and the SQL Server Browser service running. On running SQL Server Management Studio when trying to log on I get the message Login failed for user Phil-PC`Phil (Microsoft SQL Server, Error: 18456) In the SQL Server Error log I see this error 2010-09-16 10:24:16.07 Logon       Error: 18456, Severity: 14, State: 11. 2010-09-16 10:24:16.07 Logon       Login failed for user 'Phil-PC\Phil'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>] I have been unable to find anything useful on Google on how to resolve this problem. I have tried running the command line utility sqlcmd -L and this lists the following Servers:     Phil-PC     Phil-PC\SQLEXPRESS   In my list of Installed programs in Control Panel I see both MS SQL Server 2008 & MS SQL Server 2008 R2, is that correct?

Error: Access Denied on default.aspx and other pages of existing and new sites

Hi, since today we can't access the project portals of all our projects (Visual Studio TFS 2008/sharepoint admin 3.0 running on esx 4.0). I already checked every possible security settings, they're all OK. I also tried creating a new site but I can't access the site. I can however access all the settings. For example when I try to access: http://server/Sites/Profel I get the "Error: Access Denied" message and the browser is redirected to http://server/Sites/Profel/_layouts/AccessDenied.aspx?Source=http%3A%2F%2Fserver%2FSites%2FProfel%2Fdefault%2Easpx I have no problem navigating to: http://server/sites/Profel/_layouts/settings.aspx and I can go to al lot of pages, but when I want to create something like a calendar I can specify the parameters but when I click the create button I get the same error. http://server/Sites/Profel/_layouts/AccessDenied.aspx?Source=http%3A%2F%2Fserver%2FSites%2FProfel%2FLists%2Ftest%2Fcalendar%2Easpx&Type=list&name=%7B46CB3B46%2DD0F6%2D47F5%2DBA1A%2D3C9654A24A8E%7D I already tried this: http://support.microsoft.com/kb/926642  http://support.microsoft.com/kb/896861/en-us but no success Any help is welcome, Greetz, Peter

Search Administration: Default content access account - 'Error'


In Search Administration; Default content access account show 'Error'.
To fix it, I have to always restart 'Office Search Service'.

But need to find permanent solution.

I went through various blogs and found below blog with sucess response.
I tried same on my server but did not help.

Can someone help?

Error: No value given for one or more required parameters


Hi.. Im getting error: No value given for one or more required parameters. in following code. I checked the code n found that if i remove cmd.Parameters.AddWithValue("PERIOD", period); from code and [period] = ? from command text, everything works fine. I am not getting what is wrong with period.
I am using access database and every field in database is text.
string period;
public void calc()

Error creating edit form for external list - Failed to find the default form for PAGETYPE PAGE_NEWFO


For the last few days we have been unable to create custom new/edit forms for External Lists in Sharepoint Designer.  When i try to create a form Sharepoint Designer show the message 'Could not save the list changes to the server'.  The corresponding message in the ULS log file is 'Failed to find the default form for PAGETYPE PAGE_NEWFORM'.  Does this mean i have somehow managed to break the form templates for External Lists?

I saw some other posts about the 'Could not save the list changes to the server' error and followed the suggested steps including removing all versions of the list and related content type, and recreatign htem but this didn't work.

Why do I get default error page desipite setting customErrors mode="Off"?


I could never figure it out -  set  <customErrors mode="Off" /> in all web.config files that exist under Interpub/wwwroot, but still get default error page. Which web.config does SharePoint use?

On Change:Prevent Evaluation fails with error Could not load file or assembly 'Microsoft.SqlServer.


Hi All,

I am facing a strange issue with Policy Based Management. Exact problem is as below

SQL Setups


I have Lenovo laptop having 3 SQL instances installed on it.

SQL Server 2005 (x86) Developer Edition 9.00.4053  -- Named Instance : SQL2K5

SQL Server 2008 (x86) Developer Edition 10.0.1600.22 -- Named Instance : SQL2K8

SQL Server 2008 R2 (x86) Developer Edition 10.50.1600.1 -- Named Instance : SQL2K8R2


I configured SQL2K8R2 instance as a Centralized Server and registed SQL2K8 & SQL2K5 under this. I configured a new condition by using facet: MULTIPART NAME. As per this condition Table Should start with TEST_ in database TEST . Evaluation Mode set to              On Change: Prevent. Idea is if some one logs to database TEST and tries to create table with name other then starting with TEST_ then it should fail.

I Imported this policy on registered servers thru Centralized Management Server Action, as expected policy got configured on Registred SQL2K8 instance

SqlServer 2008 Express Install error


Guys, any one know how to fix this ?

I installed the SqlServer Express 2008 on top of WinXp sp2 with .net framework and sdk 3.5

Now when I try to start the sqlexpress service it does not start and event log has following exception..

FileMgr:Tongue TiedtartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

Now on my system I have only c:\ drive no e:\

Does any one know how to fix this ?
Is there a way to create this master database somehow using command line?

I really don't want to uninstall/install ?

Multiple Parameters Error OLEDB MS Access SSRS



I have created an SSRS in BIDS 2008 with OLEDB Provider so as to access MS Access 2010 DB. My report is working fine. But the only problem is when am sending multiple parameter it raises error. I have enabled AllowMultiple Value checkbox.

ERROR : "Cannot add multi value parameter '@parametername" for DataSetName because it is not supported by the data extension".

My query is "SELECT * FROM tablename WHERE ([columnname] in (@parametername))"

Thanks in advance



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend