Update Query Promble while updating the DTP

Question

jallan2010 0 Light Poster

14 Years Ago

Sir,
I am using the following code in my vb.net while using that code it will show me syntax error.

Private Sub updateinfo()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Try
              con = New OleDbConnection(connectionString)

            cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] = '" & txtfirst.Text & "', [Middle Name] = '" & txtmiddle.Text & "', [Last Name] = '" & txtlast.Text & "',[Company Name] = '" & txtcomp.Text & "', [Lani] Rs = '" & txtlani.Text & "',[Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Sr No] = Cint(txtid.text)", con)
            cmd.Parameters.AddWithValue("@Sr No", txtid.Text)
            cmd.Parameters.AddWithValue("@First Name", txtfirst.Text)
            cmd.Parameters.AddWithValue("@Middle Name", txtmiddle.Text)
            cmd.Parameters.AddWithValue("@Last Name", txtlast.Text)
            cmd.Parameters.AddWithValue("@Company Name", txtcomp.Text)
            cmd.Parameters.AddWithValue("@Lani Rs", txtlani.Text)
            cmd.Parameters.AddWithValue("@Lani_Date", DateTimePicker1.Value.Date)
            con.Open()

            'cmd = New OleDbCommand(strupdate, con)
            cmd.ExecuteNonQuery()

            ' Close and Clean up objects
            con.Close()
            cmd.Dispose()
            con.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
            'updateOK = False
        End Try
    

    End Sub
End Class

While use this code in the button click event

Please help me.

Thanks in advance

vb.net

3 Contributors
17 Replies
121 Views
5 Days Discussion Span
Latest Post 14 Years Ago Latest Post by kvprajapati

kvprajapati 1,826 Posting Genius

14 Years Ago

Have a look,

cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] =@fname,[Middle Name] = @mname,[Last Name] = @lname,[Company Name] = @cname,[Lani Rs] =@rs, [Date] =@date  WHERE [Sr No]=@srno", con)

