Command.Parameters.AddWithValue having two possible values

Question

Hi Can anybody tell me please how can I use command.Parameters.AddWithValue if the parameter can have two values for example: command.Parameters.AddWithValue(@ab 1 or 2) Thank you for your help.

Javeed M Shaikh · Answer

Hi Nel I am not clear about the question but here is my guess: how to use AddWithValue: cmd.Parameters.AddWithValue(@param1myvalue); if there are two values you need to decide which one to send or use TableType to send multiple values to one parameter.

Nel · Answer

now I get an error Extra ) in query expression '(((NOVI.GBR)>='?' And (NOVI.GBR) =?) and ((NALOG1.DATA) in the rowoleDBDataAdapter1.Fill(dataSet2 NOVI ); and I don't want to fill the dataset with that table but with the values from the query expression. I don't know how to fill the dataset with values from the three tables.

Javeed M Shaikh · Answer

Change your SQL so that the GROUP BY should come after WHERE clause. like this.. string comstring = SELECT NOVI.GBR NOVI.AB NALOG1.DATA Min(NALOG1.POCKM) AS MinOfPOCKM Max(NALOG1.KRAJKM) AS MaxOfKRAJKM (Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR where (((NOVI.GBR)>='?' And (NOVI.GBR) =?) and ((NALOG1.DATA)

Javeed M Shaikh · Answer

What is the Error?

Nel · Answer

I want to make a report but this is the first time to use data from different tables. I am trying to achieve this on the following way: ReportDocument cryRpt = new ReportDocument(); string comstring = SELECT NOVI.GBR NOVI.AB NALOG1.DATA Min(NALOG1.POCKM) AS MinOfPOCKM Max(NALOG1.KRAJKM) AS MaxOfKRAJKM (Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR GROUP BY NOVI.GBR NOVI.AB NALOG1.DATA where (((NOVI.GBR)>='?' And (NOVI.GBR) =?) and ((NALOG1.DATA) OleDbCommand command = new OleDbCommand(); command.Connection = conn; command.CommandText = comstring; if (radioButton4.Checked==true){ command.Parameters.AddWithValue(@gbr1 textBox1.Text); command.Parameters.AddWithValue(@gbr2 textBox1.Text); } if (radioButton5.Checked==true){ command.Parameters.AddWithValue(@gbr1 1001); command.Parameters.AddWithValue(@gbr2 1020); } if (radioButton6.Checked==true){ command.Parameters.AddWithValue(@gbr11020 ); command.Parameters.AddWithValue(@gbr2 1088); } if (radioButton7.Checked == true) { command.Parameters.AddWithValue(@gbr1 1001); command.Parameters.AddWithValue(@gbr2 1088); } if(checkBox1.Checked==true){ command.Parameters.AddWithValue(@ab1 1); command.Parameters.AddWithValue(@ab2 1); } else if (checkBox2.Checked==true) { command.Parameters.AddWithValue(@ab1 2); command.Parameters.AddWithValue(@ab2 2); } else if((checkBox1.Checked==true) && (checkBox2.Checked==true)) { command.Parameters.AddWithValue(@ab 1); command.Parameters.AddWithValue(@ab2); } command.Parameters.AddWithValue(@data1 dateTimePicker1.Value); command.Parameters.AddWithValue(@data2 dateTimePicker2.Value); DataSet dataSet2=new DataSet(); conn.Open(); OleDbDataAdapter oleDBDataAdapter1 = new OleDbDataAdapter(new OleDbCommand(comstring conn)); OleDbCommand command1 = new OleDbCommand(); command1.Connection = conn; oleDBDataAdapter1.SelectCommand=command; dataSet2.Clear(); oleDBDataAdapter1.Fill(dataSet2NOVI); cryRpt.SetDataSource(dataSet2.Tables[NOVI]); crystalReportViewer1.DisplayToolbar=true; cryRpt.Load(@C:\Potrosuvacka-Pregledi\Potrosuvacka-Pregledi\CrystalReport1.rpt); crystalReportViewer1.SelectionFormula = {blagajna_jspturs.br_kas_izvod} = + textBox1.Text; crystalReportViewer1.ReportSource = cryRpt; cryRpt.PrintOptions.PaperOrientation = CrystalDecisions.Shared.PaperOrientation.Landscape; crystalReportViewer1.Refresh(); conn.Close(); I am new in this and I get error in the bold part of the code. So the user should chose period between two dates check for which check box is checked (two checks are possible as well) and which radiobutton is checked. Can anybody help me please?

Javeed M Shaikh · Answer

you need SQL Server 2008 and above to use Table type following is an example: create the type first: CREATE TYPE dbo.MyType AS TABLE ( col1 int NOT NULL col2 varchar(20) NULL PRIMARY KEY (col1) ); Create the stored procedure: CREATE PROC ProcName @MyTableParam MyType READONLY AS INSERT INTO MyTable([col1][col2]) SELECT [col1][col2] FROM @MyTableParam Code snippet in .NET: SqlCommand command = new SqlCommand(ProcName conn); // assuming a valid connection command.CommandType = CommandType.StoredProcedure; //Create a parameter using the new type SqlParameter param = command.Parameters.Add(@MyTableParam SqlDbType.Structured); param.Value = table; // assuming table is a datatable with 2 columns as defined above //Execute the query command.ExecuteNonQuery();

Nel · Answer

Could you please give me an example how to use abletype?