Error in asp.net c# code (mysql database connection)

Question

I have a code in which

i bind a dropdownlist to a database and later on ,
on button click i connect to database to get some value in a label.

My 1st part works fine but when i try to do the second part i get the error message as

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at _Default.Button1_Click(Object sender, EventArgs e) in c:\Documents and Settings\a\My Documents\Visual Studio 2008\WebSites\toolbar1\Default.aspx.cs:line 56

My code is:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            try
            {
                SqlConnection myConn = new SqlConnection("Server=localhost;Database=testcase;Integrated Security=SSPI");
                SqlCommand myCmd = new SqlCommand("select skey,casecode from casetype", myConn);
                myConn.Open();
                SqlDataReader myReader = myCmd.ExecuteReader();

                //Set up the data binding.
                DropDownList3.DataSource = myReader;
                DropDownList3.DataTextField = "skey";
                DropDownList3.DataValueField = "casecode";
                DropDownList3.DataBind();

                //Close the connection.
                //myConn.Close();
                //myReader.Close();

                //Add the item at the first position.
                DropDownList3.Items.Insert(0, "<-- Select -->");

            }
            catch (Exception ex)
            {
                Response.Write(ex.StackTrace);
            }
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection myConn1 = new SqlConnection("Server=localhost;Database=testcase;Integrated Security=SSPI");
            SqlCommand myCmd1 = new SqlCommand("select casename,skey from casetype where skey=?", myConn1);
            myConn1.Open();
            SqlDataReader myReader1 = myCmd1.ExecuteReader();
            String type = DropDownList3.SelectedItem.Text;
            myCmd1.Parameters.AddWithValue("?", type);
        }
        catch (Exception exw)
        {
            Response.Write(exw.StackTrace);
        }

    }
}

Please help me to solve my problem.

Sekhar · Accepted Answer · 2010-11-24 06:44:04Z

1

You are saying it's a MySQL database .. and the connection string that you gave is "Server=localhost;Database=testcase;Integrated Security=SSPI"

As far as I'm aware.. mysql connection strings have port 3306 and some other format.

Have a look at http://www.connectionstrings.com/ for detailed connection strings for various databases.

Also, I am assuming that you are sure that the MySQL Server is running on your computer - it is usually mysqld.

answered Nov 24, 2010 at 6:44

Sekhar

5,8779 gold badges40 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ishan Over a year ago

Thank you, u said is correct than how is that with same connection string my 1st part works alright?

Sekhar Over a year ago

What is your first part? The error is on Line 56(as pointed by your stack trace). and I think It points to conn.Open...

Ishan Over a year ago

I refer my 1st part as, connecting dropdownlist to a database..this works absolutely fine.

Waqas · Accepted Answer · 2010-11-24 06:45:50Z

0

I guess this is because you are executing reader before attaching paramater to command object. Try this

protected void Button1_Click(object sender, EventArgs e) 
    { 
        try 
        { 
            SqlConnection myConn1 = new SqlConnection("Server=localhost;Database=testcase;Integrated Security=SSPI"); 
            SqlCommand myCmd1 = new SqlCommand("select casename,skey from casetype where skey=?", myConn1); 
            myConn1.Open();
String type = DropDownList3.SelectedItem.Text; 
            myCmd1.Parameters.AddWithValue("?", type); 
            SqlDataReader myReader1 = myCmd1.ExecuteReader(); 

        } 
        catch (Exception exw) 
        { 
            Response.Write(exw.StackTrace); 
        } 

    }

answered Nov 24, 2010 at 6:45

Waqas

6,7903 gold badges36 silver badges53 bronze badges

1 Comment

Waqas Over a year ago

Can you also post Error message, i mean exw.Message instead of exw.StackTrace

Kamran Khan · Accepted Answer · 2010-11-24 09:32:09Z

0

You are executing before adding value.

--EDIT--

After your updated question.

Try, following:

string type = DropDownList3.Items[DropDownList3.SelectedIndex].Text;
string commandText = "select casename,skey from casetype where skey=@key;";
using (SqlConnection connection = new SqlConnection("Server=localhost;Database=testcase;Integrated Security=SSPI"))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@key", SqlDbType.Text); //Same as System.String
    command.Parameters["@key"].Value = type ; //Value from your text box!
    //command.Parameters.AddWithValue("@key", type);

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

}

edited Nov 24, 2010 at 9:32

answered Nov 24, 2010 at 6:46

Kamran Khan

9,9842 gold badges33 silver badges41 bronze badges

2 Comments

Ishan Over a year ago

Error 1 'System.Data.SqlDbType' does not contain a definition for 'String'

Ishan Over a year ago

Error 2 The name 'customerID' does not exist in the current context

keremispirli · Accepted Answer · 2015-12-12 16:11:38Z

0

Looks like MySql does not like "SSPI" or "sspi". I tried "true" and it works.

    <add name="ConnStr" providerName="MySql.Data.MySqlClient"
    connectionString="server=localhost;port=3306;database=myDb;Integrated Security=true;"/>

answered Dec 12, 2015 at 16:11

keremispirli

3,5522 gold badges22 silver badges27 bronze badges

Collectives™ on Stack Overflow

Error in asp.net c# code (mysql database connection)

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related