I have a C# form with a MaskedTextBox that I am needing to pass as a parameter to my SQL Server.
The form:
+-------------------------------------------------------+
| ___________ |
| Date:|__/__/_____| <---MaskedTextBox |
| |
| _________ |
| |btnSubmit| <---Button |
| |
+-------------------------------------------------------+
When a date is entered in the MaskedTextBox, it looks for a file matching that date, reads from it, and then sends that data to my SQL Server. However, I'm needing to create a stored procedure to delete the data matching the day of the import- as to not have duplicated data.
The tricky part is that I have the method for sending the data to my SQL Server on another .cs file that is added to my project.
The method(Methods.cs):
public void SendTable(DataTable table, string StoredProcedure, string TableType)
{
string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";
//Use Stored procedure to send data table
using (SqlConnection connection = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = StoredProcedure;
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
var fm = new frmMain(); //Main form located in frmMain.cs
string date = fm.uDate; //setting string date to a public string, also on frmMain.cs
SqlParameter dtparam = new SqlParameter("@date", table);
dtparam.SqlDbType = SqlDbType.Structured;
dtparam.TypeName = "dbo." + TableType;
cmd.Parameters.Add(dtparam).Value = date;
cmd.ExecuteNonQuery(); //ERROR HERE: Failed to convert parameter value from a String to a IEnumerable`1
}
}
Here is uDate(frmMain.cs):
public string uDate
{
get { return mtbDate.Text; }
}
In case it's needed, my stored procedure:
ALTER Procedure [dbo].[Submit]
--Add Parameter
@date AS nvarchar(max)
AS
Begin
Set NoCount ON
--DELETE FROM _table
Begin Tran T1;
INSERT INTO [Archive]([Customer Name], IDNbr, City, Bal, ONbr, BalDate) SELECT CustName, IDNbr, City, Bal, ONbr, (@date) AS BalDate FROM myView;
Commit Tran T1;
End
Thank you in advance for any help or advice! If anything is unclear, I will gladly attempt to clarify. I have looked into Passing parameter to SQL Server. I've attempted to correct my code where I thought mistakes were made, though I'm still erroring.
How can I pass the entered date on my frmMain to my SQL Server as a parameter @date ?
EDIT:
When btnSubmit is clicked:
private void btnSubmit_Click(object sender, EventArgs e)
{
string date = uDate;
string day = date.Substring(3, 2);
string month = date.Substring(0, 2);
string year = date.Substring(8);
string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";
using (var conn = new SqlConnection(conStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Submit", conn);
cmd.CommandType = CommandType.StoredProcedure;
}
string suffix = @"\myFile.txt";
date = year + month + day;
string prefix = @"\\myDirectory\";
string fileName = prefix + date + suffix;
ImportList(fileName);
}
ImportList() then collects the data, reads through the file, and then sends
the data through another method located on Method.cs:
public void Send(string StoredProcedure, string TableType)
{
DataTable table = new DataTable();
DataRow Row;
//Gets Column info from list and creates columns in table
foreach (var column in ExportList[0])
{
table.Columns.Add(column.Key, column.Value.GetType());
}
foreach(var item in ExportList)
{
Row = table.NewRow();
foreach (var column in item)
{
Row[column.Key] = column.Value;
}
table.Rows.Add(Row);
}
//send data table to stored procedure
SendTable(table, StoredProcedure, TableType);
}
