Results 1 to 11 of 11

Thread: [RESOLVED] save data to SQL server have runtime error "3704"

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    584

    Resolved [RESOLVED] save data to SQL server have runtime error "3704"

    hi,please help.when i try to save data to SQL Server have error message.At the early time i was use access recordset to save the database, now i'm changing the recordset to SQL Server.when i try to save the data to SQL database have error message runtime error "3704"appear.

    this is the current code:
    Code:
     Dim conn As New ADODB.Connection
      Set conn = New ADODB.Connection
          Set conDataConnection = New Connection
          conn.ConnectionString = "driver={SQL Server};Server=GRACEKUO;Uid=sa;pwd=sa;database=SPC"
          conn.Open
          
      ' Dim strSQL As String
        'First insert
        strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "',#" & DTPicker1.Value & "#,#" & Txttime.Text & "#,'" & txtdata1.Text & "')"
        Debug.Print strSQL
        'conDataConnection.Execute strSQL
        conn.Execute strSQL error is highlight at this line
    thanks for help.
    Attached Images Attached Images  

  2. #2
    Hyperactive Member
    Join Date
    Jan 2008
    Location
    Merseyside
    Posts
    456

    Re: save data to SQL server have runtime error "3704"

    What is being passed from your DTPicker1.Value and Txttime.Text as it looks like '28' is being passed from one of them.

    Also with your debug.print strSQL
    what is that returning?
    Last edited by kevchadders; Mar 4th, 2008 at 05:58 AM.

  3. #3
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: save data to SQL server have runtime error "3704"

    Unlike Access, SQL Server does not support the # signs for dates. That must be changed to single quotes.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    584

    Re: save data to SQL server have runtime error "3704"

    kevchadders:
    hi,thanks ya reply.

    this is the debug.print strSQL
    INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values (' 0B21405 - DD','Parallelism 4.0','','28/02/2008','11:00','1.4')

    thanks for help!

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    584

    Re: save data to SQL server have runtime error "3704"

    hi,i try to change the sql statement but still have error message appear.

    this is the current code:
    Code:
    strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "'," & Format(DTPicker1.Value, "dd-mm-yyyy ") & "," & Format(Txttime.Text, "HH:MM:SS") & ",'" & txtdata1.Text & "')"
    this is the debug.print return
    INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values (' 0B21405 - DD','Parallelism 4.0','','28/02/2008','11:00','1.4')

    please help.
    Attached Images Attached Images  

  6. #6
    Hyperactive Member
    Join Date
    Jan 2008
    Location
    Merseyside
    Posts
    456

    Re: save data to SQL server have runtime error "3704"

    It sounds like its erroring on your [Time] field. (colon bit of the time) What is the datatype of that field in SQL? I expect it should be someting like VARCHAR.

    Also, just concentrate on getting the SQL working via Query Analyser first, so that you know what data is required for the correct insert to work in code.

    ps. Based on your requirements is it possible to change the Date and Time Fields to be just the one field of type timestamp?

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: save data to SQL server have runtime error "3704"

    ah... don't use TimeStamp... that has other purposes.... just use a DateTime field... but, I agree, concat the date and time into a single field.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    584

    Re: save data to SQL server have runtime error "3704"

    hi guys,thanks i solved the problem.now the data is able save to sql database.Thanks
    Code:
    strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "','" & Format(DTPicker1.Value, "dd-mmm-yyyy") & "','" & Txttime.Text & "','" & txtdata1.Text & "')"
    one more question how to save the txtdata is only contain value to sql server?current have 5 textbox data want to save to the sql server database ,if the txtdata.text is empty thenstrsql will not perform save the [txtData] with Null value to sql server database,it only save the txtdata is not empty value.

    this is the current code:
    Code:
     strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "','" & Format(DTPicker1.Value, "dd-mmm-yyyy") & "','" & Txttime.Text & "','" & txtdata1.Text & "')"
     conn.Execute strSQL
       
    'Second INsert
          strSQL = "INSERT INTO SpcData (PartNo,Dimension,Inspector,[Date],[Time],[Data]) Values ('" & combopartno.Text & "','" & Txtdimension.Text & "','" & Txtinspector.Text & "','" & Format(DTPicker1.Value, "dd-mmm-yyyy") & "','" & Txttime.Text & "','" & txtdata2.Text & "')"
    conn.Execute strSQL
    thanks for help.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: save data to SQL server have runtime error "3704"

    When you want to run code under certain conditions and not others, use an If statement, eg:
    Code:
    If Trim(txtdata2.Text) <> "" Then
      'Second INsert
      strSQL = "INSERT INTO SpcData ..."
      conn.Execute strSQL
    End It

  10. #10
    Hyperactive Member
    Join Date
    Jan 2008
    Location
    Merseyside
    Posts
    456

    Re: save data to SQL server have runtime error "3704"

    Quote Originally Posted by techgnome
    ah... don't use TimeStamp... that has other purposes.... just use a DateTime field... but, I agree, concat the date and time into a single field.

    -tg
    hehe... yes DateTime (started with Timestamp for one of my fields a few weeks ago for a project before realising it was the wrong one I needed!)

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    584

    Re: save data to SQL server have runtime error "3704"

    thanks you guys is done!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width