• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Sql - one table to another

Hi,

I am currently connecting to a Sql Server, using Vb6.
What i am currently doing, is connecting and writing information from a paradox database on my computer to an sql database on a server.
What i would like to do, is write information back to the paradox table, but only certain fields of a particular record.
My problem is, that the fields on my paradox database have spaces between them, and this is causing a problem, eg: Batch Number, but the field on the sql table is ok, saying BatchNo...

This is the sql string i am currently using.

WriteBatch = "INSERT INTO Batch_de" _
 & " (Batch Number,Action Date,Receive Date,Debit Total,Credit Total,Entries,User Code,Total Entries,Errors,Release) VALUES " _
& "('" & WBBatchNo & "','" & WBActionDate & "','" & WBCreationDate & "','" & WBDebitTotal & "','" & WBCreditTotal & "'" _
& ",'" & WBNoOfTrans & "','" & WBUserCode & "','" & 0 & "','" & N & "','" & X & "');"
               
SQLDB.Execute WriteBatch

Basically i think the problem is the spaces between the fields?

Thanks!
0
mscala
Asked:
mscala
  • 2
  • 2
1 Solution
 
danlevansCommented:
Try:

WriteBatch = "INSERT INTO Batch_de" _
 & " ([Batch Number],Action Date,Receive Date,Debit Total,Credit Total,Entries,User Code,Total Entries,Errors,Release) VALUES " _
& "('" & WBBatchNo & "','" & WBActionDate & "','" & WBCreationDate & "','" & WBDebitTotal & "','" & WBCreditTotal & "'" _
& ",'" & WBNoOfTrans & "','" & WBUserCode & "','" & 0 & "','" & N & "','" & X & "');"
               
SQLDB.Execute WriteBatch

Dan
0
 
twardCommented:
Try putting [] (Brackets around the fieldnames with spaces just like you have the first one [Batch Number].
0
 
twardCommented:
Opps, didn't realize that danlevans had supplied the answer as a comment, for some reason as I scrolled down I thought it was still your question!

In any event danlevans is correct, except you would need to but brackets around all the others as well (that have spaces).
0
 
danlevansCommented:
Try:

WriteBatch = "INSERT INTO Batch_de" _
 & " ([Batch Number],Action Date,Receive Date,Debit Total,Credit Total,Entries,User Code,Total Entries,Errors,Release) VALUES " _
& "('" & WBBatchNo & "','" & WBActionDate & "','" & WBCreationDate & "','" & WBDebitTotal & "','" & WBCreditTotal & "'" _
& ",'" & WBNoOfTrans & "','" & WBUserCode & "','" & 0 & "','" & N & "','" & X & "');"
               
SQLDB.Execute WriteBatch

Dan
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now