SQL Parameters

[DaveRook]

Hi

Not sure if this is an SQL question or C#.

How can I check the value of a parameter? My windowsForm allows the customer to select a date range, a new window opens and a datagrid displays the results of all transactions between the time.

When I add the variable into my sql query (where date > '"+variableFrom+"' AND date < '"+variableTo+"') it works fine. However, I get the message "Conversion failed when converting datetime from character string" because it runs on a terminal server with some profile region settings UK, others Chinese.

So, I have updated to use SQL parameters but I always get an empty database returned.

Form1.cs allows the customer to select a daterange.
Results.cs shows the datagrid results.

I have built a constructor to pass the information across the forms

form1.cs

private void btnExec_Click(object sender, EventArgs e)
        {
 
            //uses DateTime
            DateTime ddtpFrom = Convert.ToDateTime(dtpFrom.Value).Date, ddtpTo = Convert.ToDateTime(dtpTo.Value).Date;
 
            Results res1 = new Results(ddtpFrom, ddtpTo);
 
            res1.Show();
        }

Results.cs

public Results(DateTime ddtpFrom, DateTime ddtpTo)
        {     
            InitializeComponent();
            OpenDatabase(ddtpFrom, ddtpTo);
        }
 
        public void OpenDatabase(DateTime ddtpFrom, DateTime ddtpTo)
        {
 
            SqlConnection conn = new SqlConnection("Data Source=SBSHK; Initial Catalog=xxx User ID=xxx; Password=xxx;");
 
            string strSQL = "DECLARE @f DateTime, @t DateTime " +
                    "SELECT gbkmut.datum AS[mm/dd/yyyy], gbkmut.bkstnr AS [Entry Number], gbkmut.oms25 as Mark,gbkmut.reknr as Glcode, " +
                    "grtbk.oms25_1 as Gldesc, gbkmut.syscreator AS [End User], c.cmp_name AS [ Company Name ], c.crdnr AS Credit, c.debnr AS Debit  FROM gbkmut " +
 
                    "LEFT JOIN cicmpy AS c ON ((LTRIM(gbkmut.debnr) = LTRIM(c.debnr)) OR (LTRIM(gbkmut.crdnr) = LTRIM(c.crdnr)))" +
 
                    "LEFT JOIN grtbk on grtbk.reknr = gbkmut.reknr WHERE (gbkmut.datum BETWEEN @f AND @t) AND dagbknr IS NOT NULL AND transtype <> 'V' " +
 
                    "Order by gbkmut.bkstnr";
 
 
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                DataSet ds = new DataSet();
 
 
                using (conn)
                {
                    try
                    {
                        conn.Open();
 
 
                        SqlCommand cmd = new SqlCommand();
                        cmd.CommandType = CommandType.Text;
 
 
                        cmd.Parameters.Add("@f", SqlDbType.DateTime).Value = ddtpFrom;
                        cmd.Parameters.Add("@t", SqlDbType.DateTime).Value = ddtpTo;
 
 
                        da.Fill(ds, "dsVoucher");
 
 
                        dataVoucher.DataSource = ds;
                        dataVoucher.DataMember = "dsVoucher";
 
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        lbTotalRecords.Text = ex.ToString();
                    }
 
                }
        }

The reason on form1.cs I use the .date is because all entries in the database start and end at 12:00:00 AM

Any ideas why the information isn't shown?

Is there a way to write the scalar variable to label so I can see the value?

Dave

[DaveRook]

Solved

Line 12 I used the @ before the statement and therefore could remove the scalar variable decloration