How to set sql server setting let to set null value when a empty string (zero length)

How to set sql server setting let to set null value when a empty string
(zero length) set to column?
I want it automatic to set null value when maintain data is a zero-length
string press to column of table. for example,
when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null three values to table1.There is no such setting in SQL Server. You could create a trigger which per
forms this, but I would
look for some alternate options instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ABC" <abc@.abc.com> wrote in message news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...darkred">
> How to set sql server setting let to set null value when a empty string (z
ero length) set to
> column?
> I want it automatic to set null value when maintain data is a zero-length
string press to column
> of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
save abc, null and null
> three values to table1.
>|||Would probaby have to create a trigger of the form (completely untested!)
CREATE TRIGGER NullifySomeEmptyStrings
ON table1
FOR AFTER INSERT, UPDATE
AS
--NULL out column2 if it now contains an empty string ''
IF UPDATE(column2)
BEGIN
UPDATE table1
SET column2 = NULL
WHERE table1.ThePrimaryKeyColumn IN
(SELECT ThePrimaryKeyColumn
FROM INSERTED
WHERE INSERTED.column2 = '')
END
"ABC" <abc@.abc.com> wrote in message
news:%230Fav01RGHA.2276@.tk2msftngp13.phx.gbl...
> How to set sql server setting let to set null value when a empty string
> (zero length) set to column?
> I want it automatic to set null value when maintain data is a zero-length
> string press to column of table. for example,
> when a statement as "Insert into table1 values ('abc', '', '')' become to
> save abc, null and null three values to table1.
>|||Why do you want it to be Null?
If you use Front End application, validate this and send valid data to
server
Madhivanan

how to set null values to the database ?

I have some textboxes that could be blank when the form is submitted. If so, I want those fields in SQL database table to be set NULL when the insert is executed. I also need to convert the values in these textbexes to either INT or String if there are values there. I tried the following.

--Code

int RowNumber;

if (((TextBox)(e.Item.FindControl("RowNumber"))).Text != "" )
{
RowNumber = int.Parse(((TextBox)(e.Item.FindControl("RowNumber"))).Text);
}
else
{
RowNumber = DBNULL.value;
}

--Code

I got the error:

Cannot implicitly convert type "System.DBNull" to "int".

I want to pass RowNumber later to set NULL on the table. I know the data type is not correct, but how I get around with it ? and Is this the only problem ?

Thanks you very dmuchYou'll have to wait until you pass the value into the parameter itself to perform the test; once there, you can pass the parameter the value DBNull.Value.|||Thank you very much.. Can you show me how to do it.. please ?|||Assuming you are using sprocs, and passing in parameters to these sprocs:

'With your command object
myCmd.Parameters.Add("@.myParam",SQLDBType.INT).Value = IIf(myTextBox.Text <> "", myTextBox.Text, DBNull.Value)

Obviously, this is over-simplified (and in VB, no less), but I hope it gives you a general idea...|||I understand that I can do this when I put up the Sqlparameter object. But the problem is that I need to pass all the variables from my code-behind to a component which handles all the Sqlparameter things. It would not all me to pass the variables if they are not assigned certain values. so I need to assign NULL to some of them if they have no values. But I cannot do that .. any help ?|||The way I handle this is to pass a non-value to the component; for example, Integer.MinValue. The test for MinValue inside of your sub, and replace it with DBNull.Value.|||Thank you very much, I will try that...

How to set null value for a smalldatetime inside a Script Component task?

how the hell you allocate a null value for a smalldatetime sql field?

Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:

.Parameters("@.FecEnajenacion").Value = "1999-01-01"

error:

.Parameters("@.FecEnajenacion").Value = vbNull

.Parameters("@.FecEnajenacion").Value = Null

.Parameters("@.FecEnajenacion").Value = SqlDbType.?

Try

Parameters("@.FecEnajenacion").Value = Nothing

-Jamie

|||

hi jamie,

Well, it doesn't works because of my code wait any value:

Public Overrides Sub PreExecute()

sqlCmd = New SqlCommand(sSql, sqlConn)

sqlParam = New SqlParameter("@.FecEnajenacion", SqlDbType.SmallDateTime)

sqlCmd.Parameters.Add(sqlParam)

....

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

With Sqlcmd

If dFecha2 = "0000/00/00" Then

.Parameters("@.FecEnajenacion").Value = Nothing

Else

.Parameters("@.FecEnajenacion").Value = dFecha2

End If

.ExecuteNonQuery()

End With

End Sub

Prepared statement '(@.Ejercicio smallint,@.NIFPerc char(9),@.NIFRep char(9),@.Nombre va' expects parameter @.FecEnajenacion, which was not supplied.

|||enric,

the DBNull data type must be used when setting the parameter value property to null.

i recommend that you post this question to the ADO.NET forum for further assistance.|||thank you

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...[vbc
ol=seagreen]
> Can we use SQL to set a field of a table is not allow null and it's defaul
t.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to
1.
> How can I wrte this SQL?
>[/vbcol]

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?
CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

how to set integer value in buffer

I am trying to set a decimal value to the pipelinecolumn buffer, but it doesnt get set, and the value is NULL.

Here is the portion of the code of what I am trying to do:

if (columnInfos[x].colName.EndsWith("_CRC"))

{

int a;

a_cmp tst = new a_cmp();

a= tst.a_crc32(inputbufferstream); this function returns a integer value

buffer.SetDecimal(colInfo.bufferColumnIndex, Convert.ToDecimal(a));

}

Please let me know how to set a decimal value in the buffer.

That line of code looks correct, assuming the bufferColumnIndex is correct and the column is of type DT_CY, DT_DECIMAL, or DT_NUMERIC.

Are you seeing an exception?

Thanks
Mark

|||

Do you want to set an integer or do you wanta decimal. Subject says one thing, text says another.

An agnostic way of doing this is to just assign direct, e.g.

buffer[colInfo.bufferColumnIndex] = a;

How to set a ReportParameter to the value NULL?

Hi,

how can I set a ReportParameter in C# to NULL? My code is as follows

ReportParameter[] param = new ReportParameter[1];
param[0] = new ReportParameter("LanguageID", "1");
param[1] = new ReportParameter("TopCount", "30");
this.ReportViewer1.ServerReport.SetParameters(param);

Sometimes I want to pass the value NULL for the parameter TopCount so my stored proc can handle a special case. I tried

param[1] = new ReportParameter("TopCount", null);

but it doesn't work...

I know, that there is a possibility for using urls to pass parms. There you have to put the parm name, followed by ":isnull=true".

Thanks,
Dirk

You didn't say exactly how it's not working, but I'm guessing that you're getting a compilation error because the second argument to the ReportParameter constructor is ambiguos.

Try this:

string val = null;
param[1] = new ReportParameter("TopCount", val);

|||Thanks, Chris, thats it!