VB.NET中如何用SQL語句建表（復(fù)制表）？謝謝了！

分類: 電腦/網(wǎng)絡(luò) 程序設(shè)計(jì) 其他編程語言

在源城等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設(shè) 網(wǎng)站設(shè)計(jì)制作按需定制制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),成都營銷網(wǎng)站建設(shè),成都外貿(mào)網(wǎng)站建設(shè),源城網(wǎng)站建設(shè)費(fèi)用合理。

問題描述:

在SQL數(shù)據(jù)庫里已有一個(gè)現(xiàn)成的空數(shù)據(jù)表（只有字段、沒有數(shù)據(jù)），請問高手，怎樣在VB.NET中用SQL語句把該數(shù)據(jù)庫中的那個(gè)表復(fù)制一個(gè)到該數(shù)據(jù)庫（字段不變、數(shù)據(jù)為空）只是把數(shù)據(jù)表的名改了？

謝謝！謝謝！

解析:

select * into 新表 from 舊表

使用 SELECT INTO 插入行

SELECT INTO 語句創(chuàng)建一個(gè)新表，并用 SELECT 的結(jié)果集填充該表。新表的結(jié)構(gòu)由選擇列表中表達(dá)式的特性定義，例如：

SELECT Shippers.*, Link.Address, Link.City,

Link.Region, Link.PostalCode

INTO NewShippers

FROM Shippers

JOIN LinkServer.DB.dbo.Shippers AS Link

ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO 可將幾個(gè)表或視圖中的數(shù)據(jù)組合成一個(gè)表。也可用于創(chuàng)建一個(gè)包含選自鏈接服務(wù)器的數(shù)據(jù)的新表。

vb.net寫入或新增 SQL 等 代碼應(yīng)該如何實(shí)現(xiàn) 求分享求教

'引入

Imports?System.Data

Imports?System.Data.SqlClient

'數(shù)據(jù)庫配置

Dim?sql_conn?As?New?SqlConnection("server=ip;database=tb1;user?id=sa;password=ps")

'?過程

Dim?add_m?As?New?SqlCommand

add_m.Connection?=?sql_conn

add_m.CommandText?=?"insert?into?tb1(t1,t2)?values(@t1,@t2)"

add_m.Parameters.AddWithValue("@t1",TEXTBOX1.text)

add_m.Parameters.AddWithValue("@t2",TEXTBOX2.text)?

sql_conn.Open()

add_m.ExecuteNonQuery()

sql_conn.Close()

我想在vb.net的textbox里調(diào)用sql表中的數(shù)據(jù)，怎么辦？

分類: 電腦/網(wǎng)絡(luò) 程序設(shè)計(jì) 其他編程語言

解析:

Visual Basic.NET快速開發(fā)MIS系統(tǒng)

【摘 要】 本文介紹微軟最新技術(shù)Visual Basic.NET在數(shù)據(jù)庫開發(fā)方面的應(yīng)用。結(jié)合數(shù)據(jù)庫系統(tǒng)開發(fā)的知識，介紹了物理表操作的方法，利用Visual Basic.NET的面向?qū)ο蟮奶卣?，利用類的繼承知識，簡化了數(shù)據(jù)庫系統(tǒng)開發(fā)過程。

引言

以前版本的Visual Basic雖然號稱自己是一種OOP（面向?qū)ο螅┚幊陶Z言，但卻不是一個(gè)地地道道的OOP編程語言，最多只是半個(gè)面向?qū)ο蟮木幊陶Z言。但Visual Basic.NET已經(jīng)是一種完全的面向?qū)ο蟮木幊陶Z言。他支持面向?qū)ο蟮乃谢咎卣鳎豪^承、多態(tài)和重載。這使得以前在Visual Basic中很難或根本實(shí)現(xiàn)不了的問題，在Visual Basic.NET中可以順利的用簡單的方法實(shí)現(xiàn)。

自定義數(shù)據(jù)操作類

定義一個(gè)數(shù)據(jù)訪問的基類，并編寫有關(guān)數(shù)據(jù)庫操作的必要方法。

定義一個(gè)數(shù)據(jù)訪問類，類名為CData。定義連接Oracle數(shù)據(jù)庫的方法ConnOracle，獲取數(shù)據(jù)集的方法GetDataSet, 獲取物理表的方法GetDataTable, 向物理表中插入一行數(shù)據(jù)的方法Insert, 向物理表中刪除數(shù)據(jù)的方法Delete, 向物理表中更新數(shù)據(jù)的方法Update。其實(shí)現(xiàn)方法不是本文的重點(diǎn)，在此僅給出代碼，不作詳細(xì)分析。代碼如下：

Public Class CDataBase

