Asp.net insert to mysql problem

boyindie · #1 Jun 27th, 2007, 12:46

Hi Forum,

I am having some really iritating problems with an simple input page which should pass parameters into a stored procedure from the text boxes first and last name.

I keep getting the error during exection of sp_myinsert, pfirstname not defined, every time i try to submit the form

Has anyone got any suggestions I have giving my code for procedure and page

many thanks
boy
--------------------------------

CREATE DEFINER=``@`localhost` PROCEDURE `sp_myInsert`(pFirstName varchar(20), pLastName varchar(30))
BEGIN
INSERT INTO Names (FirstName, LastName) values (pFirstName, pLastName);

END
-------------------------------------------

<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">

Sub page_load()

End Sub

''' <summary>
''' Page_load
''' Recognised by ASP and must be provided on loading of page.
'''
''' Creates connection to database, passes stored procedure into test database
''' and fills a table, which is showing in web browser
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>

Sub sendData(ByVal sender As Object, ByVal e As EventArgs)
Dim litErr As New LiteralControl

'Create connection string to pass database, string holds login information to mySQL,
Dim connectionString As String
connectionString = "Server=; uid=; pwd=;database=test;"

'Builds .net mysql connection and passes connection string into method
Dim connection As New MySqlConnection(connectionString)
'Open connection to DB
connection.Open()

'Create mySql command string for passing query or SPROC(Stored Procedure)
Dim cmdString As New MySqlCommand
'Set Command to equal mySql connection,t so can pass SQL query
cmdString.Connection = connection

Try
'Set command string to equal SPROC
cmdString.CommandText = "sp_myinsert"
'ONLY PLACE THIS IF SPROC, sets the command to a SPROC
cmdString.CommandType = CommandType.StoredProcedure

Dim param As New MySqlParameter

param = cmdString.Parameters.Add("p_firstname", MySqlDbType.VarChar)
param.Direction = ParameterDirection.Input
param.Value = txtFirstName.Text

param = cmdString.Parameters.Add("p_lastname", MySqlDbType.VarChar)
param.Direction = ParameterDirection.Input
param.Value = txtLastName.Text

cmdString.ExecuteNonQuery()
connection.Close()
Catch ex As Exception
litErr.Text = ex.Message
MsgBox(ex.Message)

End Try

End Sub

</script>

<!DOCTYPE html PUBLIC
<head id="Head1" runat="server">
<title>Untitled Page</title>
<script language="vbscript" type="text/vbscript">

</script>
</head>
<body>
<form id="form1" runat="server">

ENTER FIRSTNAME<asp:TextBox runat="server" ID="txtFirstName">
</asp:TextBox><br /><br />
ENTER LAST NAME
<asp:TextBox runat="server" ID="txtLastName"></asp:TextBox>
<asp:Button runat="server" ID="submit" Text="Submit" onclick="sendData" />

</form>
</body>
</html>

QuikFrozen · #2 Aug 24th, 2007, 02:00

Simple, I think. Your parameter is called pFirstName and not p_firstname.
To avoid this kind of problem I recommend you use the DeriveParameters() method that allows you to retrieve the parameters from the stored procedure and save them into the command, then you just need to update the value of the parameter, instead of creating a new one with erroneous names.
Hope it helps...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PHP Code insert into database problem	longstand	PHP Forum	7	Oct 13th, 2007 22:03
Problem with insert into the database	kool77	PHP Forum	3	Jun 4th, 2007 19:21
PHP-MySQL problem	robertboyle	PHP Forum	4	Jun 16th, 2006 13:02
Insert record in mysql	djme	Databases	1	Dec 24th, 2005 01:42
Insert into db problem	benbigun	PHP Forum	7	Nov 15th, 2005 01:51

		Webforumz.com > Main Forums > Program Your Website > ASP.NET Forum
Asp.net insert to mysql problem