Issue Inserting Null Value Into A Formview/gridview Control

Jan 14, 2007

Hi,


My formview or gridview control stops updating or deleting a record once the record has a null value.


I have table tblTest with the following


pkID int NOT NULL **IDENTITY COLUMN**
string1 varchar(30)
string2 varchar(30)


I then create a SqlDataSource with the statement:


Select * From [tblTest]


I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.


I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.


So, if I had a record in my FormView like:


string1: foo
string2: bar


I can update and delete normally. And when I update to:


string1: foo
string2:


the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.

What am I doing wrong? Should i be binding to a different object?


Regards,


Chris

View 5 Replies


ADVERTISEMENT

Question - Inserting With Formview Control

Nov 10, 2005

Using formview control, I'm trying to insert a record. In the process of inserting, I want to save the categoryid from a shared class. The code runs fine but categoryid gets saved as null...Any pointers?? [When I display, it shows the value]
thanks
 
protected void savebutton_click(object sender, EventArgs e)    {        //this statement runs fine under debug...but values do not get saved???        SqlDataSource1.InsertParameters.Add("@category", SharedValues.category.ToString());        SqlDataSource1.Insert();    }   
 
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="itemid" DefaultMode="edit" OnDataBound="FormView1_DataBound">    <EditItemTemplate>               Title:        <asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>        </asp:TextBox><br/>        Description:        <asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">        </asp:TextBox><br/>                      <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"            Text="Update">        </asp:LinkButton>        <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"            Text="Cancel">        </asp:LinkButton>            </EditItemTemplate>    <InsertItemTemplate>        Title:        <asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>        </asp:TextBox><br/>        Description:        <asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">        </asp:TextBox><br/>            <asp:label ID="categoryLbl" runat="server" Text='<%# SharedValues.category %>'></asp:label>        <br/>               <div class="actionbuttons">                        <Club:RolloverButton ID="GreenRolloverButton3" CommandName="Insert" Text="Save"                            runat="server" OnClick="savebutton_click" />                        <Club:RolloverLink ID="GreenRolloverLink2" Text="Cancel" runat="server" NavigateURL="Classifieds.aspx" />                    </div>    </InsertItemTemplate>   </asp:FormView><asp:SqlDataSource ID="SqlDataSource1"  runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"     InsertCommand="insert into tads (title,[description], categoryid) values (@title,@description, @category)"     SelectCommand="Select title, [description], categoryid from tads where itemid=@itemid"     UpdateCommand="update tads set title = @title, [description] = @description where itemid = @itemid" >    <SelectParameters>        <asp:QueryStringParameter Name="itemid" QueryStringField="itemid" Type="Int32" />    </SelectParameters>    <UpdateParameters>        <asp:Parameter Name="title" Type="String" />        <asp:Parameter Name="description" Type="String" />    </UpdateParameters>    <InsertParameters>      <asp:Parameter Name="title" Type="String" />        <asp:Parameter Name="description" Type="String" />        <asp:Parameter Name="category" Type="Int32" />    </InsertParameters></asp:SqlDataSource>

View 1 Replies View Related

How Do I Populate A Formview From Multiple Columns In A Gridview

Jun 29, 2006

Hi,
I have a master/detail scenario whereby to populate a formview, the user selects a row in the gridview.  The SqlDataSource used to populate the formview needs to check 2 columns from the gridview, ZRef (which is the SelectedValue of the Gridview) and ZName (a string).
From searching the forums it looks like I have to programmatically assign the SelectParameters in order to get ZName from the gridview.  Seems fair enough, so I set the parameters in the 'Selecting' event of the SqlDataSource.  (Code shown below).
 However the formview never shows.  In debug I can see that the values I'm setting in the SelectParameters.Add are correct; and if I set the default values for those parameters in the SqlDataSource itself, everything works fine.
Can anyone point me to some sample VB code that does this - I'm sure this must be a common situation.
Thanks.
Protected Sub sdsOff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
Dim tmpSDS As SqlDataSource
Dim tmpLBL As Label
tmpSDS = CType(FormView1.FindControl("sdsOff"), SqlDataSource)
If tmpSDS Is Nothing Then
      Server.Transfer("ErrorOnPage.htm")
End If
tmpLBL = Me.GridView1.Rows(GridView1.SelectedIndex).FindControl("lblName")
If tmpLBL Is Nothing Then
      Server.Transfer("ErrorOnPage.htm")
End If
tmpSDS.SelectParameters.Clear()
tmpSDS.SelectParameters.Add("ZRef", GridView1.SelectedValue)   'In debug, this shows 7 - which is correct
tmpSDS.SelectParameters.Add("ZName", tmpLBL.Text)  'In debug, this shows 'Fred Bloggs' - which is correct
End Sub
 

View 5 Replies View Related

Sqldatasource, Does It Select If Not Bound To A Gridview, Formview On The Page?

Feb 22, 2008

Using 3.5
If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview?
Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell.
I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.

View 2 Replies View Related

Error While Using Database Procedure In A Gridview Control

Dec 19, 2006

Dear Forum,
I have a gridview control  which is associated to a storedprocedure  with a parameter(Customer Number) to be supplied.  In the Define Custom Statement or stored procedure section  I selected stored procedure and selected the stored procedure.  The Define Parameter window I defaulted the Parameter Source as 'none' and default value as '%'.   In the next screen, I do a test query which retuns the following error
There was an error executing the query.  Please check the syntax of the command and if present, the type and values of the parameters  and ensure that they are correct.
[Error 42000] [Microsoft][ODBC SQL Server Drive][SQL Server] Procedure 'SP_TransactionDetails' expects parameter '@cnum' which was not supplied.
I am using SQL server studio  2005 version2.0. 
But the same procedure, if I use as SQL Statement, it works.
Can somebody help me.
Thanks,
Hidayath 

View 3 Replies View Related

Setting Up Dynamic WHERE Conditions For A Gridview Control

Jun 8, 2008

I have a simple gridview control set up that contains a single ControlParameter (a DropDownList) whose value is used in my SqlDataSource's SelectCommand:
SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type])"
The ISNULL check is so that I can select "ALL" from my dropdown and get all the rows with null values.
Everything works fine. Now what I'd like to do is exclude a specific wood_type value from the query if a checkbox control is selected. So what I'd like my select query to be when it's checked is something like
SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type]) AND @wood_type <> 'pine' "
So I'd like the option of excluding that certain type of wood from the default query (all rows). I thought it might be better to just have a value in the dropdown list that was "ALL except pine" but that doesn't seem like it would work, what would I bind that to?
I fooled around with having the CheckBox oncheckchanged event set a global based on the checked status, easy enough, but then how can I modify my SelectCommand? Should I just access the SqlDataSource object programmatically in my CheckBox handler and fiddle with the SelectCommand property? I tried this and it works, but this seems messy, now if I modify my GridView in Design mode I need to remember to update my SelectCommand strings in the CheckBox handler too. Is this the best way to do this?

