INSERT Date data into SQL Server 2008 R2 Table RSS

• • Reply
  

  Darklogix

  Member

  86 Points

  33 Posts

  INSERT Date data into SQL Server 2008 R2 Table

  Mar 06, 2011 08:13 PM|LINK

  Hello all,

   

  I'm fairly new to developing with ASP.NEt and SQL Server and i have a question that I'm sure has a simple answer but I cannot seem to get it figured out.

   

  I have a pretty straightforward web form to collect information from the user

   

  And I'm trying to enter that data into a Table I created after the user clicks submit, but I keep receiving this Date/Time conversion error message

   

  I have a Field in my table with a Data Type of Date (SQL Server 2008 R2), I have a variable in my VB.NET code as type Date, when I pass this variable into the database through my INSERT statement I receive the above error.  I'm using the "DaintyDate" control to collect the date from the user (Date Picker Control)

   

  Here is my INSERT statement, the variable in question is DateShipped

   

   

  <div style="font-weight: inherit; font-style: inherit; font-family: inherit; outline-width: 0px; outline-style: initial; outline-color: initial; color: black; background-color: white; padding: 0px; margin: 0px; border: 0px initial initial;">

  SQLCommandString = "INSERT into tblDemo1(Companyname, AddressLine1, AddressLine2, City, Country, StateProv, ProductSent, DateSent, DemoLength, Notes)" & _
        " VALUES ('" & Companyname & "', '" & Address1 & "', '" & Address2 & "', '" & City & "', '" & Country & "', '" & State & "', '" & ProductSent & "', " & _
        " 'DateShipped', 'DemoLength', '" & Notes & "')"
  

  </div>

   

   

  Here is what i do to get teh data from the WebForm

   

  <div style="font-weight: inherit; font-style: inherit; font-family: inherit; outline-width: 0px; outline-style: initial; outline-color: initial; color: black; background-color: white; padding: 0px; margin: 0px; border: 0px initial initial;">

  DateShipped = DaintyDate1.SelectedDate
  

  </div>

   

  Again, i'm sure I'm missing something really simple here, please help if you can.

• • Reply
  

  coredev

  Participant

  1376 Points

  218 Posts

  Re: INSERT Date data into SQL Server 2008 R2 Table

  Mar 06, 2011 08:58 PM|LINK

  Hi,

  I think you have an error in your commandstring, shouldn't it be

  SQLCommandString = "INSERT into tblDemo1(Companyname, AddressLine1, AddressLine2, City, Country, StateProv, ProductSent, DateSent, DemoLength, Notes)" & _
        " VALUES ('" & Companyname & "', '" & Address1 & "', '" & Address2 & "', '" & City & "', '" & Country & "', '" & State & "', '" & ProductSent & "', " & _
        " '" & DateShipped & "', 'DemoLength', '" & Notes & "')"

  you have the DateShipped as a string in your SQL instead of assigning the value, perhaps that is the case for the DemoLength part too?

  Best regards
  Johan

  Life runs on Code {}
• • Reply
  

  Darklogix

  Member

  86 Points

  33 Posts

  Re: INSERT Date data into SQL Server 2008 R2 Table

  Mar 06, 2011 09:24 PM|LINK

  I might have posted that SQL Statment incorrectly due to all my tinkering to try and solve this.  I finally did solve this, but it seems like more work then there needs to be.  I ended up using a parameter for the DateShipped value

   

  Try
              
              SQLCommandString = "INSERT into tblDemo1(Companyname, AddressLine1, AddressLine2, City, Country, StateProv, ProductSent, DateSent, DemoLength, Notes)" & _
                  " VALUES ('" & Companyname & "', '" & Address1 & "', '" & Address2 & "', '" & City & "', '" & Country & "', '" & State & "', '" & ProductSent & "', " & _
                  " @DateShipped, '" & DemoLength & "', '" & Notes & "')"
  
              sqlcn = New SqlConnection(ConnString)
              cmd = New SqlCommand(SQLCommandString, sqlcn)
              cmd.Parameters.Add(New SqlParameter("@DateShipped", SqlDbType.DateTime))
              cmd.Parameters("@DateShipped").Value = DateTime.Parse(DaintyDate1.Text)
  
              sqlcn.Open()
              cmd.ExecuteNonQuery()
              lblPassFail.ForeColor = Drawing.Color.Green
              lblPassFail.Text = "Success!"
  
              ClearFields()
  
              btnNewSubmit.Visible = False
              btnCancel.Text = "Close Window"
          Catch ex As Exception
              lblPassFail.ForeColor = Drawing.Color.Red
              lblPassFail.Text = "Failure!" + ex.ToString
          Finally
              sqlcn.Close()
          End Try

   

  Is there a simpler way without having to do all that parameter work just to put a Date into a Date field in a database?

  • Edited by Darklogix on Mar 06, 2011 09:25 PM
  • Edited by Darklogix on Mar 07, 2011 03:48 AM
  • Edited by Darklogix on Mar 07, 2011 02:14 PM
• • Reply
  

  limno

  All-Star

  117326 Points

  8003 Posts

  Moderator

  MVP

  Re: INSERT Date data into SQL Server 2008 R2 Table

  Mar 07, 2011 01:58 AM|LINK

  You should use parameter for all your variables to pass the value to your database. By using the string concatenation, you are vunarable to SQL Injection attack and all kinda of potential syntax errors.

  If you are dealing with simple application for quick demonstration, you can use datacontrols and SQLDatasource to eliminate any code at all.

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  nilsan

  All-Star

  16940 Points

  3719 Posts

  Re: INSERT Date data into SQL Server 2008 R2 Table

  Mar 07, 2011 04:41 AM|LINK

  In addition to what limno said, I'd suggest you to go through below link :

  http://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

  It will solve all your problems :)

  Nilesh
  Blog | Get your forum question answered | Microsoft Community Contributor 2011
  
  • Edited by Darklogix on Mar 07, 2011 02:14 PM
  • Edited by Darklogix on Mar 10, 2011 04:00 AM
  • Marked as answer by Darklogix on Mar 10, 2011 04:00 AM