sql - 使用 vb.net 将数据插入 SQL Server 数据库
问题描述
我创建了一个 SQL Server 数据库,我想在该数据库的特定表中添加一些数据。我使用一些文本框来输入数据并使用添加按钮来完成。但是当我点击按钮时,整个过程停止并指示 DBSQL 模块中的错误,如下所示。
这是我的代码:
Imports System.Data
Imports System.Data.SqlClient
Module DBSQLServer
Public con As New SqlConnection("Data Source=JOYALXDESKTOP\SQLEXPRESS;Initial Catalog=SaleInventory;Integrated Security=True")
Public cmd As New SqlCommand
Public da As New SqlDataAdapter
Public ds As New DataSet
Public dt As DataTable
Public qr As String
Public i As Integer
Public Function searchdata(ByVal qr As String) As DataSet
da = New SqlDataAdapter(qr, con)
ds = New DataSet
da.Fill(ds)
Return ds
End Function
Public Function insertdata(ByVal qr As String) As Integer
cmd = New SqlCommand(qr, con)
con.Open()
i = cmd.ExecuteNonQuery()
con.Close()
Return i
End Function
End Module
错误发生在这一行:
i = cmd.ExecuteNonQuery()
错误是:
System.Data.SqlClient.SqlException:'')'附近的语法不正确
这是我的添加按钮代码:
Private Sub Add_Click(sender As Object, e As EventArgs) Handles add.Click
If (isformvalid()) Then
qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "',)"
Dim logincorrect As Boolean = Convert.ToBoolean(insertdata(qr))
If (logincorrect) Then
MsgBox("Stock Added Successfully ...", MsgBoxStyle.Information)
Else
MsgBox("Something Wrong. Record Not Saved. Please Check and Try Again...", MsgBoxStyle.Critical)
End If
End If
End Sub
当我复制该错误的详细信息时,它显示:
System.Data.SqlClient.SqlException
HResult=0x80131904
消息=')' 附近的语法不正确。
Source=.Net SqlClient 数据提供者堆栈跟踪:
在 System.Data.SqlClient.SqlConnection.OnError(SqlException 异常,布尔 breakConnection,操作
1 wrapCloseInAction)
1 wrapCloseInAction) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject state&Obj, Boolean dataReady) 在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) 在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 完成, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at InventoryManagement.DBSQLServer.insertdata(String qr) in C:\Users\Joy Alx\source\repos\InventoryManagement\InventoryManagement\DBClass\DBSQLServer.vb:C:\Users\Joy Alx\source\repos\InventoryManagement\ InventoryManagement.stock.Add_Click(Object sender, EventArgs e) 的第 25 行InventoryManagement\Screens\Tools\stock.vb:System.Windows.Forms.Control.OnClick(EventArgs e) 处的第 29 行 Bunifu.Framework.UI.BunifuImageButton.OnClick(EventArgs e) 处 System.Windows.Forms.Control.WmMouseUp (Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow .WndProc(Message& m) 在 System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) 在系统。System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) 在 System.Windows.Forms 的 Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)。 Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices。 WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) 在 InventoryManagement.My.MyApplication.Main(String[] Args) 在:line 81UnsafeNativeMethods.DispatchMessageW(MSG& msg) 在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) 在 System.Windows.Forms.Application.ThreadContext。 RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel( ) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) 在 InventoryManagement.My.MyApplication.Main(String[] Args) 在:第 81 行UnsafeNativeMethods.DispatchMessageW(MSG& msg) 在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) 在 System.Windows.Forms.Application.ThreadContext。 RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel( ) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) 在 InventoryManagement.My.MyApplication.Main(String[] Args) 在:第 81 行System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID,Int32 原因,Int32 pvLoopData)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner( Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID,Int32 原因,Int32 pvLoopData)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner( Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) 在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) 在 System .Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase。 Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) 在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) 在 System .Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase。 Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID,Int32 原因,Int32 pvLoopData)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 原因,ApplicationContext 上下文)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 原因,ApplicationContext 上下文) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) 在 InventoryManagement.My.MyApplication.Main() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()字符串 [] Args) 在:第 81 行IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID,Int32 原因,Int32 pvLoopData)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 原因,ApplicationContext 上下文)在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 原因,ApplicationContext 上下文) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) 在 InventoryManagement.My.MyApplication.Main() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()字符串 [] Args) 在:第 81 行ApplicationContext context) 在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic .ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81ApplicationContext context) 在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() 在 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() 在 Microsoft.VisualBasic .ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
If I have done anything wrong to ask this type question, I am sorry. I am new in this community.Thanks in advance.
解决方案
您的查询有问题:
qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "',)"
应该
qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "')"
想象一下 SQL 查询是这样的:
Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('[name]','[description]','[price]','[stock]',)
和
Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('[name]','[description]','[price]','[stock]')
编辑:我也必须同意同事的意见——使用参数化查询或存储过程——这将防止 SQL 注入。还要确保在将输入推送到 db 之前验证输入 - 将文本推送到 int 字段将失败。
推荐阅读
- unity3d - Unity和.Net Core 3.1之间如何交换json?
- ios - 我在 Swift 中的 segue 代码有什么问题?
- r - 生成一个包含大于和小于值的向量
- javascript - UnhandledPromiseRejectionWarning 即使使用 .catch() (Node.js)
- c# - 根据提供的示例,尝试在 c# 中将 string[] 更改为 rest 格式
- mysql - 希望提取每天早上 6 点到晚上 8 点之间应用程序的不同登录次数
- assembly - Is there a website or tool for parsing, breaking down, and explaining x86 bytecode
- php - ssh2_connect(): 启动 SSH 连接时出错 (-43): 获取横幅失败
- gradle - Gradle 分发插件:有条件地复制资产
- c# - XAML + Awesome:更改触发器样式