Insert Command Does Not Like Null Values.

Jan 5, 2004

- I'm a Novice -

I'd like to use an insert command to insert whatever data that is on the unbound form. The first three fields are required and I check those values prior to executing the code below. But some fields may be null.

Dim strSQL as string
.
.
code
.
.
strSQL = "INSERT INTO tblCase " & _
"(Case_SiteName, " & _
"Case_PatientSequenceNumber, " & _
"Case_PatientInitials, " & _
"Case_DOB, " & _
"Case_ProcedureDate, " & _
"Case_Investigator, " & _
"Case_Institution, " & _
"Case_Value) " & _
"VALUES ('" & Me.cboSiteName & "', " & _
Me.txtPatientNumber & ", '" & _
Me.tbxPatientInitials & "', '" & _
Me.tbxPatientDOB & "', '" & _
Me.tbxProcDate & "', '" & _
Me.tbxInvestigator & "', '" & _
Me.tbxInstitution & "', " & _
Me.grpValue & ");"

CurrentProject.Connection.Execute strSQL

But, when any of the last four are unanswered, i get an error. Any, or all of the last four can be blank. Any suggestions?

View 14 Replies


ADVERTISEMENT

Insert Null Values

Jan 28, 2005

Hi 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

View 2 Replies View Related

INSERT INTO Command (two Columns) With One Fixed Value, One String With Several Values

Nov 7, 2006

I have a string with values value1,value2, value3, value(n) which I would like to append to my table.
In the second column of my table I have a parameter which stays the same, so I end up with (if the parameter value is "123456")
Column 1   |         Column 2
123456      |         Value1
123456      |         Value2
123456      |         Value3
I would like to set up a single SQL statement which will process this regardless of the number of values (therefore rows) desired.
Something like:
INSERT INTO dbo_tblUserLevelApplicationRequests ( Column1, Column2) select EmployeeNumberParam as EmployeeNumber, ((stringofvalues) as valuestring)
Is it possible to do this with a single SQL statement?

View 2 Replies View Related

Aspnet_regsql.exe - Cannot Insert Null Values

Oct 20, 2006

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 Related

How Do I Insert A Record With Possible NULL Values Using A Tableadapter.

Feb 26, 2008

