Solved

Dynamic Update/insert statement

Posted on 2006-11-02
7
245 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
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.

 

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now