Dim OleCnnDB As New OleDbConnection()

@#連接Oracle數(shù)據(jù)庫，ServerName:服務(wù)器名，UserId:用戶名，UserPwd:用戶密碼

Public Function ConnOracle(ByVal ServerName As String, ByVal UserId As String, ByVal UserPwd As String) As OleDbConnection

Dim OleCnnDB As New OleDbConnection()

With OleCnnDB

.ConnectionString = "Provider=MSDAORA.1;Password=@#" UserPwd "@#;User ID=@#" UserId "@#;Data Source=@#" ServerName "@#"

Try

.Open()

Catch er As Exception

MsgBox(er.ToString)

End Try

End With

mOleCnnDB = OleCnnDB

Return OleCnnDB

End Function

@#獲取數(shù)據(jù)集。TableName：表名，strWhere：條件

Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet

Dim strSql As String

Dim myDataSet As New DataSet()

Dim myOleDataAdapter As New OleDbDataAdapter()

myOleDataAdapter.TableMappings.Add(TableName, TableName)

strSql = "SELECT * FROM " TableName " where " strWhere

myOleDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Try

myOleDataAdapter.Fill(myDataSet)

Catch er As Exception

MsgBox(er.ToString)

End Try

Return myDataSet

End Function

@#獲取物理表。TableName：表名

Public Overloads Function GetDataTable(ByVal TableName As String) As DataTable

Dim myDataSet As New DataSet()

myDataSet = GetDataSet(TableName)

Return myDataSet.Tables(0)

End Function

@#獲取物理表。TableName：表名，strWhere：條件

Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable

Dim myDataSet As New DataSet()

myDataSet = GetDataSet(TableName, strWhere)

Return myDataSet.Tables(0)

End Function

@#向物理表中插入一行數(shù)據(jù)。TableName：表名，Value：行數(shù)據(jù)，BeginColumnIndex：開始列

Public Overloads Function Insert(ByVal TableName As String, ByVal Value As Object, Optional ByVal BeginColumnIndex As Int16 = 0) As Boolean

Dim myDataAdapter As New OleDbDataAdapter()

Dim strSql As String

Dim myDataSet As New DataSet()

Dim dRow As DataRow

Dim i, len As Int16

strSql = "SELECT * FROM " TableName

myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataSet.Tables.Add(TableName)

myDataAdapter.Fill(myDataSet, TableName)

dRow = myDataSet.Tables(TableName).NewRow

len = Value.Length

For i = BeginColumnIndex To len - 1

If Not (IsDBNull(Value(i)) Or IsNothing(Value(i))) Then

dRow.Item(i) = Value(i)

End If

Next

myDataSet.Tables(TableName).Rows.Add(dRow)

Try

myDataAdapter.Update(myDataSet, TableName)

Catch er As Exception

MsgBox(er.ToString)

Return False

End Try

myDataSet.Tables.Remove(TableName)

Return True

End Function

@#更新物理表的一個(gè)字段的值。strSql：查詢語句，F(xiàn)ieldName_Value：字段及與對應(yīng)的值

Public Overloads Sub Update(ByVal strSql As String, ByVal FieldName_Value As String)

Dim myDataAdapter As New OleDbDataAdapter()

Dim myDataSet As New DataSet()

Dim dRow As DataRow

Dim TableName, FieldName As String

Dim Value As Object

Dim a() As String

a = strSql.Split(" ")

TableName = a(3)

a = FieldName_Value.Split("=")

FieldName = a(0).Trim

Value = a(1)

myDataAdapter.SelectCommand = New OleDbCommand(strSql, mOleCnnDB)

Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)

myDataSet.Tables.Add(TableName)

myDataAdapter.Fill(myDataSet, TableName)

dRow = myDataSet.Tables(TableName).Rows(0)

If Value Nothing Then

dRow.Item(FieldName) = Value

End If

Try

myDataAdapter.Update(myDataSet, TableName)

myDataSet.Tables.Remove(TableName)

Catch er As Exception

MsgBox(er.ToString)

End Try

End Sub

@#刪除物理表的數(shù)據(jù)。TableName:表名，strWhere：條件

Public Overloads Sub Delete(ByVal TableName As String, ByVal strWhere As String)

Dim myReader As OleDbDataReader

Dim myCommand As New OleDbCommand()

Dim strSql As String

strSql = "delete FROM " TableName " where " strWhere

myCommand.Connection = mOleCnnDB

myCommand.CommandText = strSql

Try

myReader = myCommand.ExecuteReader()

myReader.Close()

Catch er As Exception

MsgBox(er.ToString)

End Try

End Sub

End Class

定義一操作數(shù)據(jù)庫中物理表的類CData,此類繼承CDataBase,即：

