asked on

Connecting to a SQL server using VB script

Greetings:

The following script attempts to connect to a database to obtain a 'text' datatype from a stored procedure:

If WScript.Arguments.Count <> 1 Then
      WScript.Echo "Usage: cscript " & WScript.ScriptName & " <Range_ID>"

Else
      Dim SqlConn
      Dim SqlCmd
      Dim Retval
      Dim Records
const adParamInput = 1
      const adSmallInt = 2

      SpInput = WScript.Arguments.Item(0)

      Set mRecordset = CreateObject("ADODB.Recordset")
      Set SqlConn = CreateObject("ADODB.Connection")
      Set SqlCmd = CreateObject("ADODB.Command")
      SqlConn.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=db","user","**"

      If (Err.Number <> 0) Then
WScript.Echo "Unable to connect to database"
WScript.Quit
End If

With SqlCmd
.ActiveConnection = SqlConn
       .CommandText = "SP_GetXMLData" & SpInput
       .CommandType = 4
       .CreateParameter "@MyParam",adSmallInt,adParamInput, 0
      End With

      Set Records = SqlCmd.Execute

      Records.MoveFirst
Do Until Records.EOF
'add results to a file
Loop

      Set SqlCmd = Nothing
      Set SqlConn = Nothing
End If

I need help with these things:

1) the line:
Set Records = SqlCmd.Execute

I get the error:
Could not find stored procedure 'SP_GetXMLData'
Microsoft OLE DB Provider for SQL Server

Since the connection is good there shouldn't be a problem finding the stored procedure. What might this be?

Also, how can I save the results of the record set to a file?

Thanks!

RobSampson

Hi, I have a feeling your problem is due to this line:
.CommandText = "SP_GetXMLData" & SpInput

Let's assume you pass a parameter to the script of 53

Then you'd be trying to find a stored procedure called SP_GetXMLData53

So, I think if you just use
.CommandText = "SP_GetXMLData"

and have the CreateParameter line take care of passing the input, you'd be OK.

Regards,

Rob.

If WScript.Arguments.Count <> 1 Then
	WScript.Echo "Usage: cscript " & WScript.ScriptName & " <Range_ID>"
Else
	Dim SqlConn
	Dim SqlCmd
	Dim Retval
	Dim Records 
	const adParamInput = 1
	const adSmallInt = 2
	Const adCmdStoredProc = 4
	
	
	SpInput = WScript.Arguments.Item(0)
	
	Set mRecordset = CreateObject("ADODB.Recordset")
	Set SqlConn = CreateObject("ADODB.Connection")
	Set SqlCmd = CreateObject("ADODB.Command")
	SqlConn.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=db","user","**"
	
	If (Err.Number <> 0) Then
		WScript.Echo "Unable to connect to database"
		WScript.Quit
	End If
 
	With SqlCmd
		.ActiveConnection = SqlConn  
		.CommandType = adCmdStoredProc
		.CommandText = "SP_GetXMLData" & SpInput
		.Parameters.Append.CreateParameter "@RangeID",adSmallInt,adParamInput, 0
	End With         
 
	Set Records = SqlCmd.Execute 
 
	Records.MoveFirst
	strResults = ""
	Do Until Records.EOF
		'add results to a file
		If strResults <> "" strResults = strResults & vbCrLf
		For intField = 0 To Records.Fields.Count -1
			If intField = 0 Then
				strResults = strResults & Records.Fields(intField).Value
			Else
				strResults = strResults & "," & Records.Fields(intField).Value
			End If
		Next
		Records.MoveNext
	Loop
 
	MsgBox strResults
	
	Set SqlCmd  = Nothing
	Set SqlConn = Nothing
End If

Open in new window

John500

ASKER

Rob,

Really appreciate the help.

The compiler is complaining about line 38 needing a 'Then'

You would think I could do that much but it's not taking.

What do you think?

RobSampson

Oh whoops, Change this
If strResults <> "" strResults = strResults & vbCrLf

to this
If strResults <> "" Then strResults = strResults & vbCrLf

Rob.

John500

ASKER

Thanks - I thought that line might be sort of a double IF ....

Anyway, I'm getting the following error:

SP_GetXMLData' expects parameter '@RangeID', which was not supplied

For this line:

Set Records = SqlCmd.Execute

If I execute this procedure in the database it runs fine with either:

EXEC SP_GetXMLData 1
EXEC SP_GetXMLData NULL

Any ideas?

John500

ASKER

... I forgot to mention that I couldn't use the Append statement without the VS 2005 debugger complaining.

Hence I used:

.CreateParameter "@RangeID",adSmallInt,adParamInput, 0

Rather than

.Parameters.Append.CreateParameter "@RangeID",adSmallInt,adParamInput, 0

In either of our approaches the incoming parameter called 'SpInput' isn't being used right?

