Solved

MSSQL Select one Field

Posted on 2010-08-16
7
516 Views
Last Modified: 2012-05-10
I am building a Auto-It script that connects to ms sql and returns a field for a variable. Here is my query:

SELECT TOP 1 sde_id FROM  sde.SDE_process_information
Displays:

sde_id
116648


But I would like it to just return 116648 only. How do i get results for just one field without the row name.
Thanks


Sqlserver 2008
0
Comment
Question by:CityInfoSys
7 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
Without looking at your script, it's very hard to change it.
Normally, a query would only return the value, so your script must be specifically inserting the column name.
Does it actually return as a string with embedded line break?
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
if you are running via sqlcmd try the -H parameter...alternatively  use a stored procedure and an output parameter,,,
0
 
LVL 13

Expert Comment

by:George K.
Comment Utility
Have a look at these functions


_CreateTable - create a new table in the database.
_CreateColumn - create a new column in a table in the database.
_DropCol - delete a column and its contents from a table in the database.
_DropTbl - delete a table and its contents from the database.
_CountRecords - returns the number of records in a column that match a specific search parameter.
_CountTables - returns the number of tables that are in the database.
_GetColNames - returns an array with the number of columns in the first array element and all of the column names in the specified table from the database.
_GetTblNames -returns an array with the number of tables in the first array element and all of the table names in the specified database.
_GetColVals - gets all of the values in a specified column in a specified table from the database.
_GetColCount - returns the number of columns in a specified table in the database.
_GetColType - returns the data type of a column specified in a table from the database.
_GetDBNames - returns an array with the number of databases in the first array element and all of the database names in the specified server.
_ChangeCon - changes the connection object to allow for a different username, password, server, or database.



http://dailycupoftech.com/2007/04/18/mysql-data-manipulation-with-autoit/




0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:George K.
Comment Utility
Can you paste your code?
0
 
LVL 1

Author Comment

by:CityInfoSys
Comment Utility
Here is the code I am still working on. Mainly I need to figure out the query part. Also this is MSSQL not mysql.
Thanks
;AutoIt Code



$Query = "SELECT TOP 1 sde_id FROM  sde.SDE_process_information"



Func SqlConnection()

	If $adoCn.State = $adStateOpen Then $adoCn.Close

	$adoCn.ConnectionString = $Conn

	$adoCn.Open

	If $adoRs.State = $adStateOpen Then $adoRs.Close

	$adoRs = $adoCn.Execute($Query)

	If $adoRs.State = $adStateOpen Then

		$J = $adoRs.Fields.Count - 2 

		For $K = 0 To $J

		$Output = $Output & $adoRs.Fields($K).Name & @TAB

		Next

		$Output = $Output & @CRLF

		$Output = $Output & $adoRs.GetString(2, -1, @TAB, @CRLF, "Null")

		$adoRs.Close

		GUICtrlSetData($EditResults, $Output)

	EndIf

EndFunc   

Open in new window

0
 
LVL 13

Expert Comment

by:George K.
Comment Utility
I 've had a very fast look to your code.
Maybe delete this line?:
$Output = $Output & $adoRs.Fields($K).Name & @TAB
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
Delete 4 lines otherwise you have a loop that does nothing. You also get an extra CRLF

delete
            For $K = 0 To $J
            $Output = $Output & $adoRs.Fields($K).Name & @TAB
            Next
            $Output = $Output & @CRLF

so you end up with
;AutoIt Code



$Query = "SELECT TOP 1 sde_id FROM  sde.SDE_process_information"



Func SqlConnection()

	If $adoCn.State = $adStateOpen Then $adoCn.Close

	$adoCn.ConnectionString = $Conn

	$adoCn.Open

	If $adoRs.State = $adStateOpen Then $adoRs.Close

	$adoRs = $adoCn.Execute($Query)

	If $adoRs.State = $adStateOpen Then

		$J = $adoRs.Fields.Count - 2 

		$Output = $Output & $adoRs.GetString(2, -1, @TAB, @CRLF, "Null")

		$adoRs.Close

		GUICtrlSetData($EditResults, $Output)

	EndIf

EndFunc   

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now