Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 14th, 2004, 12:54 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default insert value of variable into an existing table

Here is the code. When I ran it, the error message is INSERT INTO statement is wrong. Please help me out.

Thanks.
Kyna

Private Sub RecordSetQuery()
  Const strQueryName = "test_betty_james_inv_chk"
  Dim db As Database
  Dim rs As Recordset
  Dim strSql As String
  Dim d As Integer

  Set db = CurrentDb() ' Open pointer to current database
  Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query
  strSql = "delete * from table1;"
  db.Execute (strSql)

  Do While Not rs.EOF
    d = rs![betty_inv_daily]
    strSql = "INSERT INTO table1 VALUES d;"
    rs.MoveNext
  Loop
  rs.Close
  db.Close
End Sub

 
Old October 14th, 2004, 01:04 AM
Friend of Wrox
 
Join Date: Jun 2004
Location: Chennai, Tamil Nadu, India.
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

you have mentioned d inside the query string. Which means it will take it as d and not as its value.
 you should use something like this
     strSql = "Isert into table1 (betty_inv_daily) values (" & CStr(d) + ")"

I am not very sure of the VB syntax..quite sometime since used VB (CStr is convert to string if i am not wrong).

Regards
Ganesh
 
Old October 14th, 2004, 08:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

strSQL = "INSERT INTO table1 values (" & cstr(d) & ")"

If d is not the only database field, you need to add the field parameters (insert into table1 (d) values (d))

Brian
 
Old October 14th, 2004, 11:12 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, I tried, but still didn't work. The table "test_betty_james_inv_chk" is look like this. The field (Betty_Inv_daily) is a number, how many invoices process by betty per day.

If I change d to a constant number, it works; but if I make d = rs![betty_inv_daily], it not works. How to pass the SQL statement a parameter?

ID Work_Date Week_Date Betty_Inv_Daily James_Inv_Daily Betty_Chk_Daily James_Chk_Daily
1 1/1/2004 4 24 0 0 0
2 1/2/2004 5 34 113 0 0
3 1/5/2004 1 0 27 0 4
4 1/6/2004 2 76 63 0 0
5 1/7/2004 3 86 13 6 0
6 1/8/2004 4 44 90 3 2

 
Old October 14th, 2004, 11:21 PM
Friend of Wrox
 
Join Date: Jun 2004
Location: Chennai, Tamil Nadu, India.
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

how did u frame your sql statemtnt??

     strSql = "Insert into table1 (betty_inv_daily) values (" & CStr(d) + ")"


Regards
Ganesh
 
Old October 16th, 2004, 12:48 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Check what is that comes in rs![betty_inv_daily] within the while loop everytime you move to next record.

_________________________
- Vijay G
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to insert a column values in existing db (msac gilgalbiblewheel Classic ASP Databases 0 April 6th, 2005 06:53 PM
Creating table with existing table without value kumar_kumar Oracle 1 January 4th, 2005 07:12 AM
Insert new Record to MIDDLE of existing recordset? PanzarPaw Classic ASP Databases 5 September 11th, 2004 07:50 AM
Want to insert a new record when an existing recor lionofthejungle Access 1 September 9th, 2004 11:55 AM
insert new row and merge with existing merged cell funkyProgrammer Excel VBA 1 February 9th, 2004 10:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.