SQL Query String insert a null with ADO.NET

 I have an SQL Update query with four string variables that I get from a textBox.Text to pass to DB. When data is present, no problem, but when There is NO data to Update, I want to pass a null to the database. I tried:

        string box1 = txtCustMachSN.Text;
        if( box1 == "" || box1 == "0" ) { box1 = System.DBNull.Value; }

       string box2......

        string strCmd = "UPDATE Equip2 SET CustomerSerialNo='" + box1 + "', Size='" + box2 + "', ServDt='" + box3 + "', Count='" + box4 + "' WHERE SerialNo=' " +  Item5 + " ' and EquipType= '" + Item6 + " '";

 ERROR: Cannot implicitly convert type System.DBNull to string...

 

How can I Insert a null when TextBox is MT or "0"? 

Thank you so much for any advice....

 

Edski

        

0
edski
12/18/2008 5:41:19 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
900 Views

Similar Articles

[PageSpeed] 11

Make following changes to the code. In any case I am afreaid you run the risk of SQL injection. I would recommend using parametrized sql instead, in which case you can actually make use of DBNull class. 

 

string box1 = txtCustMachSN.Text;
if( box1 == "" || box1 == "0" ) { box1 = "0" }

string box2......

string strCmd = "UPDATE Equip2 SET COALESCE(CustomerSerialNo, 0)='" + box1 + "', Size='"


Sambeet
0
sambeetpatra
12/18/2008 7:43:12 PM

 Did not like it, bad syntax to :

string MyString = "UPDATE Equip2 SET COALESCE(CustomerSerialNo, 0)='" + box1 + "', COALESCE(Size, 0)='" + box2 + "', COALESCE(ServDt, 0)='" + box3 + "', COALESCE(Count, 0)='" + box4 + "' WHERE SerialNo=' " + Item5 + " ' and EquipType= '" + Item6 + " '";

 Does my Query string look good to anyone?

Thanx

0
edski
12/19/2008 4:07:12 PM

Sorry, my bad. I guess it was a copy paste error when I posted the reply. The correct query string should have looked something like [I have only made changes for box1 here:

 string MyString = "UPDATE Equip2 SET CustomerSerialNo=" + (box1 == "0"? "NULL" : "'" + box1 + "'") + ", COALESCE(Size, 0)='" + box2 + "', COALESCE(ServDt, 0)='" + box3 + "', COALESCE(Count, 0)='" + box4 + "' WHERE SerialNo=' " + Item5 + " ' and EquipType= '" + Item6 + " '";


Sambeet
0
sambeetpatra
12/19/2008 7:03:36 PM
Reply:

Similar Artilces:

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

how to represent Membership.GetAllUsers(); as an sql query in ADO.net?
 How would I recreate Membership.GetAllUsers(); as a raw sql query? for example I found this online: If Membership is being used on the same site, just call Membership.GetUser : MembershipUser mu = Membership.GetUser("username"); Guid g = (Guid)mu.ProviderUserKey; From a SQL query standpoint - you can do the same thing with the following code in ADO.NET or in SQL in a sproc: select m.UserId from dbo.vw_aspnet_Applications a, dbo.vw_aspnet_Users u, dbo.vw_aspnet_MembershipUsers m where a.LoweredApplicationName = LOWER(@pApplication...

How to insert data into sql server in bulk using ADO.net
Hi!  I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.   Thanks! ccy Hi, Try DataAdapter's Update() method. Thanks and best regards,Faraz Shah KhanMCP, MCAD.Net, MCSD.Net, MCTS-Win/Web, MCPD-WebBlog HI, If you want to just insert the data then you can use sqlbulkinsert class. If you want to update a...

weird difference between t-sql and ado.net query results
Hi All: I have a really simple query that returns a recordset with a count for each row...if I run the query in an SSMS query, I get one set of results, but if I run the query in my code-behind, I get different results? I don't know how that could be possible (except I'm making an obvious mistake somewhere)? In SSMS I have: SELECT     s.radiation_dose, COUNT(s.radiation_dose) AS Expr1FROM         table_procedures AS s INNER JOIN               &nbs...

Query SQL DateTime column using ADO.NET Entities Framework
I have a SQL table called Slots with a Datetime column named Date.  I would like to query the db and return all rows where the date is equal to a date provided by the user on an asp.net form.  I have tried various ways to covert the datatime column, but always get an exception.  Any suggestions how to query a SQL datetime field where the date column is equal to a date specified by the user?    using (DBEntities1 myDB  new DBEntities1())   {      try        {  string querySt...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

Working in VB.Net 2005 inserting NULL into a field in Microsoft SQL Server 2005
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL'  into my microsoft sql server database.  I am not talking about the below line of code where website is the name of my paramater.  If i do that it will just place a blank into that field in the database.  If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbo...

ADO.NET 2.0 - Three-tier application - connection string error
Using ASP.NET 2.0 beta, VS2005 beta 1, W2k3 server with IIS I try to build a three-tier app structure as followed: - the store procedure in SQL with a input parameter as Username and retrieve the UserId as output parameter. - the business logic (class) with connection string defined in web.config (“AppConnectionStringASPNETapp”) - the presentation aspx file after the user has logged in through login.aspx I have work out 2 scenarios with 2 different errors. Scenario 1: Here is the code is business logic (class) (catalog.cs) using System; using System.Data; usi...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column)  I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...

Linq To Sql or Ado.net?
Which is better Linq To Sql or Ado.net for performance ?  Linq to SQL is built on top of ADO.NET, so the natural answer is that ADO.NET is the faster one. The actual performance is more a product of how you implement your solution, not what you implement it with.-- "Mark As Answer" if my reply helped you --  ADO.net (using SQLDataSource) is much faster, though Linq is great if you don't need to shift lots of data about and you want neat and tidy code with Intelesense.  Liam Mcmullen...

sql insert .net question
Hello, wrote an insert for a C#.Net web app. The insert works fine, except if there are appostrophies (spelling) for example: this isn't a problem this isnt a problem the second one will work the first will not. The info is going into a sql server database as text(16), the same result occurs with char. I believe you'll need to use an escape character "\", so something like this: "this isn\'t a problem" Also, you should try to use stored procedures or at the very least paramater queries. Both of those would solve your problem as well."I would l...

.net + dynamic SQL-query
On our page we have texbox, dropdownlist, database and gridview for it.It DropDownList we have all names of columns from database (in our situation they are Item_Name, Item_Type,  City and Country). In database we have two more columns, but we do not need them in this task.And the task is to choose from DropDownList item/value and deliver it to SQL-query. We have done searching from database, but the column to search from is static, we want to make it dynamic, we want to choose from Dropdownlist column, from which we will search.We are making all in aspx, not in .cs code. We are not fam...