Saving Data in an SQL table From a Word Document through the Click

I need help with code for a button on a Form in my VBA application.
I would like to copy and save all currently selected text from a word document to a table in an SQL Server Database in response to the Click event of this Button on a VBA Form.
The name of the database is WriterDemo
The location of the database is as follows:

conn = New SqlConnection("Data Source=GulgeePC\sqlexpress;Initial Catalog=WriterDemo;Persist Security Info=True;User ID=sa;Password=momin;Connect Timeout=30")

The Name of the table in the WriterDemo Database into which I wish to save text is dbo.t_notes (I would like to save the text from word into note column of this table. Client ID will be 222 and  all the notes will be for the Client with this ID. Note ID and t_date will be generated automatically as the note is save in the note column by the click event of the Button on my Form.

The columns in this table are as folow:
Notes_id (primary key, numeric (18,0)  not null)
Client_id (numeric((18,0)  not null)
t_date (datetime, not null)
note (nvarchar (4000), null)

Many thanks for your help.
LVL 1
FaheemAhmadGulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JoozefCommented:
Apart from generally disagreeing that you use a NUMERIC(18,0) for Primary and Foreign Keys (I would use INT or UNIQUEIDENTIFIER), here is the VBA/ADO code to INSERT new records.  It will need to be modified for UPDATEs.  An aside, I would replace this "TEXT" command with a single Stored Procedure that could test if the record existed already and then do an UPDATE or INSERT accordingly.


Sub Button1_Click()

  'Populate vNoteID
  'Populate v_ClientID
  'Populate v_DateTime

  'Write the currently highlighted text out to the DB  
  SaveSelectedText vNoteID, vClientID, vDateTime, Selection.Text

End Sub


'Routine to write to DB
Private Sub SaveSelectedText(NoteID As Long, _
                             ClientID As Long, _
                             Timestamp As Date, _
                             Notes As String)

  Dim sNote As String

  On Error GoTo SaveSelectedTextZ
 
  Dim cn As ADODB.Connection
  Dim cmd As ADODB.Command
 
  Set cn = New ADODB.Connection
  'cn.Open "Data Source=GulgeePC\sqlexpress;Initial Catalog=WriterDemo;Persist Security Info=True;User ID=sa;Password=momin;Connect Timeout=30"
  cn.Open "Provider=SQLOLEDB;Data Source=61SYDDB05-05\WANZSQL05A;Initial Catalog=AJS;User ID=JHanna;Password=Sydney02"
 
  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandTimeout = 60
    .CommandText = "INSERT INTO dbo.[t_notes] ([Notes_id], [Client_id], [t_date], [note]) VALUES (?, ?, ?, ?)"
    .Parameters.Append cmd.CreateParameter("@Notes_id", adNumeric, adParamInput, , NoteID)
    .Parameters("@Notes_id").Precision = 18
    .Parameters("@Notes_id").NumericScale = 0
    .Parameters.Append cmd.CreateParameter("@Client_id", adNumeric, adParamInput, , ClientID)
    .Parameters("@Client_id").Precision = 18
    .Parameters("@Client_id").NumericScale = 0
    .Parameters.Append cmd.CreateParameter("@Timestamp", adDBTimeStamp, adParamInput, , Format(Timestamp, "yyyy-MM-dd"))
    .Parameters.Append cmd.CreateParameter("@Notes", adVarWChar, adParamInput, 4000, Notes)
    .Execute
  End With

SaveSelectedTextX:
 
  Set cmd.ActiveConnection = Nothing
  Set cmd = Nothing

  If Not cn Is Nothing Then
    If cn.State = ADODB.adStateOpen Then
      cn.Close
    End If
    Set cn = Nothing
  End If

  Exit Sub

SaveSelectedTextZ:

  MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error during DB Operation"
  Resume SaveSelectedTextX
  Resume

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaheemAhmadGulAuthor Commented:
Many thanks for your response to my question. I have tried the code but I get the following error message:
ByRef argument type mismatch with the words vNoteID highlighted in the code for Button Click Event.

Please also let me know if I am supposed to put some values in the following line of code where there are lots of question marks.
CommandText = "INSERT INTO dbo.[t_notes] ([Notes_id], [Client_id], [t_date], [note]) VALUES (?, ?, ?, ?)"

Please also note that I am not copying text from any table in Word. The text selected in my word document is just a paragraph of normal text not in any table. However this text needs to go into the SQL database table as I indicated in my original question.
I am a beginner and would greatly appreciate further help. Thanks
0
JoozefCommented:
The variables vNoteID, vClientID and vDateTime need declaring (with Dim) and populating before calling the SaveSelectedText routine.
eg
Dim vNoteID As Long
Dim vClientID As Long
Dim vDateTime As Date
'Add your logic here
vNoteID = <set value>
vClientID = 222
vDateTime = Now()
 
The only thing you need to do is set the Connection string for the cn.Open command.  The question marks in the CommandText are deliberate.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FaheemAhmadGulAuthor Commented:
Thank you for your help. I am now at work where I do not have the facility to test the amended code. I will now test it in the evening and will then let you know if I have been successful.
Regards
0
JoozefCommented:
No problem
0
FaheemAhmadGulAuthor Commented:
I have modified the code very kindly provided by you to make it match my database table. However, on running it I get the following message:
"Incorrect syntax near patient_id"
Please note that I have arbitrarily provided a value of 42 for note ID and 222 for patient ID.
The code that I am now using is as follows:

Sub Button1_Click()

  'Populate notes_id
  'Populate v_patient_id
  'Populate v_DateTime

  'Write the currently highlighted text out to the DB
 
Dim notes_id As Long
Dim patient_id As Long
Dim t_date As Date

'Add your logic here
' the values for notes_id and patient_id are arbitrary here. In the actual program they will be provided through code

notes_id = 42
patient_id = 222
t_date = Now()
  SaveSelectedText patient_id, t_date, Selection.Text
' SaveSelectedText notes_id, patient_id, t_date, Selection.Text
End Sub


'Routine to write to DB
Private Sub SaveSelectedText(patient_id As Long, _
                             Timestamp As Date, _
                             Notes As String)

  Dim sNote As String

  On Error GoTo SaveSelectedTextZ
 
  Dim cn As ADODB.Connection
  Dim cmd As ADODB.Command
 
  Set cn = New ADODB.Connection
 
 cn.Open "Provider=SQLOLEDB;Data Source=VISTA-ON-MAC\sqlexpress;Initial Catalog=WriterDemo;Persist Security Info=True;User ID=sa;Password=momin;Connect Timeout=30"

  Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandTimeout = 60
    .CommandText = "INSERT INTO dbo.[t_notes]  [patient_id], [t_date], [note]) VALUES (?, ?, ?, ?)"
    .Parameters.Append cmd.CreateParameter("@notes_id", adNumeric, adParamInput, , notes_id)
    .Parameters("@Notes_id").Precision = 18
    .Parameters("@Notes_id").NumericScale = 0
    .Parameters.Append cmd.CreateParameter("@patient_id", adNumeric, adParamInput, , patient_id)
    .Parameters("@patient_id").Precision = 18
    .Parameters("@patient_id").NumericScale = 0
    .Parameters.Append cmd.CreateParameter("@Timestamp", adDBTimeStamp, adParamInput, , Format(Timestamp, "yyyy-MM-dd"))
    .Parameters.Append cmd.CreateParameter("@Notes", adVarWChar, adParamInput, 4000, Notes)
    .Execute
  End With

SaveSelectedTextX:
 
  Set cmd.ActiveConnection = Nothing
  Set cmd = Nothing

  If Not cn Is Nothing Then
    If cn.State = ADODB.adStateOpen Then
      cn.Close
    End If
    Set cn = Nothing
  End If

  Exit Sub

SaveSelectedTextZ:

  MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error during DB Operation"
  Resume SaveSelectedTextX
  Resume


End Sub
0
JoozefCommented:
You are missing a left bracket in the INSERT statement after the table name. It should look like this:

    .CommandText = "INSERT INTO dbo.[t_notes]  ([patient_id], [t_date], [note]) VALUES (?, ?, ?, ?)"
0
FaheemAhmadGulAuthor Commented:
I have amended the code as suggested but now I get the following error message:

Error during DB Operation - 2147217900: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the Values clause must match the number of columns specified in the INSERT statement.
0
JoozefCommented:
OK.  You have removed the notes_id column from the INSERT statement column list but but you did not remove the parameters that populate it.
Your CommandText property of the Command object should look like this (note removal of one "?")
.CommandText = "INSERT INTO dbo.[t_notes] ([patient_id], [t_date], [note]) VALUES (?, ?, ?)"
then you need to delete these lines:

    .Parameters.Append cmd.CreateParameter("@notes_id", adNumeric, adParamInput, , notes_id)
    .Parameters("@Notes_id").Precision = 18
    .Parameters("@Notes_id").NumericScale = 0
 
Joe
0
FaheemAhmadGulAuthor Commented:
Brilliant! It has worked perfectly. I am extremely grateful for your perseverance in helping me. Regards - Faheem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.