|
Hi all thanks for reading. I am kinda new to stored procedures and need a bit of help. I am calling a stored procedure which I send 2 parameters, lets say @first_name and @column_name.
I am using a
SELECT * FROM members WHERE @column_name = @first_name
but when I run the procedure I get no results.
Is this valid? MS VS.net allows it and runs the procedure but I get no return?
if I alter the procedure to say
SELECT * FROM members WHERE first_name = @first_name
I get the desired results
Here is my stored preocedure
ALTER Procedure DBSearch (@first_name varchar(40), @column_name varchar(40))
as
select * from members where @column_name = @first_name;
RETURN
Thanks in advance for any help
BigJRags
|
|
|
|
|
Try this:
declare @sql varchar(500)
set @sql='SELECT * from members WHERE ' + @column_name + ' = ' + @first_name
exec(@sql)
<italic>Work hard, Work effectively.
|
|
|
|
|
That looks good to me but it's not working???
|
|
|
|
|
Why is it not working? Try
select @sql
and run the query manually.
<italic>Work hard, Work effectively.
|
|
|
|
|
I wish I knew, here is the Procedure and the Output, when I right click and select Run Stored Procedure, I use Jeremy as @param1 and first_name as @param2
I am stumped...Any clues?????????????????/
ALTER Procedure DBSearch (@param1 varchar(40), @param2 varchar(40))
as
declare @sql varchar(500)
set @sql='SELECT * from members WHERE ' + @param2 + ' = ' + @param1
exec (@sql)
RETURN
Running dbo."DBSearch" ( @param1 = Jeremy, @param2 = first_name ).
Invalid column name 'Jeremy'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."DBSearch".
|
|
|
|
|
Edberg was right, see below.
<italic>Work hard, Work effectively.
|
|
|
|
|
I think with that one you need to add apostrophe (') as the column expects character value.
Try replacing the set @sql with this:
set @sql='SELECT * from members WHERE ' + @column_name + ' = ''' + @first_name + ''''
|
|
|
|
|
That did the trick, thanks a million
|
|
|
|
|
You can't use a parameter for a field name...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
Hello all,
I am enhancing a DTS package developed by someone else. Main purpose of the DTS package is to import the data from the input excel sheets received from multiple customers.There are 7 data pump tasks to import from 7 customers' excel sheets. Each pump task is called in a loop in an ActiveX Script to be executed.
In ActiveX Script following logic is used to execute the pump task:
set stpraj = pkg.Steps("DTSStep_DTSDataPumpTask_2")
stpraj.ExecutionStatus = DTSStepExecStat_Waiting
I added a new data pump task to import the data from a new customer and I used the same logic as above:
set stpram = pkg.Steps("DTSStep_DTSDataPumpTask_8")
stpram.ExecutionStatus = DTSStepExecStat_Waiting
when I executed the package it gave execution errors. I replaced the code stpram.ExecutionStatus = DTSStepExecStat_Waiting
with
stpram.execute.
Now the package is executing properly and populating the data, but some of the things I did not understand are:
1. Why the package gave execution erroers when I used "stpram.ExecutionStatus = DTSStepExecStat_Waiting" ?
2. In the results after the execution of the package it showed the status for
"stpraj.ExecutionStatus = DTSStepExecStat_Waiting" as "COmplete" and for "stpram.execute" it showed "Running".
What is the difference between "stpraj.ExecutionStatus = DTSStepExecStat_Waiting" and "stpram.execute"? which one is preferable?.
Any information and suggestions on above doubts please!
Thanks in advance.
|
|
|
|
|
How do I bring up the ODBC Configuration dialog box and retrieve the paramters from VB . Net?
JRM
|
|
|
|
|
Hello Guys,
I am trying to connect to the remote SQL server on a machine named blrkec15710a. I want to do this using ODBC connection. But after writing my code i get the error
An unhandled exception of type 'System.TypeLoadException' occurred in System.Windows.Forms.dll
I am attaching my code below and making BOLD the line because of which the error creeps in. CODE IS AS FOLLOWS:-
_________________________________________________________________________
Imports Microsoft.data.odbc<br />
Imports System.Data<br />
Imports System.Data.SqlServerCe<br />
<br />
Public Class Form1<br />
Inherits System.Windows.Forms.Form<br />
Friend WithEvents txtDisplay As System.Windows.Forms.TextBox<br />
Friend WithEvents btnGET As System.Windows.Forms.Button<br />
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu<br />
<br />
[+] #Region " Windows Form Designer generated code "<br />
<br />
<br />
Private Sub btnGET_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGET.Click<br />
<br />
Try 'Driver={MsSQLSERVER};<br />
txtDisplay.Text = "Hello Zalak..."<br />
<br />
Dim strConnection As String = New String("DRIVER = {SQL Server};SERVER=blrkec15710A;UID=sa;PWD=SysAdmin;DATABASE=syncDS;")<br />
<br />
txtDisplay.Text = "after writing the connection string.."<br />
Dim SQL As String = New String("SELECT * FROM Orders")<br />
txtDisplay.Text = "after writing the SQL query"<br />
<br />
' Create connection object ---- PROBLEM IS HERE<br />
<br />
Dim conn As OdbcConnection = New OdbcConnection(strConnection) <br />
<br />
txtDisplay.Text = "create odbcConnection object.."<br />
<br />
Catch ex As Exception<br />
<br />
End Try<br />
<br />
End Sub<br />
End Class
_________________________________________________________________________
Now the error creeps in when i try to create an ODBCConnection object with the connection string as the parameter.
So most likely the error lies in the connection string.
My connection string:-
Dim strConnection As String = New String("DRIVER = {SQL Server};SERVER=blrkec15710A;UID=sa;PWD=SysAdmin;DATABASE=syncDS;")
here,
driver = SQL SERVER or should it be "'Microsoft SQL Server OBDC Driver Version 03.81.9042" that i see when i am trying to make the DSN and do TEST SOURCE that time i see this driver name in the summary.
server = blrkec15710a (my remote machine name where the SQL Server resides)
database = northwind or the name of the DSN made here "syncDS" which points to the Northwind database on server blrkec15710a
UID and PWD should they be in "" "" or as it is?
I need a solution to this immediately, whether the problem is with the connectionstring attributes or something else?
also where has the DSN to be made on the desktop where i am writing my application or the desktop where the SQL Server resides?
And when should the DRIVER attribute be blank, and if it blank then how does the connection string look like?
I will be really grateful to u people if i can get a response to this as soon as possible!!!
Thanks in advance,
Zalak dedhia
|
|
|
|
|
Create a DSN in the computer and use it as ODBC.
Al
|
|
|
|
|
I have the following problem when I output a select as XML. What happens, is that the query does not append line breaks to each record, so you are stuck with something that when you view in IE, it reports an error. This is because IE is just chopping the row to start it on the next line.
I will try explain it another way now. If there is a varchar(4000) buffer, what happens when the buffer is full, is that it adds that as a new row, then continues filling itself again. So what you end up with is records that get truncated by the viewing program (eg Internet Explorer)
|
|
|
|
|
hi
im using SqlDmo.dll to attach Database to MSDE and it gives me the following error msg
[microsoft][ODBC SQL Server Driver][SQL Server]The LSN(671:291:1)passed to log scan in database 'TelecontrollerPlus'is invalid
TelecontrollerPlus : is DB file name
and the code is
Dim SqlServ As New SQLDMO.SQLServer()
SqlServ.LoginSecure = True
SqlServ.Connect(SqlNAme)
OpenFileDialog1.ShowDialog()
Dim NAmes As String = OpenFileDialog1.FileName
If NAmes <> "" Then SqlServ.AttachDB("TelecontrollerPlus", "[" & NAmes & "]") : MsgBox("Attach DB is Done")
any one know aht is this error
thank u 
|
|
|
|
|
from your database folder remove the .LDF (SQL server Database log file)
and then try to attach the database file to your server
At the end its all will be OK
|
|
|
|
|
HI all,
I have a table say table1 in which i have column1,column2,column3
I have a variable var1.
On the basis of value in var1 i wana fetch the data.
Like
if var1=1 then I wana select value from column1
if var1=2 then I wana select value from column2
can anyone please suggest the sqlserver2000 query for this.
thanks
|
|
|
|
|
|
As a college project we are making a database system, which we have planned to give to a school. we are using MySql server now. The question is this, is there a way to implement this project so that we wont have to install mysql server in the schools computer? we dont mind changing the database server or any thing for that matter. please help......
|
|
|
|
|
use ms access
|
|
|
|
|
I selected one Row from a Database and stored it in a DataTable. See code:
If ViewState("DataPeuter") Is Nothing Then
Dim sqlPConnection As SqlConnection
Dim sqlPDataAdaptor As SqlDataAdapter
Dim sqlPCommand As SqlCommand
Dim sqlPConnectionString As String
Dim sqlPSelectString As String
Try
sqlPConnectionString = "server=localhost;uid=sa;pwd=;database=KiddinSys"
sqlPSelectString = "Select * From Peuters Where Peuternummer = " & pNum
sqlPConnection = New SqlConnection(sqlPConnectionString)
sqlPCommand = New SqlCommand(sqlPSelectString, sqlPConnection)
sqlPDataAdaptor = New SqlDataAdapter(sqlPCommand)
dataTablePeuter = New DataTable
sqlPDataAdaptor.Fill(dataTablePeuter)
ViewState("DataPeuter") = dataTablePeuter
Catch exception As Exception
ErrorLabel.Text = exception.ToString()
End Try
Else
dataTablePeuter = CType(ViewState("DataPeuter"), DataTable)
End If
In the dataTablePeuter there is a Column named Geslacht as a Integer. What i would like to do is to convert this Integer to a String like this:
if dataTablePeuter.Geslacht = 1 Then
lblGeslacht.Text = "Jongen"
ElseIf dataTablePeuter.Geslacht = 2 Then
lblGeslacht.Text = "Meisje"
Else
lblGeslacht.Text = "Onbekend"
EndIf
The question is what is the correct syntaxs for if dataTablePeuter.Geslacht = 1 Then
|
|
|
|
|
Hello,
I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary records in it when testing it. I get the error "No value given for one or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
Thanks,
Miguel
These are my 3 codes:
UPDATE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id, [title_pt-PT]=@title_pt-PT, [title_en-GB]=@title_en-GB, [text_pt-PT]=@text_pt-PT, [text_en-GB]=@text_en-GB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id", 1))
.Add(New OleDbParameter("@title_pt-PT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_en-GB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_pt-PT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_en-GB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
INSERT
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "INSERT INTO [t_news] ([title_pt-PT], [title_en-GB], [text_pt-PT], [text_en-GB], [publication_date]) VALUES (@title_ptPT, @title_enGB, @text_ptPT, @text_enGB, @publication_date)"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@title_ptPT", "Test INSERT - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test INSERT - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test INSERT - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test INSERT - text EN"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
' Insert New Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
DELETE
' Set Connection
Dim connectionString As String = AppSettings("connectionString")
Dim dbConnection As IDbConnection = New OleDbConnection(connectionString)
' Set Query and Command
Dim queryString As String = "DELETE FROM [t_news] WHERE ([t_news].[news_id] = @news_id)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
' Add Parameters
dbCommand.Parameters.Add(New OleDbParameter("@news_id", 2))
' Delete Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
End Try
|
|
|
|
|
I believe oledb parameters are 'placement' only parameters (that is, the name of the parameter is irrelevant, only its order in the statement matters) In this case you have one more parameter (placement wise) in the statement than you are adding to the parameter collection (you can't get away with 'sharing' the news_id parameter just by name matching, you must add another parameter using the same value, but a different name - the names must be unique to satisfy the collection rules, but serve no role in terms of matching parameters in the statement). I suspect you dont really want to CHANGE the value of news_id anyway, so just remove it from your filed list, and move the parameter to last in the colection.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
Hi,
I did that:
' Set Query and Command
Dim queryString As String = "UPDATE [t_news] SET [news_id]=@news_id_update, [title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"
...
' Add Parameters
With dbCommand.Parameters
.Add(New OleDbParameter("@news_id_update", 5))
.Add(New OleDbParameter("@news_id", 5))
.Add(New OleDbParameter("@title_ptPT", "Test UPDATE - title PT"))
.Add(New OleDbParameter("@title_enGB", "Test UPDATE - title EN"))
.Add(New OleDbParameter("@text_ptPT", "Test UPDATE - text PT"))
.Add(New OleDbParameter("@text_enGB", "Test UPDATE - text EN"))
.Add(New OleDbParameter("@publication_date", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")))
End With
I get the error: "Cannot update 'news_id'; field not updateable."
Remember that news_id is the primary key on my Access database and set as "AutoNumber"
Then I tried the following query as i don't need to change the news_id field:
Dim queryString As String = "UPDATE [t_news] SET [title_pt-PT]=@title_ptPT, [title_en-GB]=@title_enGB, [text_pt-PT]=@text_ptPT, [text_en-GB]=@text_enGB, [publication_date]=@publication_date WHERE ([t_news].[news_id] = @news_id)"
I don't get any error but there is no change in data!!!! And record 5 is there!
I even placed a Response.Write("Done") in the code:
' Update Record
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
Finally
dbConnection.Close()
Response.Write("Done")
End Try
And it is displayed "DONE"!
My INSERT and DELETE codes are working fine.
What is going on with this UPDATE code?
Thanks,
Miguel
|
|
|
|
|
You missed my most important point. The parameters are PLACEMENT (i.e. ORDER) sensitive. You must add the parameters to the collection in the same order that you used them in the statement. You get no update because no news_id value in the table matches the publication date (politly converted to a long integer). news_id MUST be the last parameter added to the collection. To be sure that the ADD method overload is not doing some polite conversion behind your back, you should specify the data type and direction for the parameters, as well, rather than rely on the parameter constructor to determine them for you.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
