ساخت بانک اکسس با کد

[mortezamhd]

سلام

چجوری میتونم بانک و جدول و فیلدو .. رو از طریق کد بسازم ؟

[hadiit]

لزومي براي اين كار وجود داره!!!!!!!!!!!!!!!!!!!!!!!

[milad.biroonvand]

سلام دوست عزیز


با دستور cmd as new oledbcommand

همونطور که یک سطر می تونی به جدول اضافه کنی ، یا یک پرس جو انجام بدی .


strsql="create database bank"

شما باید از دستور create database استفاده کنی ، اگه خواستی کد کاملش رو برات می زارم .

[milad.biroonvand]

از این کد استفاده کن


To test this application, create a Widows application add a data grid control and some button controls. You can even test code by adding only one button or one button for each activity. Our application form looks like Figure 1.

http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/Images/CreatingDBProgrammaticallyM.jpg
Figure 1. Creating a database and it's object application.
After adding controls, add the following variables in the beginning of the form class.

Private ConnectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=localhost;"
Private reader As SqlDataReader = Nothing
Private conn As SqlConnection = Nothing
Private cmd As SqlCommand = Nothing
Private AlterTableBtn As System.Windows.Forms.Button
Private sql As String = Nothing
Private CreateOthersBtn As System.Windows.Forms.Button
Private button1 As System.Windows.Forms.Button

First thing I'm going to do is create ExecuteSQLStmt method. This method executes a SQL statement against the SQL Sever database (mydb which I will create from my program) using Sql data providers using ExecuteNonQuery method. The ExecuteSQLStmt method is listed in Listing 1.
Private Sub ExecuteSQLStmt(ByVal sql As String)
' Open the connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;"
conn.ConnectionString = ConnectionString
conn.Open()
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
Catch ae As SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub 'ExecuteSQLStmt
Listing 1. The ExecuteSQLStmt method.
After this I'm going to create a new SQL Server database. The CREATE DATABASE SQL statement creates a database. The syntax of CREATE DATABASE depends on the database you create. Depending on the database type, you can also set values of database size, growth and file name. Listing 2 creates a SQL Server database mydb and data files are stored in the C:\\mysql directory.
'This method creates a new SQL Server database
Private Sub CreateDBBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Create a connection
conn = New SqlConnection(ConnectionString)
' Open the connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim sql As String = "CREATE DATABASE mydb ON PRIMARY" + "(Name=test_data, filename = 'C:\mysql\mydb_data.mdf', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log, filename='C:\mysql\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)"
ExecuteSQLStmt(sql)
End Sub 'CreateDBBtn_Click
Listing 2. Creating a SQL Server database.
Now next step is to create a table. You use CREATE TABLE SQL statement to create a table. In this statement you define the table and schema (table columns and their data types). Listing 3 creates a table myTable with four column listed in Table 1.
Column NameTypeSizePropertymyIdinteger4 Primary Key myNamechar50Allow NullmyAddresschar255Allow NullmyBalancefloat8Allow Null

Table 1. New table myTable schema.

Private Sub CreateTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Open the connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;"
conn.ConnectionString = ConnectionString
conn.Open()
sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)"
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
' Adding records the table
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
Catch ae As SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub 'CreateTableBtn_Click
Listing 4. Creating a database table.

As you can see from Listing 5, I also add data to the table using INSERT INTO SQL statement.
The CREATE PROCEDURE statement creates a stored procedure as you can see in Listing 10-18, I create a stored procedure myPoc which returs data result of SELECT myName and myAddress column.
Private Sub CreateSPBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
sql = "CREATE PROCEDURE myProc AS" + " SELECT myName, myAddress FROM myTable GO"
ExecuteSQLStmt(sql)
End Sub 'CreateSPBtn_Click

Listing 5. Creating a stored procedure programmatically.

Now I show you how to create views programmatically using CREATE VIEW SQL statement. As you can see from Listing 6, I create a view myView which is result of myName column rows from myTable.
Private Sub CreateViewBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
sql = "CREATE VIEW myView AS SELECT myName FROM myTable"
ExecuteSQLStmt(sql)
End Sub 'CreateViewBtn_Click
Listing 6. Creating a view using CREATE VIEW
The ALTER TABLE is a useful SQL statement if you need to change your database schema programmatically. The ALTER TABLE statement can be used to add and remove new columns to a table, changing column properties, data types and constraints. The Listing 7 show that I change the database schema of myTable by first change column data type range from 50 to 100 characters and by adding a new column newCol of TIMESTAMP type.

Private Sub AlterTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
sql = "ALTER TABLE MyTable ALTER COLUMN" + "myName CHAR(100) NOT NULL"
ExecuteSQLStmt(sql)
End Sub 'AlterTableBtn_Click
Listing 7. Using ALTER TABLE to change a database schema programmatically.


The new table schema looks like Table 2.

Column NameTypeSizePropertymyIdinteger4 Primary Key myNamechar50Allow NullmyAddresschar255Allow NullmyBalancefloat8Allow NullnewColtimestamp8Allow Null

