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("@param1","myvalue"); 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("@gbr1","1020" ); 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("@ab",2); } 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(dataSet2,"NOVI"); 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?

Command.Parameters.AddWithValue having two possible values

Answers (7)