9 Replies - 5808 Views - Last Post: 04 January 2008 - 12:14 PM Rate Topic: -----

#1 bang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 02-January 08

Simple INSERT to database in Visual Studio 2005

Posted 02 January 2008 - 09:11 PM

Okay, I need a Windows Application created in Visual Studio 2005 to be able to insert into a database and view entries from a database. I am very familiar with doing this in ASP.NET, but things aren't as easy when doing a windows app.
I have created one that appears to work, but not correctly.
I have created a windows application project in VS 2005. The Form has a label, textbox and 2 buttons (One to display the contents of the DB and the other to insert). I have also added a DB and created 2 tables with no primary key. I created the DB in VS. When pressed, the Insert button takes the value in the textbox and adds it to the DB and then displays all the contents of the DB.
However, when I enter a value into the textbox and hit insert, it looks like I've added it to the DB, but when I actually open up the DB in VS the values are not there and when I run the program again, the values are not there.

If there is any help that can be given, I would really appreciate it. Whether it be adding to my code or pointing me into another direction.

Here is all my code for the Form

Imports System.Data.Sqlclient
Public Class Form1
	Private ConnString As String
	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		ConnString = "Data Source= .\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ADVENTUREWORKS_DATA.mdf;Integrated Security=True;User Instance=True"
		ReadMyData(ConnString)
	End Sub
	Public Sub ReadMyData(ByVal myConnString As String)
		Dim mySelectQuery As String = "SELECT ProductID, Name FROM Product"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
		Dim StrFiller As String
		myConnection.Open()
		Dim myReader As SqlDataReader
		myReader = myCommand.ExecuteReader()
		' Always call Read before accessing data.
		While myReader.Read()
			StrFiller += ((myReader.GetInt32(0) & ", " & myReader.GetString(1) & Environment.NewLine))

		End While
		Label1.Text = StrFiller
		' always call Close when done reading.
		myReader.Close()
		' Close the connection when done with it.
		myConnection.Close()

	End Sub 'ReadMyData

	Public Sub InsertMyData(ByVal myConnString As String, ByVal NameStr As String)
		Dim mySelectQuery As String = "SELECT ProductId, Name FROM Product"
		Dim myInsertQuery As String = "INSERT into Product(Name, ProductID) VALUES('" & NameStr & "','" & 1110 & "')"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
		Dim myCommand2 As New SqlCommand(myInsertQuery, myConnection)
		Dim retvalue As Integer
		Dim StrFiller As String
		myConnection.Open()
		retvalue = myCommand2.ExecuteNonQuery()
		Label1.Text = retvalue
		Dim myReader As SqlDataReader
		myReader = myCommand.ExecuteReader()
		' Always call Read before accessing data.
		While myReader.Read()
			StrFiller += ((myReader.GetInt32(0) & ", " & myReader.GetString(1) & Environment.NewLine))
		End While
		Label1.Text = StrFiller
		' always call Close when done reading.
		myReader.Close()
		' Close the connection when done with it.
		myConnection.Close()

	End Sub 'ReadMyData

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		
	End Sub

	Private Sub Insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Insert.Click
		Dim NameStr As String
		Dim id As Integer
		NameStr = Me.TextBox1.Text
		'ConnString = "Data Source= .\SQLEXPRESS;Initial Catalog = C:\ADVENTUREWORKS_DATA.mdf;Integrated Security=True;User Instance=True;"
		ConnString = "Data Source= .\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ADVENTUREWORKS_DATA.mdf;Integrated Security=True;User Instance=True"
		InsertMyData(ConnString, NameStr)
	End Sub

	Private Sub exitbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitbtn.Click
		Me.Close()
	End Sub
End Class





Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Simple INSERT to database in Visual Studio 2005

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Simple INSERT to database in Visual Studio 2005

Posted 02 January 2008 - 09:32 PM

Well first, I would change my connection string to

	
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;



When you use User Instance = True; you're creating a new SQL Server instance on the fly, thus not touching the MDF you have in the DataDirectory. Try that and see what happens
Was This Post Helpful? 0
  • +
  • -

#3 bang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 02-January 08

Re: Simple INSERT to database in Visual Studio 2005

