用VB连数据库,做一个简单的图书馆的查询、修改操作界面时的问题
要求:1、将数据库中的BOOK表连接到VB中的DATAGRIDVIEW中(完成)2、表中一共4个字段:书号、书名、作者、出版社。点击DATAGRIDVIEW中的任一行单元格后显示在控件TEXTBOX1,TEXTBOX2,TEXTBOX3,TEXTBOX4中(完成)
3、设置一个修改按钮,通过在TEXTBOX控件中输入值来修改DATAGRIDVIEW中的值,并同步更新到数据库。
我在第三步的时候卡住了,点击了修改按钮后,不管是DATAGRIDVIEW还是数据库里面的我想要修改的数值,都没有修改,由于还是个新手,上网搜看了半天也未得其果,我的代码如下,请各位看看:
Public Class Form1
Dim myConnection As SqlClient.SqlConnection
Dim myDataSet As DataSet = New DataSet
Dim myDataAdapter As SqlClient.SqlDataAdapter
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
myConnection = New SqlClient.SqlConnection("Data Source =8640498E619C4CF; User ID = jj; Password = 111; Persist Security Info = False; Initial Catalog = shiyan")
myConnection.Open()
myDataAdapter = New SqlClient.SqlDataAdapter("select BOOK_ID AS 书号,BOOK_NAME AS 书名,BOOK_WRITER AS 作者,BOOK_PUB AS 出版社 from BOOK", myConnection)
myDataSet = New DataSet
myDataAdapter.Fill(myDataSet, "BOOK")
DataGridView1.DataSource = myDataSet.Tables(0)
myConnection.Close()
End Sub
Private Sub dataGridView1_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
Dim n As Integer
n = DataGridView1.CurrentRow.Index
TextBox1.Text = DataGridView1.Rows(n).Cells("书号").Value
TextBox2.Text = DataGridView1.Rows(n).Cells("书名").Value
TextBox3.Text = DataGridView1.Rows(n).Cells("作者").Value
TextBox4.Text = DataGridView1.Rows(n).Cells("出版社").Value
End Sub
Private Sub 修改_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 修改.Click
myConnection = New SqlClient.SqlConnection("Data Source = 8640498E619C4CF; User ID = guojie; Password = 111; Persist Security Info = False; Initial Catalog = shiyan")
myDataAdapter = New SqlClient.SqlDataAdapter("select BOOK_ID AS 书号,BOOK_NAME AS 书名,BOOK_WRITER AS 作者,BOOK_PUB AS 出版社 from BOOK", myConnection)
myDataSet = New DataSet
Dim s As String
myConnection.Open()
s = "update BOOK set BOOK_NAME='" & TextBox2.Text & "',BOOK_WRITER='" & TextBox3.Text & "',BOOK_PUB='" & TextBox4.Text & "'where BOOK_ID='TextBox1.Text'"
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(s, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()
--------------------编程问答-------------------- s = "update BOOK set BOOK_NAME='" & TextBox2.Text & "',BOOK_WRITER='" & TextBox3.Text & "',BOOK_PUB='" & TextBox4.Text & "'where BOOK_ID='TextBox1.Text'"
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(s, myConnection)
myCommand.ExecuteNonQuery()
msgbox(myCommand.ExecuteNonQuery()) '看看这儿返回值是多少
myConnection.Close() --------------------编程问答-------------------- Dim ds As New DataSet()
Dim sda As SqlDataAdapter
Dim scb As New SqlCommandBuilder(sda)
sda.Update(ds)
Me.dataGridView1.DataSource = ds.Tables(0) --------------------编程问答-------------------- s = "update BOOK set BOOK_NAME='" & TextBox2.Text & "',BOOK_WRITER='" & TextBox3.Text & "',BOOK_PUB='" & TextBox4.Text & "'where BOOK_ID='"&TextBox1.Text&"'"
--------------------编程问答--------------------
是0
那究竟是哪错了,能说说吗 --------------------编程问答-------------------- 检查下你的更新语句,参考3楼。
如果ID是数值型,改成下面试试:
s = "update BOOK set BOOK_NAME='" & TextBox2.Text & "',BOOK_WRITER='" & TextBox3.Text & "',BOOK_PUB='" & TextBox4.Text & "'where BOOK_ID="& TextBox1.Text &"" --------------------编程问答-------------------- 我按照楼上的方法改了,修改DATAGRIDVIEW里的数据成功,但是还差同步更新到数据库
我将修改_CLICK控件改为
Private Sub 修改_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 修改.Click
myConnection = New SqlClient.SqlConnection("Data Source = 8640498E619C4CF; User ID = guojie; Password = 111; Persist Security Info = False; Initial Catalog = shiyan")
myDataAdapter = New SqlClient.SqlDataAdapter("select BOOK_ID AS 书号,BOOK_NAME AS 书名,BOOK_WRITER AS 作者,BOOK_PUB AS 出版社 from BOOK", myConnection)
myDataSet = New DataSet
Dim s As String
myConnection.Open()
s = "update BOOK set BOOK_NAME='" & TextBox2.Text & "',BOOK_WRITER='" & TextBox3.Text & "',BOOK_PUB='" & TextBox4.Text & "'where BOOK_ID="& TextBox1.Text &""
Dim myCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand(s, myConnection)
Dim scb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(myDataAdapter)'这一句是根据人生如梦的提示加的
myCommand.ExecuteNonQuery()
myDataAdapter.Update(myDataSet,"BOOK")'这一句是根据人生如梦的提示加
DataGridView1.DataSource = myDataSet.Tables(0)'这一句是根据人生如梦的提示加
myConnection.Close()
End Sub
运行之后,在myDataAdapter.Update(myDataSet,"BOOK")这一句提示错误:Update 无法找到 TableMapping['BOOK'] 或 DataTable“BOOK”
将"BOOK"删掉变为myDataAdapter.Update(myDataSet),提示错误:Update 无法找到 TableMapping['TABEL'] 或 DataTable“TABLE”
各位知道是怎么回事吗
--------------------编程问答-------------------- 解决了!!!!!
现在第4步,在FORM1里有控件TEXBOX5,TEXTBOX6,TEXTBOX7,TEXTBOX8,分别对应书号,书名,作者,出版社,以及一个检索按钮
要求在TEXTBOX控件里随意输入信息,比如在TEXTBOX6中输入:C++ TEXTBOX8中输入:清华
那么所有书名包含"C++",出版社中包含"清华"的书 的信息全部显示在DATAGRIDVIEW里
我做出来,并且运行成功了,但是觉得我写的语句有些麻烦,大家帮我看看能否改简单点,分全部给你们了,谢谢!!!!
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
myConnection = New SqlClient.SqlConnection("Data Source =8640498E619C4CF; User ID = guojie; Password = 111; Persist Security Info = False; Initial Catalog = shiyan")
myConnection.Open()
Dim s As String
s = "select BOOK_ID AS 书号,BOOK_NAME AS 书名,BOOK_WRITER AS 作者,BOOK_PUB AS 出版社 from BOOK where "
If TextBox5.Text <> "" Then'如果textbox5不为空
s = s + "BOOK_ID like '%" & TextBox5.Text & "%'"
End If
If TextBox6.Text <> "" And TextBox5.Text <> "" Then'如果TEXTBOX6和TEXTBOX5不为空
s = s + "and BOOK_NAME like '%" & TextBox6.Text & "%'"
Elseif TextBox6.Text <> "" then s = s + "BOOK_NAME like '%" & TextBox6.Text & "%'" '若TEXTBOX6不为空,且TEXTBOX5为空的情况下
End If
If (TextBox7.Text <> "" And TextBox5.Text <> "") Or (TextBox7.Text <> "" And TextBox6.Text <> "") Then '若TEXTBOX7与TEXTBOX5都不为空 或者 TEXTBOX7与TEXTBOX6不为空 的情况下
s = s + "and BOOK_WRITER like '%" & TextBox7.Text & "%'"
ElseIf TextBox7.Text <> "" Then '若TextBox7不为空
s = s + "BOOK_WRITER like '%" & TextBox7.Text & "%'"
End If
If (TextBox8.Text <> "" And TextBox5.Text <> "") Or (TextBox8.Text <> "" And TextBox6.Text <> "") Or (TextBox8.Text <> "" And TextBox7.Text <> "") Then
'若TEXTBOX8与TEXTBOX5都不为空 或者 TEXTBOX8与TEXTBOX7不为空 或者TEXTBOX8与TEXTBOX6 不为空的情况下
s = s + "and BOOK_PUB like '%" & TextBox8.Text & "%'"
ElseIf TextBox8.Text <> "" Then'在TEXTBOX8不为空的情况下
s = s + "BOOK_PUB like '%" & TextBox8.Text & "%'"
End If
myDataAdapter = New SqlClient.SqlDataAdapter(s, myConnection)
myDataSet = New DataSet
myDataAdapter.Fill(myDataSet, "BOOK")
DataGridView1.DataSource = myDataSet.Tables(0)
myConnection.Close()
End Sub
此处的检索SQL语句,写得太复杂,主要是因为,WHERE 语句条件之间要用AND连接,但有不知道前一个TEXTBOX控件里面输没输得有值,所以要用IF语句设定条件,把AND连在该连的语句之前、
各位帮我想想,谢谢,分都给你们。。。。 --------------------编程问答-------------------- 恭喜哦,呵呵 --------------------编程问答-------------------- jf --------------------编程问答-------------------- Dim s As StringBuilder
s=New StringBuilder
s = "select BOOK_ID AS 书号,BOOK_NAME AS 书名,BOOK_WRITER AS 作者,BOOK_PUB AS 出版社 from BOOK where "
If TextBox5.Text <> "" Then '如果textbox5不为空
s.append( "BOOK_ID like '%" & TextBox5.Text & "%'" )
End If
If TextBox6.Text <> "" Then '如果TEXTBOX6不为空
s.append( "BOOK_NAME like '%" & TextBox6.Text & "%'" )
End If
If TextBox7.Text <> "" Then '若TEXTBOX7不为空
s.append( "BOOK_WRITER like '%" & TextBox7.Text & "%'" )
End If
If TextBox8.Text <> "" Then '若TEXTBOX8不为空
s.append( "BOOK_PUB like '%" & TextBox8.Text & "%'" )
End If
myDataAdapter = New SqlClient.SqlDataAdapter(s, myConnection)
myDataSet = New DataSet
myDataAdapter.Fill(myDataSet, "BOOK")
DataGridView1.DataSource = myDataSet.Tables(0)
myConnection.Close()
End Sub
--------------------编程问答-------------------- 祝你好运,呵呵 --------------------编程问答-------------------- 更新词句写错了 --------------------编程问答-------------------- Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim dbname As String
dbname = Server.MapPath("users.mdb")
myConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" & dbname)
myConnection.Open()
myCommand = New OleDbCommand("select * FROM users", myConnection)
Dim dr As OleDbDataReader = myCommand.Executereader() --------------------编程问答-------------------- 先跟数据库建立连接,然后在绑定。 --------------------编程问答-------------------- 路过
补充:.NET技术 , VB.NET