There must be a better way to write this where clause. Can somebody help
if @id is null
begin
(bla bla bla) where parentid is null
end
else
begin
(bla bla bla) where parentid = @id
end
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I have a Store Procedure that kept wanting to specify something like maidenName = null if there is not value for maidenName field. The maidenName is already set up to allow null value. How do I write the store procedure to automaticaly recognized that if a field has nothing in it's a null? Below is the error I kept getting if I do not have the expiration number.
Msg 201, Level 16, State 4, Procedure spInsertName, Line 0
Procedure 'spInsertName' expects parameter '@expNo', which was not supplied.
I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required.
How can I get around this?
hello
my friends
i want write Select without write filed name
example
mytable(id,name1,family)
orginal selected is
select id,name1 from mytable
but i want write:
select field[0],field[1] from my table
that field[0] point to id and field[1] point to name1
thanx
M.O.H.I.N
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
Hello,While working through my encryption questions from preivous posts, I amfinding that I may have to resort to use triggers to do the encryption(not that this is the only way, but might be the best way for mycircumstances).I would like to create a trigger that will encrypt the field before thewrite is committed.I've found serveral posts about triggers, but nothing that fits what Iwish to do.Upon an insert or update I want to modify the ssn field with this:cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))so, ssn '123456789' in SSN would become <something encrypted> in SSNThis is the trigger I have so far, but it is generating an error:CREATE TRIGGER F_Student_SSN.eSSNON F_STUDENT_SSNINSERT, UPDATEAS SELECT cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), cast(SSN asnvarchar(11))) as nvarchar(40))TIARob
View 3 Replies View RelatedI need some help in SSIS Package I am trying to write a byte array to an image (blob) in DB2 destination. I am getting SQL code -290 Invalid Description, if i set the output column to a byte stream. If I set the output column to an Image data type then I get a different error the package will not at that time even process it errors out right away. At least using a byte stream datatype it errors out when it is about to write to the olebd destination. Anybody have success using BLOB fields in SSIS package data flow? Thanks for any help.
View 5 Replies View RelatedI have a table with a column [FileData] of type TEXT or XML. I need to write the content of the column to a file, using sp_OAWrite for example, but the stored procedure who needs to read the content and write it to the file does not work because the limitation is no local variable for those types. What use is that datatype if i can not use it in procedures like this?
Anyone out there who could give me a tip on how to solve that?
I have a web form that is an interface for a database. The code was working fine until a field needed to be added. So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't. I am at wits end as to why. Can someone please help!!! I am using VS Studio 2000 ASP.NET and C#. The code is below. The bold items are the new fields.using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;namespace TrafficDept{/// <summary>/// Summary description for AddOwner./// </summary>public class AddOwner : System.Web.UI.Page{protected System.Web.UI.WebControls.Button Button1;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;protected System.Data.SqlClient.SqlConnection sqlConnection1;protected System.Web.UI.WebControls.TextBox TextBox1;protected System.Web.UI.WebControls.TextBox TextBox2;protected System.Data.SqlClient.SqlDataAdapter daAddOwner;protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;protected System.Web.UI.WebControls.Panel namePanel;protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;protected System.Web.UI.WebControls.Button Button4;protected SiteCubed.EditWorksProfessional tbNote;protected System.Web.UI.WebControls.Panel notePanel;protected System.Web.UI.WebControls.DropDownList ddlStatus;protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;protected System.Web.UI.WebControls.TextBox tbOwner;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;protected System.Web.UI.WebControls.TextBox tbOwnerMI;protected System.Web.UI.WebControls.TextBox tbOwnerLName;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;protected System.Web.UI.WebControls.TextBox tbBusinessName;protected System.Web.UI.WebControls.TextBox tbAddress;protected System.Web.UI.WebControls.TextBox tbRegNo;protected System.Web.UI.WebControls.TextBox tbPreviousOwner;protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;protected System.Web.UI.WebControls.TextBox tbPORegNo;protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;protected System.Web.UI.WebControls.DropDownList ddlMeans;protected System.Web.UI.WebControls.Button Button3;protected System.Web.UI.WebControls.Button Button2;protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;protected TrafficDept.dsAddOwner dsAddOwner1; private void Page_Load(object sender, System.EventArgs e){// Put user code to initialize the page here}#region Web Form Designer generated codeprivate void Button2_Click(object sender, System.EventArgs e){Response.Redirect("AddOthers.aspx");}private void Button1_Click(object sender, System.EventArgs e){daAddOwner.Fill(dsAddOwner1);dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();drOwner.Plates_ID = int.Parse(Session["PlatesID"].ToString());drOwner.Status = ddlStatus.SelectedItem.Text;drOwner.Current_Legal_Owner = tbOwner.Text;drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;if (!tbRegDate.xIsEmpty)drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());drOwner.Address_of_Current_Owner = tbAddress.Text;drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;drOwner.Previous_Owner = tbPreviousOwner.Text;drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;if (!tbTransferDate.xIsEmpty)drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());drOwner.By_Means_of = ddlMeans.SelectedItem.Text;drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;drOwner.Owner_Notes = tbNote.Text;dsAddOwner1.Ownership.Rows.Add(drOwner);daAddOwner.Update(dsAddOwner1);Response.Redirect("AddOthersTP.aspx");}private void Button3_Click(object sender, System.EventArgs e){//daAddOwner.Fill(dsAddOwner1);//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();namePanel.Visible = true; }private void Button4_Click(object sender, System.EventArgs e){notePanel.Visible = true;}}}
View 1 Replies View RelatedHi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies View RelatedHello All.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt
10 1
11 2
12 3
13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
---------------------------------------------------------
if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month
Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
Select @WeekCount = count(*) from TempWeekFile
Select @WeekCount
Select @Cnt=1
CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,
while @Cnt<=@WeekCount
Begin
select @WeekNo = Week_No from TempWeekFile
where WeekCnt=@Cnt
[Week_@WeekNo] [Money] NULL
@Cnt=@Cnt+1
End
) ON [PRIMARY]
GO
--------------------------------------------------------
sorry, this is probably a really basic question ..........
is there any way to enable the bit data type in SQL Server to accept nulls?
thanks
nyoobee
Hi all,
I have a table, named systems, with fields as follows.
field datatype
___________________
system varchar(50)
modelID int
.... ....
... .... etc...
The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.
In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.
My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.
Is this a typical issue that can be resolved with a simple property change?
Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?
Thank you for any help in advance.
I have a column called person.last_name, and I want to set this field to NULL if person.last_name ='test'///
I have tried this but don't work: ISNULL ({person. last_name},'<>"test'))
May i know how to generate a SQL query to set a field of row to Null? Thank you.
View 2 Replies View RelatedHello all,
Can some one help me with this issue. It seems to be a common question and I have tried a lot of options but nothing seems to work for me.
I have a text box and this is linked to ajax calendar extender. They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure.
This is the code I use to grab the data from my web form
If txtTrimsEntryDate.Text = String.Empty Then
_oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text)
End If
If txtEstCompletion.Text = String.Empty Then
_oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text)
End If
If txtProjectClosedDate.Text = String.Empty Then
_oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null
Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text)
End If
_oTrim.InsertOrUpdateAProject()
This is the code for the stored Procedure
Dim oSql As clsSqlServer = Nothing
oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input)
Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database. I want the value to be Null in database.
Is there a way to do this.
Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null... This is in SQL Server 2005
View 2 Replies View RelatedI would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
My tests have shown that if you use BCP to import records with a
date field as " ", it won't work.
Is there a way to get BCP to accept empty date fields?
Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"
Keeps on generating a syntax error
ad1 ad2
-----------------------------
younge joe
null null
youn null
want a result like that. anyone can help me to do it?
ad1+ad2
-----------------------
younge joe
null
youn
Hi everyone,
I have two tables - caseinfo and steps.
Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.
Steps shows the steps the case has gone through and the dates of those particular steps.
I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.
If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).
Does anybody have any ideas?
Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.
Thank you for reading.
--- create sample data
set dateformat ymd
declare @caseinfo table (caseid int, startdate smalldatetime, enddate smalldatetime)
insert @caseinfo
select 10, '2006-12-23', '2006-12-27' union all
select 20, '2006-12-23', NULL union all
select 30, '2006-12-23', NULL union all
select 40, '2007-1-15', '2007-3-4'
declare @steps table (caseid int, stepnumber int, stepdate smalldatetime)
insert @steps
select 10, 1, '2006-12-24' union all
select 10, 2, '2007-1-3' union all
select 10, 3, '2007-2-5' union all
select 20, 1, '2006-12-26' union all
select 20, 2, '2007-1-7' union all
select 20, 3, '2007-1-9' union all
select 30, 1, '2007-1-14' union all
select 40, 1, '2007-1-23' union all
select 40, 2, '2007-3-2' union all
select 40, 3, '2007-4-16'
--- the main idea (with the wrong results)
select *
from @caseinfo c
left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate
--- expected result
declare @expresult table (caseidexp int, startdateexp smalldatetime, enddateexp smalldatetime, stepnumberexp int, stepdateexp smalldatetime)
insert @expresult
select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all
select 20, '2006-12-23', NULL, 1, '2006-12-26' union all
select 20, '2006-12-23', NULL, 2, '2007-1-7' union all
select 20, '2006-12-23', NULL, 3, '2007-1-9' union all
select 30, '2006-12-23', NULL, 1, '2007-1-14' union all
select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all
select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'
select *
from @expresult
hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:
declare @number int
set @number = (select number from numbers where number_id = 1)
if (@number = null)
begin
set @number = 12
end
Thank you very much
Visual Basic 2005 Professional Edition:
I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.
Hello,
After the "DAYS" column, some fields are coming null. We don't want it to come null. Beside this, i.e; in the "MONDAY"column, the same "NAMESURNAME" is coming more then one. How can I do the grouping by "NAMESURNAME"?
MachType DayPeriod Monday Tuesday Wednesday Thursday Friday Saturday
Negative- || NIGHT || NULL || CAN AK || NULL || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || CEM TEK || NULL || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || FÄ°RDEVS ATEÅž || MÃœKREMÄ°N EKELÄ°K || NULL
Negative- || NIGHT || NULL || CAN AK || CEM TEK || FÄ°RDEVS ATEÅž || NULL || NULL
Negative- || NIGHT || NULL || NULL || NULL || NULL || ÖMER CANOĞLU || NULL
select MachineType,DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END AS MONDAY,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END AS TUESDAY,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END AS WEDNESDAY,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END AS THURSDAY,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END AS FRIDAY,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE
WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE
WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and
SUBSTRING(SICK.EUCLID,1,5)= '18016' AND SEANCEDATEBETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T ORDER BY MachineType
I 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
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
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,
All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command.
I did this but it only works when the field is made empty, not when it contains NULL:
dim field1 as string = ""...sql = "delete from mytable WHERE (field1=@field1)" comd = New SqlCommand(sql, oConnection) comd.Parameters.AddWithValue("@field1", field1) comd.ExecuteNonQuery()
How can i do that?
Thanks
Tartuffe
Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima
I have the following database structure:
The table aspnet_users, which we all know
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this:
string assigned is null, i am passing this as a method parameter
...............
string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
...................
System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter();
dbParam_au_pr.ParameterName = "@assigned";
if (assigned == null)
{
dbParam_au_pr.Value = null;
}
else
{
dbParam_au_pr.Value = assigned;
}
dbParam_au_pr.DbType = System.Data.DbType.String ;
dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all.
I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned
nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned,
which was not supplied.
Please help with this as soon as possible.Thanks
I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.
i.e. This does not work if something is NULL
UPDATE myTable SET something = @somethingWHERE ID = @o_ID AND something = @o_something
Thank You,Jason