|
Hi all,
I'm developing an application in Visual Studio 2005 using VB.NET. Now I would like to store the variables in a SQL database. Each member of the databse has roughly 1.000 values to store. But most of them are in arrays like this:
dim results(6, 50) as string
The VB-programme generates the values and thus I thought it would be possible to declare an array in the Database declaration like Results(index1, index2) and then index1.max = 6 and index2.max = 50, something like that.
And then I could add it like this:
myCommand.Parameters.AddWithValue("@mResults", Results)
I'm concerned for how to describe the input variables in the SQL Database. As far as I could tell it seems not possible?
I'm thinking that I will still have to give each and everyone of the 300 values a description, if it will allow nulls etc.
If I still have to do it that way then I guess a loop like this will also do the work:
for i as integer = 1 to 6
for j as integer = 1 to 50
myCommand.Parameters.AddWithValue("@mResults" & i.ToString & j.ToString, Results(i, j))
next
next
An other idea is of course to save the array as a text with delimiters between the values.
Or is there any smart way that I'm unaware of to store the arrays?
Any advice would be appriciated. I see a lot of work to first declare the 1.000 values and its equivalent code and so on. Of course, I'm planning to describe the arrays as seperate tables with the identical primary key.
Best, Per
|
|
|
|
|
There are a few ways to save arrays to SQL Server, but none of them built in (that will change for SQL 2008 with table-value parameters).
It would be easier to answer your question if I knew what your target schema is. You mention separate tables and a large number of variables, so I'm picturing (based on your array declaration) that we're looking at 6 tables with 50 columns each (plus primary key??).
You can submit a delimited array to SQL Server as a varchar parameter (varchar(max) if you're using 2005) and then write a user defined function to parse it in the database (I'd recommend writing a CLR function if you're using SQL 2005 as it would be much, much faster).
Here's a link to an article I wrote on passing arrays to SQL Server. http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx[^]
As for parsing the delimited string the article only addresses arrays with a single dimension. You'll need to alter the code to handle multiple dimensions, but it can be done without too much trouble. Or you can submit the data as 6 separate queries (one for each of the major dimensions).
A method not mentioned in the article is sending XML instead of a delimited string, I can't imagine it would be faster than the methods in the article, but it's an alternative nonetheless. I know I've seen an article about it here on codeproject, but I don't remember which one.
Without more information it's difficult to be more specific.
|
|
|
|
|
Dear Mark,
Many thanks for your kind help! Great. If I make an upgrade to Visual Studio 2008 then the simple handling is included.
The situation is that data in the VB-programme are stored in 10 arrays, in 2 of them it's (6, 50) while the other 8 are single columns of 50 values each. And I can of course re-organise the data in anyway to suit the SQL system.
Thanks again for your help!
Best wishes, Per
|
|
|
|
|
I've been using SQL for several production applications, but I have to say, my knowledge level of SQL is still at the beginner level, maybe just scraping the intermediate knowledge level.
I need some help from those of you who are more experienced with SQL code.
I want to take a table with several records that have identical file numbers and merge one of the fields in the table with the other records. The consolidated record can be written into another table.
Not all the records have duplicates, and I want to just write those records to the second table also.
For example the table I would be reading from would contain the following:
FileNo FlagInfo
1234 A
1234 B
1234 C
2345 A
3456 A
3456 B
The table being written to would contain the following:
FileNo FlagInfo
1234 A,B,C
2345 C
3456 A,B
Can this be done with a stored procedure or should I just write VB code to read the file in and write out the single records?
Any suggestions? 
Lost in the vast sea of .NET
|
|
|
|
|
I can think of two possibilities:
1) PIVOT if you are using SQL Server 2005
2) Use a user defined function like this:
ALTER FUNCTION dbo.NormalizeChildren<br />
( @FileNo INT )<br />
returns VARCHAR(8000)<br />
AS<br />
BEGIN<br />
DECLARE @result VARCHAR(8000)<br />
<br />
<br />
SELECT @result = ISNULL(@result, '') + CASE WHEN @result IS NULL THEN '' ELSE ',' END + FlagNo<br />
FROM FlagTable WHERE FileNo = @FileNo<br />
<br />
RETURN @result<br />
END<br />
GO<br />
<br />
--Call the function like this<br />
SELECT FileNo, dbo.NormalizeChildren(FileNo) AS Children<br />
FROM FileTable
|
|
|
|
|
Hi,
iam doing c#(desktop application)
my database is ms-access database.
how can i save and retrive images from ms-access database.
is it possible to save images in ms-access database.
murali krishna
|
|
|
|
|
Try searching the code project articles or google. Here[^] is one article that I have used for help in the past.
Keywords that may aid your search:
blob
clob
.mdb
Hope this gets you started.
|
|
|
|
|
Hi,
iam going to develop c#(desktop application).iam searching for embedded database for visual c#.
please give me the details of what are the embedded databases available for c#.net
Thanks in Advance.
murali krishna
|
|
|
|
|
Some options:
VistaDB
SQLite
Also, I think SQL Server CE can be embedded now.
|
|
|
|
|
Hello there guys.
I have a problem with an update statement.Im have an intranet site from work that I copied and tried to upload on my home machine.The problem is when I try to login....I get this error on the update quesry.
Whenever someone logs in, the database is updated to see who logged in at what time.
I get this error "Error #214767259 Operation must use an Updateable Query"
SQL = UPDATE tbl_employees SET lastlogin = "" Where employeelogin = ""
The problem was may have been write rights to write onto the database, but the files are on my Inetput folder, so it cant be that.I checked the rights and its not that.
What else could be the problem.
Thanx in advance guys.
kagiso
|
|
|
|
|
|
How to retrive the transactions back using log files
G. Satish
|
|
|
|
|
Have you looked up RESTORE LOG in BOL?
|
|
|
|
|
I have one doubt.
Can stored procedure retun bulk data through procedure itself.
I have studied in net that using OUTPUT parameters we can get multiple values from stored proceudre...is there any other way to get the data??
G. Satish
|
|
|
|
|
There are 2 ways to return data from a SP.
1) OUTPUT params
2) SELECT statements (of which you can have multiple select statements in the same sp)
|
|
|
|
|
|
SELECT distinct u.domain,u.username, count(d.downloadid) as DocumentCount
FROM primaryusertable AS u inner join tbluseraccessinfo a on(u.userid=a.userid and a.courntryid=51 )
right join download d on(u.userid = d.userid and CDate( d.downloadutctime) <now())
group by u.username, u.domain
it is not working giving syntax error.
i could not use inner and right join together in access database
|
|
|
|
|
Do you need a space between your on and your bracket?
|
|
|
|
|
SELECT DISTINCT u.domain, u.username, count(d.downloadid) AS DocumentCount
FROM primaryusertable AS u RIGHT JOIN download AS d ON u.userid=d.userid
WHERE CDate([d.downloadutctime])>= CDate(#2/2/2008#) And CDate([d.downloadutctime])<=CDate(#2/15/2008#)
GROUP BY u.username, u.domain;
by this query i am not getting date equal including
it is only > and < no =.
|
|
|
|
|
Not sure why that doesn't work, but you could always try doing this:
WHERE (CDate([d.downloadutctime])> CDate(#2/2/2008#) And CDate([d.downloadutctime])<CDate(#2/15/2008#)) OR
CDate([d.downloadutctime])= CDate(#2/2/2008#) OR CDate([d.downloadutctime])=CDate(#2/15/2008#)
|
|
|
|
|
Hi
I m using this procedure and want to insert data in multiple table.
First it insert data in ST_STUDENT which auto generates the 'Identity' value of 'Student_Code' which i want to use in other tables for 'Student_Code' field.
I m using this line for getting the last generated 'Student_Code' but it gives me this error
'Expects Parameter '@STUDENT_CODE" WHICH WAS NOT SUPPLIED"
what is wrong in my procedure.
Kindly Tell me
alter Procedure SP_INSERT_STUDENT_COMPLETEINFO_BYAGENT
@STUDENT_CODE varchar(4),
@FIRST_NAME VARCHAR(50),
@LAST_NAME VARCHAR(50),
@EMAIL_ADDRESS VARCHAR(50),
@HOME_PHONE VARCHAR(50),
@HOMEEXT VARCHAR(10),
@OFFICE_PHONE VARCHAR(50),
@OFFICEEXT VARCHAR(50),
@MOBILE VARCHAR(50),
@HIGHEST_EDUCATION_LEVEL VARCHAR(50),
@DOB DATETime,
@COUNTRY_OF_RESIDENCE VARCHAR(50),
@COUNTRY_OF_NATIONALITY VARCHAR(50),
@US_SECURITY_NUMBER VARCHAR(50),
@GENDER VARCHAR(20),
@MARITAL_STATUS VARCHAR(20),
@ETHNIC_GROUP VARCHAR(20),
AS
INSERT INTO ST_STUDENT(FIRST_NAME,LAST_NAME,EMAIL_ADDRESS,HOME_PHONE,HOMEEXT,OFFICE_PHONE,OFFICEEXT,MOBILE)
VALUES(@FIRST_NAME,@LAST_NAME,@EMAIL_ADDRESS,@HOME_PHONE,@HOMEEXT,@OFFICE_PHONE,@OFFICEEXT,@MOBILE)
SET @STUDENT_CODE =(SELECT @@IDENTITY)
Insert into ST_STUDENTBIODATA(HIGHEST_EDUCATION_LEVEL,DOB,COUNTRY_OF_RESIDENCE,COUNTRY_OF_NATIONALITY,
US_SECURITY_NUMBER,GENDER,MARITAL_STATUS,ETHNIC_GROUP,STUDENT_CODE)
VALUES(@HIGHEST_EDUCATION_LEVEL,@DOB,@COUNTRY_OF_RESIDENCE,@COUNTRY_OF_NATIONALITY,@US_SECURITY_NUMBER,@GENDER,@MARITAL_STATUS,
@ETHNIC_GROUP,@STUDENT_CODE)
GO
Regards
|
|
|
|
|
You have a comma just before your AS.
|
|
|
|
|
has anyone here used DB2Connect to call a DB2 stored procedure. I'm having trouble registering the input/output parameters.
here is my code
Dim conn As New DB2Connection("DATABASE=DB2DSST;UID=c132;PWD=*******;")
conn.Open()
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = conn.CreateCommand()
Dim procName As String = "MG1006SP"
Dim procCall As String = "CALL MG1006SP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
cmd.Transaction = trans
cmd.CommandType = CommandType.Text
cmd.CommandText = procCall
' Register input-output and output parameters for the DB2Command
cmd.Parameters.Add("DB2_ID", "DSST")
cmd.Parameters.Add("GRP_ID", "19 ")
cmd.Parameters.Add("LN1_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("LN2_ADDR", DB2Type.Char, 55)
cmd.Parameters.Add("CITY_NM", DB2Type.Char, 30)
cmd.Parameters.Add("ST_CD", DB2Type.Char, 2)
cmd.Parameters.Add("ZIP5_CD", DB2Type.Char, 5)
cmd.Parameters.Add("ZIP4_CD", DB2Type.Char, 4)
cmd.Parameters.Add("INFO_CHG_DT", DB2Type.Date, 4)
cmd.Parameters.Add("AIBRETRN", DB2Type.Integer, 4)
cmd.Parameters.Add("AIBREASN", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLCODE", DB2Type.Integer, 4)
cmd.Parameters.Add("SQLSTATE", DB2Type.Char, 5)
cmd.Parameters.Add("SQLMESSAGE", DB2Type.VarChar, 1320)
cmd.Parameters.Add("APPL_RTN_CD", DB2Type.Char, 5)
cmd.Parameters.Add("APPL_MESSAGE", DB2Type.VarChar, 500)
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
|
|
|
|
|
We use DataDirect Connect for .NET[^] to connect to DB2.
We simply set the CommandText property to the name of the stored procedure and set CommandType to CommandType.StoredProcedure.
For an example of using IBM's data provider - which appears to use the same scheme - see the SpClient.vb sample[^].
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
HI,
Can any one suggest me how to do the following.
I am having a two table, table1,table 2. table 2 fkey depends on table 1 primary key(auto generated). My doubt is if i copy a row with in table1, a new primary key is generated,how to retrive this new auto generated primary key, because using this new primary key i have to copy the rows with in table 2. for eg:
table 1:
id name
1 aaa
2 aaa
table 2:
id fid age
1 1 23
2 2 23
please suggest me
thanks in advance
Know is Drop, Unknown is Ocean
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