cmd.Parameters.AddWithValue("@fname", txtfirst.Text)
cmd.Parameters.AddWithValue("@mname", txtmiddle.Text)
cmd.Parameters.AddWithValue("@lname", txtlast.Text)
cmd.Parameters.AddWithValue("@cname", txtcomp.Text)
cmd.Parameters.AddWithValue("@rs", txtlani.Text)
cmd.Parameters.AddWithValue("@date", DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@srno", txtid.Text)
.....

kvprajapati 1,826 Posting Genius

14 Years Ago

>No value given for one or more required parameters

Please verify the names of column used in commandText (Command) and in database table.

kvprajapati 1,826 Posting Genius

14 Years Ago

@jallan2010.

Code in post #5 is completely incorrect. I have mentioned some correction in my post #2 and #6.

Problem in post #5 is improper use of parameters. You have two ways to go: first, do not use parametrized query and second, use it.

Code without parameter,

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
    

       con = New OleDbConnection(connectionString)

       cmd = New OleDbCommand("UPDATE [Lani] SET [Lani_First Name] = '" & txtfirst.Text & "', [Lani_Middle Name] = '" & txtmiddle.Text & "', [Lani_Last Name] = '" & txtlast.Text & "',[Lani_Company Name] = '" & txtcomp.Text & "', [Lani_Lani Rs] = '" & txtlani.Text & "',[Lani_Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Lani_Sr No] =" &  Cint(txtid.text), con)
        
     con.Open()
     cmd.ExecuteNonQuery()
     con.Close()
     cmd.Dispose()
     con.Dispose()
End Sub

Code with parametrized query,

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
    
        con = New OleDbConnection(connectionString)

        cmd = New OleDbCommand("UPDATE [Lani] SET [Lani_First Name] =@fname, [Lani_Middle Name] =@mname, [Lani_Last Name] = @lname,[Lani_Company Name] = @cname, [Lani_Lani Rs] = @rs,[Lani_Date] =@ldate WHERE [Lani_Sr No] = @srno", con)
        
 cmd.Parameters.AddWithValue("@fname", txtfirst.Text)
 cmd.Parameters.AddWithValue("@mname", txtmiddle.Text)
 cmd.Parameters.AddWithValue("@lname", txtlast.Text)
 cmd.Parameters.AddWithValue("@cname", txtcomp.Text)

 If IsNumeric(txtlani.Text) = False Then
     cmd.Parameters.AddWithValue("@rs", DBNull.Value)
 Else
   cmd.Parameters.AddWithValue("@rs", txtlani.Text)
 End If
 cmd.Parameters.AddWithValue("@ldate", DateTimePicker1.Value.Date)
 cmd.Parameters.AddWithValue("@srno", txtid.Text)

 con.Open()
 cmd.ExecuteNonQuery()
 con.Close()
 cmd.Dispose()
 con.Dispose()
End Sub

Edited 14 Years Ago by kvprajapati because: n/a

kvprajapati 1,826 Posting Genius

14 Years Ago

>No value given for one or more required parameters.

Upload your database here please.

kvprajapati 1,826 Posting Genius

14 Years Ago

Sir,
I am using my code it will show me an error i.e. "No value given for one or more required parameters"

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Try
            ' Build update statement to update Contact table with data
            ' on Textbox.

            con = New OleDbConnection(connectionString)


            cmd = New OleDbCommand("UPDATE [Lani] SET [Lani_First Name] = '" & txtfirst.Text & "', [Lani_Middle Name] = '" & txtmiddle.Text & "', [Lani_Last Name] = '" & txtlast.Text & "',[Lani_Company Name] = '" & txtcomp.Text & "', [Lani_Lani Rs] = '" & txtlani.Text & "',[Lani_Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Lani_Sr No] = Cint(txtid.text)", con)
        
            cmd.Parameters.AddWithValue("@Lani_First Name", txtfirst.Text)
            cmd.Parameters.AddWithValue("@Lani_Middle Name", txtmiddle.Text)
            cmd.Parameters.AddWithValue("@Lani_Last Name", txtlast.Text)
            cmd.Parameters.AddWithValue("@Lani_Company Name", txtcomp.Text)


            If IsNumeric(txtlani.Text) = False Then
                cmd.Parameters.AddWithValue("@Lani_Lani Rs", DBNull.Value)
            Else
                cmd.Parameters.AddWithValue("@Lani_Lani Rs", txtlani.Text)
            End If
            cmd.Parameters.AddWithValue("@Lani_Date", DateTimePicker1.Value.Date)
            cmd.Parameters.AddWithValue("@Sr No", txtid.Text)
con.Open()
                     cmd.ExecuteNonQuery()
            ' Close and Clean up objects
            con.Close()
            cmd.Dispose()
            con.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
            'updateOK = False
        End Try
            End Sub

Please anyone help me.

Thank you

Please post correct column (field) names. Columns found in attached database are:

Sr No
First Name
Middle Name
Last Name
Company Name
Lani Rs
Date

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

M.PREETHA -2 Newbie Poster · Answer 1 · 2010-03-10T21:50:41+00:00

Sir,
I am using the following code in my vb.net while using that code it will show me syntax error.

Private Sub updateinfo()
        Dim cnn as string ="specify the connection string path"
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Try
              con = New OleDbConnection(cnn)

            cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] = '" & txtfirst.Text & "', [Middle Name] = '" & txtmiddle.Text & "', [Last Name] = '" & txtlast.Text & "',[Company Name] = '" & txtcomp.Text & "', [Lani] Rs = '" & txtlani.Text & "',[Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Sr No] = Cint(txtid.text)", con)
            cmd.Parameters.AddWithValue("@Sr No", txtid.Text)
            cmd.Parameters.AddWithValue("@First Name", txtfirst.Text)
            cmd.Parameters.AddWithValue("@Middle Name", txtmiddle.Text)
            cmd.Parameters.AddWithValue("@Last Name", txtlast.Text)
            cmd.Parameters.AddWithValue("@Company Name", txtcomp.Text)
            cmd.Parameters.AddWithValue("@Lani Rs", txtlani.Text)
            cmd.Parameters.AddWithValue("@Lani_Date", DateTimePicker1.Value.Date)
            con.Open()

            'cmd = New OleDbCommand(strupdate, con)
            cmd.ExecuteNonQuery()

            ' Close and Clean up objects
            con.Close()
            cmd.Dispose()
            con.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
            'updateOK = False
        End Try
    

    End Sub
