How to handle insert apostrophe from textbox to database?

Hi experts,

Databases reserve the ' sign to start and end strings. So if user enters a ' (e.g., 'McDonald's') in the company field, the database will give me an error and the insert into is failed. I have lots of textboxes in my asp.net application and how can I handle this exception?

First of all, I retrieved the textbox value from a stored session value:
Dim AppCompany As String = CStr(Session("Company"))

Then I have a SQL commandtext for my Insert Into statement:

Dim dbComm As New SqlClient.SqlCommand
dbComm.Connection = conHRISDb
dbComm.CommandText = "Insert Into Crew_Applicant_Table (First_Name, Last_Name,Main_Phone,App_Street,App_City,App_State,App_Zip,App_Company) Values ( '" & FName & "','" & LName & "','" & AppPhone & "','" & AppStreet & "','" & AppCity & "','" & AppState & "','" & AppZip & "','" & AppCompany & "')
dbComm.CommandType = CommandType.Text

Try usingParametrized queries
Yes, use parameterized queries. But to answer your questionregarding apostrophe, simply append another apostrophe next to yourstring. For example MacDonald's = MacDonald''s.
Insert into company(companyName) value 'MacDonald''s'
will work.
Good Luck

I am trying to insert as a parameterize way, but I got another problem:

On the first page, I stored a textbox value into session value : Session("FromDate1") = txtFromDate1.Text

Then on this page, I retrieve the stored value and take care the null value :

Dim AppFromDate1 As Date
If Session("FromDate1") Is DBNull.Value Then
AppFromDate1 = Nothing
Else
AppFromDate1 = CDate(Session("FromDate1"))
End If

The inserting data part:
dbDetail.Parameters.Add(New SqlParameter("@.AppFromDate1", SqlDbType.SmallDateTime))
sqldatenull = SqlDateTime.Null
If AppFromDate1 = "" Then
dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull
Else
dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)
End If

But it throws me an error when AppFromDate1 is null: Cast from string "" to type 'Date' is not valid.
(on Else
AppFromDate1 = CDate(Session("FromDate1"))
End If)

any suggestions?

How is AppFromDate1 declared in code?
It looks as though your Session variable isn't getting populated in thefirst form. For now I would just pass the FromDate value as a URLparameter and extract it via a Request.QueryString. If this worksyou will at least know that your SQL is valid.

I would try the following:
1) Load Session variables into String variables
dim fromDate1Str as String = Session("FromDate1")
2) Cast to Date only if String variable is not empty
if fromDate1Str <> String.Empty then
dim AppFromDate1 = CType(Session("FromDate1"),Date)
end if
Good Luck

I have took care of the seesion variable in the first form:

If txtFromDate1.Text.Trim().Length = 0Then

Session("FromDate1") = DBNull.Value

Else

Session("FromDate1") = txtFromDate1.Text

EndIf

In the 2nd form, I retrieve the value:

If Session("FromDate1")Is DBNull.ValueThen

AppFromDate1 =Nothing

Else

AppFromDate1 =CDate(Session("FromDate1"))

EndIf

HOWEVER, I found the null session date value becomes "#12:00:00AM#" even AppFromDate1=Nothing dosen't set the AppFromDate1 to null but to a string value.

Then I take care the parameter value by:

sqldatenull = SqlDateTime.Null

If AppFromDate1 =NothingThen

dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull

Else

dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)

EndIf

But dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull doesn't do the job either, it still try to insert "#12:00:00AM #"

Please someone help me!

If I try to load session variable into String variable: dim fromDate1Str as String = Session("FromDate1")
, I will get an error:

Cast from type 'DBNull' to type 'String' is not valid

you can also do an

If fromDate1Str.Trim.Length = 0 then

'pass the sqldatenull

else

'pass the actual value

end if

In the first form, I stored session variable to:
If txtFromDate1.Text.Trim().Length = 0 Then
Session("FromDate1") = DBNull.Value
Else
Session("FromDate1") = txtFromDate1.Text
End If

Then I retrieve the variable in the 2nd form:

Dim AppFromDate1 As Object
Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null
If AppFromDate1.Trim.Length = 0 Then
AppFromDate1 = sqldatenull
Else
AppFromDate1 = (Session("FromDate1"))
End If

I received the error:
Object variable or With block variable not set on

If AppFromDate1.Trim.Length=0

Any Suggestions?

>>Dim AppFromDate1 As Object
what kind of object is it ? a string, an int ?
you might want to checkthis article

Here is what I would do:
1) In form 1, initialize Session("FromDate1") to string.empty and setSession("FromDate1") to txtFromDate1.Text.ToString only iftxtFromDate1.text <> string.empty
2) In form 2 replace your parameterized Insert SQL statement witha parameterized Insert store procedure. This will providebenefits in the area of performance, security and flexibility as youwill see.
3) In your store procedure indicate which parameters are optional in TSQL by initializing them to null
4) In form 2 check whether Session("FromDate1") = string.empty. If it does, do not add the FromDate1 parameter to the parameterlist ofyour Command object. If it is not equal to string.empty then addthe FromDate1 parameter to the parameterlist.
I've done this before and it should work - provided you properlyidentified your optional paramaters in your TSQL store procedure. In a nutshell here's what should happen: If the FromDate1 parameter isnot added to the parameterlist, your store procedure will use thedefault value that you defined otherwise it should read the value fromyour Session variable and process normally.
Good Luck