Solved

Dynamic Update/insert statement

Posted on 2006-11-02
7
247 Views
Last Modified: 2011-10-03
I am attempting to create an update/insert statement dynamically based on information that exists in another table (it lists the column names in another table to be updated). The columns in that table are added to the array ColumnsInAccess and we are attempting to devise the statements. I keep getting the error "one or more values do not have required parameters." Please any help would be awesome. First listed is a debug.Writeline of the statements generated then the code in which it is being created.

Thanks





UPDATE NETWORKDATA SET 'CustomerName' = @CustomerName, SET 'DateEntry' = @DateEntry, SET 'HourNo' = @HourNo, SET 'NetBIOSSMBTx' = @NetBIOSSMBTx, SET 'NetBIOSSMBRx' = @NetBIOSSMBRx, SET 'LotusNotesTx' = @LotusNotesTx, SET 'LotusNotesRx' = @LotusNotesRx, SET 'WebTx' = @WebTx, SET 'WebRx' = @WebRx, SET 'CitrixTx' = @CitrixTx, SET 'CitrixRx' = @CitrixRx, SET 'ServiceTx' = @ServiceTx, SET 'ServiceRx' = @ServiceRx, SET 'VoiceVideoTx' = @VoiceVideoTx, SET 'VoiceVideoRx' = @VoiceVideoRx, SET 'GopherTx' = @GopherTx, SET 'GopherRx' = @GopherRx, SET 'RoutingTx' = @RoutingTx, SET 'RoutingRx' = @RoutingRx, SET 'TelnetTx' = @TelnetTx, SET 'TelnetRx' = @TelnetRx, SET 'SNMPTx' = @SNMPTx, SET 'SNMPRx' = @SNMPRx, SET 'OtherTx' = @OtherTx, SET 'OtherRx' = @OtherRx, SET 'PrintingTx' = @PrintingTx, SET 'PrintingRx' = @PrintingRx, SET 'SAPTx' = @SAPTx, SET 'SAPRx' = @SAPRx, SET 'NWIPTx' = @NWIPTx, SET 'NWIPRx' = @NWIPRx, SET 'DatabaseTx' = @DatabaseTx, SET 'DatabaseRx' = @DatabaseRx, SET 'FTPTx' = @FTPTx, SET 'FTPRx' = @FTPRx, SET 'TFTPTx' = @TFTPTx, SET 'TFTPRx' = @TFTPRx, SET 'BOOTPTx' = @BOOTPTx, SET 'BOOTPRx' = @BOOTPRx, SET 'TunnelTx' = @TunnelTx, SET 'TunnelRx' = @TunnelRx, SET 'MailTx' = @MailTx, SET 'MailRx' = @MailRx, SET 'FileShareTx' = @FileShareTx, SET 'FileShareRx' = @FileShareRx, SET 'MessageTx' = @MessageTx, SET 'MessageRx' = @MessageRx, SET 'StreamingTx' = @StreamingTx, SET 'StreamingRx' = @StreamingRx, SET 'T120Tx' = @T120Tx, SET 'T120Rx' = @T120Rx, SET 'SSHTx' = @SSHTx, SET 'SSHRx' = @SSHRx, SET 'PeopleSoftTx' = @PeopleSoftTx, SET 'PeopleSoftRx' = @PeopleSoftRx, SET 'NNTPTx' = @NNTPTx, SET 'NNTPRx' = @NNTPRx, SET 'FIXTx' = @FIXTx, SET 'FIXRx' = @FIXRx, SET 'DLSWTx' = @DLSWTx, SET 'DLSWRx' = @DLSWRx, SET 'DCERPCTx' = @DCERPCTx, SET 'DCERPCRx' = @DCERPCRx, SET 'CustomAppsTx' = @CustomAppsTx, SET 'CustomAppsRx' = @CustomAppsRx, SET 'VinesTx' = @VinesTx, SET 'VinesRx' = @VinesRx, SET 'SNATx' = @SNATx, SET 'SNARx' = @SNARx, SET 'IPXTx' = @IPXTx, SET 'IPXRx' = @IPXRx, SET 'DecNetTx' = @DecNetTx, SET 'DecNetRx' = @DecNetRx, SET 'AppleTx' = @AppleTx, SET 'AppleRx' = @AppleRx, SET 'TotalTx' = @TotalTx, SET 'TotalRx' = @TotalRx WHERE @CustomerName, @DateEntry, @HourNo, @NetBIOSSMBTx, @NetBIOSSMBRx, @LotusNotesTx, @LotusNotesRx, @WebTx, @WebRx, @CitrixTx, @CitrixRx, @ServiceTx, @ServiceRx, @VoiceVideoTx, @VoiceVideoRx, @GopherTx, @GopherRx, @RoutingTx, @RoutingRx, @TelnetTx, @TelnetRx, @SNMPTx, @SNMPRx, @OtherTx, @OtherRx, @PrintingTx, @PrintingRx, @SAPTx, @SAPRx, @NWIPTx, @NWIPRx, @DatabaseTx, @DatabaseRx, @FTPTx, @FTPRx, @TFTPTx, @TFTPRx, @BOOTPTx, @BOOTPRx, @TunnelTx, @TunnelRx, @MailTx, @MailRx, @FileShareTx, @FileShareRx, @MessageTx, @MessageRx, @StreamingTx, @StreamingRx, @T120Tx, @T120Rx, @SSHTx, @SSHRx, @PeopleSoftTx, @PeopleSoftRx, @NNTPTx, @NNTPRx, @FIXTx, @FIXRx, @DLSWTx, @DLSWRx, @DCERPCTx, @DCERPCRx, @CustomAppsTx, @CustomAppsRx, @VinesTx, @VinesRx, @SNATx, @SNARx, @IPXTx, @IPXRx, @DecNetTx, @DecNetRx, @AppleTx, @AppleRx, @TotalTx, @TotalRx)


