Solved

Dynamic Update/insert statement

Posted on 2006-11-02
7
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

690 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