View 3 Replies View Related

[:#] Problems Viewing The Custom Sql Statement In The Gridview Control

Jul 28, 2007

Hi,
 I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier."
Here is my sql statement:
SELECT     TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME,                       TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST,                       TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNITFROM         TBLPROJECTS INNER JOIN                      TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN                      TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN                      TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN                      TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN                      TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID
I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!
 

View 5 Replies View Related

Issue With Getting Values From Child Controls In A Gridview, To Use For The Update Using A SQLDataSource Control

Nov 21, 2006

Hi all,
I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
 
<asp:GridView ID="GridEditSettlement" runat="server" AutoGenerateColumns="False" BackColor="Navy"
BorderColor="IndianRed" BorderStyle="Solid" Font-Names="Verdana" Font-Size="X-Small" DataSourceID="SqlDataSource_grid" AllowPaging="True" AllowSorting="True" ForeColor="White" DataKeyNames="legid">
<Columns>
<asp:CommandField ShowEditButton="True" CancelImageUrl="~/App_Graphics/quit.gif" CancelText="" EditImageUrl="~/App_Graphics/EditGrid.GIF" EditText="" UpdateImageUrl="~/App_Graphics/save.gif" UpdateText="" ButtonType="Image" />
<asp:BoundField DataField="StartDate" HeaderText="Start Date" ReadOnly="True" />
<asp:BoundField DataField="EndDate" HeaderText="End Date" ReadOnly="True" />
<asp:BoundField DataField="CounterpartDealRef" HeaderText="CP Deal Ref" ReadOnly="True" />
 
<asp:TemplateField HeaderText="Preliminary Settlement Price" ><ItemTemplate>
<asp:Label ID=lblPreliminary runat=server Text='<%# Bind("PrimarySettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtPrimaryPrice Text='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>
 
</EditItemTemplate></asp:TemplateField>
 
<asp:TemplateField HeaderText="Agreed Settlement Price"><ItemTemplate>
<asp:Label ID=lblAgreed runat=server Text='<%# Bind("AgreedSettlementPrice") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtAgreedPrice Text='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>
 
</EditItemTemplate></asp:TemplateField>
 
 
<asp:BoundField DataField="Volume" HeaderText="Volume" ReadOnly="True" />
<asp:BoundField DataField="Price" HeaderText="Price" ReadOnly="True" />
<asp:BoundField DataField="TotalVolume" HeaderText="Total Volume" ReadOnly="True" />
<asp:BoundField DataField="InstrumentName" HeaderText="Instrument" ReadOnly="True" />
<asp:BoundField DataField="NominalValue" HeaderText="Nominal Value" ReadOnly="True" />
<asp:BoundField DataField="Strike" HeaderText="Strike" ReadOnly="True" />
<asp:BoundField DataField="DeliveryDate" HeaderText="Delivery Date" ReadOnly="True" />
<asp:TemplateField HeaderText="LegId" SortExpression="LegId">
<ItemTemplate>
<asp:Label ID="lblLegID" runat="server" Text='<%# Bind("LegId") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID=txtLegID Text='<%# Bind("LegId") %>'></asp:TextBox>
 
</EditItemTemplate>
</asp:TemplateField>
 
 
</Columns>
<RowStyle BackColor="#FFFF66" ForeColor="#333333" />
<EditRowStyle BackColor="#FFFF66" Font-Names="Verdana" Font-Size="X-Small" ForeColor="#333333" />
<PagerStyle ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#333333" />
</asp:GridView>
&nbsp;<br />
 
<asp:SqlDataSource ID="SqlDataSource_grid" runat="server" ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>" SelectCommand="sp_get_single_deal" SelectCommandType="StoredProcedure" UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@primarysettlement, AgreedSettlementprice=@agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@legid" EnableCaching="True" ConflictDetection="CompareAllValues" ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="" Name="dealnum" QueryStringField="deal"
Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter ControlID="txtLegId" PropertyName="Text" Name="legId" />
<asp:ControlParameter ControlID="txtPrimarySettlement" Name="primarysettlement" PropertyName="Text" />
<asp:ControlParameter ControlID="txtAgreedSettlement" Name="agreedsettlement" PropertyName="Text"/>
<asp:SessionParameter DefaultValue="" Name="userid" SessionField="userid" />
</UpdateParameters>
</asp:SqlDataSource>
 
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview.  In the .aspx code I cannot use FindControl (at least I don't think it will work).
So the questions are:  Is it possible to reference the child controls, if so - how?  Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.
What is the best approach?  Anyone come up against this issue before?
 Thanks,
KB

View 1 Replies View Related

Sqldatasource Control Inserting Problems

Oct 6, 2006

I set up a Sqldatasource control in 2.0 and I can retrieve data through a SQL Server connection from a stored procedure.  My problem is when I set up the insert command object through the wizard for the Sqldatasource control with another stored procedure for inserting data and call the insert method of my Sqldatasource object i get nothing not even an error it just goes through the code like nothing was wrong and I don't get anything inserted.  I don't know if this could be a problem but one of the parameters in the stored procedure is declared like this: @return tinyint output. I don't know how the Sqldatasource accounts for return parameters.  Here is the code for the insert for the Sqldatasource object.<asp:SqlDataSource ID="sdsMain" runat="server" ConnectionString="<%$ ConnectionStrings:SN_CUSTOMERConnectionString %>"InsertCommand="uspSNOrder_Promo_Live" InsertCommandType="StoredProcedure" SelectCommand="uspOPFillPromo"SelectCommandType="StoredProcedure"><InsertParameters><asp:ControlParameter ControlID="ddlPromo" Name="promoid" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="1" Name="datasourceid" Type="Int32" /><asp:Parameter DefaultValue="0" Name="datasourcekey" Type="String" /><asp:Parameter DefaultValue="9" Name="salesroomid" Type="Int32" /><asp:Parameter DefaultValue="9999" Name="userid" Type="Int32" /><asp:ControlParameter ControlID="txtFName" DefaultValue="" Name="firstname" PropertyName="Text"Type="String" /><asp:ControlParameter ControlID="txtLName" Name="lastname" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtAddress" Name="address" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtCity" Name="city" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtState" Name="state" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtZip" Name="zip" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtPhone" Name="phone" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="txtEmail" Name="email" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValue"Type="Int32" /><asp:Parameter DefaultValue="0" Direction="InputOutput" Name="return" Type="Byte" /></InsertParameters></asp:SqlDataSource>Thanks in advance

View 1 Replies View Related

Tempdb Gets Out Of Control When Inserting Records

Jul 23, 2005

I have a very big table with 20 million records DistinctProjectionKeywhich i join several times to different tables in this query.select distinct distinctprojectionkeyid,dpk.MarketID,dpk.Classificationid,dpk.DistributorID,dpk.ManufacturerID,dpk.LocationID,dpk.TimeID,P4.FACTOR as factor1,P3.FACTOR as factor2 ,P2.FACTOR asfactor3,P1.FACTOR as factor4into Projectionfactors1FROM DistinctProjectionKey dpk INNER JOIN D_Time tON t.TimeID = dpk.TimeIDINNER JOIN (select * from (select distinctClassificationID_Major,'fam' as lab fromStagingOLTP..ClassificationFlat) cf1)cfON cf.ClassificationID_Major = dpk.ClassificationIDLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p4ON t.TheDate = p4.TheDateAND p4.Name = 'FAM'AND cast(cf.Lab as varchar(20)) = cast(p4.Lab as varchar(20))AND dpk.MarketID = p4.MarketIDAND p4.ManufacturerID IS NULLAND p4.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p3ON t.TheDate = p3.TheDateAND p3.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p3.Lab as varchar(20))AND dpk.MarketID = p3.MarketIDAND p3.ClassificationID = dpk.ClassificationIDAND p3.ManufacturerID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p2ON t.TheDate = p2.TheDateAND p2.Name = 'fam'AND cast(cf.Lab as varchar(20)) = cast(p2.Lab as varchar(20))AND dpk.MarketID = p2.MarketIDAND p2.ManufacturerID = dpk.ManufacturerIDAND p2.ClassificationID IS NULLLEFT OUTER JOIN StagingOLTP..ProjectionDefaultFlat p1ON t.TheDate = p1.TheDateAND p1.Name = 'fam'AND cast(cf.Lab as varchar(20))= cast(p1.Lab as varchar(20))AND dpk.MarketID = p1.MarketIDAND p1.ManufacturerID = dpk.ManufacturerIDAND p1.ClassificationID = dpk.ClassificationIDthe other table have fewer number of records .I find that when I try to do the insert tempdb goes out of control ,itgrows above 100 GB?Would anyone know the reason why and the solution to apply to avoidthis problem?The other tables have fewer recodsClassification flat has 5000 records and projection default flat has32652 records.AjayAjay

View 2 Replies View Related

Inserting A Control Record Into A Flat Text File Through SSIS

May 2, 2006

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

View 4 Replies View Related

Inserting A Null Value

Dec 5, 2003

Hello,

I have got a database with a datetime field. I insert a date into the database from a textbox. All is fine if someone enters a date. If someone enters nothing I want a null to be inserted. How do I do this?

Grant

View 1 Replies View Related

Inserting “&<NULL&>”

Oct 13, 2005

how do you write an insert into statement and keep “<NULL>” in the null cells?

i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.


ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,

View 4 Replies View Related

Inserting Null Value

Sep 19, 2005

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

View 8 Replies View Related

Inserting Null Into A Record

Sep 8, 2007

I am simply trying to use SQLCommand in .net to insert a record into a SQL Server 2005 table. There are 2 fields being pulled from a user input for that could have no values selected. In this case I want to insert a null value into the record. I get an error that Null is no longer supported and that I should use System.DBNull, but that can't be used as an expression.
 How do I do this?
Thank you in advance.

View 1 Replies View Related

Inserting Null Values

May 11, 2004

Hi,

I am trying to insert null values into sql server from my access from. I am using sql statement. But it says 'Syntex error in Insert statement'. When i remove null values it works fine? How can I insert null values into a table?

Any help will be highly appreciated.

View 3 Replies View Related

INSERTing A String Containing NULL () From PHP App

Jul 1, 2006

Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.

I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character (). It's OK if the string is truncated during insertion, just so long as everything before the is there.

The string being inserted looks like:




$testString = "The NULL character should be escaped !";



(Note the between "escaped " and " !")

The error that the Gallery test script is getting is:




[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")



It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.

My simple script is:




<?php

$localhost = exec("hostname");
$database = "gallery2";
$uid = "g2user";
$pwd = "g2pwd";
$connectString = "Host=PROVIDER=MSDASQL;DRIVER={SQL Server};";
$connectString .= "SERVER=$localhost\sqlexpress;";
$connectString .= "DATABASE=$database;";
$connectString .= "UID=$uid;PWD=$pwd";

$sqlTableDrop = "drop table ljmtemp";
$sqlTableCreate = "create table ljmtemp (col1 nvarchar(100))";
$sqlTableQuery = "select len(col1) from ljmtemp";

// Connect to the db
$db = new COM("ADODB.Connection") or die("Cannot start ADO");
$db->open($connectString);

// Drop & recreate the table
$db->Execute ($sqlTableDrop);
$db->Execute ($sqlTableCreate);

// Insert the test data
//$testString = "The NULL character should be escaped !";
$testString = "This is a test string.";
$res = $db->Execute("insert into ljmtemp (col1) values ('$testString')");
if (!$res) die ("INSERT failed");

// Disconnect from the db
$db->Close();

?>



And it results in:




C:MyServer>php testMsSqlInsertNull.php
PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27
Stack trace:
#0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...')
#1 {main}
thrown in C:MyServer estMsSqlInsertNull.php on line 27



I'm not sure if this is the same problem as Gallery is reporting or another one.

It looks like somebody is treating the as a string terminator, but when i double the backslash the literal '' is inserted into the database.

How can I get SQL Server to insert at least everything up to the ?

Hopefully once I have my simple script working I'll be able to figure out what's wrong in the Gallery test.

Thanks.

View 10 Replies View Related

Want Error Message To Appear When No Database Results Were Returned In GridView, Also Other GridView Issues.

Jun 12, 2008

Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having.   Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class  I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks! 

View 2 Replies View Related

Inserting NULL Into Datetime Field

Aug 17, 2007

I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime valueâ€?), so I don’t know how I can insert this value when the field is blank? 
Can anyone shed some light please? Thanks
 

View 2 Replies View Related

Problems With Inserting Null Values

Dec 14, 2000

Hi,

We are using an SQL Server database and seem to be having some problems with inserting null values into numeric fields. The field is set to accept nulls, but when we try and write a record into the database and that field is null, the application craps out on us. Are there any issues that we should be aware of when inserting null values into numeric fields? What might the problem be? Thanks.

View 1 Replies View Related

SQLXMLBULKLOAD Not Inserting Null Values

May 3, 2006

i have attached XML and XSD file
i bulkload xml file into 2 tables .ManifestID is the Relationship between 2 tables

CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Detail] (
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_num] [bigint] NULL ,
[track_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scan_time] [datetime] NULL ,
[scac_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Header] (
[trailer_id] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trailer_close_date] [datetime] NULL ,
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manifest_qty] [int] NULL ,
[origin_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destination_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

When the first TrackCodeid is NULL
all remaining NULL TrackCodeid is entered as null in the database table
but if the first/prev trackcodeID is not NULL then following null trackCODEID is populated with the prev trackcodeID and not as null in database
<TrackCodeId>ABCDEFG</TrackCodeId>
All Null TrackCOdeID is populated as ABCDEFG
if we remove ABCDEFG and then bulkload all null values are populated
if a null trackCODEID is to be inserted the prev trackCODEID must be null and must not contain any value

View 1 Replies View Related

Inserting Null Values Into Smalldatetime

Feb 14, 2004

hi

how can we insert null into a small datetime field (from client application VB)

View 2 Replies View Related

Enterprise Manager ... Inserting &<NULL&> Value.

Apr 15, 2004

What is the keyboard shortcut for entering a <NULL> into a database field from within the Enterprise Manager? I've done it before, but can't remember it for the life of me! Also, is there a query that I can use to insert <NULL> in place of ''?

View 5 Replies View Related

Cannot Set Numeric To Null Before Inserting To Table

Jun 21, 2007

Hi,



I have a flat file whose data looks like this:



"Opening Balance", Acct1234, 1001.01

"Closing Balance", Acct1234, 1001.01



In my script component for "Opening Balance", I set my output columns like this:



Row.AccountNumber = CDate(rowValues(1)

Row.OpeningBalance = CDec(rowValues(2))

Row.ClosingBalance = Nothing



Because I don't have a value for ClosingBalance in this row, I set ClosingBalance = Nothing.



And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.



Row.AccountNumber = CDate(rowValues(1)

Row.OpeningBalance = Nothing

Row.ClosingBalance = Nothing



Then, in my column mappings I map OpeningBalance = OpeningBalance and ClosingBalance = ClosingBalance.



The idea being that if there's no value, then it should be set to NULL. However, in my table, I see that instead of NULLs, there's 0.00000. Which is not what I want to see.



Any ideas why this is happening?



Thanks



View 5 Replies View Related

INSTEAD OF Trigger - Error Inserting Into NOT NULL Field

Oct 7, 2004

I've just noticed some strange behavior that seems like a bug to me.
It's much easier to follow an example of it that to outright explain it, so here goes.

I have a table defined with a NOT NULL constraint on a column and a default clause:
-- DROP TABLE TestTable
CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )

I have a view defined on the table, in this example case, the view just mirrors the table one to one:
-- DROP VIEW TestView
CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable

So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field:
INSERT INTO TestView (TestField0) SELECT 'test'

Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt:
-- DROP TRIGGER TestTrigger
CREATE TRIGGER TestTrigger ON TestView
INSTEAD OF INSERT AS
BEGIN
INSERT INTO TestTable (TestField0, TestField1)
SELECT TestField0, COALESCE(TestField1, 'X')
FROM inserted
END

Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error:
INSERT INTO TestView (TestField0) SELECT 'test'

Server: Msg 233, Level 16, State 2, Line 1
The column 'TestField1' in table 'TestView' cannot be null.


Am I missing something or is this a bug?
Thanks

View 1 Replies View Related

Inserting Text Value Into Null Records In A Grid

Feb 11, 2014

I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.

create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);

All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.

View 5 Replies View Related

SqlException Inserting NULL Into A Varbinary(MAX) Column.

Jul 12, 2007

I get the following error when my insert has a DBNull value for a column of type varbinary(MAX). "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query." In my opinion, the .NET SqlDataAdapter is attempting to convert the null value to a nvarchar. Is it possible to insert a null value in varbinary(max)? I didn't have this problem with the image datatype. Is this a bug or is there a work around to this problem?



Any help would be appreciated.

View 5 Replies View Related

Inserting NULL Values On Date Fields Trhough DAL

Jul 25, 2006

I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'.
I am assigning SqlTypes.SqlDateTime.Null.
But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database?
THANK YOU!!!

View 3 Replies View Related

Efficiency In Inserting Null Values Into Fields Which Allow Nulls.

Aug 9, 2004

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanks

View 2 Replies View Related

Converting Empty String To Null When Inserting/updating

Mar 10, 2006

    I am using the following query to calculate date differences:select ..........DATEDIFF(d,  recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.

View 1 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

Inserting Empty Values Into NOT NULL Columns Via ODBC

Jul 20, 2005

We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.

View 2 Replies View Related

Inserting Few Recordsets Into Temp Table Containing NULL Values

Jul 20, 2005

HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved