CODEWITHSUNDEEP
sql-servervb.net
user2980316
user2980316

Reputation: 149

SQL Date Format

I'm working with an SQL Server and I'm create a program to add records to the database. However, the database's field for the Dates of Births isn't being accepted.

At the server side, the data type is 'Date' on MS Express SQL Server that should be YYYY-MM-DD. However, when trying to 'upload' the new records from the program the dates are being rejected. I know it's down to how I'm formatting them and particularly I know it's literally just two lines of code; But I can't get it going! 

  SQL = "Insert into PersonsA(Members_ID," & _
          "Gamer_Tag," & _
          "Screenname," & _
          "First_Name," & _
          "Last_Name," & _
          "DoB," & _
          "E_Mail_Address," & _
          "Position," & _
          "U_G_Studio," & _
          "Cautions," & _
          "Record," & _
          "Event_Attendance," & _
          "Member_Status) values('" & Me.midtxt.Text.Trim & "'," & _
          "'" & Me.gttxt.Text.Trim & "'," & _
          "'" & Me.sntxt.Text.Trim & "'," & _
          "'" & Me.fntxt.Text.Trim & "'," & _
          "'" & Me.lntxt.Text.Trim & "'," & _
          "" & Val(Me.dobtxt.Text) & "" & _ 'THIS IS THE DATES OF BIRTHS
          "'" & Format(Me.dobtxt.Text, "YYYY-MM-DD") & "'," & _ 'THIS IS FORMATTING
          "'" & Me.emailtxt.Text.Trim & "'," & _
          "'" & Me.teamptxt.Text.Trim & "'," & _
          "'" & Me.ugptxt.Text.Trim & "'," & _
          "'" & Me.ugctxt.Text.Trim & "'," & _
          "'" & Me.recordtxt.Text.Trim & "'," & _
          "'" & Me.eventatxt.Text.Trim & "'," & _
          "'" & Me.Mstattxt.Text.Trim & "')"

So as you can see the two lines I'm having trouble are: 

    "" & Val(Me.dobtxt.Text) & "" & _ 
   "'" & Format(Me.dobtxt.Text, "YYYY-MM-DD") & "'," & _

I know it'll be something really stupid, but I'm newish to programming.

Upvotes: 0

Views: 261

Answers (1)

Bjørn-Roger Kringsjå
Bjørn-Roger Kringsjå

Reputation: 10001

Reject your command string and start using SqlParameter.

Dim conn As New SqlConnection("conStr")
Dim cmd As SqlCommand = conn.CreateCommand()

cmd.CommandText = "INSERT INTO [PersonsA] ([Members_ID], [Gamer_Tag]) VALUES (@Members_ID, @Gamer_Tag);"
cmd.Parameters.AddWithValue("@Members_ID", Me.midtxt.Text.Trim) '<- If Int type change to: Integer.Parse(Me.midtxt.Text.Trim)
cmd.Parameters.AddWithValue("@Gamer_Tag", Me.gttxt.Text.Trim)

conn.Open()
cmd.ExecuteNonQuery()

Date column example:

cmd.Parameters.AddWithValue("@MY_DATE_PARAM", Date.Parse(Me.dateTextBox.Text.Trim))

Upvotes: 3

Related Questions