Solved

Dynamic Update/insert statement

Posted on 2006-11-02
7
244 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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.   …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

705 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

19 Experts available now in Live!

Get 1:1 Help Now