EDN Admin
Well-known member
<pre><span style="font-family:Segoe UI,Lucida Grande,Verdana,Arial,Helvetica,sans-serif; font-size:13px; white-space:normal; color:#333333; line-height:16px <p style=" i try to make SQL programmatically <p style=" <p style=" but in this site http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx <p style=" <p style=" i can create the database and table name <p style=" <p style=" how to insert data to table ? <p style=" <p style=" thank you <p style=" <p style=" this is my source code [/code]
<pre>Public Class Form1
Private ConnectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=johnson;"
Private reader As SqlClient.SqlDataReader = Nothing
Private conn As New SqlClient.SqlConnection
Private cmd As SqlClient.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
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=;" + "Data Source=.;"
conn.ConnectionString = ConnectionString
conn.Open()
cmd = New SqlClient.SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
Catch ae As SqlClient.SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub ExecuteSQLStmt
This method creates a new SQL Server database
Private Sub CreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateDB.Click
TextBox1.Text = NamaDB
Create a connection
conn = New SqlClient.SqlConnection(ConnectionString)
Open the connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim sql As String = "CREATE DATABASE NamaDB ON PRIMARY" + "(Name=test_data, filename = C
rogram FilesMicrosoft SQL ServerMSSQLDataNamaDB_data.mdf, size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log, filename=Crogram FilesMicrosoft SQL ServerMSSQLDataNamaDB_log.ldf,size=3," + "maxsize=20,filegrowth=1)"
ExecuteSQLStmt(sql)
End Sub CreateDBBtn_Click
Private Sub CreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTable.Click
Try
sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)" & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1001, Puneet Nehra, A 449 Sect 19, DELHI, 23.98 ) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1002, Anoop Singh, Lodi Road, DELHI, 353.64) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, Rakesh M, Nag Chowk, Jabalpur M.P., 43.43) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, Madan Kesh, 4th Street, Lane 3, DELHI, 23.00) "
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=NamaDB;" + "Data Source=johnson;"
conn.ConnectionString = ConnectionString
conn.Close()
conn.Open()
cmd = New SqlClient.SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Sub
End Class
[/code]
<br/>
View the full article
<pre>Public Class Form1
Private ConnectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=;" + "Data Source=johnson;"
Private reader As SqlClient.SqlDataReader = Nothing
Private conn As New SqlClient.SqlConnection
Private cmd As SqlClient.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
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=;" + "Data Source=.;"
conn.ConnectionString = ConnectionString
conn.Open()
cmd = New SqlClient.SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
Catch ae As SqlClient.SqlException
MessageBox.Show(ae.Message.ToString())
End Try
End Sub ExecuteSQLStmt
This method creates a new SQL Server database
Private Sub CreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateDB.Click
TextBox1.Text = NamaDB
Create a connection
conn = New SqlClient.SqlConnection(ConnectionString)
Open the connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim sql As String = "CREATE DATABASE NamaDB ON PRIMARY" + "(Name=test_data, filename = C
rogram FilesMicrosoft SQL ServerMSSQLDataNamaDB_data.mdf, size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log, filename=Crogram FilesMicrosoft SQL ServerMSSQLDataNamaDB_log.ldf,size=3," + "maxsize=20,filegrowth=1)"ExecuteSQLStmt(sql)
End Sub CreateDBBtn_Click
Private Sub CreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTable.Click
Try
sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)" & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1001, Puneet Nehra, A 449 Sect 19, DELHI, 23.98 ) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1002, Anoop Singh, Lodi Road, DELHI, 353.64) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, Rakesh M, Nag Chowk, Jabalpur M.P., 43.43) " & vbCrLf
sql = sql & "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, Madan Kesh, 4th Street, Lane 3, DELHI, 23.00) "
ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=NamaDB;" + "Data Source=johnson;"
conn.ConnectionString = ConnectionString
conn.Close()
conn.Open()
cmd = New SqlClient.SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
End Sub
End Class
[/code]
<br/>
View the full article