4 Replies - 397 Views - Last Post: 17 December 2015 - 01:16 PM Rate Topic: -----

#1 rhansen816  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 03-December 15

writing data to access 2007

Posted 17 December 2015 - 12:49 PM

so i am currently collection data from a scale via ethernet and trying to insert it into a access 2007 database a table. everything runs good until the execute statement. there i get an error "no value given for one or more required parameters". any help would be greatly appreciated. the access table reads as follows:

UnitWeight - Number
ScaleDateTime - Date/Time (Primary Key)
ScaleID - Number



my code reads as follows:




Private Sub Form_Load()

Dim status As Long

If (Winsock1.State = sckClosed) Then
                                    ' Invoke the Connect method to initiate a connection.
    Winsock1.RemotePort = "1702"     'port number on scale
    Winsock1.RemoteHost = "192.168.1.1"     'IP address of the scale
    Winsock1.Connect
End If

If Winsock1.State = sckConnected Then
    Debug.Print "Connected"
End If

End Sub


Private Sub txtDisplay_Change()

Dim statement As String
Dim conn As ADODB.Connection
Dim currentdate As Date
Dim scaleid As Integer
Dim unitweight As Long

scaleid = "5"   'set scale id to #5
currentdate = DateTime.Now  'setting the current system date and time
unitweight = CLng(txtDisplay.Text)
Set conn = New ADODB.Connection     'establishing new database connection
    'next line will set the connection string for access 2007
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Database\Scale_Data.accdb; Persist Security Info = False;"
conn.Open

    'next line will build the transfer string to the access DB
statement = "INSERT INTO tblScale(UnitWeight],ScaleDateTime,ScaleID) VALUES(unitweight,currentdate,scaleid)"
conn.Execute statement  'executing the sending of the transfer string
conn.Close

End Sub

Public Sub Winsock1_DataArrival(ByVal bytesTotal As Long)   'monitors the remoteport and host for incoming data

Dim strData As String    'strData represent the scale weight

Winsock1.GetData strData, vbString

txtDisplay.Text = Trim(strData)   'display scale weight in text box

End Sub
:code:



any help would be greatly appreciated.

This post has been edited by modi123_1: 17 December 2015 - 12:53 PM
Reason for edit:: Plese select the text that is code and click the 'code' button in the editor.


Is This A Good Question/Topic? 0
  • +

Replies To: writing data to access 2007

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 11491
  • View blog
  • Posts: 45,342
  • Joined: 12-June 08

Re: writing data to access 2007

Posted 17 December 2015 - 12:54 PM

36	statement = "INSERT INTO tblScale(UnitWeight],ScaleDateTime,ScaleID) VALUES(unitweight,currentdate,scaleid)"

This is all just a string. If you want to add values you need to use parameters or concatenate the values where they need to go.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • Beach ready
  • member icon

Reputation: 5463
  • View blog
  • Posts: 21,473
  • Joined: 12-December 12

Re: writing data to access 2007

Posted 17 December 2015 - 12:57 PM

The square bracket is obviously unmatched as well.
Was This Post Helpful? 0
  • +
  • -

#4 rhansen816  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 03-December 15

Re: writing data to access 2007

Posted 17 December 2015 - 01:04 PM

View Postmodi123_1, on 17 December 2015 - 12:54 PM, said:

36	statement = "INSERT INTO tblScale(UnitWeight],ScaleDateTime,ScaleID) VALUES(unitweight,currentdate,scaleid)"

This is all just a string. If you want to add values you need to use parameters or concatenate the values where they need to go.



i tried it with brackets and it did not work i must have missed that one when i took them out. i have tried it with the line reading

statement = "INSERT INTO tblScale([UnitWeight], [ScaleDateTime], [ScaleID]) VALUES(unitweight,currentdate,scaleid)"

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 11491
  • View blog
  • Posts: 45,342
  • Joined: 12-June 08

Re: writing data to access 2007

Posted 17 December 2015 - 01:16 PM

Just because you have a variable's name inside a string does not mean you do not just have the characters of the variable name and not the value. Again.. concatenation or use parameters (preferably the latter).

        Dim bar As String = "abc"
        Dim foo As String = "barnutzhere" '-- this is all a string.  It does not mean substitute "bar" with the value of variable bar's value of "abc".. it is literally a string of characters.

        foo = bar + "nutzhere" '-- basic string concatenation so you get the variable's value.

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1