10 Replies - 1298 Views - Last Post: 27 May 2013 - 03:48 PM Rate Topic: -----

#1 Joni_78  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 24-November 12

VS2012 Local SQL Database

Posted 26 May 2013 - 03:34 AM

I once did a website, it used SQL server 2008 for the database. I've tried to change it to use the local SQL database but everytime I get error on
con.Open();
. Any ideas what i'm missing.

I've added this to web.config
<connectionStrings>
    <add name ="Movies" connectionString="Data source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDatabase.mdf;Integrated Security=True;User Instance=True"/>
  </connectionStrings>


This is how I try to get the data to ListBox from "Movies" table
string conStr = WebConfigurationManager.ConnectionStrings["Movies"].ConnectionString;
    private void Movie()
    {
        SqlConnection con = null;
        SqlCommand cmd = null;

        string sql = "Select * from Name";
        int selected = lbList.SelectedIndex;
        lbList.Items.Clear();
        using (con = new SqlConnection(conStr))
        {
            cmd = con.CreateCommand();
            con.Open();
            cmd.CommandText = sql;
            SqlDataReader dr = null;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string name = (string)dr["Name"];
                lbList.Items.Add(name);
            }
            dr.Close();
            con.Close();
        }
        lbList.SelectedIndex = selected;
    }


Is This A Good Question/Topic? 0
  • +

Replies To: VS2012 Local SQL Database

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 26 May 2013 - 04:01 AM

Any chance you could tell us what the error is :whistling:
Was This Post Helpful? 0
  • +
  • -

#3 Joni_78  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 24-November 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 08:05 AM

View Postandrewsw, on 26 May 2013 - 04:01 AM, said:

Any chance you could tell us what the error is :whistling:/>/>


Got it working, had to use System.Data.SqlServerCe for local db.

One other thing I can't figure out. I got this, apparently it should delete movie that is currently selected on the listbox, but it throws error on cmd.ExecuteNonQuery(), something about the Input string was not in a correct format. It works if I get MovieID into label, then use cmd.Parameters.AddWithValue("@Id", lblID.Text) in code below. Should this work without needing to get the MovieID to label, so that I could delete what is selected on the listbox?

