从VB将记录插入数据库时​​出错 [英] Error while Insert Record into database from VB From

问题描述

全部，
我正在VS2010中开发VB应用程序.当我使用select读取数据库并将其显示在Datagrid视图上时，在这里我没有遇到任何问题.但是，当我将新记录插入该数据库时，出现错误...
这是我用于插入新记录的代码...

Hai All,
I am developing VB Application in VS2010. When i read the data base by using select and display it on Datagrid view , here i didn''t get any problem. But when i insert the new record into that database have an error...
Here is my code for insert new record...

cmdInsert.CommandText = "INSERT INTO Alarm (Alarm, Date,Time) VALUES ('" & al & "', '" & dt & "', '" & tm & "')"
            MsgBox(cmdInsert.CommandText)
            cmdInsert.CommandType = CommandType.Text
            cmdInsert.Connection = cnnOLEDB
            cmdInsert.ExecuteNonQuery()
            cmdInsert.Dispose()

dt，tm和al是字符串

就像INSERT INTO语句一样在运行时发生错误error

dt,tm and al are strings

error is occur at runtime just like INSERT INTO Statement error

推荐答案

确保要存储在表中的值的类型以及要传递给查询的类型是正确的.警报可能是整数(数字)类型，而不是字符串.

Ensure that the type of values you are storing into the tables and the type that you are passing into the query are correct. Alarm may be an int (number) type and not string.

VALUES("&al&''，``"&dt&''，``"& tm&'')"

试试这个''''''''

昏暗的S ="(&chr(34)&al&Chr(34)&"，&chr(34)&dt&Chr(34)&"，&chr(34)&tm&Chr(34) &)"


值&S

VALUES (''" & al & "'', ''" & dt & "'', ''" & tm & "'')"

Try This ''''''''

dim S= "(" & chr(34) & al & Chr(34) & "," & chr(34) & dt & Chr(34) & "," & chr(34) & tm & Chr(34) & ")"


Values & S

切勿将实际值连接到SQL语句.而是使用 OleDbParameter [

Never concatenate actual values to the SQL statement. Instead use OleDbParameter[^].

So your code could look something like:

cmdInsert.CommandText = "INSERT INTO Alarm (Alarm, Date,Time) VALUES (?, ?, ?)"
MsgBox(cmdInsert.CommandText)
cmdInsert.CommandType = CommandType.Text
cmdInsert.Connection = cnnOLEDB
cmdInsert.Parameters.AddWithValue("@alarm", al);
cmdInsert.Parameters.AddWithValue("@date", dt);
cmdInsert.Parameters.AddWithValue("@time", tm);
cmdInsert.ExecuteNonQuery()
cmdInsert.Dispose()

这将帮助您避免转换问题和SQL注入.

也不要在日期或时间中使用字符串数据.而是使用本机数据类型.由于您分别拥有日期和时间，因此建议您仅在表中定义一个字段，数据类型为datetime2.请参阅: http://msdn.microsoft.com/en-us/library/bb677335.aspx [ ^ ]

That would help you against conversion problems and SQL injections.

Also don''t use string data for dates or times. Instead use the native data types. As you''re having both date and time separately, I''d suggest that you define only one field in the table and the data type would be datetime2. See: http://msdn.microsoft.com/en-us/library/bb677335.aspx[^]

这篇关于从VB将记录插入数据库时​​出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！