There is a form in an Access Project (.adp, Access front end with SQL
Server) for entering data into a table for temporary storing. Then, by
clicking a botton, several action stored procedures (update, append) should
be activated in order to transfer data to other tables.
I tried to avoid any coding in VB, as I am not a professional, but I have
found a statement in an article, that, unlike select queries, form's Input
Property can't be used for action queries. Therefore, parameters can be
passed to action stored procedure only by using ADO through VB.
As I'm not very familiar with VB, I had to search in literature.
So, this is a solution based on creating Parameter object in ADO and then
appending values to Parameter collection.
Please, consider the following procedure I created for passing parameters
from form's control objects (Text boxes) to a stored procedure
DTKB_MB_UPDATE:
Private Sub Command73_Click()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "DTKB_MB_UPDATE"
cmd.CommandType = adCmdStoredProc
Dim par As ADODB.Parameter
Set par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,
50)
cmd.Parameters.Append par
cmd.Parameters("@DATE") = Me.DATE
cmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBER
cmd.Parameters("@STATUS") = Me.STATUS
cmd.Parameters("@DEPARTMENT") = Me.DEPARTMENT
cmd.Parameters("@PRODUCTION") = Me.PRODUCTION
cmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPE
cmd.Execute
Set cmd = Nothing
End Sub
Unfortunately, when clicking on the botton, the following error apears:
"Run-time error'-2147217913 (80040e07)':Syntax error converting datetime
from character string."
Obviously, there is some problem regarding parameter @DATE. In SQL Server it
is datetime, on the form's onbound text box it is short date (dd.mm.yyyy)
data type. I have found in literature that in ADO it should be
adDBTimeStamp.
So, what is the problem ?
Greetings,
Zlatko 2 17331
Hi
I replied in microsoft.public.sqlserver.programming, please don't post
separately to multiple groups.
John
"zlatko" <zl***********@sb.htnet.hr> wrote in message
news:cn**********@ls219.htnet.hr... There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures (update, append) should be activated in order to transfer data to other tables.
I tried to avoid any coding in VB, as I am not a professional, but I have found a statement in an article, that, unlike select queries, form's Input Property can't be used for action queries. Therefore, parameters can be passed to action stored procedure only by using ADO through VB.
As I'm not very familiar with VB, I had to search in literature.
So, this is a solution based on creating Parameter object in ADO and then appending values to Parameter collection.
Please, consider the following procedure I created for passing parameters from form's control objects (Text boxes) to a stored procedure DTKB_MB_UPDATE:
Private Sub Command73_Click() Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "DTKB_MB_UPDATE"
cmd.CommandType = adCmdStoredProc Dim par As ADODB.Parameter Set par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput, 50)
cmd.Parameters.Append par cmd.Parameters("@DATE") = Me.DATE
cmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBER
cmd.Parameters("@STATUS") = Me.STATUS
cmd.Parameters("@DEPARTMENT") = Me.DEPARTMENT
cmd.Parameters("@PRODUCTION") = Me.PRODUCTION
cmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPE cmd.Execute Set cmd = Nothing End Sub
Unfortunately, when clicking on the botton, the following error apears: "Run-time error'-2147217913 (80040e07)':Syntax error converting datetime from character string." Obviously, there is some problem regarding parameter @DATE. In SQL Server it is datetime, on the form's onbound text box it is short date (dd.mm.yyyy) data type. I have found in literature that in ADO it should be adDBTimeStamp.
So, what is the problem ? Greetings, Zlatko
zlatko (zl***********@sb.htnet.hr) writes: There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures (update, append) should be activated in order to transfer data to other tables.
I tried to avoid any coding in VB, as I am not a professional, but I have found a statement in an article, that, unlike select queries, form's Input Property can't be used for action queries. Therefore, parameters can be passed to action stored procedure only by using ADO through VB.
As I'm not very familiar with VB, I had to search in literature. ... Set par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput) ... cmd.Parameters("@DATE") = Me.DATE Unfortunately, when clicking on the botton, the following error apears:
"Run-time error'-2147217913 (80040e07)':Syntax error converting datetime from character string."
Obviously, there is some problem regarding parameter @DATE. In SQL Server it is datetime, on the form's onbound text box it is short date (dd.mm.yyyy) data type. I have found in literature that in ADO it should be adDBTimeStamp.
This is more likely to be a Visual Basic or Access problem than an
SQL Server problem. The code looks good to me, but I don't really know
what's in Me.DATE.
There are things to check:
o Verify that the error actually is on the line where you assign to
cmd.Parameters("@DATE").
o Check that the regional settings of your computer really is one
which uses dd.mm.yyyy as date format, and you are not accidently not
running with, for instance, US English settings.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Zlatko |
last post by:
A question concerning Access Project with SQL Server:
I use a stored procedure that is calling several other stored procedure
which update or append values in several tables. All of them are...
|
by: zlatko |
last post by:
There is a form in an Access Project (.adp, Access front end with SQL
Server) for entering data into a table for temporary storing. Then, by
clicking a botton, several action stored procedures...
|
by: Mark |
last post by:
I created a test to check the execution time difference between executing a
SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code,...
|
by: Brew |
last post by:
Hello,
I'm writing an ASP.NET 2005 (VB) Website with an Oracle 9i backend
I have an oracle package with stored procedures and functions I need to
use from the
VB Code, I'm trying to use the...
|
by: ZRexRider |
last post by:
Hi,
I've written reports based on stored procedures that receive parameters
and it usually seems straight forward.
Today I would like to use Crystal Reports parameter interface t prompt
for a...
|
by: alexs |
last post by:
Hi,
I've been using the db2 developer workbench to produce some java stored
procedures.
The ones I'm working on now use some dom4j objects internally, so i
need to ensure that the dom4j jar...
|
by: Fir5tSight |
last post by:
Hi,
I'll need to replace an existing stored procedure (let's call it
old_SP), with a new one (let's call it new_SP) in a Visual Studio .NET
project.
Here's what I did:
1) Open...
|
by: nidaar |
last post by:
From a security point of view, is accepting wildcards like "%" in
input parameters of stored procedures against any best practices?
As an example, if a user defined function uses "Productname...
|
by: leesquare |
last post by:
Hello,
I need some help getting output values from my stored procedures when
using adodbapi. There's an example
testVariableReturningStoredProcedure in adodbapitest.py, and that
works for my...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| | |