SQL Insert Into Code Not working (1 Viewer)

[CharlesWhiteman]

I'm using the following code on an conclick button event. Can anyone see why this code is not working please?

Private Sub CbSaveCall_Click()
Dim strSQL As String
CompanyCode = Me.txtCompanyCode
CallDate = Me.txtDate
Department = Me.txtDepartment
Person = Me.txtPerson
CallNote = Me.txtCallnote
Result = Me.txtCallResult

strSQL = "INSERT INTO TblCalls (CompanyCode, CallDate, Department, Person, CallNote, Result)"
strSQL = strSQL & " VALUES(txtCompanyCode, txtDate, txtDepartment, txtPerson, txtCallNote, txtCallResult)"

 

[Moniker]

You didn't post what is wrong. What result are you expecting versus what result are you getting?

 

[CharlesWhiteman]

The code is behind an onlick event of a command button on my form with text boxes which contain the data which i want the code to insert into the table.

I don't get any error messages but equally the data does not get inserted into the TblCalls.

 

[peej228]

SQl Insert

Your missing this: docmd.runsql strsql

But you should be using ADO with Parameters
Sql injection is possible when you use strings to pass your values to parameters. Also excaping characters in your string must be done if your going to use strings to pass your values

Ado tutorial
http://www.functionx.com/vbaccess/Lesson04d.htm

dim strSQL as string
dim cn as adodb.connection
Dim str_Connect As String
Dim strSQLServer As String
strSQLServer = "SQL Server 2000"
dim DatabaseServer as string
dim DatabaseUsername as string
dim DatabasePassword as string
DatabaseServer = "DbServer"
DatabaseUsername = "User"
DatabasePassword = "Passwd"
'Create Connection string
If strSQLServer = "SQL Server 2000" Then
str_Connect = "Driver={SQL Server};SERVER=" & DatabaseServer & ";" & _
"DATABASE=" & DatabaseName & ";UID=" & DatabaseUsername & ";PWD=" & DatabasePassword & ";OPTION=35;"
ElseIf strSQLServer = "SQL Server 2005" Then
str_Connect = "Driver={SQL Native Client};SERVER=" & DatabaseServer & ";" & _
"DATABASE=" & DatabaseName & ";UID=" & DatabaseUsername & ";PWD=" & DatabasePassword & ";OPTION=35;"
Else
str_Connect = Application.CurrentProject.Connection
End If
cn.ConnectionString = str_Connect
cn.Open 'Open the Connection

'Example query
strSQL = "UPDATE Orders SET Orders.Comment=? WHERE Orders.OrderID=?;"
'Pass the parameters in the order that the question marks are parsed

dim Param as adodb.parameter 'Parameter object for passing values



dim cmd as new adodb.command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdUnknown 'SQL Query not stored procedure (could be Select, Insert, Update, or Delete [Select statement will return a recordset] )
cmd.CommandText = strSQL
dim strValue as string
dim intValue as Long
strValue = "Comment" ' Value being passed to comments field
intValue = 1 'Order ID Parameter value being passed
'Get the Datatypes from your sql server table design (adChar, adInteger, etc.)
Set Param = cmd.CreateParameter(, adChar, adParamInput, 50, strValue)
cmd.Parameters.Append Param
set Param = nothing
Set Param = cmd.CreateParameter(, adInteger, adParamInput,, intValue)
cmd.Parameters.Append Param
set Param = nothing
cmd.Execute 'Runs the query

set cmd = nothing 'Clear the Memory of the command

cn.close 'Close the Connection
set cn = nothing 'Clear memory

 

[WayneRyan]

Charles,

You don't have to declare any VBA variables:

DoCmd.RunSQL "INSERT INTO TblCalls (CompanyCode, " & _    <-- Number
             "                      CallDate, " & _       <-- Date
             "                      Department, " & _     <-- Number
             "                      Person, " & _         <-- String
             "                      CallNote, " & _       <-- String
             "                      Result) " & _         <-- Number
             "VALUES(" & Me.txtCompanyCode & ", #" & _
                         Me.txtDate & "#, " & _
                         Me.txtDepartment & ", '" & _
                         Me.txtPerson & "','" & _
                         Me.txtCallNote & "', " & _
                         Me.txtCallResult & ")"

You could also:

DoCmd.RunSQL "INSERT INTO TblCalls (CompanyCode, " & _    <-- Number
             "                      CallDate, " & _       <-- Date
             "                      Department, " & _     <-- Number
             "                      Person, " & _         <-- String
             "                      CallNote, " & _       <-- String
             "                      Result) " & _         <-- Number
             "Select Forms!YourForm!txtCompanyCode, " & _
             "       Forms!YourForm!txtDate, " & _
             "       Forms!YourForm!txtDepartment, " & _
             "       Forms!YourForm!txtPerson, " & _
             "       Forms!YourForm!txtCallNote, " & _
             "       Forms!YourForm!txtCallResult"

I don't ever use the second version, but it does resolve some of the
punctuation issues. I think it's more useful when the data might
contain single-quotes (like CallNote might).

hth,
Wayne

 

[CharlesWhiteman]

Thanks for highlighting my silly mistake with the DoCmd. Tiredness! Also Ta for the advice. 4 months ago I didnt know what a listbox was so will have to read up on the advice before i can make use of it.