Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP.NET connect MySQL

Posted on 2004-09-30
12
1,010 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASCX file or a newer alternative? 1 40
mail.To.Add 5 33
Example works but why do I need to click twice on button 4 48
Function after success of Jquery/AJAX action 9 27
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…

807 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