Click here to Skip to main content
15,895,667 members
Home / Discussions / Visual Basic
   

Visual Basic

 
AnswerRe: Visual Basic Noob, in need of some direction Pin
Member 1207985922-Oct-15 13:25
Member 1207985922-Oct-15 13:25 
GeneralRe: Visual Basic Noob, in need of some direction Pin
Wombaticus3-Nov-15 7:22
Wombaticus3-Nov-15 7:22 
QuestionMessage Closed Pin
15-Oct-15 10:35
Gie-wel15-Oct-15 10:35 
AnswerRe: it always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same". please help me Pin
Chris Quinn15-Oct-15 21:13
Chris Quinn15-Oct-15 21:13 
Questionit always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same" what should i do? i need answer as soon as possible please i'm new Pin
Gie-wel15-Oct-15 10:17
Gie-wel15-Oct-15 10:17 
AnswerRe: it always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same" what should i do? i need answer as soon as possible please i'm Pin
Wombaticus15-Oct-15 12:37
Wombaticus15-Oct-15 12:37 
AnswerRe: it always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same" what should i do? i need answer as soon as possible please i'm Pin
Eddy Vluggen16-Oct-15 1:37
professionalEddy Vluggen16-Oct-15 1:37 
AnswerRe: it always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same" what should i do? i need answer as soon as possible please i'm Pin
Richard Deeming16-Oct-15 3:03
mveRichard Deeming16-Oct-15 3:03 
You attempt to execute an INSERT statement which specifies 23 columns, but only inserts 20 values.

That throws the "Number of query values and destination fields are not the same" exception.

Your code then fails to close the connection, so the next time you click the button, you get the "Not allowed to change the 'ConnectionString' property..." exception.

As the others have said, create your connection object inside the method, and wrap it in a Using block[^] to ensure that it is always disposed of properly.

Make sure your INSERT statement has the same number of parameter placeholders in the VALUES part as the number of columns in the INSERT INTO part.

There's no need to convert the Text property to a string, as it's already a string.

You can simplify adding the parameters by using the AddWithValue method.

You should avoid catching the general Exception type. Only catch the exceptions that you want to handle. In this case, System.Data.Common.DbException is the only one you want to catch.
VB.NET
Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database3.mdb"

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Const CommandText As String = "INSERT INTO Inquiry_Form([Date], [RefNo], [NameP], [Address], [Age], [PhoneNo], [MobileNo], [EmailAd], [ReferralQuestion], [Type_of_Unit], [Date_of_Purchase], [Purpose_of_Purchase], [Monthly_Income], [RName], [RAddress], [RMobileNo], [R2Name], [R2Address], [RMobileNo], [R2Name], [R2Address], [R2MobileNo], [AgentsName]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    
    Using connection As New OleDbConnection(ConnectionString)
    Using cmd As New OleDbCommand(CommandText, connection)
        cmd.Parameters.AddWithValue("Date", TextBox1.Text)
        cmd.Parameters.AddWithValue("RefNo", TextBox2.Text)
        cmd.Parameters.AddWithValue("NameP", TextBox3.Text)
        cmd.Parameters.AddWithValue("Address", TextBox4.Text)
        cmd.Parameters.AddWithValue("Age", TextBox5.Text)
        cmd.Parameters.AddWithValue("PhoneNo", TextBox6.Text)
        cmd.Parameters.AddWithValue("MobileNo", TextBox8.Text)
        cmd.Parameters.AddWithValue("EmailAd", TextBox7.Text)
        cmd.Parameters.AddWithValue("ReferralQuestion", ComboBox1.Text)
        cmd.Parameters.AddWithValue("Type_of_Unit", ComboBox2.Text)
        cmd.Parameters.AddWithValue("Date_of_Purchase", ComboBox3.Text)
        cmd.Parameters.AddWithValue("Purpose_of_Purchase", ComboBox4.Text)
        cmd.Parameters.AddWithValue("Monthly_Income", ComboBox5.Text)
        cmd.Parameters.AddWithValue("RName", TextBox10.Text)
        cmd.Parameters.AddWithValue("RAddress", TextBox11.Text)
        cmd.Parameters.AddWithValue("RMobileNo", TextBox15.Text)
        cmd.Parameters.AddWithValue("R2Name", TextBox12.Text)
        cmd.Parameters.AddWithValue("R2Address", TextBox13.Text)
        cmd.Parameters.AddWithValue("R2MobileNo", TextBox14.Text)
        cmd.Parameters.AddWithValue("AgentsName", TextBox16.Text)
        
        Try
            connection.Open()
            cmd.ExecuteNonQuery()
            
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()
            TextBox5.Clear()
            TextBox6.Clear()
            TextBox8.Clear()
            TextBox7.Clear()
            TextBox10.Clear()
            TextBox11.Clear()
            TextBox15.Clear()
            TextBox12.Clear()
            TextBox13.Clear()
            TextBox14.Clear()
            TextBox16.Clear()
            
        Catch ex As System.Data.Common.DbException
            MsgBox(ex.Message)
        End Try
    End Using
