System.Data.SQLite / ADO.表输入格式不正确- c#
本文关键字：格式 不正确 输入 Data SQLite ADO System | 更新日期: 2023-09-27 18:16:25
我想做一个应用程序，创建一个数据库文件，跟踪锻炼。按下"New Database"按钮，进入Save File对话框，为SQLite创建.db文件。表名来自WPF控件上的文本框。到目前为止，我已经完成了全部工作。除了一句继续考验我耐心的话。完整的代码如下:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace WorkoutDB1
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void btnNewDatabase_Click(object sender, RoutedEventArgs e)
{
// Configure save file dialog box
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.FileName = "Document"; // Default file name
dlg.DefaultExt = ".db"; // Default file extension
dlg.Filter = "SQLite3 Database File (.db)|*.db"; // Filter files by extension
string dateTime = DateTime.Today.ToString("dd_mm_yyyy");
// Show save file dialog box
Nullable<bool> result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
string filename = dlg.FileName;
string cs = string.Format("URI=file:{0}", filename);
string tableName = txtDataTableName.Text;
using (SQLiteConnection con = new SQLiteConnection(cs))
{
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
cmd.CommandText = string.Format("CREATE TABLE {0}(date INTEGER PRIMARY KEY, machine TEXT NOT NULL, sets INTEGER NOT NULL, repetitions INTEGER NOT NULL, weight NOT NULL)", tableName);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
con.Close();
}
using (SQLiteConnection con = new SQLiteConnection(cs))
{
DataTable table = new DataTable(tableName);
DataRow row;
table.Columns.Add("Date", System.Type.GetType("System.String"));
table.Columns.Add("Machine", System.Type.GetType("System.String"));
table.Columns.Add("Sets", System.Type.GetType("System.Int32"));
table.Columns.Add("Repetitions", System.Type.GetType("System.Int32"));
table.Columns.Add("Weight", System.Type.GetType("System.Int32"));
row = table.NewRow();
row["Date"] = dateTime;
row["Machine"] = "Seated Row";
row["Sets"] = 1;
row["Repetitions"] = 10;
row["Weight"] = 100;
table.Rows.Add(row);
string sql = String.Format("SELECT * FROM {0}", tableName);
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, con))
{
using (new SQLiteCommandBuilder(da))
{
da.Fill(table);
da.Update(table);
}
}
con.Close();
}
}
}
}
它说我的da.Update(table)中的table参数格式不正确。这是因为我的date字段在数据库中…它不喜欢我喂它的东西。我如何正确地格式化和键入date字段，以便数据适配器不会出现错误?
为伟大的正义堆栈跟踪。
System.FormatException was unhandled
HResult=-2146233033
Message=Input string was not in a correct format.
Source=System.Data
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at WorkoutDB1.MainWindow.btnNewDatabase_Click(Object sender, RoutedEventArgs e) in c:'Users'Aaron'SkyDrive'WorkoutDB2'WorkoutDB2'MainWindow.xaml.cs:line 96
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.UIElement.RaiseEvent(RoutedEventArgs e)
at System.Windows.Controls.Primitives.ButtonBase.OnClick()
at System.Windows.Controls.Button.OnClick()
at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted)
at System.Windows.Input.InputManager.ProcessStagingArea()
at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
at System.Windows.Threading.Dispatcher.Run()
at System.Windows.Application.RunDispatcher(Object ignore)
at System.Windows.Application.RunInternal(Window window)
at System.Windows.Application.Run(Window window)
at System.Windows.Application.Run()
at WorkoutDB1.App.Main() in c:'Users'Aaron'SkyDrive'WorkoutDB2'WorkoutDB2'obj'Debug'App.g.cs:line 0
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: null
您的问题是，在您的CREATE TABLE语句中，您将date列声明为类型Integer。然后将date字段声明为String。
我不太确定为什么你会声明一个日期作为一个整数(或主键)，特别是因为似乎你可以有许多重复的人谁工作一天不止一次。
无论如何，解决方案是:您需要将CHANGE TABLE命令更改为以下命令:
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
cmd.CommandText = string.Format("CREATE TABLE {0}(date TEXT PRIMARY KEY, machine TEXT NOT NULL, sets INTEGER NOT NULL, repetitions INTEGER NOT NULL, weight NOT NULL)", tableName);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
这将创建一个带有字段的表，该字段接受string类型的输入，并将处理input string not in correct format错误。