I'm sorry, this question has a pretty long preamble, but bear with me as this will fully explain my problem, and save a number of clarification questions.I am using Visual Web Developer 2005 Express, with SQL Server 2005. After reading a Microsoft article saying that it was much better to use a three tier architecture with data adapters than to write explicit queries, I have used this approach extensively, and generally it works very well.  Typical code patterns are: -        Dim taPassenger As New ShippingTableAdapters.VPassengerTableAdapter        Dim tbpassenger As Shipping.VPassengerDataTable        Dim trpassenger As Shipping.VPassengerRow        .....        '   to process all passengers from a voyage: -        tbpassenger = taPassenger.GetDataByVOYid(Voyageid)        For Nbr As Integer = 1 To tbpassenger.Count            trpassenger = tbpassenger(Nbr - 1)             '    I now have the row available in properly-typed fields, eg trpassenger.vpxName             ...        Next        '   to read and process a single passenger        tbpassenger = tapassenger.GetDataByVPXid(VPXid)        trpassenger = tbpassenger(0)The table adapter has been created with Insert,Update, Delete methods.   Thus you can assign values to row fields, and then update the row: -        trpassenger.VPXName = "New Name"        ....        tapassenger.update(trpassenger)There are actually several overloads of the update method:  you could have written a field list, like this, where the names (VPXid etc) would be defined in my program as variables of the appropriate type for the corresponding column.        tapassenger.update(VPXid, VPXName, VPXAge, ....HOWEVER,  this doesn't work properly if you have non-string data that could be null.   Thus if the fourth field is a foreign key that could be null, if you write        Dim VPXVoyId as Nullable(of GUID)        VPXVoyid = nothing        tapassenger.update(VPXid, VPXname, VPXAge, VPXVoyid, ...or         Trpassenger.VPXVoyid = Nothing        tapassenger.update(trpassenger) you do NOT put a null value into the database.  Instead you store a value of "00000000-0000-0000-0000-000000000000".   You can solve this annoying problem in two ways: -A.   If you will ALWAYS be storing a null value at this point of your program, then simply write the update statement like this: -        tapassenger.update(VPXid, VPXname, VPXAge, Nothing, ...Here "Nothing" actually means "DBNull" even though it doesn't mean this when used in a normal VB assignment (and neither does DBNull).B   You can use the SetxxxxNULL method: -        If trpassenger.VPXVoyid = nothing then             trpassenger.SetVPXVoyidNull        End If        tapassenger.update(trpassenger) Thus when the program logic is complex and there are many paths to the update statement I will often put statements such as this in front of the update statement, using SetxxxxxNull for all non-string values that may be null.  You don't need to worry about string values because the Nullvalue property can be used to simply say that Null values are empty, and it all works properly, but for reasons unknown Microsoft have prohibited this for other data types. Now at last we get to my question, which is "How do I do the equivalent for an Insert?"   tapassenger.insert does not have any overloads, and the only form is  tapassenger.insert(field list).   But this creates the problem above:  if VPXVoyid = nothing then I insert the not-null value of "00000000-0000-0000-0000-000000000000".   If I COULD use the form tapassenger.insert(trpassenger), then I'd have another problem: if you haven't set trpassenger (for example with trpassenger = tbpassenger(0)) then it will not exist, so you will get an exception when you try to reference it.  Yet you can't write    If isnothing(trpassenger) then       trpassenger = new shipping.vpassengerrow)         (I've tries this both as an assignment, and as a Dim statement)    end ifSo far I can think of only two solutions, both awful: -a/    Use multi-choice logic to choose an appropriate insert statement.  If there is only one or two possibly-null fields, I could write:-        If field4 = nothing then            trpassenger.insert (VPXid, VPXname, VPXAge, Nothing, ..        However if there is more than one possibly-null field, you need (2 to the power of  nbr-of-possibly-null-fields) insert statements, with of course a lot of testing to choose which oneb/    I could insert the record with nulls, then read it back and update it.  Surely there is a better way!   Help! Thank you, Robert Barnes.    

View 2 Replies View Related

How To Insert Null Values Into A DataBase Field

May 9, 2005

I have a function that updates a table in sql server
If a field is left blank I want the database field set to Null. I use:sqlCmd.Parameter.Add("@somefield, someintvalue) to pass all my values to the database.
if someintvalue is null I want to set @somefield to dbnull
How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?
Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

View 2 Replies View Related

Insert NULL Values To Foreign Key Fields

Jan 15, 2008

Hi all,

I have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:

The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.

An insert statement for the above would look something like the following:

INSERT INTO tblPackages (PkgNo, PkgName, PkgEngineerID, PkgContrAdmin, PkgRemark)VALUES (1234, 'My Package', NULL, NULL, 'My Package remark')

And the create table statements are:

USE [PASSQL]GO/****** Object: Table [dbo].[tblPackages] Script Date: 01/15/2008 23:25:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblPackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PkgNo] [nvarchar](20) NULL, [PkgName] [nvarchar](255) NULL, [PkgEngineerID] [int] NULL, [PkgContrAdmin] [int] NULL, [PkgRemark] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblPackages_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees] FOREIGN KEY([PkgEngineerID])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees1] FOREIGN KEY([PkgContrAdmin])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees1]

And:

USE [PASSQL]GO/****** Object: Table [dbo].[tblEmployees] Script Date: 01/15/2008 23:28:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblEmployees]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](255) NULL, [EmpShort] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblEmployees_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Any ideas on how to accomplish this would be great!
Thanks in advance!

View 4 Replies View Related

How To Insert NULL Char Values In SQLSERVER With A SQL Sentence?

Jul 20, 2005

Hi everyone!I am working with Delphi v7 and MS SQLServer.I am trying to insert data in a table with a SQL sentence. Some of thefields of my table are type char or varchar, and they can have nullvalues.¿What do i have to write in the SQL sentence to insert a null value inthose fields?I tried with '', an empty String, but it doesnt work, the tablesstores an empty String (logical :-)).In the SQLServer GUI you have to press CTRL + 0 to insert a NULLvalue, but how can i tell this to the SQLServer through a SQLSentence?Well, thank you very much.

View 2 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Integration Services :: SSIS Insert Non Null Value Into Null Rows

Jul 15, 2015

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.

View 7 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Use Order By But Want NULL Values As High Values

Nov 9, 2000

Hi,

My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list

e.g.

Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -

At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?

Thanks
Dan

View 1 Replies View Related

Cannot Insert The Value NULL Into Column 'OrderID' -- BUT IT IS NOT NULL!

Apr 2, 2007

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

View 8 Replies View Related

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
 
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
 
 
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
 
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
 
</tr>
</table>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
 
 
</asp:SqlDataSource>
</asp:WizardStep>
 
 Event Handler
 
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
 
}
 

View 1 Replies View Related

NULL As Defaultvalue In Select Command

Feb 13, 2007

Hello everybody,
I have following datasource
  <asp:SqlDataSource ID="sqlIncidentTemplates" runat="server" ConnectionString="<%$ ConnectionStrings:Standard %>"
SelectCommand="SELECT [IncidentText] FROM [IncidentTemplates] WHERE [ConstrAreaID] = @ConstrAreaID AND [ConstrDeviceID] = @ConstrDeviceID">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="ConstrAreaID" Type="Int32" ConvertEmptyStringToNull="true" />
<asp:Parameter DefaultValue="" Name="ConstrDeviceID" Type="int32" ConvertEmptyStringToNull="true" />
</SelectParameters>
 As far as I understand default select command should be "SELECT IncidentText FROM IncidentTemplates WHERE ConstrAreaID IS NULL AND ConstrDeviceID IS NULL". Unfortunately this is not the case. I assume that the "[ConstrAreaID] = @ConstrAreaID " is translated into "ConstrAreaID = NULL".
Could this be the reason and how can I solve this problem?
Thanks in advance

View 3 Replies View Related

Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column. 

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far. 

View 3 Replies View Related

Command.ExecuteNonQuery() Error Occurring NULL Value

Apr 2, 2008

Here is the table that I am wanting to insert the information into (BTW I am wanting the UserID, EntryDate, Note) On my page i do have a text box for the UserID as well as a Note textbox. When I hite the submit button on my page I am already sending the UserID textbox information to be sent to another table (called RequestTable). However, I am wanting to take that same UserID and insert it into the RequestNote table as well. Let me know if you have any questions for me to further explain anything.
**RequestNote**RequestNoteKey (PK)  (has identity set to yes)RequestKey (allows nulls)NoteEntryDateEntryUserID  (allows Nulls)****This is my stored procedure that I am calling called "NoteInsert"***@Note nvarchar(1000),@EntryUserID nvarchar(50)AS INSERT INTO RequestNote (Note, EntryDate,EntryUserID)
VALUES (@Note,GetDate(), @EntryUserID)
RETURNGO
****THIS IS THE PAGE THAT CONNECTS THE USER INTERFACE AND STORED PROCEDURE***public static void AddRequestNote(string requestNote, string userID){using (SqlConnection connection = new SqlConnection(connRequestNote)){using (SqlCommand command = new SqlCommand("NoteInsert", connection)){command.CommandType = CommandType.StoredProcedure;command.Parameters.Add(new SqlParameter("@Note", requestNote));command.Parameters.Add(new SqlParameter("@EntryUserID", userID));connection.Open();command.ExecuteNonQuery(); <--THIS IS WHERE I GET AN ERROR THAT SAYS Cannot insert the value NULL into column 'RequestNoteKey', table 'RequestNote'; column does not allow nulls. INSERT fails}

View 5 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View 4 Replies View Related

How Can I Insert Date Values From Char Values

Jan 4, 1999

My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?

Thank's

View 1 Replies View Related

'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go

Sep 1, 2007

Please be easy on me...I haven't touched SQL for a year. Why given;



Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF


do I get

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ','.
for the following;




Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');


Thank you,
hazz

View 3 Replies View Related

Using NOT IN With Null Values

May 19, 2008

Hi, I ve a table,  I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here,  Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb        

View 6 Replies View Related

SQL Null Values In VB

Apr 10, 2006

I have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything.  I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>.  In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me).  If a field is entered, then it is included in the search.  Otherwise it should be ignored.  In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1  SET @tagnum=NULL  SET @ownername = '%'+@ownername+'%'  SET @mailaddress = '%'+@mailaddress+'%'  SET @address2='%'+@address2+'%'  SET @city = '%'+@city+'%'  SET @telephone='%'+@telephone+'%'  SET @dogcolour='%'+@dogcolour+'%'  SET @dogbreed='%'+@dogbreed+'%'  SET @dogage='%'+@dogage+'%'  SET @doggender='%'+@doggender+'%'  SET @dogname='%'+@dogname+'%'  SET @applicationdate='%'+@applicationdate+'%'
 SELECT DISTINCT  * FROM DogRegistry WHERE (  TagNum = @tagnum OR  OwnerName LIKE @ownername OR  MailAddress LIKE @mailaddress OR  Address2 LIKE @address2 OR  City LIKE @city OR  Telephone LIKE @telephone OR  DogGender LIKE @doggender OR  DogBreed LIKE @dogbreed OR  DogAge LIKE @dogage OR  DogColour LIKE @dogcolour OR  DogName LIKE @dogname OR  ApplicationDate LIKE @applicationdate  ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them.  TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered).  Debugging has shown that when nothing is entered it passes "".
Any ideas?

View 9 Replies View Related

Null Values

Jun 29, 2000

I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?

View 3 Replies View Related

SUM When There Are Null Values.

Nov 2, 2007

Would this take care of null values in either a.asset or b.asset?

SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets

What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.

View 8 Replies View Related

Null Values

Jan 16, 2006

Hi,

How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert  00/00/00 instead. ?

The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.

And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors

So far I have tried to use something like

ISNULL([Column 34])

Or

SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]

Or


SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34

but none seems to work [Column 34] being the offending column.

What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors

Any ideas?

View 2 Replies View Related

Null Values

Apr 20, 2007

I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.



Before I get to involved with SQL, is it a bad practice to use nulls?



If it is what do you enter in place of the null value,

which will lead to more code, right?



Davids Learning SQL

View 13 Replies View Related

Comma Delimited Values List In Where Command

Jan 14, 2014

Trying to run a simple query below

Update CustomerID
set CustomerLevelID='5'
where CustomerID='97000,57700,560046,462334,454453,522444,539743'

When I try this I get

Conversion failed when converting the varchar value

Is it possible to use comma delimited values in a where command?

View 3 Replies View Related

Problem With Insert , Cannot Insert NULL Value

Jan 30, 2007

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!

View 12 Replies View Related

Dealing With Null Values

Jul 31, 2006

hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date    .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then    .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks

View 1 Replies View Related







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