INSERT INTO NetworkData (CustomerName, DateEntry, HourNo, NetBIOSSMBTx, NetBIOSSMBRx, LotusNotesTx, LotusNotesRx, WebTx, WebRx, CitrixTx, CitrixRx, ServiceTx, ServiceRx, VoiceVideoTx, VoiceVideoRx, GopherTx, GopherRx, RoutingTx, RoutingRx, TelnetTx, TelnetRx, SNMPTx, SNMPRx, OtherTx, OtherRx, PrintingTx, PrintingRx, SAPTx, SAPRx, NWIPTx, NWIPRx, DatabaseTx, DatabaseRx, FTPTx, FTPRx, TFTPTx, TFTPRx, BOOTPTx, BOOTPRx, TunnelTx, TunnelRx, MailTx, MailRx, FileShareTx, FileShareRx, MessageTx, MessageRx, StreamingTx, StreamingRx, T120Tx, T120Rx, SSHTx, SSHRx, PeopleSoftTx, PeopleSoftRx, NNTPTx, NNTPRx, FIXTx, FIXRx, DLSWTx, DLSWRx, DCERPCTx, DCERPCRx, CustomAppsTx, CustomAppsRx, VinesTx, VinesRx, SNATx, SNARx, IPXTx, IPXRx, DecNetTx, DecNetRx, AppleTx, AppleRx, TotalTx, TotalRx) VALUES (@CustomerName, @DateEntry, @HourNo, @NetBIOSSMBTx, @NetBIOSSMBRx, @LotusNotesTx, @LotusNotesRx, @WebTx, @WebRx, @CitrixTx, @CitrixRx, @ServiceTx, @ServiceRx, @VoiceVideoTx, @VoiceVideoRx, @GopherTx, @GopherRx, @RoutingTx, @RoutingRx, @TelnetTx, @TelnetRx, @SNMPTx, @SNMPRx, @OtherTx, @OtherRx, @PrintingTx, @PrintingRx, @SAPTx, @SAPRx, @NWIPTx, @NWIPRx, @DatabaseTx, @DatabaseRx, @FTPTx, @FTPRx, @TFTPTx, @TFTPRx, @BOOTPTx, @BOOTPRx, @TunnelTx, @TunnelRx, @MailTx, @MailRx, @FileShareTx, @FileShareRx, @MessageTx, @MessageRx, @StreamingTx, @StreamingRx, @T120Tx, @T120Rx, @SSHTx, @SSHRx, @PeopleSoftTx, @PeopleSoftRx, @NNTPTx, @NNTPRx, @FIXTx, @FIXRx, @DLSWTx, @DLSWRx, @DCERPCTx, @DCERPCRx, @CustomAppsTx, @CustomAppsRx, @VinesTx, @VinesRx, @SNATx, @SNARx, @IPXTx, @IPXRx, @DecNetTx, @DecNetRx, @AppleTx, @AppleRx, @TotalTx, @TotalRx)

 Sub AddRows(ByVal dsutiliz11 As DataSet)
        Dim jj, i As Integer

        Dim da As New OleDbDataAdapter 'NetworkReports.dsutilizeTableAdapters.NetworkDataTableAdapter

        Dim cmd As New OleDbCommand
        cmd.Connection = sConn41


        cmd.CommandText = "UPDATE NETWORKDATA SET 'CustomerName' = @CustomerName, SET 'DateEntry' = @DateEntry, "
        For jj = 0 To ColumnsInAccess.Count - 1
            If jj <> ColumnsInAccess.Count - 1 Then
                cmd.CommandText = cmd.CommandText & "SET '" & ColumnsInAccess.Item(jj) & "' = @" & ColumnsInAccess.Item(jj) & ", "
            Else
                cmd.CommandText = cmd.CommandText & "SET '" & ColumnsInAccess.Item(jj) & "' = @" & ColumnsInAccess.Item(jj)
            End If
        Next

        cmd.CommandText = cmd.CommandText & " WHERE @CustomerName, @DateEntry, "
        For jj = 0 To ColumnsInAccess.Count - 1
            If jj <> ColumnsInAccess.Count - 1 Then
                cmd.CommandText = cmd.CommandText & "@" & ColumnsInAccess.Item(jj) & ", "
            Else
                cmd.CommandText = cmd.CommandText & "@" & ColumnsInAccess.Item(jj) & ")"
            End If
        Next
       
        Debug.WriteLine(cmd.CommandText)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("CustomerName", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "CustomerName", System.Data.DataRowVersion.Current, False, Nothing))

        cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateEntry", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateEntry", System.Data.DataRowVersion.Current, False, Nothing))

        For jj = 0 To ColumnsInAccess.Count - 1
            cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("""" & ColumnsInAccess.Item(jj) & """", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "'" & ColumnsInAccess.Item(jj) & "'", System.Data.DataRowVersion.Current, False, Nothing))
        Next

        da.UpdateCommand = cmd



        Dim cmd1 As New OleDbCommand
        cmd1.Connection = sConn41

        cmd1.CommandText = "INSERT INTO NetworkData (CustomerName, DateEntry, "


        For jj = 0 To ColumnsInAccess.Count - 1
            If jj <> ColumnsInAccess.Count - 1 Then
                cmd1.CommandText = cmd1.CommandText & "" & ColumnsInAccess.Item(jj) & ", "
            Else
                cmd1.CommandText = cmd1.CommandText & "" & ColumnsInAccess.Item(jj) & ")"
            End If
        Next

        cmd1.CommandText = cmd1.CommandText & " VALUES (@CustomerName, @DateEntry, "

        For jj = 0 To ColumnsInAccess.Count - 1
            If jj <> ColumnsInAccess.Count - 1 Then
                cmd1.CommandText = cmd1.CommandText & "@" & ColumnsInAccess.Item(jj) & ", "
            Else
                cmd1.CommandText = cmd1.CommandText & "@" & ColumnsInAccess.Item(jj) & ")"
            End If
        Next

        cmd1.CommandType = System.Data.CommandType.Text
        cmd1.Connection = sConn41

        cmd1.Parameters.Add(New System.Data.OleDb.OleDbParameter("CustomerName", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "CustomerName", System.Data.DataRowVersion.Current, False, Nothing))

        cmd1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateEntry", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateEntry", System.Data.DataRowVersion.Current, False, Nothing))

        For jj = 0 To ColumnsInAccess.Count - 1
            cmd1.Parameters.Add(New System.Data.OleDb.OleDbParameter("""" & ColumnsInAccess.Item(jj) & """", System.Data.OleDb.OleDbType.VarWChar, 0, System.Data.ParameterDirection.Input, CType(0, Byte), CType(0, Byte), """" & ColumnsInAccess.Item(jj) & """", System.Data.DataRowVersion.Current, False, Nothing))
        Next
       
        Debug.WriteLine(vbCrLf & cmd1.CommandText)
        da.InsertCommand = cmd1
        sConn41.Open()

        Try
            'cmd1.ExecuteNonQuery()
            da.Update(dsutiliz11.Tables(0))
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub
   
0
Comment
Question by:scorpion53061
  • 4
7 Comments
 

Author Comment

by:scorpion53061
ID: 17858963
UPDATE:

Error has now changed to "Parameter @HourNo has no default value." which is the first column listed in the array "ColumnsInAccess"
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 17859356
All those words that start with "@" are sql server variables and they need to be set to a value.  The update statement you built would work well inside a stored procedure (which could take all the @variables as parameters).
0
 

Author Comment

by:scorpion53061
ID: 17859385
in both the update and insert or just update? Would you mean I would loop through the dataset and put the value of the row at @?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:scorpion53061
ID: 17859392
by the way this is an access database I dont know if that matters.
0
 

Author Comment

by:scorpion53061
ID: 17873348
I figured it out. I add each row indivudually and create the insert statement with those values to insert.
0
 
LVL 1

Accepted Solution

by:
kodiakbear earned 0 total points
ID: 17975480
Closed, 500 points refunded.
kb
Experts Exchange Moderator
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question