how to insert null value into database?

hi guys. i'm using vb.net with vs 2003 and MSSQL Managment Studio Express as my database server.
i want to insert null value into database, so i use System.DBNull.Value
but it doesn't show NULL, but a empty field. I tried System.DBNull.Value.ToString, it gives me same result.
what can i do to have NULL in that field?
thanks for any advise, i would appreciate it very much.

Hello,

If you use an INSERT statement and don't mention the columns you want null values in then they will contain null values for the inserted row.

--Bonnie

|||Could you please show us your query ? I am not sure, from which application you want to insert the NULL value, either through code (then we need your code to investigate your problem) or through the GUI (then you will simply have to press STRG+0 staying inside the actual data cell)

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to insert data with single qoute?

Hi guys, Anyone of you kindly reply on this.

Thanks guys,

Make the single quote as double-single-quotes:
select 'Chris''s'
will select, Chris's
Hope this helps,
-chris|||Thanks a lot Chris...........|||

Hi emkcah,

Chris suggestion is valid, but beware if the character data has two (or more) singe quotes as this will then fail (as in the string You're at Chris's). Also, the suggestion is erroneous as your souce data is in essence now corrupt and no longer portable.

In this scenario, you should:

SET QUOTED_IDENTIFIER OFF

GO

SELECT "You're at Chris's"

Cheers

Rob

|||Thanks Robert |||

You can use a function to check user input for a "single quote" and add the second "single quote" if you're adding records based on user input into a form. The following function changes a last name such as "O'Day" to "O''Day".

Function call:

lastname = fixapostrophe(Server.HtmlEncode(lastname))

Function:

Private Function fixapostrophe(ByVal newText As String) As String
Dim apostrophe As String = "'"
Dim pos As Integer
Dim start As Integer = 1
Do While InStr(start, newText, apostrophe) > 0
pos = InStr(start, newText, apostrophe)
newText = newText.Insert(pos, apostrophe)
start = pos + 2
Loop
Return newText
End Function

It seems to work OK, but you should check with a real programmer before using it.

|||There's a much easier solution, just use the replace function :
select (replace,"'","`",field1) from tablename
eg insert into [destination table] select replace("'","`",fieldname) from [sourcetable]
look it up on books on line..|||

Hello,

Using Replace will corrupt your souce data. If the string "you're" is passed to the app, it should remain as "you're" and stored as such.

Cheers

Rob

How to insert data with single qoute?

Hi guys, Anyone of you kindly reply on this.

Thanks guys,

Make the single quote as double-single-quotes:
select 'Chris''s'
will select, Chris's
Hope this helps,
-chris|||Thanks a lot Chris...........|||

Hi emkcah,

Chris suggestion is valid, but beware if the character data has two (or more) singe quotes as this will then fail (as in the string You're at Chris's). Also, the suggestion is erroneous as your souce data is in essence now corrupt and no longer portable.

In this scenario, you should:

SET QUOTED_IDENTIFIER OFF

GO

SELECT "You're at Chris's"

Cheers

Rob

|||Thanks Robert |||

You can use a function to check user input for a "single quote" and add the second "single quote" if you're adding records based on user input into a form. The following function changes a last name such as "O'Day" to "O''Day".

Function call:

lastname = fixapostrophe(Server.HtmlEncode(lastname))

Function:

Private Function fixapostrophe(ByVal newText As String) As String
Dim apostrophe As String = "'"
Dim pos As Integer
Dim start As Integer = 1
Do While InStr(start, newText, apostrophe) > 0
pos = InStr(start, newText, apostrophe)
newText = newText.Insert(pos, apostrophe)
start = pos + 2
Loop
Return newText
End Function

It seems to work OK, but you should check with a real programmer before using it.

|||There's a much easier solution, just use the replace function :
select (replace,"'","`",field1) from tablename
eg insert into [destination table] select replace("'","`",fieldname) from [sourcetable]
look it up on books on line..|||

Hello,

Using Replace will corrupt your souce data. If the string "you're" is passed to the app, it should remain as "you're" and stored as such.

Cheers

Rob

sql

How to insert data with single qoute?

Hi guys, Anyone of you kindly reply on this.

Thanks guys,

Make the single quote as double-single-quotes:
select 'Chris''s'
will select, Chris's
Hope this helps,
-chris|||Thanks a lot Chris...........|||

Hi emkcah,

Chris suggestion is valid, but beware if the character data has two (or more) singe quotes as this will then fail (as in the string You're at Chris's). Also, the suggestion is erroneous as your souce data is in essence now corrupt and no longer portable.

In this scenario, you should:

SET QUOTED_IDENTIFIER OFF

GO

SELECT "You're at Chris's"

Cheers

Rob

|||Thanks Robert |||

You can use a function to check user input for a "single quote" and add the second "single quote" if you're adding records based on user input into a form. The following function changes a last name such as "O'Day" to "O''Day".

Function call:

lastname = fixapostrophe(Server.HtmlEncode(lastname))

Function:

Private Function fixapostrophe(ByVal newText As String) As String
Dim apostrophe As String = "'"
Dim pos As Integer
Dim start As Integer = 1
Do While InStr(start, newText, apostrophe) > 0
pos = InStr(start, newText, apostrophe)
newText = newText.Insert(pos, apostrophe)
start = pos + 2
Loop
Return newText
End Function

It seems to work OK, but you should check with a real programmer before using it.

|||There's a much easier solution, just use the replace function :
select (replace,"'","`",field1) from tablename
eg insert into [destination table] select replace("'","`",fieldname) from [sourcetable]
look it up on books on line..

|||

Hello,

Using Replace will corrupt your souce data. If the string "you're" is passed to the app, it should remain as "you're" and stored as such.

Cheers

Rob

How to insert current date in SQL?

Hi guys,

how do i insert the current date in SQL?

GetDate()

http://msdn2.microsoft.com/en-us/library/ms188383.aspx

Hope this helps.

|||

You can get the value of the current date in thegetdate() function.

How to increase the default row size in sql server 2000 only

Hi, Guys.
I am in bit trouble.I am importing the table from mysql to sql server which has having nearly 65000 records.
I starts the import of table by import/export utility.But when the records reaches 65000, the error message came.."THIS TABLE HAVING ROW SIZE 8190 WHICH IS EXCCEDING THE DEFAULT SIZE OF 8060".
So please let me know how to over come this problem..

I CAN'T UPGRADE SQL SERVER SO GIVE SOLUTION FOR SQL SERVER 2000 ONLY...

Thanks in Advance to all of you.

pchadha20For my knowledge it isn't possible to change that limit. You should split your table or in some cases use Ntext-type but that would generate a lot of other troubles.

how to improve performance in this query?

Hey guys,

Here's my situation:

I have a table called lets say 'Tree', as illustred bellow:

Tree
====
TreeId (integer)(identity) not null
L1(integer)
L2(integer)
L3(integer)
...
L10(integer)

The combination of the values of L1 thru L10 is called a "Path" , and
L1 thru L10 values are stored in a second table lets say called
'Leaf':

Leaf
====
LeafId (integer)(identity) not null
LeatText varchar(2000)

Here's my problem:

I need to lookup for a given keyword in each path of the tree table,
and return each individual column for the paths that match the
criteria. Here's the main idea of how I have this now.

SELECT TreeId,L1,L2,...,L10, GetText(L1) + GetText(L2) as L2text + ...
+ GetText(L10) AS PathText
INTO #tmp FROM Tree //GetText is a lookup function for the Leaf table

SELECT L1,GetText(L1),L2,GetText(L2),...,L10,GetText(L10) FROM #tmp
WHERE
CharIndex(@.keyword,a.pathtext) > 0

Does anyone would know a better,smart, more efficient way to
accomplish this task? :)

Thks,On 1 Nov 2004 08:23:44 -0800, Silvio wrote:

>Does anyone would know a better,smart, more efficient way to
>accomplish this task? :)

Hi Silvio.

Yep. Improve your table design. You should normalize down to at least
third normal form. That emans that the repeating group (L1 ... L10) in the
Leaf table has to go in it's own table:

CREATE TABLE Paths
(TreeID int NOT NULL REFERENCES Trees,
PathNo int NOT NULL CHECK (PathNo BETWEEN 1 AND 10),
LeafID int NOT NULL REFERENCES Leaves,
PRIMARY KEY (TreeID, PathNo)
)

If the same leaf can't occur twice in a tree, you could also add a
UNIQUE(TreeID, LeafID) constraint, or make that the primary key. If the
order of the leaves in a tree is unimportant, you can leave out the PathNo
column.

>I need to lookup for a given keyword in each path of the tree table,
>and return each individual column for the paths that match the
>criteria.

Probably something like this:

SELECT P.PathNo, L.LeafText
FROM Paths AS P
INNER JOIN Leaves AS L
ON L.LeafNo = P.LeafNo
WHERE EXISTS (SELECT *
FROM Paths AS P2
INNER JOIN Leaves AS L2
ON L2.LeafNo = P2.LeafNo
WHERE P2.TreeID = P.TreeID
AND CHARINDEX(@.keyword, L2.LeafText) > 0)
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

How to import data from Excel file to SDF database

Hi Guys,

Can anyone tell me how can I achieve the file import of XLS file and read the data inside to insert into the respective table in my SDF database in the mobile devices?

Thanks.

Regards,

Jenson

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

|||

Ilya Tumanov wrote:

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

Hi IIya,

Thanks for replying to my question, I'm still have some doubts. First of all, how would the parser like, what is the processes need to be taken care of? And I have a form that allow users to upload the Excel file, do I check whether the file has been finished uploaded, and then run the parser to parse all the data out into a text file, from that file insert into SQLCE? Please pardon me for my dumb questions, as I have no prior experience dealing with SQLCE in this way, I only did simple storing and retrieving of data on SQLCE.

Thanks.

Regards,

Jenson

|||anyone could help me with this?|||Jenson, you have to explain a bit more about your requirements for us to help you. Do you want to import Excel Mobile files that reside on the mobile device via an app on the mobile device or ?|||

Hi ErikEJ,

I have found out that I can't do that =p

So I give up on that and have a workaround, now I have problem exporting data from SDF to Excel file. I will make a new thread for this, hope you and the rest would be able to help me out, very urgent.

Thanks.

Regards,

Jenson

How to import data from Excel file to SDF database

Hi Guys,

Can anyone tell me how can I achieve the file import of XLS file and read the data inside to insert into the respective table in my SDF database in the mobile devices?

Thanks.

Regards,

Jenson

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

|||

Ilya Tumanov wrote:

Since Pocket Excel does not have any object model you’d need to reverse engineer Excel file format, write a parser for these files and then (the easy part) parse it, load data and insert it into SQL CE database. On a desktop you can open data connection to the Excel file and that task become much easier. You might also consider using some other format, e.g. XML or CSV.

Hi IIya,

Thanks for replying to my question, I'm still have some doubts. First of all, how would the parser like, what is the processes need to be taken care of? And I have a form that allow users to upload the Excel file, do I check whether the file has been finished uploaded, and then run the parser to parse all the data out into a text file, from that file insert into SQLCE? Please pardon me for my dumb questions, as I have no prior experience dealing with SQLCE in this way, I only did simple storing and retrieving of data on SQLCE.

Thanks.

Regards,

Jenson

|||anyone could help me with this?|||Jenson, you have to explain a bit more about your requirements for us to help you. Do you want to import Excel Mobile files that reside on the mobile device via an app on the mobile device or ?|||

Hi ErikEJ,

I have found out that I can't do that =p

So I give up on that and have a workaround, now I have problem exporting data from SDF to Excel file. I will make a new thread for this, hope you and the rest would be able to help me out, very urgent.

Thanks.

Regards,

Jenson

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de