Public Class CData：Inherits CDataBase

此類應(yīng)該由供用戶提供所操作的物理表的表名，指定了表名就可取得該表的所有性質(zhì)。該表主要完成插入、刪除、更新功能。定義其屬性、方法如下：

申明類CData的變量:

@#所要操作的表名

Private Shared UpdateTableName As String

@#所要操作的表對象

Public Shared UpdateDataTable As New DataTable()

@#對應(yīng)表的一行數(shù)據(jù)197

Public Shared ObjFields() As Object

@#表的字段數(shù)

Public Shared FieldCount As Int16

@#主關(guān)鍵字。我們假設(shè)每個(gè)物理表都有一個(gè)主關(guān)鍵字字段fSystemID

Public Shared SystemID As String

說明：Shared 關(guān)鍵字指示一個(gè)或多個(gè)被聲明的編程元素將被共享。共享元素不關(guān)聯(lián)于某類或結(jié)構(gòu)的特定實(shí)例。可以通過使用類名或結(jié)構(gòu)名稱或者類或結(jié)構(gòu)的特定實(shí)例的變量名稱限定共享元素來訪問它們。

申明類CData的屬性UpdateTable,當(dāng)向UpdateTable賦給了一個(gè)已知表的表名，就可確定表的字段數(shù)，定義出數(shù)據(jù)行。這里，先打開表，再重新定義數(shù)據(jù)行.

Public Property UpdateTable() As String

Get

UpdateTable = UpdateTableName

End Get

Set(ByVal Value As String)

UpdateTableName = Value.Trim

UpdateDataTable = DB.GetDataTable(UpdateTableName)

UpdateTableFieldNames = UpdateDataTable.Clone

FieldCount = UpdateDataTable.Columns.Count

ReDim ObjFields(FieldCount - 1)

End Set

End Property

@#刪除由主關(guān)鍵值fSystemID指定的數(shù)據(jù)行

Public Sub Delete()

Dim strSQL As String

strSQL = "Delete from " UpdateTableName " where fSystemID=" SystemID

DB.Delete(strSQL)

UpdateDataTable.Rows.Remove(GetRow)

End Sub

@#向表UpdateTableName中插入一行數(shù)據(jù)。數(shù)據(jù)由ObjFields給出

Public Function Insert() As Boolean

DB.Insert(UpdateTableName, ObjFields)

End Function

@#更新表UpdateTableName所指定的行

Public Shadows Sub Update()

Dim SetField As String

Dim i As Int16

For i = 1 To FieldCount - 1

SetField = UpdateTableFieldNames.Columns(i).ColumnName "=" ObjFields(i)

UpdateField(SetField)

Next

End Sub

Public Sub UpdateField(ByVal SetField As String)

Dim StrSQL As String

StrSQL = "select * from " UpdateTableName " where fSystemID= " SystemID

DB.Update(StrSQL, SetField)

End Sub

@#填充網(wǎng)絡(luò)數(shù)據(jù)

Public Overloads Sub FillGrid(ByVal GridName As DataGrid)

GridName.DataSource = UpdateDataTable

End Sub

@#把數(shù)據(jù)網(wǎng)格的當(dāng)前行數(shù)據(jù)定寫入到輸入控件中

Public Sub DataGridToText(ByVal frm As Form)

Dim RowIndex, i As Int16

Dim value

Dim obj As Control

Dim DataGrid As New DataGrid()

If FieldCount = 0 Then Exit Sub

For Each obj In frm.Controls

If obj.GetType.Name = "DataGrid" Then

DataGrid = obj

Exit For

End If

Next

RowIndex = DataGrid.CurrentRowIndex

For i = 1 To FieldCount - 1

value = DataGrid.Item(RowIndex, i)

If IsDBNull(value) = True Then

value = ""

End If

For Each obj In frm.Controls @#

If obj.TabIndex = i Then

obj.Text = value

Exit For

End If

Next

Next

End Sub

VB.NET如何插入sql記錄，高手指點(diǎn)

Const?sql?As?String?=?"INSERT?INTO?idc?(name,?tel)?VALUES?(22,11)"

Const?cnStr?As?String?=?"Data?Source=(local);Initial?Catalog=master;Integrated?Security=False;User?ID=sa;Password=123456;"

Dim?cn?As?OleDbConnection?=?New?OleDbConnection(cnStr)

Dim?cmd?As?OleDbCommand?=?New?OleDbCommand(sql)

cmd.ExecuteNonQuery()

標(biāo)題名稱：包含vb.net插入sql的詞條
轉(zhuǎn)載注明：http://jinyejixie.com/article8/dosgeip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、域名注冊、微信小程序、外貿(mào)網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作、網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)