Need help with an Unbound Form that executes a storedprocedure to add records

  • Thread starter Frank Verschoor
  • Start date
F

Frank Verschoor

I am working on a new project database using Access and SQL Server. The
program is designed for the Theatre Business. For 2 years we worked with an
Access frontend and backend, but the time has come to consider working with
SQL server.

Everything seems to be working accept one form. It is an unbound form in
which is used for entering client data. After all the nessary data has been
entered to make a reservation a button appears. On pushing the button a
stored procedure is executed via VB using the following RunSQL statement:

DoCmd.RunSQL "EXECUTE Klant_toevoegen " & "'" & Me.Titel & "'" & "," & "'" &
Me.Voorletters & "'" & "," & "'" & Me.Tussenvoegsels & "'" & "," & "'" &
Me.Achternaam & " '" & "," & "'" & Me.Adres2 & "'" & "," & "'" & Me.Postcode
& "'" & "," & "'" & Me.Plaats & "'" & "," & "'" & Me.Telefoon & "'" & "," &
"'" & Me.Mobiel & "'" & "," & "'" & Me.Bedrijf & "'" & "," & "'" & Me.Email
& "'"

The stored procedure on the server looks like this:

INSERT INTO dbo.Klantenbestand
(Titel, Voorletters, Tussenvoegsels, Naam, Adres,
Postcode, Plaats, Telefoon, Mobiel, [Naam Bedrijf], Email)
VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7,
@Param8, @Param9, @Param11, @Param10)

Everything seems to be working fine until I enter an email adress. I get an
error message. I think the cause is the @ sign. Does anybody know how to
handle this ? Could somebody please help me ?


Kind Regards,

Frank Verschoor
 
S

Sylvain Lafontaine

As you are using the old ODBC command, you should try using the old ODBC
canonical format:

DoCmd.RunSQL "{ call dbo.Klant_toevoegen ('aaa', .... , 'bb@bb') }"

Of course, the real solution would be to forget about DoCmd.RunSQL and use
the ADO objects.
 
A

Andrew Backer

Yeap, something like this (but I wrap this all in helper functions...)

dim cmd as ADODB.Command
set cmd = new ADODB.Command

'-- config the command
cmd.CommandText = "dbo.Klant_toevoegen"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = CurrentProject.Connection

cmd.parameters.add ( cmd.CreateParameter("@param1", ..info.., value1) )
cmd.parameters.add ( cmd.CreateParameter("@param2", ..info.., value2) )
cmd.parameters.add ( cmd.CreateParameter("@param3", ..info.., value3) )
....
You can leave out most parmeters to those calls, but you might want to
put in the info about he data type (and length, if it's a string) just
to be safe :

....CreateParameter("@name", adVarWChar, , 200, me.Title )

cmd.Execute

Notes
1) I would highly recommend giving your SP parameters meaningful names.
2) Datatype for a NVARCHAR in vb is vbVarWChar

HTH,
Andrew Backer
 
F

Frank Verschoor

Thanx it seems to be working now.

The names of the stored procedures are in dutch and represent actions in the
database !
 
T

Teodora Molova

ÁÎËÛÅÐÌ ÇÖÉ kjeav bytsd @@# 976542.khfdn.l.koi
Frank Verschoor said:
Thanx it seems to be working now.

The names of the stored procedures are in dutch and represent actions in
the database !

Frank Verschoor said:
I am working on a new project database using Access and SQL Server. The
program is designed for the Theatre Business. For 2 years we worked with
an Access frontend and backend, but the time has come to consider working
with SQL server.

Everything seems to be working accept one form. It is an unbound form in
which is used for entering client data. After all the nessary data has
been entered to make a reservation a button appears. On pushing the
button a stored procedure is executed via VB using the following RunSQL
statement:

DoCmd.RunSQL "EXECUTE Klant_toevoegen " & "'" & Me.Titel & "'" & "," &
"'" & Me.Voorletters & "'" & "," & "'" & Me.Tussenvoegsels & "'" & "," &
"'" & Me.Achternaam & " '" & "," & "'" & Me.Adres2 & "'" & "," & "'" &
Me.Postcode & "'" & "," & "'" & Me.Plaats & "'" & "," & "'" & Me.Telefoon
& "'" & "," & "'" & Me.Mobiel & "'" & "," & "'" & Me.Bedrijf & "'" & ","
& "'" & Me.Email & "'"

The stored procedure on the server looks like this:

INSERT INTO dbo.Klantenbestand
(Titel, Voorletters, Tussenvoegsels, Naam, Adres,
Postcode, Plaats, Telefoon, Mobiel, [Naam Bedrijf], Email)
VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6,
@Param7, @Param8, @Param9, @Param11, @Param10)

Everything seems to be working fine until I enter an email adress. I get
an error message. I think the cause is the @ sign. Does anybody know how
to handle this ? Could somebody please help me ?


Kind Regards,

Frank Verschoor
Click to expand...
Click to expand...
 
Top