[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

ASP.NET TableAdapter using SQL IN statement.... Won't work for me

Hello!

I'm using a table adapter in ASP.NET and I'm trying to do an IN statement in SQL. Example:

SELECT * FROM table
WHERE tableColumnKey IN (@Ids)

When I attempt to pass an array of ints to the @Ids parameter it chokes and says it was only expecting a single int.  What's the trick/step I'm missing?  Thanks for any help you can offer!!
0
siteMaker
Asked:
siteMaker
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
DhaestCommented:
How is @Ids declared ?
0
 
Gautham JanardhanCommented:
instead of array u need to make it in the format
1,2,3,4 and then add it as the value
0
 
DhaestCommented:
If you are using an Array, you can try Array.ToString()

But I guess you need to declare the @lds-parameter as a string and not as an integer
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
siteMakerAuthor Commented:
Basically the way I'm declaring it right now is:

int ids[] = {1,2,3,4};
 then passing that in.  How should I be declaring it? What's the syntax you are suggesting?
0
 
DhaestCommented:
>> Basically the way I'm declaring it right now is:

Assign this as value of your parameter: ids.ToString()

and declare your @lds-parameter as a string
0
 
siteMakerAuthor Commented:
Ok, so here's the code snippet.... This might shed some light on the subject:

int ids[] = {1,2,3,4};
MyTableAdapters.ASingleTableAdapter myAdapter = new MyTableAdapters.ASingleTableAdapter();
myAdapter.GetRowsById(ids);

The GetRowsById is asking for a single int.  The wuery that lives in it is:
SELECT * FROM table
WHERE tableColumnKey IN (@Ids)

So I'm honestly unsure how to pass anything but an int.  The type it is requesting is an int and I get an error if I attempt to pass anything else.
0
 
DhaestCommented:
You'll have to change your GetRowsById-function
public void GetRowsById(ids as string)
0
 
siteMakerAuthor Commented:
This is a TableAdapter though.... Implemented through a dataset.  So I don't have direct access to a typical programmatic interface like that.  I have access to the .xsd file that contains the xml for the table adapter which has a parameter in it that looks like this:

<Parameter AllowDbNull="False" AutogeneratedName="FolderLinkId" ColumnName="" DataSourceName="" DataTypeServer="unknown" DbType="Int32" Direction="Input" ParameterName="@FolderLinkId" Precision="0" Scale="0" Size="0" SourceColumn="" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>

So I'm unsure of what in this would need to be changed to make that work.
0
 
Gautham JanardhanCommented:
int []ids = new int []{1,2,3,4};
                  string str = "";
                  for(int i=0;i<ids.Length;i++)
{
str += ids[i].ToString()+",";
                  }
                  str = str.Substring(0,str.Length-1);
0
 
DhaestCommented:
If the GetRowsById-function only accepts one integer-value, then I don't think you can give multiple id's at once
0
 
Gautham JanardhanCommented:
s u will have to change the parameter of that function to accept string if u want to query the database as expected
0
 
riyazthadCommented:
One method is goto ur dataset.Designer.cs file and locate your Fill method, change
this.Adapter.SelectCommand = this.CommandCollection[1]; method to ur own method like

this.Adapter.SelectCommand = new sqlCommand("select * from table where id IN" + urpaprameter,_Connection)

where _Connetion will be same connetion other method using.

If you cannot debug that method, u need to comment DebuggerNonUserCodeAttribute attribute.

Why int error is bcos, if ur field type is string , u should pass string, if it is int u should pass int. so "1,2,3" wont work here.


0
 
siteMakerAuthor Commented:
So the answer is...... You cannot have an IN statement in a table adapter. The real solution is to use a stored proc.

Here's an example of the one I have working:
CREATE [dbo].[usp_procname]
@IdString VARCHAR(100)
AS

DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT * FROM table WHERE tableKey IN (' + @IdString + ')'

EXEC (@SQL)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now