Table 2. MyTable after ALTER TABLE
You can also create other database object such as index, rule, and users. The code listed in Listing 8 creates one rule and index on myTable.
Note: Create Index can only create an index if you don't have an index on a table. Otherwise you will get an error message.
Private Sub CreateOthersBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
sql = "CREATE UNIQUE CLUSTERED INDEX " + "myIdx ON myTable(myName)"
ExecuteSQLStmt(sql)
sql = "CREATE RULE myRule " + "AS @myBalance >= 32 AND @myBalance < 60"
ExecuteSQLStmt(sql)
End Sub 'CreateOthersBtn_Click
Listing 8. Creating rules and indexes using SQL statement.
The DROP TABLE command can be used to delete a table and its data permanently. The code listed in Listing 9 deletes myTable.
Private Sub DropTableBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sql As String = "DROP TABLE MyTable"
ExecuteSQLStmt(sql) '
End Sub 'DropTableBtn_Click

Listing 9. Deleting table using DROP TABLE.

Now next step is to view data from the table, view and stored procedure. The ViewDataBtn_Click method listed in Listing 10 shows the entire data from the table. The ViewSPBtn_Click and ViewViewBtn_Click methods view stored procedure and view data we have created earlier. As you can see using views and stored procedures work same as you use a SQL Statement. We have discussed working with Views and stored procedures in the beginning of this chapter. As you can see from Listing 10, 11, and 12, I view data from stored procedure and view.
Private Sub ViewDataBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'/ Open the connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;"
conn.ConnectionString = ConnectionString
conn.Open()
' Create a data adapter
Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)
' Create DataSet, fill it and view in data grid
Dim ds As New DataSet("myTable")
da.Fill(ds, "myTable")
dataGrid1.DataSource = ds.Tables("myTable").DefaultView
End Sub 'ViewDataBtn_Click
Listing 10. Viewing data from a database table.

Private Sub ViewSPBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'/ Open the connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;"
conn.ConnectionString = ConnectionString
conn.Open()
' Create a data adapter
Dim da As New SqlDataAdapter("myProc", conn)
' Create DataSet, fill it and view in data grid
Dim ds As New DataSet("SP")
da.Fill(ds, "SP")
dataGrid1.DataSource = ds.DefaultViewManager
End Sub 'ViewSPBtn_Click
Listing 11.Using a stored procedure to view data from a table.

Private Sub ViewViewBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Open the connection
If conn.State = ConnectionState.Open Then
conn.Close()
End If
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=localhost;"
conn.ConnectionString = ConnectionString
conn.Open()
' Create a data adapter
Dim da As New SqlDataAdapter("SELECT * FROM myView", conn)
' Create DataSet, fill it and view in data grid
Dim ds As New DataSet
da.Fill(ds)
dataGrid1.DataSource = ds.DefaultViewManager
End Sub 'ViewViewBtn_Click
Listing 12.Using a view to view data from a table.
Finally, I create AppExit method which releases the connection and reader objects and I call them from the Dispose method as you can see in Listing 13.
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
AppExit()
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub 'Dispose
' Called when you are done with the applicaton
' Or from Close button
Private Sub AppExit()
If Not (reader Is Nothing) Then
reader.Close()
End If
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Sub 'AppExit

[milad.biroonvand]

از این هم می تونی کمک بگیری

[mortezamhd]

hadiit (http://barnamenevis.org/forum/member.php?u=64371) :
سلام
میشه بگین چرا لزومی برای این کار وجود نداره ؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟؟

[saadi2]

با sql خیلی راحت تر میشه این کارو انجام داد ولی با اکسس فک کنم باید از component های آفیس استفاده کرد که من نمیدونم. در ضمن کار کردن با خود access برای ایجاد دیتابیس و تیبلها خیلی راحته .تا اینکه بخوای اونارو با کد ایجاد کنی

[mortezamhd]

با sql خیلی راحت تر میشه این کارو انجام داد ولی با اکسس فک کنم باید از component های آفیس استفاده کرد که من نمیدونم. در ضمن کار کردن با خود access برای ایجاد دیتابیس و تیبلها خیلی راحته .تا اینکه بخوای اونارو با کد ایجاد کنی
سلام
مرسی از راهنماییت
آخه میخوام کاربر خودش بانکو از طریق برنامه بسازه
شما گفتید "با sql خیلی راحت تر میشه این کارو انجام داد" میشه طریقه ساخت شو بزارید
ممنون

[saadi2]

برنامه ای که milad گذاشته خیلی واضحه .بازم ببین اگه متوجه نشدی برات یه نمونه میزارم

[mehrdad hamid reza]

برنامه ای که milad گذاشته خیلی واضحه .بازم ببین اگه متوجه نشدی برات یه نمونه میزارم

اگر یک نمونه با access بزارید خیلی ممنون میشم

[mehrdad hamid reza]

اگر یک نمونه با access بزارید خیلی ممنون میشم

نمونه دیگه ای نداری من با این کد به مشکل خوردم

Try
Dim com As OleDbCommand
Dim cnn As OleDbConnection
Dim b As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\h\Desktop\New Folder\db.accdb"
cnn = New OleDbConnection(b)
Dim a As String = "create table" + TextBox1.Text + "(Field1 text(60), Field2 text(10), Field3 text(100) , Field4 text(10) "
com = New OleDbCommand(a, cnn)
cnn.Open()
com.ExecuteNonQuery()
cnn.Close()
MsgBox("ok")
Catch ex As OleDb.OleDbException
MsgBox(ex.Message)
End Try

[saadi2]

کدهای بالا فقط در sql جواب میده
وقت کردم یه نمونه sql برات مینویسم