End Sub

You should also give your controls meaningful names, rather than accepting the default names created by the designer. You might remember that TextBox11 is the "RAddress" now, but when you come back to this code in a few months, you'll be cursing yourself for leaving the controls with the default names!



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


AnswerRe: it always say's "Not allowed to change the 'ConnectionString' property. The connection's current state is open." and " Number of query values and destination fields are not the same" what should i do? i need answer as soon as possible please i'm Pin
عبد الرؤوف شوركي25-Oct-15 11:08
عبد الرؤوف شوركي25-Oct-15 11:08 
QuestionNIC Card Configuration for Multiple Servers in a list Pin
Member 1206068515-Oct-15 4:45
Member 1206068515-Oct-15 4:45 
AnswerRe: NIC Card Configuration for Multiple Servers in a list Pin
Dave Kreskowiak15-Oct-15 5:51
mveDave Kreskowiak15-Oct-15 5:51 
Questionhow to read STL file in vb.net Pin
Abimbola Sammy O'banj15-Oct-15 3:27
Abimbola Sammy O'banj15-Oct-15 3:27 
AnswerRe: how to read STL file in vb.net Pin
Dave Kreskowiak15-Oct-15 3:54
mveDave Kreskowiak15-Oct-15 3:54 
Questionvb.net desktop app on one computer share by several users Pin
dcof13-Oct-15 4:48
dcof13-Oct-15 4:48 
AnswerRe: vb.net desktop app on one computer share by several users Pin
ZurdoDev13-Oct-15 5:38
professionalZurdoDev13-Oct-15 5:38 
GeneralRe: vb.net desktop app on one computer share by several users Pin
dcof13-Oct-15 7:09
dcof13-Oct-15 7:09 
GeneralRe: vb.net desktop app on one computer share by several users Pin
ZurdoDev13-Oct-15 7:53
professionalZurdoDev13-Oct-15 7:53 
GeneralRe: vb.net desktop app on one computer share by several users Pin
dcof13-Oct-15 9:11
dcof13-Oct-15 9:11 
GeneralRe: vb.net desktop app on one computer share by several users Pin
ZurdoDev13-Oct-15 9:12
professionalZurdoDev13-Oct-15 9:12 
AnswerRe: vb.net desktop app on one computer share by several users Pin
Richard MacCutchan13-Oct-15 8:05
mveRichard MacCutchan13-Oct-15 8:05 
QuestionSql Value to buttons........ Pin
dha NAA12-Oct-15 20:36
dha NAA12-Oct-15 20:36 
AnswerRe: Sql Value to buttons........ Pin
Richard MacCutchan12-Oct-15 23:20
mveRichard MacCutchan12-Oct-15 23:20 
SuggestionRe: Sql Value to buttons........ Pin
ZurdoDev13-Oct-15 5:39
professionalZurdoDev13-Oct-15 5:39 
QuestionRe: Sql Value to buttons........ Pin
Paul Conrad13-Oct-15 5:54
professionalPaul Conrad13-Oct-15 5:54 
Questionvb.net 2010 determine what files each user has access to Pin
dcof12-Oct-15 11:36
dcof12-Oct-15 11:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.