I want to use the VS debugger so that I can get into the code and see what's going on. Howeve, I've noticed it isn't as friendly.............

If I use the following statement:

.CreateParameter "@RangeID",adSmallInt,adParamInput, 0, SpInput

I get the same exact error

??

John500

ASKER

Also, I tried replacing the line I'm using with the line you suggested

.Parameters.Append.CreateParameter "@RangeID",adSmallInt,adParamInput, 0, SpInput

But I got the error message below:

parameter-error.JPG

John500

ASKER

Sorry for all the comments but I think I can nail this if can map 'CreateParameter' correctly. It requires the following:

Name, Type, Direction, Size, Value

I'm supplying:

"@RangeID"
const adSmallInt = 2
adParamInput = 1
?????
SpInput

What do I need for that size value?

Thanks

RobSampson

What about this?

This line
.Parameters.Append.CreateParameter("@RangeID",adSmallInt,adParamInput, 255, SpInput)

may need to be
.CreateParameter("@RangeID",adSmallInt,adParamInput, 255, SpInput)

Rob.

If WScript.Arguments.Count <> 1 Then
	WScript.Echo "Usage: cscript " & WScript.ScriptName & " <Range_ID>"
Else
	Dim SqlConn
	Dim SqlCmd
	Dim Retval
	Dim Records 
	const adParamInput = 1
	const adSmallInt = 2
	Const adCmdStoredProc = 4
	
	
	SpInput = WScript.Arguments.Item(0)
	
	Set mRecordset = CreateObject("ADODB.Recordset")
	Set SqlConn = CreateObject("ADODB.Connection")
	Set SqlCmd = CreateObject("ADODB.Command")
	SqlConn.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=db","user","**"
	
	If (Err.Number <> 0) Then
		WScript.Echo "Unable to connect to database"
		WScript.Quit
	End If
 
	With SqlCmd
		.ActiveConnection = SqlConn  
		.CommandType = adCmdStoredProc
		.CommandText = "SP_GetXMLData"
		.Parameters.Append.CreateParameter("@RangeID",adSmallInt,adParamInput, 255, SpInput)
	End With         
 
	Set Records = SqlCmd.Execute 
 
	Records.MoveFirst
	strResults = ""
	Do Until Records.EOF
		'add results to a file
		If strResults <> "" Then strResults = strResults & vbCrLf
		For intField = 0 To Records.Fields.Count -1
			If intField = 0 Then
				strResults = strResults & Records.Fields(intField).Value
			Else
				strResults = strResults & "," & Records.Fields(intField).Value
			End If
		Next
		Records.MoveNext
	Loop
 
	MsgBox strResults
	
	Set SqlCmd  = Nothing
	Set SqlConn = Nothing
End If

Open in new window

RobSampson

You could also try leaving the size Null...

.Parameters.Append.CreateParameter("@RangeID",adSmallInt,adParamInput, , SpInput)

Rob.

John500

ASKER

believe it or not, its the same:

If I run it in the debugger with '.CreateParameter' using either 255 or NULL I get:

SP_GetXMLData' expects parameter '@RangeID', which was not supplied

If I don't use the debugger and include '.Parameters.Append.CreateParameter' I get the same error in the picture (with 255 or NULL)

RobSampson

OK, as you can tell, I'm not good with Stored Parameters...I've never used them, so I'm flying a bit blind....I have now tried to use some code from here:
http://msdn.microsoft.com/en-us/library/ms675860(VS.85).aspx

Regards,

Rob.

If WScript.Arguments.Count <> 1 Then
	WScript.Echo "Usage: cscript " & WScript.ScriptName & " <Range_ID>"
Else
	Dim SqlConn
	Dim SqlCmd
	Dim Retval
	Dim Records 
	const adParamInput = 1
	const adSmallInt = 2
	Const adCmdStoredProc = 4
	
	
	SpInput = WScript.Arguments.Item(0)
	
	Set mRecordset = CreateObject("ADODB.Recordset")
	Set SqlConn = CreateObject("ADODB.Connection")
	Set SqlCmd = CreateObject("ADODB.Command")
	SqlConn.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=db","user","**"
	
	If (Err.Number <> 0) Then
		WScript.Echo "Unable to connect to database"
		WScript.Quit
	End If
 
	With SqlCmd
		.ActiveConnection = SqlConn  
		.CommandType = adCmdStoredProc
		.CommandText = "SP_GetXMLData"
		Set prmRangeID = .CreateParameter("@RangeID",adSmallInt,adParamInput)
		.Parameters.Append prmRangeID
		prmRangeID.Value = spInput
	End With         
 
	Set Records = SqlCmd.Execute 
 
	Records.MoveFirst
	strResults = ""
	Do Until Records.EOF
		'add results to a file
		If strResults <> "" Then strResults = strResults & vbCrLf
		For intField = 0 To Records.Fields.Count -1
			If intField = 0 Then
				strResults = strResults & Records.Fields(intField).Value
			Else
				strResults = strResults & "," & Records.Fields(intField).Value
			End If
		Next
		Records.MoveNext
	Loop
 
	MsgBox strResults
	
	Set SqlCmd  = Nothing
	Set SqlConn = Nothing
