Re: Open Recordset & AddNew vs. RunSQL "INSERT INTO..."
From: rhovey (anonymous_at_discussions.microsoft.com)
Date: 03/08/04
- Next message: Les: "Post code in cobo box not showing all records"
- Previous message: Jonathan Blitz: "Reseting/selecting value in combo"
- In reply to: Ron Weiner: "Re: Open Recordset & AddNew vs. RunSQL "INSERT INTO...""
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 08:59:01 -0800
Thanks for all your help. I didn't get to work on it this
weekend, but I'll try to apply your suggestions today and
let you know if it helped.
>-----Original Message-----
>OK looks like you are attempting to insert a string
(strVol) which is not
>wrapped in quotes in the VALUES() sectionof the Insert
Into Sql statement.
>Here is the Code I used to make it work:
>
>***** Start Code *****
>Private Sub cmdWithLoop_Click()
> Dim StrVol As String, strSql As String
> Dim i As Integer, intPage As Integer
>
> intPage = 2
> StrVol = "Ron W"
>
> For i = 1 To 12
> strSql = "INSERT INTO tblProduction (wellid,
prodmonth, prodyear,
>vol, page, emplid, dicreated, dimodifiedc) " & _
> "VALUES (" & Me.wellID & ", " & i & ", "
& Me.EnterYear & ",
>'" & FixQuotes(StrVol) & "', " & intPage & ", " &
Me.emplID & _
> ", #" & Now() & "#, #" & Now() & "#)"
> DoCmd.SetWarnings False
> DoCmd.RunSQL strSql
> DoCmd.SetWarnings True
> Next i
>
>End Sub
>
>Private Function FixQuotes(strQuoted As String) As String
>' Purpose Double up single quotes for use in a Sql
Update query
> Dim i As Integer, strOut As String
>
> strOut = ""
> For i = 1 To Len(strQuoted)
> If Mid(strQuoted, i, 1) = Chr(39) Then
> strOut = strOut & Chr(39)
> End If
> strOut = strOut & Mid(strQuoted, i, 1)
> Next
> FixQuotes = strOut
>End Function
>***** End Code *****
>
>Note the Sql statement now has strVol wraped in in a
function FixQuotes()
>which is also wraped in Single quotes. Your problem was
in that you were
>attempting to insert as string that was not wraped in
quotes. The
>FixQuotes() function doubles up any single quotes it
finds in strVol which
>would create an error in the Sql statement.
>
>Since the Month is the only field that changes there is
even a faster way to
>do this query without having to use a For Loop, but it
requires another
>table (tblMonth) that has at least one field (MonthNum)
with 12 records
>(Int's) from 1 to 12. Here is a function that uses a
slightly different Sql
>syntax for Insert Into. If you really need the speed and
the month is the
>only changing variable loose the loop and go with the
extra table.
>
>***** Start Code *****
>Private Sub cmdNoLoop_Click()
> Dim StrVol As String, strSql As String
> Dim intPage As Integer
>
> intPage = 2
> StrVol = "Ron W"
>
> strSql = "INSERT INTO tblProduction ( WellID,
ProdMonth, ProdYear, Vol,
>Page, EmplID, DICreated, DIModifiedc ) " _
> & "SELECT " & Me.wellID & ", MonthNum, " &
Me.EnterYear & ", '" &
>FixQuotes(StrVol) & "', " & intPage & ", " & Me.emplID & _
> ", #" & Now() & "#, #" & Now() & "# " _
> & "FROM tblMonth;"
> DoCmd.SetWarnings False
> DoCmd.RunSQL strSql
> DoCmd.SetWarnings True
>End Sub
>***** End Code *****
>
>Ron W
>
>"rhovey" <anonymous@discussions.microsoft.com> wrote in
message
>news:7bef01c402fc$b2f632f0$a301280a@phx.gbl...
>>
>> Here is the DDL for the table:
>>
>> CREATE TABLE [dbo].[tblProduction] (
>> [ProdID] [int] IDENTITY (1, 1) NOT NULL ,
>> [WellID] [int] NULL ,
>> [ProdMonth] [int] NULL ,
>> [ProdYear] [int] NULL ,
>> [Allowable] [int] NULL ,
>> [GasProd] [int] NULL ,
>> [OilProd] [int] NULL ,
>> [Vol] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Page] [int] NULL ,
>> [EmplID] [int] NULL ,
>> [DICreated] [datetime] NULL ,
>> [DIModifiedc] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>>
>> ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
>> CONSTRAINT [DF__Temporary__WellI__1DE57479]
>> DEFAULT (0) FOR [WellID],
>> CONSTRAINT [DF__TemporaryU__Page__1ED998B2]
>> DEFAULT (0) FOR [Page],
>> CONSTRAINT [DF__Temporary__EmplI__1FCDBCEB]
>> DEFAULT (0) FOR [EmplID],
>> CONSTRAINT [aaaaatblProduction_PK] PRIMARY KEY
>> NONCLUSTERED
>> (
>> [ProdID]
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [ProdID] ON [dbo].[tblProduction]
([ProdID])
>> ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [UserID] ON [dbo].[tblProduction]
([EmplID])
>> ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [WellID] ON [dbo].[tblProduction]
([WellID])
>> ON [PRIMARY]
>> GO
>>
>>
>>
>>
>> ***
>>
>> Here is the actual code (I was using a trimmed down
>> version for my original post)
>>
>>
>> Slow, but "GOOD":
>>
>> rst.Open "tblProduction", con, adOpenKeyset,
>> adLockOptimistic, adCmdTable
>> For i = 1 To 12
>> rst.AddNew
>> rst![WellID] = Me.WellID
>> rst![ProdMonth] = i
>> rst![ProdYear] = Me.EnterYear
>> rst![Vol] = strvol
>> rst![Page] = intpage
>> rst![EmplID] = Me.EmplID
>> rst![DICreated] = Now()
>> rst![DIModifiedc] = Now()
>> rst.Update
>> Next i
>> rst.Close
>>
>>
>>
>> This is the fast, but "BAD" (freezes all machines):
>>
>> For i = 1 To 12
>> strSQL = "INSERT INTO tblProduction (wellid,
>> prodmonth, prodyear, vol, page, emplid, dicreated,
>> dimodifiedc) " & _
>> "VALUES (" & Me.WellID & ", " & i
>> & ", " & Me.EnterYear & ", " & strVol & ", " & intPage
>> & ", " & Me.EmplID & _
>> ", #" & Now() & "#, #" & Now()
& "#)"
>> DoCmd.RunSQL strSQL
>> Next i
>>
>>
>>
>> Again, thanks for taking a look at this. I'm totally
>> stumped!
>>
>>
>> >-----Original Message-----
>> >Yikes!
>> >
>> >Based on the Sql statement I am assuming that all of
the
>> fields "Val1",
>> >"prodMonth", and "prodYear" are numeric. If this is
the
>> case then I can not
>> >see any reason for this query to fail. If you post the
>> DDL to for the Sql
>> >Table tblProduction I will try to reproduce that
problem
>> here.
>> >
>> >Ron W
>> >
>> >"rhovey" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:7b2601c402ed$3aee3190$a301280a@phx.gbl...
>> >> Sorry - just wrote the example wrong. The field names
>> are
>> >> actually "prodMonth" and "prodYear".
>> >> >-----Original Message-----
>> >> >Year and Month are Reserved words in SQL (they are
>> >> Functions that take an
>> >> >argument) . Try
>> >> >
>> >> >strSQL = "INSERT INTO tblProduction (val1, [month],
>> >> [year]) VALUES (" &
>> >> >Me.Val1 & ", " & i & ", " & Me.EnterYear)"
>> >> >
>> >> >Ron W
>> >> >
>> >> >"rhovey" <anonymous@discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:4f3f01c402db$7c3ea470$a601280a@phx.gbl...
>> >> >> I have an Access2000 front end on 50 computers
linked
>> >> to a
>> >> >> SQLServer2000 backend. I need to insert monthly
>> records
>> >> >> for the chosen year when the related parent
record is
>> >> >> selected. I first used the open recordset &
addnew
>> >> method
>> >> >> to insert the records as below:
>> >> >>
>> >> >> rst2.Open strSQL2, con, adOpenKeyset,
>> adLockOptimistic,
>> >> >> adCmdText
>> >> >> For i = 1 To 12
>> >> >> rst2.AddNew
>> >> >> rst2![val1] = Me.val1
>> >> >> rst2![month] = i
>> >> >> rst2![Year] = Me.EnterYear
>> >> >> rst2.Update
>> >> >> Next i
>> >> >> End If
>> >> >> rst2.Close
>> >> >>
>> >> >> This is obviously very slow when 50 people are
>> entering
>> >> >> data. However, it didn't cause the SQL server to
>> >> freeze.
>> >> >> So to improve performance I switched to the
>> following:
>> >> >>
>> >> >> For i = 1 To 12
>> >> >> strSQL = "INSERT INTO tblProduction (val1,
month,
>> >> year)
>> >> >> VALUES (" & Me.Val1 & ", " & i & ", " &
>> >> Me.EnterYear)"
>> >> >> DoCmd.RunSQL strSQL
>> >> >> Next i
>> >> >>
>> >> >>
>> >> >> This is definitely faster (GOOD) but causing the
SQL
>> >> >> Server to lock up and all the users are frozen
>> (BAD!).
>> >> >> Any suggestions as to why this problem is
occurring
>> and
>> >> if
>> >> >> there is a better way to handle this.
>> >> >>
>> >> >> Thanks!
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>
- Next message: Les: "Post code in cobo box not showing all records"
- Previous message: Jonathan Blitz: "Reseting/selecting value in combo"
- In reply to: Ron Weiner: "Re: Open Recordset & AddNew vs. RunSQL "INSERT INTO...""
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
