vb怎样将sql数据库中的一个数值读取到变量中

Question

我想将Tb_room中的roomprice读到integer变量price中，然后作为price插入到Tb_customer表中，不知道该怎么写，请大神指教

Public Sub addcustomer(ByVal p1 As Integer, ByVal p2 As String, ByVal p3 As String, ByVal p4 As Integer, ByVal p5 As String, ByVal p6 As Integer)
Dim dbConn As SqlConnection = Nothing '数据库连接
Dim strDBConn As String = System.Configuration.ConfigurationManager.AppSettings("strDBConn")
dbConn = New SqlConnection(strDBConn) '建立数据库联接

dbConn.Open()

‘这里从Tb_room中读roomPrice （integer型）
Dim str1 As String = "select roomPrice from " & "Tb_room" & "where" & "roomId" & "='" & p4 & "';"
’然后想把值放进price中，可以怎么做？

Dim price As Integer

Dim cmd2 As SqlCommand
Dim str2 As String = "insert into Tb_customer (customerId,customerName,IDnum,roomId,teleNum,days,price) values(" & p1 & ",'" & p2 & "' ,'" & p3 & "' ," & p4 & ",'" & p5 & "' ," & p6 & "," & price & ");"
cmd2 = New SqlCommand(str2, dbConn)
cmd2.ExecuteNonQuery()
dbConn.Close()
End Sub

Answer 1

'看你的代码不是vb6.0的 ，我有现成的vb.net下面的SQl操作模块，你直接调用就可以了
'一共2个函数，DataModify用来更新数据库操作，Search用来查询数据库
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Module Module1
Public cn As New SqlConnection("Data Source=ip地址或者电脑名;Initial Catalog=数据库名;User ID=用户名;Password=密码") '定义数据库连接
Public DataBaseRST As Integer'用来返回数据库执行结果---数据更新了多少条
Public Function DataModify(ByVal str AsString) As Boolean'进行数据库修改操作函数
Dim cmdinsert As New SqlCommand
Try
cmdinsert.CommandText = str
cmdinsert.Connection = cn
If cn.State = ConnectionState.Closed Then cn.Open()
DataBaseRST = cmdinsert.ExecuteNonQuery() '用来返回执行的结果
cn.Close()
Return True
Catch ex As Exception
MessageBox.Show(Err.Description, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return False
End Try
End Function
Public Function Search(ByVal str AsString) As DataTable '查询 str---查询命令 tb是返回的数据表
Dim tb As New DataTable
Try
Dim ap As New SqlDataAdapter(str, cn)
ap.Fill(tb)
Return tb
Catch ex As Exception
MessageBox.Show(Err.Description, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return Nothing
End Try
End Function
End Module

'下面是调用举例----在窗体Form1类中的Button1按钮下
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'以下执行更新操作
If DataModify("delete from a表 where a表字段1='内容'") = True Then
MsgBox("更新成功")
Else
Exit Sub
End If
'下面执行查询操作
Dim tb As DataTable = Search("select * from a表")
If tb Is Nothing Then
Exit Sub
End If
If tb.Rows.Count > 0 Then
MsgBox(tb.Rows(0).Item(0).ToString) '这里的tb.Rows(0).Item(0)表示第一行第一列的值
End If
End Sub
End Class追问

那从数据库里取出的值怎么赋值给一个变量呢？

追答

如果只有一个值，不就是tb.Rows(0).Item(0)吗？
Dim tb As DataTable = Search("select * from a表")

Dim price As Integer=tb.Rows(0).Item(0)'这就是赋值给变量

本回答被提问者采纳

Answer 2

从表的SELECT MAX（场）
Cell.Value =字段值
可能的话，检查VB连接到数据库表

Answer 3

'看你的代码不是vb6.0的 ，我有现成的vb.net下面的SQl操作模块，你直接调用就可以了
'一共2个函数，DataModify用来更新数据库操作，Search用来查询数据库
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Module Module1
Public cn As New SqlConnection("Data Source=ip地址或者电脑名;Initial Catalog=数据库名;User ID=用户名;Password=密码") '定义数据库连接
Public DataBaseRST As Integer'用来返回数据库执行结果---数据更新了多少条
Public Function DataModify(ByVal str AsString) As Boolean'进行数据库修改操作函数
Dim cmdinsert As New SqlCommand
Try
cmdinsert.CommandText = str
cmdinsert.Connection = cn
If cn.State = ConnectionState.Closed Then cn.Open()
DataBaseRST = cmdinsert.ExecuteNonQuery() '用来返回执行的结果
cn.Close()
Return True
Catch ex As Exception
MessageBox.Show(Err.Description, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return False
End Try
End Function
Public Function Search(ByVal str AsString) As DataTable '查询 str---查询命令 tb是返回的数据表
Dim tb As New DataTable
Try
Dim ap As New SqlDataAdapter(str, cn)
ap.Fill(tb)
Return tb
Catch ex As Exception
MessageBox.Show(Err.Description, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return Nothing
End Try
End Function
End Module

'下面是调用举例----在窗体Form1类中的Button1按钮下
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'以下执行更新操作
If DataModify("delete from a表 where a表字段1='内容'") = True Then
MsgBox("更新成功")
Else
Exit Sub
End If
'下面执行查询操作
Dim tb As DataTable = Search("select * from a表")
If tb Is Nothing Then
Exit Sub
End If
If tb.Rows.Count > 0 Then
MsgBox(tb.Rows(0).Item(0).ToString) '这里的tb.Rows(0).Item(0)表示第一行第一列的值
End If
End Sub
End Class追问

那从数据库里取出的值怎么赋值给一个变量呢？

追答

如果只有一个值，不就是tb.Rows(0).Item(0)吗？
Dim tb As DataTable = Search("select * from a表")

Dim price As Integer=tb.Rows(0).Item(0)'这就是赋值给变量

本回答被提问者采纳

Answer 4

从表的SELECT MAX（场）
Cell.Value =字段值
可能的话，检查VB连接到数据库表