Hi All,
I am using VB6 to access a database supplied to us by the copmany supplying our POS system. One of the tables holds details of the till keyboard layouts. I would like to be able to copy selected records from one shops layout to another.
The problem I have is that one of the fields contains the text as it is displayed on the keyboard layout ans as such contains control characters to space the text correctly spread over three lines.
Is there any way to copy the offending field and insert it into the new record. below is the code I have at the moment. The offending field is rsSPSK!SheetName

'Delete existing records for the "To" shop
   sqlDelete = "Delete from SPSKeyboard where LocationNo = " & sglToShp & ""
   rsDel.Open sqlDelete, cnn1, , , adCmdText
  
'Read through the SPSKeyboard Table
   sqlSPSK = "Select* from SPSKeyboard where LocationNo = " & sglFrmShp & " "
   rsSPSK.Open sqlSPSK, cnn1, , , adCmdText

'get the location name and till type
   rsSPSK.MoveFirst
 


Do While Not rsSPSK.EOF

sqlUpdate = "Insert into SPSKeyboard (LocationNo,Level,KeyNo,KeyType,Function,Colour,TextColour,SheetName,Font) VALUES (" & sglToShp & ", " & rsSPSK!Level & ", " & rsSPSK!KeyNo & ", " & rsSPSK!KeyType & ", " & "'" & rsSPSK!Function & "'" & ", " & rsSPSK!Colour & ", " & rsSPSK!TextColour & ", " & "'" & rsSPSK!SheetName & "'" & ", " & rsSPSK!Font & ")"
rsSPSKrs.Open sqlUpdate, cnn1, , , adCmdText

rsSPSK.MoveNext

Loop

Appreciate your thoughts
Malcolm

Malcom

I'm not right sure if I have correctly understand your problem. The
SheetName field includes CR/LF and such other characters? In Delphi we have
a function QuotedStr wich does double quote any quotes and should work in
this case. I don't know if VB6 has someting similar. Another solution may be
to use parameters instead a constructed string. Again, I don't know how you
can use parameters in VB6, but I think you shlould know it yourself.

Regards
Rolf


"Malcolm Fowler" schrieb im Newsbeitrag
news:46CE4C2C-C708-42C5-9232-33EB68AED065@news.elevatesoft.com...

Hi All,
I am using VB6 to access a database supplied to us by the copmany supplying
our POS system. One of the tables holds details of the till keyboard
layouts. I would like to be able to copy selected records from one shops
layout to another.
The problem I have is that one of the fields contains the text as it is
displayed on the keyboard layout ans as such contains control characters to
space the text correctly spread over three lines.
Is there any way to copy the offending field and insert it into the new
record. below is the code I have at the moment. The offending field is
rsSPSK!SheetName

'Delete existing records for the "To" shop
   sqlDelete = "Delete from SPSKeyboard where LocationNo = " & sglToShp &
""
   rsDel.Open sqlDelete, cnn1, , , adCmdText

'Read through the SPSKeyboard Table
   sqlSPSK = "Select* from SPSKeyboard where LocationNo = " & sglFrmShp & "
"
   rsSPSK.Open sqlSPSK, cnn1, , , adCmdText

'get the location name and till type
   rsSPSK.MoveFirst



Do While Not rsSPSK.EOF

sqlUpdate = "Insert into SPSKeyboard
(LocationNo,Level,KeyNo,KeyType,Function,Colour,TextColour,SheetName,Font)
VALUES (" & sglToShp & ", " & rsSPSK!Level & ", " & rsSPSK!KeyNo & ", " &
rsSPSK!KeyType & ", " & "'" & rsSPSK!Function & "'" & ", " & rsSPSK!Colour &
", " & rsSPSK!TextColour & ", " & "'" & rsSPSK!SheetName & "'" & ", " &
rsSPSK!Font & ")"
rsSPSKrs.Open sqlUpdate, cnn1, , , adCmdText

rsSPSK.MoveNext

Loop

Appreciate your thoughts
Malcolm

Hi Rolf,
Many thanks for your response, You understand the problem correctly.
I am a self taught VB6 "programmer" and as such do not know all the finer elements, I will investigate.

If anyone else can enlighten it will be appreciated.

Regards
Malcolm

Malcolm,

<< I am using VB6 to access a database supplied to us by the copmany
supplying our POS system. One of the tables holds details of the till
keyboard layouts. I would like to be able to copy selected records from one
shops layout to another. The problem I have is that one of the fields
contains the text as it is displayed on the keyboard layout ans as such
contains control characters to space the text correctly spread over three
lines. Is there any way to copy the offending field and insert it into the
new record. below is the code I have at the moment. The offending field is
rsSPSK!SheetName  >>

What you should do is two-step the process:

1) DELETE FROM on the "To" side to delete all matching records:

DELETE FROM SPSKeyboard WHERE LocationNo = 'xxx'

2) INSERT INTO the "To" from the "From" side:

INSERT INTO SPSKeyboard
(LocationNo,Level,KeyNo,KeyType,Function,Colour,TextColour,SheetName,Font)
SELECT
(LocationNo,Level,KeyNo,KeyType,Function,Colour,TextColour,SheetName,Font)
FROM SPSKeyboard WHERE LocationNo = 'xxx'

That's it, and no looping, etc. needed.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I see what you are saying, just need to know how to change the locationno. otherwise I will be trying to write duplicate records, ie
if the records i'm selecting reads locationno of 21 but the records i'm writing needs the locationno to be say 52

Regards
Malcolm

Malcolm Fowler wrote:

Hi Tim,

I see what you are saying, just need to know how to change the locationno. otherwise I will be trying to write duplicate records, ie
if the records i'm selecting reads locationno of 21 but the records i'm writing needs the locationno to be say 52

Regards
Malcolm
Hi Tim,
forgive me for my ignorance. I have written the sql as suggested into dbsys :-

delete from spskeyboard where locationno = 1;
insert into spskeyboard (locationno, level, keyno, keytype, function,colour, textcolour, sheetname, font)
select (locationno,level,keyno,keytype,function,colour,textcolour,sheetname,font)
from spskeyboard where locationno = 3

But receive an error saying expected ')' after locationno in the select line

Regards
Malcolm

Malcolm


Do a second SQL statement

UPDATE SPSKeyboard SET locationno = 52 WHERE locationno = 21

Roy Lambert [Team Elevate]

Hi All,

Many thanks for all your help, the following works for me as a straight sql, I can change the locationno's as needed.

delete from spskeyboard where locationno = 1;
insert into spskeyboard (level, keyno, keytype, function,colour, textcolour, sheetname, font)
select level,keyno,keytype,function,colour,textcolour,sheetname,font
from spskeyboard where locationno = 3;

update spskeyboard set locationno = 1 where locationno = null

Unless someone has a better way.

Havent worked out how toput this into VB yet but at least I can do what I needed.

Thank-you
Malcolm

Malcolm

> delete from spskeyboard where locationno = 1;
> insert into spskeyboard (level, keyno, keytype, function,colour, textcolour, sheetname, font)
> select level,keyno,keytype,function,colour,textcolour,sheetname,font
> from spskeyboard where locationno = 3;
>
> update spskeyboard set locationno = 1 where locationno = null
>
> Unless someone has a better way.

You eliminate the update with

insert into spskeyboard (locationno,level, keyno, keytype, function,colour, textcolour, sheetname, font)
select 1,level,keyno,keytype,function,colour,textcolour,sheetname,font
from spskeyboard where locationno = 3;

John