Problem inserting new row with MySQL and ASP.NET 2.0

Discussion in 'Databases' started by Keen2Learn, May 20, 2006.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Hi, I'm stumped.

    I'm using ASP.NET written in VB and connecting successfully to a MySQL database on my domain hosted by DiscountASP.net.
    As a trial, I've set up a MySQL database with three columns: plantID, name_bot and name_common.

    I successfully used SQLyog to set up the database.

    Then I wrote a page that worked well - it added rows by hard-coding the insert with a VB page as follows ...



    <script runat='server'>

    Dim objConn As New OdbcConnection('DSN=MYCONNECTION')

    Dim objCmd As OdbcCommand

    Sub AddPlant(s As Object, e As EventArgs)

    objCmd = New OdbcCommand('INSERT INTO wildplants (name_bot,
    name_common) VALUES ('Banksia serrata', 'Old Man Banksia')', objConn)

    objConn.Open()

    objCmd.ExecuteNonQuery()

    objConn.Close()

    End Sub

    </script>


    That works fine - the data is added and the row is incremented, but when I try the following code pulling the data this time from textboxes on a form, the row is incremented, but no data is added ....



    <script runat='server'>

    Dim objConn As New OdbcConnection('DSN=MYCONNECTION')

    Dim objCmd As OdbcCommand

    Sub AddPlant(s As Object, e As EventArgs)

    objCmd = New OdbcCommand('INSERT INTO wildplants (name_bot, name_common) VALUES (@name_bot, @name_common)', objConn)

    objCmd.Parameters.AddWithValue('@name_bot', txtNameBot.text)

    objCmd.Parameters.AddWithValue('@name_common', txtNameCommon.text)

    objConn.Open()

    objCmd.ExecuteNonQuery()

    objConn.Close()

    End Sub

    </script>

    <html>

    <head>

    <title></title>

    </head>



    <form runat='server'>

    <p>Add a new plant:</p>

    <p>Botanical Name:

    <asp:TextBox id='txtNameBot' runat='server' />

    </p>

    <p>Common Name:

    <asp:TextBox id='txtNameCommon' runat='server' />

    </p>

    <asp:Button id='btnInsertPlant' text=' Add Plant ' OnClick='AddPlant' runat='server' />

    </form>

    </body>

    </html>

    I put my problem to the www.asp.net forums on Databases(MySQL) and got the suggestion: 'I reccomend that you use Connector/Net 1.0 instead of ODBC. Your code should not use the '@' '

    That's way above my head and looks to me like an addition to the MySQL server.

    So back to my problem ...


    Am I trying to do something that cannot be done in MySQL with ASP.NET - or is there a problem with my code?

    I have no trouble pulling data out of the MySQL database and displaying it with VB on ASP.NET. So the connection is working fine and I have a reasonable grasp of the code involved.

    Any help will be greatly appreciated as I'm making a determined effort to switch to ASP.NET 2.0.

    I use a text editor for coding so I understand every bit of the code.

    I need to use MySQL for this job. So far I've had to resort to coding the site in PHP to be able to insert and update the MySQL database.

    I just hope someone out there can guide me.

    Best regards,

    Keen2Learn
     
  2. Try using a '?' for ODBC parameter placeholders.

    So the query will be 'INSERT INTO wildplants (name_bot, name_common) VALUES (?, ?)'

    The order that you add the parameters will be the same as order of the question marks.

    Aristotle

    DiscountASP.NET
    www.DiscountASP.NET
     
  3. Thanks a million, Aristotle!!

    Your suggestion worked perfectly.

    I greatly appreciate your help.

    Best regards,

    Keen2Learn
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page