End If

Open in new window

John500

ASKER

>> ...I've never used them, so I'm flying a bit blind

You can be sure I'm not complaining. I've worked quite a bit with VB and ADO but not in VB scripts. I can't believe this has been so difficult.

I'm getting the following error for line 36 if you're still interested:

Records.MoveFirst

Thanks!
parameter-error2.JPG

RobSampson

Hey well that's progress!

Try this...

Regards,

Rob.

If WScript.Arguments.Count <> 1 Then
	WScript.Echo "Usage: cscript " & WScript.ScriptName & " <Range_ID>"
Else
	Dim SqlConn
	Dim SqlCmd
	Dim Retval
	Dim Records 
	const adParamInput = 1
	const adSmallInt = 2
	Const adCmdStoredProc = 4
	
	
	SpInput = WScript.Arguments.Item(0)
	
	Set mRecordset = CreateObject("ADODB.Recordset")
	Set SqlConn = CreateObject("ADODB.Connection")
	Set SqlCmd = CreateObject("ADODB.Command")
	SqlConn.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=db","user","**"
	
	If (Err.Number <> 0) Then
		WScript.Echo "Unable to connect to database"
		WScript.Quit
	End If
 
	With SqlCmd
		.ActiveConnection = SqlConn  
		.CommandType = adCmdStoredProc
		.CommandText = "SP_GetXMLData"
		Set prmRangeID = .CreateParameter("@RangeID",adSmallInt,adParamInput)
		.Parameters.Append prmRangeID
		prmRangeID.Value = spInput
	End With         
 
	Set Records = SqlCmd.Execute 
 
	If Records.EOF Then MsgBox "No Records returned."
		strResults = ""
	Else
		Do Until Records.EOF
			'add results to a file
			If strResults <> "" Then strResults = strResults & vbCrLf
			For intField = 0 To Records.Fields.Count -1
				If intField = 0 Then
					strResults = strResults & Records.Fields(intField).Value
				Else
					strResults = strResults & "," & Records.Fields(intField).Value
				End If
			Next
			Records.MoveNext
		Loop
 	End If
	MsgBox strResults
	
	Set SqlCmd  = Nothing
	Set SqlConn = Nothing
End If

Open in new window

RobSampson

Also, try changing this line
Set mRecordset = CreateObject("ADODB.Recordset")

to this
Set Records = CreateObject("ADODB.Recordset")

Rob.

John500

ASKER

I hate to say it but it's the exact error as last picture only for the next line down:

If Records.EOF Then ...

I was really feeling like a fool when I saw mRecordset but as it turns out I guess Variance took effect because the error didn't change when I fixed that.

I'm thinking the connection is closed when the 'With SqlCmd' is interrupted by the Set prm RangeID statement.

Maybe not but we probably both need a break ...

RobSampson

Can you do me a favor and above this line:
If Records.EOF Then MsgBox "No Records returned."

put this

MsgBox TypeName(Records)

to see that the object type is....

Rob.

John500

ASKER

I thought surely you had called it a day.

I did as you stated and received the following:

You might be interested in this link which as a boat load of examples. My faith is waivering though:

http://www.voicequide.com/vghelp/source/html/modvbs.htm
parameter-error3.JPG

John500

ASKER

That link doesn't seem to be working. How about this:

http://www.voiceguide.com/vghelp/source/html/modvbs.htm

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RobSampson

This may also help.
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0e78-errors.html

Regards,

Rob.

John500

ASKER

Rob,

That was the ticket my friend - SET NOCOUNT ON !!

I should have know it too. I've been out of the saddle too long. I tried the other stuff first but when I changed the NOCOUNT it came through nice.

Am I pushing it in regard to the file writing? Here's what I know:

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
set fso = CreateObject("Scripting.FileSystemObject")
set tsFile = fso.OpenTextFile("C:\LogCalls.txt", ForAppending, True)
tsFile.WriteLine ??????????
tsFile.Close
set tsFile = Nothing
set fso = Nothing

How the RecordSet is assigned to 'tsFile' and what comes after WriteLine are my questions. I'll open this up on another question.

Thanks!

https://www.experts-exchange.com/questions/23481759/Writing-record-sets-out-to-file.html

John500

ASKER

Thanks man

RobSampson

Hey John! Wow, I'm glad we got there! So I wonder how much of the code (or which version) was right? You may have had it right from the start!

Anyway, that's cool. I'll see you in the next question.

Rob.