Solved

ASP.NET connect MySQL

Posted on 2004-09-30
12
999 Views
Last Modified: 2013-11-25
i need to know how to connect the MySQL in webserver from my ASP.NET program and how to close that connection.

if i need some sample for this.

thanks
0
Comment
Question by:Viji4u
  • 3
  • 3
  • 2
  • +1
12 Comments
 

Author Comment

by:Viji4u
ID: 12197444
i know the followings...
$dbhost  
$dbport
$user
$pass
$database

MySQL is in MyPHPAdmin.
0
 
LVL 6

Expert Comment

by:viola123
ID: 12197609
<%@ Page Language="C#" AutoEventWireup="False"
  EnableSessionState="False" EnableViewState="False" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Odbc" %>

<script runat="server">
  private const string ConnStr = "Driver={MySQL ODBC 3.51 Driver};" +
    "Server=localhost;Database=test;uid=root;pwd=yourpassword;option=3";

  protected override void OnInit(EventArgs e)
  {
    base.OnInit(e);

    using(OdbcConnection con = new OdbcConnection(ConnStr))
    using(OdbcCommand cmd = new OdbcCommand("SELECT * FROM Names", con))
    {
      con.Open();
      dgrAllNames.DataSource = cmd.ExecuteReader(
        CommandBehavior.CloseConnection |
        CommandBehavior.SingleResult);
      dgrAllNames.DataBind();
    }
  }
</script>

<html>
<head>
  <title>Displaying Records from MySQL 'Names' table</title>
  <style>
  body { font: 100% Verdana; }
  </style>
</head>
<body>

<p align="center">All records in the 'Names' table:</p>

<asp:DataGrid ID="dgrAllNames" HorizontalAlign="Center"
      CellPadding="3" Runat="server" />

</body>
</html>

regards
viola
0
 
LVL 6

Expert Comment

by:viola123
ID: 12197614
0
 

Author Comment

by:Viji4u
ID: 12197881
hi viola
thanks for u r answer.

i tried these above 2 answers,but  its not working.

MySQL is in MyPHPAdmin webserver. ( URL  http://c1.ibone.ch/phpMyAdmin/index.php )

so how can i connect to that webserver database.

anything using Page Language in VB.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Accepted Solution

by:
viola123 earned 75 total points
ID: 12199029
hi,

i tested the code just now and it is working well. u probably failed to set the right server.
please change the localhost in my code to http://c1.ibone.ch or 217.150.243.4

i will try to find a VB code. however, it should be quite similar because they both use the same .net object(eg. odbcConnection)

if still not working, do u mind post the error message here?

regards,
viola
0
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 75 total points
ID: 12199062
use the bytefx data provider for MySQL
http://www.bytefx.com/DotData.aspx

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            If Request.QueryString("pid") <> "" Then
                Me.lblProductID.Text = Request.QueryString("pid")
                LoadProductData()
            End If
        End If
    End Sub
    Private Sub LoadProductData()
        Dim cnn As New ByteFX.Data.MySqlClient.MySqlConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New ByteFX.Data.MySqlClient.MySqlCommand
        Dim dr As ByteFX.Data.MySqlClient.MySqlDataReader
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM Products WHERE ProductID = " & Me.lblProductID.Text.ToString
        cmd.Connection = cnn
        cnn.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While dr.Read
            Me.imgProduct.ImageUrl = "~/Pictures/" & dr.GetString(1).ToString
            Me.txtSKU.Text = dr.GetString(11).ToString
            Me.txtItemName.Text = dr.GetString(7).ToString
            Me.txtALTImage.Text = dr.GetString(2).ToString
            Me.txtDescription.Text = dr.GetString(8).ToString
            Me.txtPrice.Text = dr.GetString(9).ToString
            Me.txtWeight.Text = dr.GetString(12).ToString
            If dr.GetString(13).ToString = "True" Then
                rdoEnabled.Checked = True
            Else
                rdoDisabled.Checked = True

            End If
        End While
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
    End Sub
    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Dim cnn As New ByteFX.Data.MySqlClient.MySqlConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New ByteFX.Data.MySqlClient.MySqlCommand
        Dim IncomingFile As IO.FileInfo
        Dim ImageSource, PriceValue As String
        Dim ImageResizer As New ResizeImage(True, 268, 452)
        ImageResizer.ThumbX = 130
        ImageResizer.ThumbY = 219
        'Me.RequiredFieldValidator1.Enabled = True
        'Me.RequiredFieldValidator2.Enabled = True
        'Me.RequiredFieldValidator3.Enabled = True
        'Me.RequiredFieldValidator4.Enabled = True
        'Me.RequiredFieldValidator5.Enabled = True
        'Page.Validate()
        'If Me.IsValid Then
        If Not ImageSrc.PostedFile Is Nothing And ImageSrc.PostedFile.ContentLength > 0 Then
            IncomingFile = New IO.FileInfo(Request.Files(0).FileName)
            IncomingFile = New IO.FileInfo(Server.MapPath("~/Pictures/") & IncomingFile.Name)
            Request.Files(0).SaveAs(IncomingFile.FullName)
            ImageResizer.ResizeImage(IncomingFile.FullName)
            ImageSource = IncomingFile.Name
        Else
            ImageSource = "ImageSrc"
        End If
        cmd.CommandType = CommandType.Text
        If Me.rdoUseCurrent.Checked Then
            If Me.rdoEnabled.Checked Then
                cmd.CommandText = "UPDATE Products SET SKU = '" & Me.txtSKU.Text.ToString & "', " & _
       "ItemName = '" & Me.txtItemName.Text.ToString & "', ImageDesc = '" & Me.txtALTImage.Text.ToString & "', " & _
       "Description = '" & Me.txtDescription.Text.ToString & "', Price = " & Me.txtPrice.Text.ToString & ", Weight = " & _
       Me.txtWeight.Text.ToString & ", Enabled = 'True'" & " WHERE ProductID = " & Me.lblProductID.Text.ToString
                cmd.Connection = cnn
                cnn.Open()
                cmd.ExecuteNonQuery()
            ElseIf Me.rdoDisabled.Checked Then
                cmd.CommandText = "UPDATE Products SET SKU = '" & Me.txtSKU.Text.ToString & "', " & _
       "ItemName = '" & Me.txtItemName.Text.ToString & "', ImageDesc = '" & Me.txtALTImage.Text.ToString & "', " & _
       "Description = '" & Me.txtDescription.Text.ToString & "', Price = " & Me.txtPrice.Text.ToString & ", Weight = " & _
       Me.txtWeight.Text.ToString & ", Enabled = 'False'" & " WHERE ProductID = " & Me.lblProductID.Text.ToString
                cmd.Connection = cnn
                cnn.Open()
                cmd.ExecuteNonQuery()
            End If
        ElseIf Me.rdoUploadNew.Checked Then
            If Me.rdoEnabled.Checked Then
                cmd.CommandText = "UPDATE Products SET ImageSrc = '" & ImageSource & "', SKU = '" & Me.txtSKU.Text.ToString & "', " & _
           "ItemName = '" & Me.txtItemName.Text.ToString & "', ImageDesc = '" & Me.txtALTImage.Text.ToString & "', " & _
           "Description = '" & Me.txtDescription.Text.ToString & "', Price = " & Me.txtPrice.Text.ToString & ", Weight = " & _
           Me.txtWeight.Text.ToString & ", Enabled = 'True'" & " WHERE ProductID = " & Me.lblProductID.Text.ToString
                cmd.Connection = cnn
                cnn.Open()
                cmd.ExecuteNonQuery()
            ElseIf Me.rdoDisabled.Checked Then
                cmd.CommandText = "UPDATE Products SET ImageSrc = '" & ImageSource & "', SKU = '" & Me.txtSKU.Text.ToString & "', " & _
       "ItemName = '" & Me.txtItemName.Text.ToString & "', ImageDesc = '" & Me.txtALTImage.Text.ToString & "', " & _
       "Description = '" & Me.txtDescription.Text.ToString & "', Price = " & Me.txtPrice.Text.ToString & ", Weight = " & _
       Me.txtWeight.Text.ToString & ", Enabled = 'False'" & " WHERE ProductID = " & Me.lblProductID.Text.ToString
                cmd.Connection = cnn
                cnn.Open()
                cmd.ExecuteNonQuery()
            End If
        End If
        cnn.Close()
        cmd.Dispose()
        cnn.Dispose()
        Response.Redirect("ManageProducts.aspx")
        'End If
    End Sub
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12199087
connection string in web.config would look like so:
<appSettings>
            <!--<add key="CyToys" value="DSN=cytoys.com;" /> -->
            <add key="SiteDB" value="Persist Security Info=False;database=cytoys;server=XX.XXX.XXX.XX;user id=XXXt;pwd=XXXXXX"></add>
      </appSettings>

if you use the bytefxdata provider its pretty much like using regular mssql

Regards,

Aeros
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12199144
For help on connection strings: www.connectionstrings.com
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12394546
if  viola123  agrees I recomend a split.

Aeros
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now