?
Solved

vbscript sql syntax problem with datetime

Posted on 2012-09-06
4
Medium Priority
?
916 Views
Last Modified: 2012-09-10
Hi All,

I am extracting data from one database table and importing into another database table.
One of the fields I need to populate is a datetime field.
I just cant seem to get it to work in my vbscript.

Extracts below so you can see what I have been doing and tried.

What do I need to put in my sql string to populate this field        

      strMemberTypeId = "{DA053225-8C79-452F-A49B-31BE596C6C4B}"
      strMembershipProductId = "{361159A2-590C-4B33-9181-281BE0478D62}"
      'strRenewdate = FormatDateTime("2040-01-21 00:00:00")
      'Dim strDate As Date  
      'strRenewDate = "2040-01-21"
      


      SQLString = DECLARE @datetime datetime = '2040-01-21 00:00:00';
      'SQLString = DECLARE @datetime datetime = @smalldatetime;
      SQLString = "INSERT INTO T_MEMBERS ("
      SQLString = SQLString & "[MemberTypeId],[MembershipProductId],[Renewal_Date]



Thankyou

Sid.
0
Comment
Question by:sid20vt
4 Comments
 
LVL 16

Assisted Solution

by:kmslogic
kmslogic earned 501 total points
ID: 38374816
Well, you need to surround your date in single quotes for SQL server like:

INSERT INTO T_MEMBERS('2040-01-21') (assuming your table only has one field and it is the datetime field)

better is

INSERT INTO T_MEMBERS (MyColumnName) VALUES ('2040-01-21')
0
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 498 total points
ID: 38374855
Hi Sid,
You could give the field [Renewal_Date] a default value/binding of GetDate() in the table designer, then you could exclude the parameter from the Insert SQL. Having said that, there may be instances when you don't want to use todays date for the default value, in which case you can pass in any date using your SQL.
    ' Guids can be passed in as strings, just lose the braces.
    Dim strMemberTypeId As String = "DA053225-8C79-452F-A49B-31BE596C6C4B"
    Dim strMembershipProductId As String = "361159A2-590C-4B33-9181-281BE0478D62"
    
    Dim SQLString As String = ""
    
    Dim Renewal_Date As Date
    
    ' Renewal_Date = Date.Now
    ' or 
    ' Renewal_Date = Convert.ToDateTime(Me.Renewal_Datetextbox.Text)
    ' or
    Date.TryParse(Me.Renewal_Datetextbox.Text, Renewal_Date)
    
    SQLString += "INSERT INTO T_MEMBERS ([MemberTypeId],[MembershipProductId],[Renewal_Date]) "
    SQLString += "Values (" & strMemberTypeId & "," & strMembershipProductId & "," & Renewal_Date & ")"
    
    ' copy from the debug window and validate the syntax in your db query window.
    System.Diagnostics.Debug.WriteLine(SQLString)
    

Open in new window

Alan
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 501 total points
ID: 38376935
set c = createobject("adodb.connection")
c.open "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<datebase-name>;Data Source=<your-server-name>"

      strMemberTypeId = "{DA053225-8C79-452F-A49B-31BE596C6C4B}"
      strMembershipProductId = "{361159A2-590C-4B33-9181-281BE0478D62}"
      strRenewDate = "2040-01-21"


set r = createobject("adodb.recordset")
r.open "select [MemberTypeId],[MembershipProductId],[Renewal_Date] from T_MEMBERS where 1=0",c,3,3
r.addnew
r("MemberTypeId")=strMemberTypeId 
r("MembershipProductId")=strMembershipProductId 
r("Renewal_Date") = strRenewDate

r.update

Open in new window

0
 

Author Closing Comment

by:sid20vt
ID: 38382134
After all that, the solution was simple.

The datetime column I was trying to change was a computed column so couldnt be updated.
Other columns elsewhere needed the datetime values.
Wasnt until I started looking at the sql table designs and editing in query that I stumbled across it.

Thank you all for your help.  I am splitting the points.
Sometimes all you need is someone you can bounce ideas off.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question