| Author |
Topic |
|
Daninpa
Starting Member
13 Posts |
 Posted - 2009-07-09 : 14:15:43
|
| I am trying to use a sql select command to select a field name called "Docket#" from a file on the iseries and convert it to a column in sql server 2005. Apparently # is a wildcard in sql and I can not figure out how to write the select statement without getting an error about the #. Any help would be greatly appreciated.Thanks |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 14:26:11
|
| Thanks for the response, unfortunately when I type:SELECT "DOCKET#"FROM SMPXXLIB.XXXCP50PI get this error:Error in Select clause: expression near "".Unable to parse query text.I also tried the other method you suggested and got an error also. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 14:50:40
|
| Sorry, I'm using VS2008 and sql server 2005. In VS, I connect to the iseries and use a dataset to drag/drop database tables and write Fill commands to retrieve my information. However, because this iseries table has a column named "Docket#", I can not drag/drop, so I created a generic table in my dataset and am only trying to retrieve Docket#. I have a datagridview on my form with the bindingsource set to that table in my dataset and I can use the following select statement but it simple writes "Docket#" in every single row of the datagridview:SELECT 'DOCKET#' AS Expr1FROM SMPXXLIB.XXXCP50PThe single quotes were put in automatically when I aliased it. This select statement does not give an error but like I said, it only writes "Docket#" in every row of the datagridview. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:06:02
|
| Correct, this is vb.net. I'm sure theres a way to do this, I just dont know how yet. Thanks for taking time to try and help though, much appreciated! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 15:20:41
|
Use [ and ] around column name.SELECT [DOCKET#]FROM SMPXXLIB.XXXCP50P
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:24:23
|
| When I use this:SELECT [DOCKET#]FROM SMPXXLIB.XXXCP50PI get this error:Error in select clause: expression near '['.Unable to parse text. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 15:28:26
|
I am stumped about you saying # is a wildcard character...Tried this and it worked without any column delimitercreate table #temp (docket# int)insert #temp values(2000)select docket# from #tempdrop table #temp
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:35:55
|
| I assumed it was a wildcard based on the error I was getting, maybe it is a reserved character or something. I'm not doing a create table or insert, I'm using a fill command in the tableadapter of a datatable so all I have to do is write the select statement and use vb.net to access the data. The file I am having problems with is an iseries file yet I have other iseries files (without the # in the field names) that are just fine. Every iseries file with a # in a field name will not let me build the select statement. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-09 : 15:37:09
|
| what driver are you using? client access? |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:39:53
|
| Yes I believe client access to access the iseries and oledb tableadapter. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:43:34
|
| I'm not exactly sure what you mean by that. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 15:44:04
|
 No offense, but I thought it was worth to post again, since OP didn't mention he tried it in his efforts.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:45:53
|
| I appreciate you trying to help, thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-09 : 15:50:45
|
| how are you building your select statement? i suspect a .Net syntax error. what if you echo the query?by the way "#" is not a special character in DB2.SELECT docket# should work. SELECT "docket#" should also work. Seems something else is going on |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 15:55:04
|
| I'm not sure what it means to echo the query but in terms of building my select statement, I simply right click on the tableadapter in the dataset and take the option to create a new query. At that point I use the query builder to select my table and fields that I want to select and the select statement is automatically built. The 2 examples you gave do build correctly in terms of the select statement however when I run this project, I do not get the docket numbers I am looking for...every row in the grid (remember I am only returning docket#) has "Docket#" in it, not actualy numbers. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-09 : 15:59:54
|
| try writing the query instead of letting the gui do it for you.by echoing the query, i mean print it to the screen so you/we can examine it more closely. you need to know what IS being passed to the dbms, not what you THINK is being passed |
|
|
|
Daninpa
Starting Member
13 Posts |
Posted - 2009-07-09 : 16:06:46
|
| I still got the same result: "Docket#" in every single row. I'm not sure how to tell what is being passed as I am only using a fill command, I'm not passing any parameters. |
|
|
|
Next Page
|
Convert iseries field to sql column