Posted 02 January 2008 - 10:09 PM

Okay, I changed the connection string in the Insert button to what you suggested and I get this error when I try to input a value:

Directory lookup for the file "C:\..my project path..\bin\Debug\ADVENTUREWORKS_DATA.mdf" failed with the operating system error 5(Access is denied.).
Cannot attach the file 'C:\..my project path..\bin\Debug\ADVENTUREWORKS_DATA.mdf' as database 'ADVENTUREWORKS_DATA'.



and here's the code that I changed.

	
ConnString = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\ADVENTUREWORKS_DATA.mdf; Database=ADVENTUREWORKS_DATA;Trusted_Connection=Yes;"



Let me know if I made an error in the change.

Thanks.
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Simple INSERT to database in Visual Studio 2005

Posted 02 January 2008 - 11:11 PM

Well I suggested a connection string of

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;



Which is one of 2 or 3 correct connection strings for SQLExpress, check out ConnectionStrings.com for more options. Using UserInstance does indeed create a new instance of SQL server on the fly so when you insert thats the DB you're inserting into, not your database.

In a windows application the MDF (your SQLExpress DB file) should go in the Bin folder
Was This Post Helpful? 0
  • +
  • -

#5 bang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 02-January 08

Re: Simple INSERT to database in Visual Studio 2005

Posted 03 January 2008 - 04:26 PM

I tried several connection strings, but I am still getting some errors:

with this string:
ConnString = "Server=.\SQLExpress\|DataDirectory|\ADVENTUREWORKS_DATA.mdf;Database=ADVENTUREWORKS_DATA;Trusted_Connection=yes;"



I get this error
SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)



I have tried multiple strings but I am still getting either the same error as previous post or the one above.

Please forgive my ignorance, is there a setting I am not enabling or permission I am not giving?

Thanks.
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Simple INSERT to database in Visual Studio 2005

Posted 03 January 2008 - 05:21 PM

You need to enable remote connections in SQL Server.

You can find instructions on how to do that at the following link.

http://www.datamaske..._NetworkCfg.htm
Was This Post Helpful? 0
  • +
  • -

#7 bang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 02-January 08

Re: Simple INSERT to database in Visual Studio 2005

Posted 04 January 2008 - 10:45 AM

View Postjayman9, on 3 Jan, 2008 - 05:21 PM, said:

You need to enable remote connections in SQL Server.

You can find instructions on how to do that at the following link.

http://www.datamaske..._NetworkCfg.htm


Stupid question, I used Visual Studio to create the database. I don't have SQL Server installed on my machine. I have looked everywhere for a step-by-step on how to do a simple insert but have yet to find anything. If you have link to a tutorial, that would be great.

Thanks for the replies btw.
Was This Post Helpful? 0
  • +
  • -

#8 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Re: Simple INSERT to database in Visual Studio 2005

Posted 04 January 2008 - 11:22 AM

Hi Bang

I Have been using VB express 2008 to create a database. One thing I Know is you can write data to the tables but it will not save the data to that database, why I dont Know. For it to work correctly you need to publish the application.

Hopes this helps
Was This Post Helpful? 0
  • +
  • -

#9 sharpy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 36
  • View blog
  • Posts: 394
  • Joined: 02-June 07

Re: Simple INSERT to database in Visual Studio 2005

Posted 04 January 2008 - 11:32 AM

View Postbang, on 4 Jan, 2008 - 06:45 PM, said:

Stupid question, I used Visual Studio to create the database. I don't have SQL Server installed on my machine. I have looked everywhere for a step-by-step on how to do a simple insert but have yet to find anything. If you have link to a tutorial, that would be great.

Thanks for the replies btw.


I Have tried to use code with my database created in VB Express but is does not work correctly when you publish the app.

Have alook at these videos

http://msdn2.microso...c/bb466226.aspx
Was This Post Helpful? 0
  • +
  • -

#10 bang  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 02-January 08

Re: Simple INSERT to database in Visual Studio 2005

Posted 04 January 2008 - 12:14 PM

It actually worked when I published it. I added a new row, closed it and reopened it. The new row was there.
However, this is a real tedious task and I'll take a look at those videos. Thanks for the replies.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1