I have a small form where a user can give comments if they wish, but the comments are not required. If a person doesn't enter comments and I enter the form data into my database, it will insert NULL.
Is there a way that I can insert just blank text instead of NULL?
I have a flat file with the following columns
SampleID  Rep_Number  Product  Protein  Fat  Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
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.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Good evening,
I have created a table like this:
create table Teacher(tid integer,
fname varchar(20),
lname varchar(30),
primary key(tid));
Well i insert many Teachers uning : insert into values ... etc.
These inserts are being placed in an sql file named insert.sql.
All the insertions are fine.
Now I'm trying to insert another teacher using another file e.g. my_insert.sql and it says cannot insert NULL into teacher tid etc.
Why does this happen,in my my_insert.sql file all the filds of the tid are filled with non null values?
What's going wrong?
Thanks, in advance!
i am using asp2003 and sql server 2000. in my SQL Server i am generating a field city_id automatically by incrementing the value by 1 and it is the primary key. so when i insert any data using my asp.net application it works fine on my system but it gives me error when i upload it to the web server. the error isCannot insert the value NULL into column 'city_id', table 'nevadb.nevauser.city_master'; column does not allow nulls. INSERT fails. The statement has been terminated.any help?
View 4 Replies View RelatedDear all,If user left the field blank, i would like to insert null value into dbHowever, I get by using following codeAny Solution private void RunTest() { sqlConnection1.Open(); sqlCommand1.Parameters["@Time"].Value = DateTime.Now; sqlCommand1.Parameters["@Info"].Value = null; //Error appear !sqlCommand1.ExecuteNonQuery(); Response.Write("Executive successfuly"); sqlConnection1.Close(); }
View 1 Replies View RelatedHow can we insert the null vales into the table..?
insert into test (4,NULL)
or
insert into test (4,'')
is this the right format..?
Hello Everyone,
This is my first time here.
I have been trying to turn off the "null" property for my id column.
And then I put yes for identity.
when i go to save it i get the error message,
"Cannot insert the value NULL into column 'ID'"
Can someone please help me out with this, its probably something simple, but i just cant figure it out.
Thanks,
PJ
Dear All,
I am populating some new tables from old tables, after I did some re-structuring to the database.
I have created a table with langID and langCode.
I also created a temptable, with the data being captured from an old table. The fields that are the most relevant at the moment are lid, and fk_langid.
In the old table, lid, has strings in it, for example en, fr, bg etc, and fk_langId will be the foreign key to the new table.
Now my problem is that there are some lid's which do not exist in the new table, and as so I need to hardcode an id for them in the new table.
So I was doing something like this:-
update #tempTable
SET fk_langid =
(
SELECT lid,
CASE
WHEN lid like '%sr%' THEN '1234'
END
FROM #tempTable
)
But I am getting an error:-
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Can you help me out on how I can update the fk_langid in the temptable
I have a datetime field I'm inserting records into from a field that is int. So some of the values have 20141214 and some have zero.If the field is zero how do I insert NULL? I'm using this to convert a non zero value to a date.
convert(datetime,cast(picked_dt, as char(8)))
insert into oeordlin(
picked_dt
select
?????
from oeord2
Does anyone have a simple way of inserting a NULL value into sql 2000 datetime. I'm using vb.net.
All I want is if the user does not enter a date in a textbox to to send a NULL value to the DB instead of having the db enter the default value as 1/1/1900.
Thank you
I am trying to create a drop down list with the possible usernames that you can send an email to.  Everything works fine except for the dropdown list and I get the following error message:Cannot insert the value NULL into column 'EmailTo', table 'db191165913.dbo191165913.Email'; column does not allow nulls. INSERT fails.The statement has been terminatedMy code is below:<%@ Page Language="VB" MasterPageFile="~/real_world/realworld_MasterPage.master" AutoEventWireup="false" CodeFile="send_messages.aspx.vb" Inherits="send_messages" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:Label ID="email_label" runat="server" Visible="False"></asp:Label> <asp:SqlDataSource ID="emailsend_source" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DB %>" DeleteCommand="DELETE FROM WHERE [EmailID] = @EmailID" InsertCommand="INSERT INTO ([UserID], [EmailTo], [EmailFrom], [Subject], [Message], [Status], [Reply], [Forward], [UserName], [SentOn], [IP], [BrowserInfo], [DNSInfo]) VALUES (@UserID, @EmailTo, @EmailFrom, @Subject, @Message, '@Status', '@Reply', '@Forward', @UserName, @SentOn, @IP, @BrowserInfo, @DNSInfo)" SelectCommand="SELECT * FROM WHERE ([UserID] = @UserID)" UpdateCommand="UPDATE SET [UserID] = @UserID, [EmailTo] = @EmailTo, [EmailFrom] = @EmailFrom, [Subject] = @Subject, [Message] = @Message, [Status] = @Status, [Reply] = @Reply, [Forward] = @Forward, [UserName] = @UserName, [BrowserInfo] = @BrowserInfo, [DNSInfo] = @DNSInfo WHERE [EmailID] = @EmailID"> <DeleteParameters> <asp:Parameter Name="EmailID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="UserID" /> <asp:Parameter Name="EmailTo" Type="String" /> <asp:Parameter Name="EmailFrom" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="Message" Type="String" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="Reply" Type="String" /> <asp:Parameter Name="Forward" Type="String" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="BrowserInfo" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="email_label" Name="UserID" PropertyName="Text" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="UserID" /> <asp:Parameter Name="EmailTo" Type="String" /> <asp:Parameter Name="EmailFrom" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="Message" Type="String" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="Reply" Type="String" /> <asp:Parameter Name="Forward" Type="String" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="IP" Type="String" /> <asp:Parameter Name="SentOn" Type="datetime" /> <asp:Parameter Name="BrowserInfo" Type="String" /> <asp:Parameter Name="DNSInfo" Type="String" /> </InsertParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="username_email" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DB %>" SelectCommand="SELECT [UserName] FROM [Membership] ORDER BY [UserName]"> </asp:SqlDataSource> <asp:ValidationSummary ID="ValidationSummary1" runat="server" HeaderText="You must fill out the following forms:" ValidationGroup="email_send" /> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="EmailID" DataSourceID="emailsend_source" DefaultMode="Insert" GridLines="None" Height="50px" Width="125px"> <Fields> <asp:BoundField DataField="EmailID" HeaderText="EmailID" InsertVisible="False" ReadOnly="True" SortExpression="EmailID" /> <asp:TemplateField HeaderText="To" SortExpression="EmailTo"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("EmailTo") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="emailsend_source" DataTextField="UserName" SelectedValue='<%# Bind("UserName") %>' AppendDataBoundItems="True" DataValueField="UserName"> <asp:ListItem>-- Receiving User --</asp:ListItem> </asp:DropDownList> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="DropDownList1" Display="Dynamic" ErrorMessage="Message Receiver (To)" ValidationGroup="email_send"></asp:RequiredFieldValidator> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("EmailTo") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="From" SortExpression="EmailFrom"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("EmailFrom") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:LoginName ID="login_name" runat="server" /> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("EmailFrom") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Subject" SortExpression="Subject"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Subject") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox3" runat="server" MaxLength="50" Text='<%# Bind("Subject") %>' ValidationGroup="send_email"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox3" Display="Dynamic" ErrorMessage="Subject" ValidationGroup="email_send"></asp:RequiredFieldValidator> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Subject") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Message" SortExpression="Message"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Message") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Columns="40" MaxLength="500" Rows="10" Text='<%# Bind("Message") %>' TextMode="MultiLine" ValidationGroup="send_mail"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox4" Display="Dynamic" ErrorMessage="Message" ValidationGroup="email_send"></asp:RequiredFieldValidator> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("Message") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:CommandField InsertText="Send Message" ShowInsertButton="True" ValidationGroup="email_send" /> </Fields> </asp:DetailsView></asp:Content>
View 2 Replies View RelatedHi,I have SQL Server DB, and I tried to insert some data. I used StorProc to do the insertion as follows:ALTER PROCEDURE dbo.InsertPage ( @PageID int, @ParID int, @ChiID int, @PageContent ntext )AS INSERT INTO MP_Page (PageID, ParID, ChiID, PageContent) VALUES (@PageID,@ParID,@ChiID,@PageContent) RETURNThe problem happened when I tired to insert int null value in field ChiID which is allow null and here is my codeprivate void InsertPage(PagesDB PageDB, out pagedetails pages) { if (txtChild.Text == "") { int varChildID; varChildID = int.Parse(txtChild.Text.Trim()); pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), varChildID, int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim()); } else { pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), int.Parse(txtChild.Text.Trim()), int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim()); } PageDB.InsertPage(pages); } the error message says Input string was not in a correct format.Any idea ??Thank you
View 3 Replies View RelatedHi all
Iam having some problems with null values
try
{
cmd.Parameters["@fmv"].Value = Convert.ToDecimal(TextBox13.Text);
}
catch
{
cmd.Parameters["@fmv"].Value =System.Data.SqlTypes.SqlDecimal.Null;
}
for Empty Textbox values
i am getting the following error,
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
My Database is allowed to accept null values.
System.Data.SqlType.SqlDatetime.Null works but SqlDecimal.Null does not work.
Could any one help me with this?
Thanks
Raj
Does anyone know the trick when inserting an empty variable into a MSSQL table for it to show as null and not a blank or a zero?
It is a php variable.
Thanks
Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls.
INSERT fails.
<code>
create table country(country_code nvarchar(2), country_name nvarchar(255))
create table states(country_code nvarchar(2),state_code nvarchar(20),state_name nvarchar(255))
create table weblocations (country_code nvarchar(2), state_code nvarchar(20),city_name nvarchar(255),timezoneid varchar(255))
INSERT INTO Location
([Country],[City], [State] )
[Code] .....
i have a table which has a column with datetime datatype... there is an application which inserts a blank space '' but gets inserts the value 1/1/1900 in that table. . is there any way which inserts a NULL into the table
View 1 Replies View RelatedHi friends,
I am trying to add records from one table into another.
SQL
use riqdb2
INSERT INTO tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO
Error msg
/*------------------------
use riqdb2
INSERT tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO
------------------------*/
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'ItemGradesID', table 'RIQDB2.dbo.tblItemGrades'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Please advise how to insert new records - Is it because I havent set up Identity (1,1)????
Thanks
Cm
I'd like insert null value from a query but i can do it,if my query is :UPDATE table:name UPDATE euro='' WHERE id='5';i have a sybase message warning like this :Error converting data type varchar to numeric.Why ????
View 4 Replies View Relatedhi guys. i'm using vb.net with vs 2003 and MSSQL Managment Studio Express as my database server.
i want to insert null value into database, so i use System.DBNull.Value
but it doesn't show NULL, but a empty field. I tried System.DBNull.Value.ToString, it gives me same result.
what can i do to have NULL in that field?
thanks for any advise, i would appreciate it very much.
Using SSCE 3.5 beta 2, I am loading a CSV file into a SqlCeUpdatableRecord using VB Net. The parsed contents of the CSV have three data types which are being set in the record using SetInt32, SetString and SetFloat. How can I insert a NULL value in the Int32, String and Float data type fields? A VB Net or C# example would be appreciated.
The .sdf database was created specifying NULLable fields.
Robert Wishlaw
I've 2 tables with a relation:
Customers
- CustId
- Name
- LevelId ==> FK relation to Levels.LevelId
Levels
- LevelId
- Description
What I want to do, is save a NULL value in the Customers.LevelId when no level is selected. I'm using C#, a CLR StoredProcedure and the DbType.Int32 for that column. When I try to save a null value, it says "cannot convert null to int", what is clear. But in the database it is possible to save a NULL value.
The question is: How to save a null value in a int column via a C# CLR Stored Procedure?
Hi dear experts:
The message is
"Cannot insert the value NULL into column 'ID_Month', table 'Database_DW.dbo.Incident_Summary'; column does not allow nulls..."
At my table, I have a primary key which make autonumber for any record set in column 'ID_Month'. How can I insert autonumber in column 'ID_Month'by SQL statement?
Thank you in advance.
Good Afternoon,
I'm using the personal website starter kit. I would like to add tags to my photos so I've added a Tags table (identity, tagDesc) and an intersection table PhotoTags (PhotoID,TagID). I pass a set of tags as a comma deliminated string such as (Home, Family). The procedure below (sorry I'm not a TSQL programmer) errors out with the message "Cannot insert null into TagDesc" or something like that.
Code Snippet
ALTER PROCEDURE dbo.AddNewPhoto
@AlbumID int,
@Caption nvarchar(50),
@BytesOriginal image,
@BytesFull image,
@BytesPoster image,
@BytesThumb image,
@Latitude numeric,
@Longitude numeric,
@Tags varchar(500)
AS
Begin
declare @Photoid numeric, @TagID numeric,@Spot smallint, @Tag varchar(25)
BEGIN TRAN
--First Insert into the Photos Table
INSERT INTO [Photos] (
[AlbumID],
[BytesOriginal],
[Caption],
[BytesFull],
[BytesPoster],
[BytesThumb],
[Latitude],
[Longitude] )
VALUES (
@AlbumID,
@BytesOriginal,
@Caption,
@BytesFull,
@BytesPoster,
@BytesThumb,
@Latitude,
@Longitude )
SET @Photoid = SCOPE_IDENTITY()
IF @@ERROR <> 0 ROLLBACK TRAN
------------------------------------------------------------
--now parse the tags and enter into PhotoTags and Tags Table
------------------------------------------------------------
--see if the tag exists
WHILE @Tags <>''
BEGIN
SET @Spot = CHARINDEX(',',@Tags)
IF @Spot > 0
BEGIN
SET @Tag = LEFT(@Tag,@Spot -1)
SET @Tags = RIGHT(@Tags,LEN(@Tags) - @Spot)
END
ELSE
BEGIN
SET @Tag = @Tags
SET @Tags = ''
END
IF (NOT EXISTS(SELECT TagID FROM Tags WHERE LOWER(TagDesc) = LOWER(@Tag)))
BEGIN
INSERT INTO Tags (TagDesc) VALUES (@Tag)
SET @TagID = SCOPE_IDENTITY()
END
ELSE
SELECT @TagID = TagID FROM Tags WHERE LOWER(TagDesc) = LOWER(@Tag)
--finally insert into the intersection table
INSERT INTO [PhotoTags] (
[PhotoID],
[TagID]
)
VALUES (
@Photoid,
@TagID
)
END
COMMIT TRAN
RETURN
END
Can someone see my error?
Hi,
If a datetime variable is empty I want to insert null into the database table.
How do i do this? Below code doesn't work. Get this error...
Cannot implicitly convert type 'System.DBNull' to 'System.DateTime'
Code Block
if (myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString() != "")
{
myDateReport = DateTime.Parse(myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString());
}
else
{
myDateReport = DBNull.Value;
}
Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errorsSetup failed.Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated.----------------------------------------Details of failure----------------------------------------SQL Server: Database: [AddressVerification]SQL file loaded:InstallCommon.sqlCommands failed:CREATE TABLE #aspnet_Permissions( Owner sysname, Object sysname, Grantee sysname, Grantor sysname, ProtectType char(10), [Action] varchar(20), [Column] sysname)INSERT INTO #aspnet_PermissionsEXEC sp_helprotectIF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Setup_RestorePermissions') AND (type = 'P')))DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissionsSQL Exception:System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??** I am logged in with full admin privaliges tiaMark.
View 3 Replies View RelatedI need to insert a null valvue when the user does not impute any text.
here is my code
If cell_phone.Text = "" Then
cell_phone.Text = "dbnull.value"
End IfDim mySqlConnection As New SqlConnection
mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection)
mySqlConnection.Open()
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
THanks
Mike
hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount, CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record: (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null. How could I do it?Please advice!
View 5 Replies View RelatedHi:
Trying to insert null value into sql table, but not working, if I use:
if (strMyText.Length == 0) command.Parameters.Add("@Text", DBNull.Value); // or using:("@Text", null), or using:("@Text", DBNull) else command.Parameters.AddWithValue("@Text", strMyText);
When I go back to table, I see the value is: 'NULL', has single quotation mark, suppose to be: NULL
Where is the problem?
Thanks a lot.
Jt
Hello,
I'm working on a website that allows users to upload small JPEG files. I followed the article at http://aspnet.4guysfromrolla.com/articles/120606-1.aspx. On the webpage I'm using a formview control to insert new records in the database. As suggested in the article I removed the "type='object'" from the insert parameters for the image column. The data is saved by using a sqldatasource with stored procedures. The image column is of type varbinary(max) and allows null values. Everythings works fine as long as the user uploads an image. The data is saved correctly and on another page the image can be viewed. However if the user does not upload a picture and tries to save the new record the following error is thrown:
�Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.�
In the formviews iteminserting event I have the following code: Dim imageBytes(fileupload1.PostedFile.InputStream.Length) As Byte fileupload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) e.Values("Image") = imageBytes What code should I use in case the fileupload1 has no file? I tried something like: e.values("Image") = dbnull.value But that doesn't work. Any suggestions?
I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim null As New Nullable(Of Integer)
Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter
Dim GiftOcc_ID As Guid
da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1)
Catch ex As Exception
Response.Write("Ooops")
End Try
End Sub
The code from the table adapter regarding inserts is:
<InsertCommand>
<DbCommand CommandType="Text" ModifiedByUser="False">
<CommandText>INSERT INTO [dbo].[Gift_Occasion] ([GO_ID], [Go_Type], [GO_Description], [GO_DateOn], [GO_DateOff], [GO_Active]) VALUES (@GO_ID, @Go_Type, @GO_Description, @GO_DateOn, @GO_DateOff, @GO_Active);
SELECT GO_ID, Go_Type, GO_Description, GO_DateOn, GO_DateOff, GO_Active FROM Gift_Occasion WHERE (GO_ID = @GO_ID)</CommandText>
<Parameters>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="Guid" Direction="Input" ParameterName="@GO_ID" Precision="0" ProviderType="UniqueIdentifier" Scale="0" Size="0" SourceColumn="GO_ID" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@Go_Type" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="Go_Type" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Description" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="GO_Description" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOn" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOn" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOff" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOff" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Active" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="GO_Active" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
</Parameters>
</DbCommand>
</InsertCommand>
Many thanks in advance for any assistance
Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty.
I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime.
If I don't set null value as above, some large date (01/01/2033) has been inserted in my database.
Can anyone help me?
Moe
I'm atempting to use an Instead of Insert Trigger on a view to insert a record into a base table. The base table boundaryline has an identity primary key defined. The following is the error I'm receiving:
The column 'id1' in table 'dbo.ParcelBoundaries1' cannot be null.
Could not insert a record in the database.
The view is as follows:
SELECT IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION,
IS_CONFLICT, TYPE, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI, id1
FROM dbo.BoundaryLine
The trigger is as follows:
CREATE TRIGGER TR_ParcelBoundaries1 on ParcelBoundaries1
INSTEAD OF INSERT
as
BEGIN
INSERT INTO BoundaryLine
(IS_TAXLOT,
IS_OTHER,
IS_CARTOGRAPHIC,
IS_RAILROAD_ROW,
IS_IRRIGATION_ROW,
IS_STREET_ROW,
IS_CLOSING_ROW,
IS_CONSTRUCTION,
IS_CONFLICT,
GMPID,
BEARING,
DISTANCE_LEGAL,
DISTANCE_CALCULATED,
GEOMETRY,
GEOMETRY_XLO,
GEOMETRY_XHI,
GEOMETRY_YLO,
GEOMETRY_YHI)
Select 'YES', 'NO','NO','NO','NO','NO','NO','NO','NO', GMPID, BEARING, DISTANCE_LEGAL,
DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI
from
inserted
END
Any thoughts would be greatly appreciated?