End Class

While use this code in the button click event

jallan2010 0 Light Poster · Answer 2 · 2010-03-11T14:03:53+00:00

Sir,
While using the update code it will not update my data it will show me as it is.

I am using adatapost code

Please help me

Thank you

jallan2010 0 Light Poster · Answer 3 · 2010-03-12T11:07:37+00:00

Sir,
I am using my code it will show me an error i.e. "No value given for one or more required parameters"

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Try
            ' Build update statement to update Contact table with data
            ' on Textbox.

            con = New OleDbConnection(connectionString)


            cmd = New OleDbCommand("UPDATE [Lani] SET [Lani_First Name] = '" & txtfirst.Text & "', [Lani_Middle Name] = '" & txtmiddle.Text & "', [Lani_Last Name] = '" & txtlast.Text & "',[Lani_Company Name] = '" & txtcomp.Text & "', [Lani_Lani Rs] = '" & txtlani.Text & "',[Lani_Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Lani_Sr No] = Cint(txtid.text)", con)
        
            cmd.Parameters.AddWithValue("@Lani_First Name", txtfirst.Text)
            cmd.Parameters.AddWithValue("@Lani_Middle Name", txtmiddle.Text)
            cmd.Parameters.AddWithValue("@Lani_Last Name", txtlast.Text)
            cmd.Parameters.AddWithValue("@Lani_Company Name", txtcomp.Text)


            If IsNumeric(txtlani.Text) = False Then
                cmd.Parameters.AddWithValue("@Lani_Lani Rs", DBNull.Value)
            Else
                cmd.Parameters.AddWithValue("@Lani_Lani Rs", txtlani.Text)
            End If
            cmd.Parameters.AddWithValue("@Lani_Date", DateTimePicker1.Value.Date)
            cmd.Parameters.AddWithValue("@Sr No", txtid.Text)
con.Open()
                     cmd.ExecuteNonQuery()
            ' Close and Clean up objects
            con.Close()
            cmd.Dispose()
            con.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
            'updateOK = False
        End Try
            End Sub

Please anyone help me.

Thank you

jallan2010 0 Light Poster · Answer 4 · 2010-03-13T10:44:18+00:00

Sir,
I have check everything is correct and than still this error has been generated can I attach my project for your checking.

pLease help me

Thank you

jallan2010 0 Light Poster · Answer 5 · 2010-03-15T12:54:04+00:00

Sir,
First of all sorry for late reply.

I have put the code in the update subroutine in a first code you have post it will generate an error i.e. No value given for one or more required parameters.

And in the parameter code it will nothing to update in my code so please help me.

I have tried my leve best but no update at all.

Thank you.

jallan2010 0 Light Poster · Answer 6 · 2010-03-15T14:03:24+00:00

jallan2010 0 Light Poster

14 Years Ago

Sir,
I am enclosing my database.

Thank you

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

Lakadganj_Database.zip (8.96 KB)

kvprajapati 1,826 Posting Genius Team Colleague · Answer 7 · 2010-03-15T14:24:19+00:00

Code without parameter,

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
    

       con = New OleDbConnection(connectionString)

       cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] = '" & txtfirst.Text & "', [Middle Name] = '" & txtmiddle.Text & "', [Last Name] = '" & txtlast.Text & "',[Company Name] = '" & txtcomp.Text & "', [Lani Rs] = '" & txtlani.Text & "',[Date] = '" & DateTimePicker1.Value.Date & "'  WHERE [Sr No] =" &  Cint(txtid.text), con)
        
     con.Open()
     cmd.ExecuteNonQuery()
     con.Close()
     cmd.Dispose()
     con.Dispose()
End Sub

Code with parametrized query,

Private Sub updateinfo()

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
    
        con = New OleDbConnection(connectionString)

        cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] =@fname, [Middle Name] =@mname, [Last Name] = @lname,[Company Name] = @cname, [Lani Rs] = @rs,[Date] =@ldate WHERE [Sr No] = @srno", con)
        
 cmd.Parameters.AddWithValue("@fname", txtfirst.Text)
 cmd.Parameters.AddWithValue("@mname", txtmiddle.Text)
 cmd.Parameters.AddWithValue("@lname", txtlast.Text)
 cmd.Parameters.AddWithValue("@cname", txtcomp.Text)

 If IsNumeric(txtlani.Text) = False Then
     cmd.Parameters.AddWithValue("@rs", DBNull.Value)
 Else
   cmd.Parameters.AddWithValue("@rs", txtlani.Text)
 End If
 cmd.Parameters.AddWithValue("@ldate", DateTimePicker1.Value.Date)
 cmd.Parameters.AddWithValue("@srno", txtid.Text)

 con.Open()
 cmd.ExecuteNonQuery()
 con.Close()
 cmd.Dispose()
 con.Dispose()
End Sub

jallan2010 0 Light Poster · Answer 8 · 2010-03-15T14:39:24+00:00

Sir,

I am using without parameter code

It will show me an error i.e. data type mismatch in criteria expressions.

I think i will better to enclose my project .

What do you think sir please reply

Thank you

jallan2010 0 Light Poster · Answer 9 · 2010-03-15T14:57:56+00:00

jallan2010 0 Light Poster

14 Years Ago

Sir,
I am enclosing my project please help me.

I am appreciate your reply

Please help me.

Thank you

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

Lakadganj_Project.zip (96.44 KB)

kvprajapati 1,826 Posting Genius Team Colleague · Answer 10 · 2010-03-15T16:39:47+00:00

Have a look at code in your uploaded project of updateInfor() method,

You are adding @rs parameter twice.

cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] = @fname, [Middle Name] = @mname, [Last Name] = @lname,[Company Name] = @cname, [Lani Rs] = @rs,[Date] = @ldate WHERE [Sr No] = @srno", con)
            cmd.Parameters.AddWithValue("@fname", txtfirst.Text)
            cmd.Parameters.AddWithValue("@mname", txtmiddle.Text)
            cmd.Parameters.AddWithValue("@lname", txtlast.Text)
            cmd.Parameters.AddWithValue("@cname", txtcomp.Text)
            cmd.Parameters.AddWithValue("@rs", txtlani.Text)

            If IsNumeric(txtlani.Text) = False Then
                cmd.Parameters.AddWithValue("@rs", DBNull.Value)
            Else
                cmd.Parameters.AddWithValue("@rs", txtlani.Text)
            End If
            cmd.Parameters.AddWithValue("@ldate", DateTimePicker1.Value.Date)

            cmd.Parameters.AddWithValue("@srno", txtid.Text)

Correct code is,

Private Sub updateinfo()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        con = New OleDbConnection(connectionString)

        cmd = New OleDbCommand("UPDATE [Lani] SET [First Name] = @fname, [Middle Name] = @mname, [Last Name] = @lname,[Company Name] = @cname, [Lani Rs] = @rs,[Date] = @ldate WHERE [Sr No] = @srno", con)
        cmd.Parameters.AddWithValue("@fname", txtfirst.Text)
        cmd.Parameters.AddWithValue("@mname", txtmiddle.Text)
        cmd.Parameters.AddWithValue("@lname", txtlast.Text)
        cmd.Parameters.AddWithValue("@cname", txtcomp.Text)
        cmd.Parameters.AddWithValue("@rs", txtlani.Text)
 
        cmd.Parameters.AddWithValue("@ldate", DateTimePicker1.Value.Date)

        cmd.Parameters.AddWithValue("@srno", txtid.Text)

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()
        cmd.Dispose()
        con.Dispose()
    End Sub

jallan2010 0 Light Poster · Answer 11 · 2010-03-15T20:17:00+00:00

Sir,

It is general question how to improve my logic in programming if you have link so please sent me on my email-id <<Email Snipped>>.

Thank you so much sir,

kvprajapati 1,826 Posting Genius Team Colleague · Answer 12 · 2010-03-16T07:13:48+00:00

>It is general question how to improve my logic in programming if you have link so please sent me.

Improve your understanding. You can improve your mind by reading programming/non-programming books/blogs/forum threads.