July 21, 2008 at 12:36 pm
Hi All,
I found the SQL code below that allows me to add multiple records with one statement (in this case the 7 days of the week into a table). What I need to figure out is how to do this in vb.net with an OleDbCommand. I really don't want to loop through 7 insert statements if I don't have to do it that way. I also cannot use a stored procedure because the application will not have access to it. These records will be inserted into an empty access table. Does anybody know how I can accomplish this without looping the inserts for each record?
Thanks!
SQL CODE:
INSERT INTO Week (DayID, DayOfWeek)
SELECT 1, 'Saturday' UNION ALL
SELECT 2, 'Sunday' UNION ALL
SELECT 3, 'Monday' UNION ALL
SELECT 4, 'Tuesday' UNION ALL
SELECT 5, 'Wednesday' UNION ALL
SELECT 6, 'Thursday' UNION ALL
SELECT 7, 'Friday'
VB CODE (causes error):
strSQL = "INSERT INTO Week (DayID, DayOfWeek) SELECT 1, 'Saturday' UNION ALL SELECT 2, 'Sunday' UNION ALL SELECT 3, 'Monday' UNION ALL SELECT 4, 'Tuesday' UNION ALL SELECT 5, 'Wednesday' UNION ALL SELECT 6, 'Thursday' UNION ALL SELECT 7, 'Friday'"
objConn.Open()
Dim objCmd As New OleDbCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
objConn.Close()
July 21, 2008 at 3:57 pm
What error do you get?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 6:27 am
There error is:
Syntax error (missing operator) in query expression "Saturday' UNION ALL SELECT 2'.
I did some more reseach and found some posts that said Access does not support multiple records being inserted with one statement while SQL Server does.
For now, I have written the inerts in a loop like this to make it easier:
MDBInsert("DayID", "6", "DayOfWeek", "Thursday", strTable, objConn)
MDBInsert("DayID", "7", "DayOfWeek", "Friday", strTable, objConn)
etc...
Public Sub MDBInsert(ByVal strFieldAName As String, ByVal strFieldA As String, ByVal strFieldBName As String, ByVal strFieldB As String, ByVal strTable As String, ByVal objConn As OleDbConnection)
Try
strSQL = "INSERT INTO [" & strTable & "] (" & strFieldAName & ", " & strFieldBName & ") VALUES (" & CInt(strFieldA) & ", '" & strFieldB & "')"
objConn.Open()
Dim objCmd As New OleDbCommand(strSQL, objConn)
objCmd.ExecuteNonQuery()
objConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & vbCrLf & "The import into the database failed!")
Finally
If objConn.State <> ConnectionState.Closed Then
objConn.Close()
End If
End Try
End Sub
This works for now and is flexible. Thanks for your help.
July 23, 2008 at 8:14 am
Umm... why would you even need this in an Access table? Wouldn't one of VB's weekday functions suffice, or even the ones in Access? I remember there being a parameter that allowed you to specify which weekday was to be day 1 of a given week, where for the specification of this parameter, Sunday was 1 and Saturday was 7.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 23, 2008 at 8:23 am
That's a good thought smunson, but for now I know that the table is referenced in several queries so I want to make it available.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply