SQL Server datetime Null insert

[andrewsmd]

We have a system running on sql server 2000 and I need to insert a null value into the sql database for a datetime column. I have an if statement in vb that checks to see if a textbox is empty. If it is, then I need to insert null, otherwise I need to insert the value here is my vb code

'we need these to hold the payment dates
Dim lastPaymentDate As String = ""
Dim lastChargeDate As String = ""

'if these are null then we need to enter blank
'quotes so the date does not get defaulted to
'01/01/1900
If Not (lastPaymentTBox.Text = "") Then
lastPaymentDate = lastPaymentTbox.Text
Else

'this needs to be null but I don't know
'what to set it as I have tried
'null, NULL, <null>, <NULL>,
'and system.DBNull.Value
lastPaymentDate = "null"

End If

If Not (lastChargeDateTBox.Text = "") Then
lastChargeDate = lastChargeDateTBox.Text
Else
'this needs to be null but I don't know
'what to set it as I have tried
'null, NULL, <null>, <NULL>,
'and system.DBNull.Value
lastChargeDate = "NULL"
End If

Dim addDebtorSQL As String = "insert into tblPendingAccounts (UserID, LastName, FirstName, Address1, Address2, POBox, ApartmentNo, City, State, ZipCode, HomePhone, SpouseName, PatientName, ClientIdentifier, LastPaymentDate, LastChargeDate, MailReturned, Employment, EmploymentPhone, AdditionalInfo1, AdditionalInfo2, AdditionalInfo3, AdditionalInfo4, Notes, Amount, NameSSN, SpouseSSN, ReportCredit, DisputedAccount, TypeID, DateSubmitted) values (" & getuserid(User.Identity.Name.ToString) & ", '" & safequotes(debtorLastNameTBox.Text) & "', '" & safequotes(debtorFirstNameTBox.Text) & "', '" & safequotes(debtorAddress1TBox.Text) & "', '" & safequotes(debtorAddress2TBox.Text) & "', '" & safequotes(POBoxTBox.Text) & "', '" & safequotes(apartmentTBox.Text) & "', '" & safequotes(debtorCityTBox.Text) & "', '" & debtorStateDD.SelectedItem.Text & "', '" & phonenumber(safequotes(debtorZipTBox.Text)) & "', '" & safequotes(phonenumber(homePhoneTBox.Text)) & "', '" & safequotes(spouseNameTBox.Text) & "', '" & safequotes(patientNameTBox.Text) & "', '" & safequotes(clientIdentifierTBox.Text) & "', '" & safequotes(lastPaymentDate) & "', '" & safequotes(lastChargeDate) & "', " & mailreturnedTF & ", '" & safequotes(employerTBox.Text) & "', '" & safequotes(phonenumber(employerPhoneTBox.Text)) & "', '" & safequotes(additionalInfo1.Text) & "', '" & safequotes(additionalInfo2.Text) & "', '" & safequotes(additionalInfo3.Text) & "', '" & safequotes(additionalInfo4.Text) & "', '" & safequotes(notesTBox.Text) & "', CONVERT(money, '" & safequotes(amountTBox.Text) & "'), '" & safequotes(phonenumber(SSNTBox.Text)) & "', '" & safequotes(phonenumber(spouseSSNTBox.Text)) & "', " & creditTF & ", " & disputedTF & ", " & accountTypeDD.SelectedValue & ", '" & DateTime.Now.ToString & "')"
Dim addAccountCommand As New Data.SqlClient.SqlCommand(addDebtorSQL, myConnection)
Try
myConnection.Open()
If addAccountCommand.ExecuteNonQuery() Then
messagePanel.Visible = "true"
messageLabel.Text = "<b>" & debtorFirstNameTBox.Text & " " & debtorLastNameTBox.Text & "</b> has been added to your pending accounts."
introPanel.Visible = False
'Response.Redirect("here.aspx")
End If
Catch ex As Exception
messageLabel.Text = ex.ToString & "<br /><Br />" & addDebtorSQL
Finally
myConnection.Close()
End Try

[Ocean10000]

I am going to past some code below wrote in C#. Seeing that you are doing this using VB.Net it shouldn't be that hard to translate back for you.

This function creates a Command object for a inlined stored procedure with strongly typed parameters which will protect you from sql injection issues, which your current code will not. I made some guesses at data types but did not spend that much time on it.

The items you wanted to know about is called "Parameter.IsNullable", set it to true, and do not put a value for that parameter and it will put in a null for you.

[script language="C#" runat='server']
private System.Data.SqlClient.SqlCommand tblPendingAccounts_InsertCommand()
{
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand();
Cmd.CommandText =@"Insert into [tblPendingAccounts]
(
[lastname],[firstname],[address1],[address2],[pobox],
[apartmentno],[city],[state],[zipcode],[homephone],
[spousename],[patientname],[clientidentifier],
[lastpaymentdate],[lastchargedate],[mailreturned],
[employment],[employmentphone],[additionalinfo1],
[additionalinfo2],[additionalinfo3],[additionalinfo4],
[amount],[namessn],[reportcredit],[disputedaccount],
[typeid],[datesubmitted]
)
values
(
@lastname,@firstname,@address1,@address2,@pobox,
@apartmentno,@city,@state,@zipcode,@homephone,
@spousename,@patientname,@clientidentifier,
@lastpaymentdate,@lastchargedate,@mailreturned,
@employment,@employmentphone,@additionalinfo1,
@additionalinfo2,@additionalinfo3,@additionalinfo4,
@amount,@namessn,@reportcredit,@disputedaccount,
@typeid,@datesubmitted
)

Select 
[userid],[lastname],[firstname],[address1],[address2],[pobox],[apartmentno],[city],[state],[zipcode],[homephone],[spousename],[patientname],[clientidentifier],[lastpaymentdate],[lastchargedate],Isnull([mailreturned],0) as 'mailreturned',[employment],[employmentphone],[additionalinfo1],[additionalinfo2],[additionalinfo3],[additionalinfo4],[amount],[namessn],[reportcredit],[disputedaccount],[typeid],[datesubmitted]
From [tblPendingAccounts]
Where (
[userid]=@@identity)
";

Cmd.CommandType = System.Data.CommandType.Text;
Cmd.Connection = this.CreateConnection();
System.Data.SqlClient.SqlParameter Parameter;

//---------------------------------------------------
// LastName
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@LastName";
Parameter.SourceColumn = "LastName";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// FirstName
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@FirstName";
Parameter.SourceColumn = "FirstName";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// Address1
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@Address1";
Parameter.SourceColumn = "Address1";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// Address2
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@Address2";
Parameter.SourceColumn = "Address2";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// POBox
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@POBox";
Parameter.SourceColumn = "POBox";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// ApartmentNo
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@ApartmentNo";
Parameter.SourceColumn = "ApartmentNo";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// City
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@City";
Parameter.SourceColumn = "City";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// State
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@State";
Parameter.SourceColumn = "State";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// ZipCode
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@ZipCode";
Parameter.SourceColumn = "ZipCode";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// HomePhone
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@HomePhone";
Parameter.SourceColumn = "HomePhone";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// SpouseName
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@SpouseName";
Parameter.SourceColumn = "SpouseName";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// PatientName
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@PatientName";
Parameter.SourceColumn = "PatientName";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// ClientIdentifier
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@ClientIdentifier";
Parameter.SourceColumn = "ClientIdentifier";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// LastPaymentDate
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@LastPaymentDate";
Parameter.SourceColumn = "LastPaymentDate";
Parameter.SqlDbType = System.Data.SqlDbType.DateTime;
Parameter.Precision = 23;
Parameter.Size = 16;
Parameter.Scale = 3;
Parameter.IsNullable = true;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// LastChargeDate
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@LastChargeDate";
Parameter.SourceColumn = "LastChargeDate";
Parameter.SqlDbType = System.Data.SqlDbType.DateTime;
Parameter.Precision = 23;
Parameter.Size = 16;
Parameter.Scale = 3;
Parameter.IsNullable = true;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// MailReturned
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@MailReturned";
Parameter.SourceColumn = "MailReturned";
Parameter.SqlDbType = System.Data.SqlDbType.Bit;
Parameter.Precision = 1;
Parameter.Size = 1;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// Employment
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@Employment";
Parameter.SourceColumn = "Employment";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// EmploymentPhone
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@EmploymentPhone";
Parameter.SourceColumn = "EmploymentPhone";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// AdditionalInfo1
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@AdditionalInfo1";
Parameter.SourceColumn = "AdditionalInfo1";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// AdditionalInfo2
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@AdditionalInfo2";
Parameter.SourceColumn = "AdditionalInfo2";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// AdditionalInfo3
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@AdditionalInfo3";
Parameter.SourceColumn = "AdditionalInfo3";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// AdditionalInfo4
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@AdditionalInfo4";
Parameter.SourceColumn = "AdditionalInfo4";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// Amount
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@Amount";
Parameter.SourceColumn = "Amount";
Parameter.SqlDbType = System.Data.SqlDbType.Money;
Parameter.Precision = 19;
Parameter.Size = 21;
Parameter.Scale = 4;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// NameSSN
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@NameSSN";
Parameter.SourceColumn = "NameSSN";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// ReportCredit
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@ReportCredit";
Parameter.SourceColumn = "ReportCredit";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// DisputedAccount
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@DisputedAccount";
Parameter.SourceColumn = "DisputedAccount";
Parameter.SqlDbType = System.Data.SqlDbType.VarChar;
Parameter.Size = 50;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// TypeID
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@TypeID";
Parameter.SourceColumn = "TypeID";
Parameter.SqlDbType = System.Data.SqlDbType.Int;
Parameter.Precision = 10;
Parameter.Size = 4;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

//---------------------------------------------------
// DateSubmitted
//---------------------------------------------------
Parameter = new System.Data.SqlClient.SqlParameter();
Parameter.ParameterName = "@DateSubmitted";
Parameter.SourceColumn = "DateSubmitted";
Parameter.SqlDbType = System.Data.SqlDbType.DateTime;
Parameter.Precision = 23;
Parameter.Size = 16;
Parameter.Scale = 3;
Parameter.IsNullable = false;
Cmd.Parameters.Add(Parameter);

return Cmd;
}
[/script]

Sample code how to use it.

System.Data.SqlClient.SqlCommand cmd = tblPendingAccounts_InsertCommand();
cmd.Connection = myConnection;
cmd.Parameter["@LastName"].Value=debtorLastNameTBox.Text;
.
.
.
if (addAccountCommand.ExecuteNonQuery())
{
.
.
}