VB.NET - Insert Multiple Records Without Stored Procedure

• charles.frank

  SSC-Addicted

  Points: 497

  More actions

  July 21, 2008 at 12:36 pm

  #93683

  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()

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  July 21, 2008 at 3:57 pm

  #846061

  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]

• charles.frank

  SSC-Addicted

  Points: 497

  More actions

  July 22, 2008 at 6:27 am

  #846352

  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.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 23, 2008 at 8:14 am

  #847076

  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) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• charles.frank

  SSC-Addicted

  Points: 497

  More actions

  July 23, 2008 at 8:23 am

  #847083

  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