private void Delete(string ID)
    {


        SqlCeConnection connection = new SqlCeConnection(GetConnectionString());
        string sqlStatement = "DELETE FROM Movies WHERE MovieID = @Id";

        try
        {
            connection.Open();
            SqlCeCommand cmd = new SqlCeCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@Id", ID);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Deletion Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }


protected void Button5_Click(object sender, EventArgs e)
    {
        if (lb1.SelectedItem.Text == null)
        {
            //blaablaablaa
        }
        else
        {
            for (int i = 0; i <= lb1.Items.Count - 1; i++)
            {
                if (lb1.Items[i].Selected)
                {
                    Delete(lb1.Items[i].Value.ToString());
                }
            }
            string remove = lb1.SelectedItem.Text;
            lb1.Items.Remove(remove);
        }

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 08:26 AM

Quote

something about the Input string was not in a correct format

Again, you should post the precise error message, together with confirmation as to which line it refers to in your posted code. But you haven't posted the code that doesn't work. It is difficult to guess why it would not have worked.

I would guess, however, that id is a number and you are not passing the correct value (from the listbox) to your Delete() function.

This post has been edited by andrewsw: 27 May 2013 - 08:30 AM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 08:35 AM

BTW ExecuteNonQuery returns an integer for a DELETE query, indicating the number of rows affected. You could check whether this value is 1 rather than relying on error handling. The error handling would notify of some failure, but not if it just didn't delete any rows (or deleted too many).

BTWW I would rename your function to something more descriptive like DeleteMovie(); as your application grows you may end up with a few Delete methods.

This post has been edited by andrewsw: 27 May 2013 - 08:36 AM

Was This Post Helpful? 0
  • +
  • -

#6 Joni_78  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 24-November 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 09:35 AM

View Postandrewsw, on 27 May 2013 - 08:35 AM, said:

BTW ExecuteNonQuery returns an integer for a DELETE query, indicating the number of rows affected. You could check whether this value is 1 rather than relying on error handling. The error handling would notify of some failure, but not if it just didn't delete any rows (or deleted too many).

BTWW I would rename your function to something more descriptive like DeleteMovie(); as your application grows you may end up with a few Delete methods.

I'll check that. VS gives errors on my native language, it's stupid that everything in VS is english but they desided to translate the error messages :/
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 09:48 AM

View PostJoni_78, on 27 May 2013 - 04:35 PM, said:

I'll check that. VS gives errors on my native language, it's stupid that everything in VS is english but they desided to translate the error messages :/

Ah, right! Now I see your reluctance ;). There is Google Translate I suppose, but it doesn't do very well with technical terms.

This post has been edited by andrewsw: 27 May 2013 - 09:50 AM

Was This Post Helpful? 0
  • +
  • -

#8 Joni_78  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 24-November 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 03:07 PM

The problem was that I didn't bind the MovieID from SQL into listbox. That gives yet another problem, as I haven't worked with databinding before, and i've googled and tried for couple of hours.

This is the binding to listbox part:

public void BindListBoxData()
    {
        using (SqlCeConnection conn = new SqlCeConnection(GetConnectionString()))
        {
            try
            {
                conn.Open();
                SqlCeCommand sql = new SqlCeCommand("Select MovieID, Title from Movies", conn);
                SqlCeDataAdapter mySqlDataAdapter = new SqlCeDataAdapter(sql);
                DataSet ds = new DataSet();
                mySqlDataAdapter.Fill(ds);
                lb1.DataSource = ds;
                lb1.DataTextField = "Title";
                lb1.DataValueField = "MovieID";
                lb1.DataBind();
            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
    }


Now my old code for example this (add data to textboxes) doesn't work. I think that it can't get the selected item anymore because of the above code. Replacing " + selected + " with Gladiator, it gets all data for that movie into textboxes.

protected void btnDetails_Click(object sender, EventArgs e)
    {  
        string selected = lb1.SelectedValue;
        string sql = "Select * from Movies where Title='" + selected + "'";
        using (SqlCeConnection conn = new SqlCeConnection(GetConnectionString()))
        {
            try
            {
                conn.Open();
                SqlCeCommand cmd = new SqlCeCommand(sql, conn);
                SqlCeDataReader dr = null;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    tbTitle.Text = (string)dr["Title"];
                    tbDirector.Text = (string)dr["Director"];
                    ...
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
    }


How to get the selected item working with binded listbox?
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 03:20 PM

Not sure. Do you receive errors?

tbTitle.Text = (string)dr["Title"];

This code and the following line are over-writing the text in the textbox each time, so it will end up displaying only the last retrieved value.

Just to confirm, this is ASP.NET? (Which I don't use.) I believe it is (WebConfigurationManager). However, the principles are the same as C# WinForms.

Do you know how to debug and step through btnDetails_Click? There is a tutorial in my signature; it's not for ASP though but, again, similar.
Was This Post Helpful? 0
  • +
  • -

#10 Joni_78  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 24-November 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 03:29 PM

View Postandrewsw, on 27 May 2013 - 03:20 PM, said:

Not sure. Do you receive errors?

tbTitle.Text = (string)dr["Title"];

This code and the following line are over-writing the text in the textbox each time, so it will end up displaying only the last retrieved value.

Just to confirm, this is ASP.NET? (Which I don't use.) I believe it is (WebConfigurationManager). However, the principles are the same as C# WinForms.

Do you know how to debug and step through btnDetails_Click? There is a tutorial in my signature; it's not for ASP though but, again, similar.


Thank you! That wasn't the problem but your post made me realize what it was. :)
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3524
  • View blog
  • Posts: 12,031
  • Joined: 12-December 12

Re: VS2012 Local SQL Database

Posted 27 May 2013 - 03:48 PM

View PostJoni_78, on 27 May 2013 - 10:29 PM, said:

Thank you! That wasn't the problem but your post made me realize what it was. :)

Care to share, for someone else reading this?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1