Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
codehappy
Starting Member
1 Post |
 Posted - 2005-04-15 : 12:55:57
|
| I am getting the Syntax Error or Access Violation when I attempt to run my program. I have an idea as to what the problem is but can't figure out how to fix it. I am using the command object to execute a stored procedure that has 4 input parameters and 2 output parameters. Here is my code: VB:--------------------------------------------------------------------------------Call CrossingFiles 'Calls function Application.ActiveWorkbook.Save Application.DisplayAlerts = False Application.ActiveWorkbook.Close Set MyXL = Nothing Unload OpenFile ErrorHandler: ' Error-handling routine. Dim StrErr As String StrErr = Err.Number & " - " & Err.Description If Err = 364 Then Exit Sub End If MsgBox (StrErr), vbOKOnly, Error End Sub Sub CrossingFiles() Dim wsData As Worksheet Dim fund As String Dim trans_type As String Dim security_id As String Dim shares As String Dim strRangeA As String Dim strRangeB As String Dim strRangeC As String Dim strRangeE As String Dim prmfund As ADODB.Parameter Dim prmtrans As ADODB.Parameter Dim prmsec As ADODB.Parameter Dim prmshare As ADODB.Parameter Dim prmFlg As ADODB.Parameter Dim prmErr As ADODB.Parameter Dim strSQL As String Dim strDesc As String Dim cmd As Command Dim i As Integer Dim adoConn As ADODB.Connection Dim rst As New ADODB.Recordset Dim txtLog As String Dim flag As String Dim desc As String Set wsData = ActiveSheet Set adoConn = New ADODB.Connection Set rst = New ADODB.Recordset Set cmd = New ADODB.Command i = 1 txtLog = " " flag = " " desc = " " wsData.Rows(1).Delete adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main" adoConn.Open 'This will go through the worksheet, row by row, and send the necessary data to the DB Dim MyColumns_Range As Range Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown)) For Each c In MyColumns_Range strRangeA = "A" & i strRangeB = "B" & i strRangeC = "C" & i strRangeE = "E" & i fund = Range(strRangeA).Value trans_type = Range(strRangeB).Value security_id = Range(strRangeC).Value shares = Range(strRangeE).Value Set cmd.ActiveConnection = adoConn cmd.CommandType = adCmdStoredProc cmd.CommandText = "Execute stored_proc '" & fund & "', '" & trans_type & "', '" & security_id & "', '" & shares & "', " 'Declare the output parameters Set prmFlg = cmd.CreateParameter("flg", adVarChar, adParamOutput, 1, "") cmd.Parameters.Append prmFlg Set prmErr = cmd.CreateParameter("Err", adVarChar, adParamOutput, 255, "") cmd.Parameters.Append prmErr 'adoConn.Execute strSQL, , adCmdText 'rst.Open strSQL, adoConn, adOpenStatic, adLockReadOnly Set rst = cmd.Execute 'strDesc = rst.Fields("flag") & " " & rst.Fields("desc") strDesc = prmFlg.Value + " " + prmErr.Value txtLog = txtLog + strDesc 'rst.Close i = i + 1 fund = " " trans_type = " " security_id = " " shares = " " strDesc = " " cmd.CommandText = " " Set prmFlg = Nothing Set prmErr = Nothing Next c Set wsData = Nothing adoConn.Close 'saves any changes made to the workbook and turns off the prompts ('are you sure') Application.DisplayAlerts = False ActiveWorkbook.Save Application.DisplayAlerts = True End Sub--------------------------------------------------------------------------------I have a watch set for the cmd.CommandText to see what it is set for (before I get the error). This is what it is set for: "{ call Execute stored_proc '520150472', 'BUY', '36144810', '700', (?, ?) }" Note the (?,?). Why are there parantheses and why are there question marks? Even when I type this into SQL I get the same error. Why? What can I do to fix it? |
|
|
|
|
|
|
|
VB Error: Syntax Error or Access Violation